Legacy PHP on Modern MySQL: Fixing Strict-Mode Failures Without Switching Strict Mode Off

When an older PHP application moves to a current MySQL server, the first thing that usually breaks is not the code you changed. It is a query that worked for years because the old server was willing to guess. We hit exactly this on a licensed social-networking platform with iOS and Android wrapper apps, and the fix that held up was simple to describe: find every failing query, correct it, and leave no debugging code behind.

This post walks through how we approached it, and why we did not take the shortcut most people reach for first.

Why old queries fail on a modern MySQL server

Modern MySQL enables strict SQL modes by default. The MySQL 8.0 default sql_mode includes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_DATE and related flags, according to the MySQL reference manual on server SQL modes.

Code written against older, permissive settings tends to break in a few predictable ways:

• A GROUP BY query selects columns that are neither grouped nor aggregated, and the server refuses to pick an arbitrary value.
• An insert or update passes a value that does not fit the column, and the server raises an error instead of silently truncating or zeroing it.
• String values are quoted inconsistently across the query layer, which works until one path sends something the server will not coerce.

On this platform, one of the queries that needed rewriting was the social-graph query, the one that works out who is connected to whom. In a social product, almost every screen depends on that graph, which is why this kind of failure is never cosmetic.

The shortcut we did not take

The fastest way to make the errors disappear is to set sql_mode back to a permissive value on the new server. It works in the sense that the pages load again.

We did not do it, for two reasons:

1. It hides data problems instead of preventing them. Permissive mode is what allows truncated values and arbitrary group results into the database in the first place.
2. It ties the application to one server configuration. The next hosting move, managed database or version upgrade brings the same failures back, usually at a worse time.

So the goal was to make the queries correct under the default modes, not to make the server tolerate incorrect queries.

Step 1: Make the failures visible

In an inherited codebase you rarely know where every query lives. We added a temporary wrapper around the database layer that logged each statement, its parameters and the error it produced. That turned vague symptoms into a concrete list of failing statements.

The important word is temporary. A logging wrapper like this can record user data, it slows every request down, and it is easy to forget. We treated it as scaffolding with a removal date from the moment it went in.

Step 2: Fix the queries, not the symptoms

With the failing statements in hand, the work split into two kinds:

Rewrite what is logically wrong

The social-graph query was rewritten to be valid under strict mode instead of relying on the server to guess. That is not just a compatibility fix. A query that depends on permissive behaviour, such as an arbitrary row from a GROUP BY or a silently truncated value, can return different answers on different servers, which is a correctness bug waiting for the right data.

Normalise what is inconsistent

String quoting was normalised across the whole query layer, rather than patched at the one call site that happened to fail first. Fixing only the visible failure leaves the next one in place for a later release.

Step 3: Clean up while the patient is open

Two further changes made the platform easier to run, and both came out of the same pass:

• Environment-based configuration. Hard-coded constants were replaced with environment-based configuration, so a new host becomes a configuration change rather than a code change.
• Removing deprecated packages and constants. Anything the current PHP version warns about is a future outage. It is cheaper to remove it while you already understand the surrounding code.

Step 4: Remove the debugging code

Once the queries passed under strict mode, the logging wrapper came out completely. We call this a debug-then-clean workflow: instrument aggressively to find the problem, then leave the codebase with no diagnostic code, no stray log files and no performance cost.

Skipping this step is how production systems end up with logging that nobody remembers adding, quietly filling disks and capturing data it should not.

What this means if you are about to move a legacy PHP app

If you run an older PHP product and a hosting or database upgrade is coming, the lesson generalises well:

• Expect query failures, and plan time for them. They are normal, not a sign the code is beyond repair.
• Keep strict mode on. Treat each failure as a query to fix.
• Log temporarily, then remove it. Put the removal on the task list at the same time as the logging.
• Fix categories, not instances. If one query has a quoting problem, check the whole layer.
• Leave it more configurable than you found it. Environment configuration pays for itself on the next move.

We did this work as part of stabilising an inherited platform, which is typical of our software rescue work. The anonymised write-up of the project is in our work library, and if your platform also needs its files moved off local disk, our object storage migration playbook covers the next step.