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
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'