Pitfalls I hit on MySQL 5.7

27 Sep 2026

I recently loaded a batch of Chinese text data full of rare characters into a cloud-hosted MySQL 5.7, with a spreadsheet-style data browser and a small web app of my own in front of it. The data isn’t big, but there were plenty of traps. Here they are, roughly ordered by how hard they are to notice.

1. Every four-byte character is “equal” §

This is the most dangerous one, because it never raises an error.

utf8mb4 can store four-byte characters, which is where the CJK extension blocks live (𬶍, 𡌶, 𣿀 and so on). But 5.7’s default collation utf8mb4_unicode_ci is based on the old UCA 4.0.0, which gives every supplementary-plane character the same weight:

SELECT '𬶍' = '𡌶';   -- returns 1

So deduplicating by name merges two different places into one; WHERE name = '𬶍兴巷' returns a pile of unrelated rows; and a unique index complains about duplicates that aren’t. For any column that is mostly Chinese text and may contain rare characters, such as place names or personal names, use utf8mb4_bin. The database default can stay as it is; change only those columns. MySQL 8.0’s utf8mb4_0900_* collations don’t have this problem.

Also: a managed instance may still have latin1 as its server default. Say utf8mb4 explicitly when creating the database, the tables and the connection.

2. No CTEs, so modern tools break in odd places §

5.7 has no WITH common table expressions and no window functions. Many newer tools write SQL for 8.0 by default. On 5.7 they don’t fail as a whole; one feature breaks on its own. In my case it was the “group by” view of the data browser: the query it generated used WITH and failed with a syntax error.

Date formats are similar. A tool that writes ISO strings like '2026-09-16T14:38:10.364Z' gets rejected in strict mode (Incorrect datetime value), and settings such as NO_ZERO_DATE and explicit_defaults_for_timestamp can make some tools’ schema migrations fail. The data itself is fine, but it looks broken. If you have a choice, start on 8.0.

3. Half-dead long-lived connections §

My web app used a single long-lived connection to the database. After five idle hours overnight, the first request the next day froze the whole site and nginx returned 504.

The connection had been cut silently in the middle. The server reached the database over the cloud provider’s private interconnect, and a gateway on that path drops idle flows from its connection table without telling either end. The app sent its query on a socket that still looked alive; the packets were dropped, with neither a reply nor a reset, and TCP kept retransmitting for more than ten minutes before giving up. And because the app used one connection behind one lock, the first request held the lock while waiting and every later request queued behind it.

ping(reconnect=True) doesn’t save you here. It detects connections the server closed properly, but against silent packet loss it waits just the same. The real fix is three things: read and write timeouts on the connection (I used 20 seconds), throw away the old connection and reconnect on error, and retry once.

One more lesson: unauthenticated requests never touched the database, so external probes kept reporting the service as healthy. A health check should run a real query.

4. The public endpoint is slow §

From a server in the same region, pulling 64 MB through the database’s public address took 27.8 seconds, about 2.3 MB/s. Over the private network the same query took 0.8 seconds. The public address goes through a shared proxy with no guaranteed bandwidth. Use the private network whenever you can, then turn the public address off. While it was on, the error log showed an unknown IP trying the root password every 20 seconds.

Also, managed instances usually disable local_infile, so LOAD DATA LOCAL is unavailable and you are left with batched multi-row INSERTs. With a few thousand rows per batch, a million rows take ten-odd minutes.

5. Two limits for spatial data §

5.7 can store POINTs with a spatial index and has ST_Distance_Sphere, which is enough for “nearby points” queries. But it has no per-column SRID (that arrived in 8.0), so the coordinate system lives only in convention and documentation; and a spatial index can’t be built on a nullable column. I put the rows that have coordinates into a separate table with a NOT NULL POINT column.

Looking back, #1 and #3 are the ones that really bite: one silently merges data, the other silently hangs the service. The rest raise errors, and a quick search explains them.