Legacy Apps Break After Migration — MariaDB Strict Mode Errors

After I moved a website from an old server (CentOS + an old MariaDB version) to a new one (Debian + the current MariaDB version), one membership system started behaving strangely: every page looked fine, but clicking the registration button just returned "temporary error" with no details at all.

If you have not encountered strict mode before, a case like this could have you tracing PHP code all day without finding the cause, because the code is not wrong — the environment has changed. So I thought I would write up how I tracked it down.

Tracking Down the Cause

Fortunately, this system has its own error log table, and checking it revealed the actual error:

SQLSTATE[HY000]: General error: 1364
Field 'middlename' doesn't have a default value

Some systems will encounter another number in the same family:

SQLSTATE[22007]: Invalid datetime format: 1366
Incorrect integer value: '' for column 'status' at row 1

Both point to the same problem: the code runs INSERT without supplying values for every column, or passes an empty string into a numeric column. This was common in PHP 5-era apps, because MySQL back then would just issue a warning and quietly insert a default value.

It breaks because MariaDB changed the default sql_mode to include STRICT_TRANS_TABLES starting with 10.2 — what was a warning immediately becomes an error. Same website, same code, same data, and just moving servers breaks it.

Check what your server is set to:

mysql -e 'SELECT @@GLOBAL.sql_mode;'
# STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The Fix

The most correct fix is to update the code to supply all values in INSERT and cast them correctly. But for an old app that nobody maintains anymore (or one encoded with ionCube that cannot be modified), the practical fix is to remove strict mode — put it in a separate conf file, rather than editing the distro's main file:

cat > /etc/mysql/conf.d/98-legacy-sqlmode.cnf <<'EOF'
# แอปเก่า (pre-strict era) พังกับ STRICT_TRANS_TABLES หลังย้ายเซิร์ฟเวอร์
# error 1364 (no default) / 1366 (incorrect value) ตอน INSERT
[mysqld]
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

systemctl restart mariadb
mysql -e 'SELECT @@GLOBAL.sql_mode;'

The reason for using a separate 98-*.cnf file: when the main config file gets overwritten during the next MariaDB upgrade, our conf.d file survives, and opening it makes the reason for the setting immediately clear — a comment in the file is a letter to my future self.

If You Do Not Want to Disable It for the Whole Server

Strict mode really is useful for newer apps (it keeps junk data out of the DB). If the server hosts a mix of old and new apps, there are more targeted options.

// แบบที่ 1: หลัง new PDO(...) หรือ mysqli_connect(...)
$pdo->exec("SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'");

-- แบบที่ 2: ระดับ server (ไม่ apply กับ user สิทธิ์ SUPER)
SET GLOBAL init_connect = "SET sql_mode='NO_ENGINE_SUBSTITUTION'";

I chose to disable it for the whole server because most of the apps on that machine were old — choose based on your setup.

Checklist When "Forms Break, but Pages Look Fine" After a Server Move

1. Find the app's error log first — many systems store it in their own DB, without writing to the PHP error log
2. 1364/1366 = definitely strict mode
3. Compare SELECT @@GLOBAL.sql_mode with the old server
4. Decide: fix the code / SET SESSION / disable globally
5. Use a separate conf.d file + a comment explaining why

This case took me less than half an hour to track down because I had seen it before — if you are planning a server move, save the old server's @@GLOBAL.sql_mode before the move so you can compare it and avoid confusion later. If I have left anything out or made a typo, I will come back and correct it later.