Issue MariaDB 11.8.8: STRICT_TRANS_TABLES suddenly enabled after reboot AlmaLinux 9.7 → 9.8

[Azurel]

Server operating system version

AlmaLinux 9.8

Plesk version and microupdate number

18.0.80#1

I have a rather strange issue with MariaDB 11.8.8 on a Plesk server and would be interested to know if anyone has seen something similar. The server originally ran AlmaLinux 8.10 with Plesk MariaDB 11.8.8. Everything had been working fine for long time. In particular, STRICT_TRANS_TABLES was not enabled.

The sequence of events was as follows:

1. The server was running AlmaLinux 8.10 + MariaDB 11.8.8 and everything was working normally.
2. I started the almalinux8to9 migration.
3. The migration to AlmaLinux 9.7 failed partway through due to errors, so I had to manually repair/complete parts of the upgrade.
4. After that, the system was running AlmaLinux 9.7 and MariaDB 11.8.8 was still working normally.
5. A subsequent kernel update upgraded the system to AlmaLinux 9.8, but the new kernel only became active after a reboot.
6. I rebooted the server today:

reboot system boot 5.14.0-687.36.1 Sun Aug 9 07:17 still running <- AlmaLinix 9.8
shutdown system down 5.14.0-687.34.1 Sun Aug 9 07:15 - 07:17 (00:01) <- AlmaLinix 9.7

Directly after this reboot, MariaDB suddenly started using:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This was unexpected because the server had been running for years without STRICT_TRANS_TABLES, and the problem only appeared after this reboot. The practical consequence is that one of the applications using MariaDB started failing with errors such as:

Field 'parentid' doesn't have a default value
Field 'Locked' doesn't have a default value

If I manually change the SQL mode to `NO_ENGINE_SUBSTITUTION` everything works again. However, after restarting MariaDB `systemctl restart mariadb` the SQL mode is back to:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I have now added the following to /etc/my.cnf:

[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"

This fixes the problem after restarting MariaDB.

What confuses me is where the previous value is coming from. There is no sql_mode setting in /etc/my.cnf or /etc/my.cnf.d/ before I added the above configuration. Interestingly, if I run:

mariadbd --no-defaults --verbose --help | grep -A 1 "^sql-mode"

I get:

sql-mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So I assume this may be the built-in default for this MariaDB build/version? However, if that is the default, why did this never cause any problems before the AlmaLinux last reboot to version 9.8? The MariaDB version was and is 11.8.8. I would be very interested to know:

• Is this SQL mode the expected default for MariaDB 11.8.8 on AlmaLinux 8 and 9?
• Could the AlmaLinux 8 → 9 migration or the kernel update have changed how MariaDB is started/configured?
• Why would the default suddenly changed after this reboot?
• Has anyone else running MariaDB 11.8.8 on AlmaLinux 9 seen the same output from:

mariadbd --no-defaults --verbose --help | grep -A 1 "^sql-mode"

If possible, could someone run that command on their Plesk/AlmaLinux server and post the result?

I'd mainly like to understand where this suddenly came from and why it changed after the reboot, rather than simply keeping the workaround in /etc/my.cnf.

 

[Azurel]

During the AlmaLinux upgrade, /etc/my.cnf appears to have been replaced or migrated, and the previous configuration was saved as /etc/my.cnf.rpmsave. The old configuration explicitly disabled STRICT_TRANS_TABLES. The new configuration does not contain sql_mode, so MariaDB falls back to its built-in default.

# diff -u /etc/my.cnf /etc/my.cnf.rpmsave
--- /etc/my.cnf 2025-01-30 12:01:25.000000000 +0100
+++ /etc/my.cnf.rpmsave 2026-07-29 07:08:31.725516024 +0200
@@ -1,11 +1,15 @@
 #
-# This group is read both by the client and the server
+# This group is read both both by the client and the server
 # use it for options that affect everything
 #
 [client-server]

 #
-# include *.cnf from the config directory
+# include all files from the config directory
 #
 !includedir /etc/my.cnf.d

+[mysqld]
+sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+bind-address = 127.0.0.1
+local-infile=0

Why did the AlmaLinux/Plesk/MariaDB upgrade remove the existing sql_mode configuration from /etc/my.cnf? And why is the current /etc/my.cnf dated 2025, while the corresponding /etc/my.cnf.rpmsave file was created in July 2026?

 

[Azurel]

Okay I found the reason. The reason is almalinux8to9 silent do this:

DEBUG - The '/etc/my.cnf' file has a '.rpmnew' analogue file. Going to replace the file with this rpmnew file. The file itself will be saved as .rpmsave