Mariadb ENUM default not used

I have a weird case of default values gone bad.

A client creates sites via the ispconfig API but when later enables php for the site via the ispconfig webinterface the php fpm server fails to restart.

systemd[1]: Starting The PHP 7.4 FastCGI Process Manager...
php-fpm7.4[1047134]: ERROR: [/etc/php/7.4/fpm/pool.d/web35.conf:9] unable to parse value for entry 'pm': invalid process manager (static, dynamic or ondemand)
php-fpm7.4[1047134]: ERROR: Unable to include /etc/php/7.4/fpm/pool.d/web35.conf from /etc/php/7.4/fpm/php-fpm.conf at line 9
php-fpm7.4[1047134]: ERROR: failed to load configuration file '/etc/php/7.4/fpm/php-fpm.conf'
php-fpm7.4[1047134]: ERROR: FPM initialization failed

The pm options is empty so php-fpm is right to fail. But in the database the field is also empty which should be impossible ... The column is defined as:

`pm` enum('static','dynamic','ondemand') NOT NULL DEFAULT 'ondemand',

This server is running Debian 11 with MariaDB 10.5.12-0+deb11u1 (Confirmed also on Debian 10 with MariaDB 10.3.31-0+deb10u1)

CREATE TABLE `enum_test` (
  `domain_id` int(11) UNSIGNED NOT NULL,
  `pm` enum('static','dynamic','ondemand') NOT NULL DEFAULT 'ondemand'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `enum_test` (`domain_id`, `pm`) VALUES ('1', '');

SELECT * FROM `enum_test`;

The select above will show an empty value for pm.

On MySQL 8.0.27-0ubuntu0.20.04.1 the insert query gives an error (as it should).

ERROR 1265 (01000): Data truncated for column 'pm' at row 1

I'm not the first to run into this ... https://stackoverflow.com/questions/63392899/how-to-make-mariadb-produce-an-error-if-value-not-specified-on-not-null-enum-col And more details on https://mariadb.com/kb/en/enum/

The solution to getting an error is setting the Mysql config option sql-mode to include STRICT_ALL_TABLES. But that does not solve my issue with the API client. Unfortunately I do not have access to the client's source as it's compiled/obfuscated 😞 by https://www.hostfact.nl/ But as it's an optional field I cannot blame them for leaving it out.

The patch below seems to solve it. It calls 'default(pm)' to let the mysql daemon figure out the default value.

diff --git a/interface/lib/classes/tform_base.inc.php b/interface/lib/classes/tform_base.inc.php
index cfaf0958d..78848bdfc 100644
--- a/interface/lib/classes/tform_base.inc.php
+++ b/interface/lib/classes/tform_base.inc.php
@@ -1388,6 +1388,17 @@ class tform_base {
                                                        } else {
                                                                $sql_insert_val .= "'".$record[$key]."', ";
                                                        }
+                                               } elseif ($field['formtype'] == 'SELECT') {
+                                                       $sql_insert_key .= "`$key`, ";
+                                                       if (is_null($record[$key])) {
+                                                               $sql_insert_val .= 'NULL';
+                                                       } elseif ($record[$key] === '') {
+                                                               $sql_insert_val .= "default(`$key`)";
+                                                       }
+                                                       else {
+                                                               $sql_insert_val .= "'".$record[$key]."'";
+                                                       }
+                                                       $sql_insert_val  .= ", ";
                                                } else {
                                                        $sql_insert_key .= "`$key`, ";
                                                        $sql_insert_val .= (is_null($record[$key]) ? 'NULL' : "'".$record[$key]."'") . ", ";

Reproducing the API call can be done with our demo code by commenting the pm value on line 56 and changing line to 'php' => 'n'