MySQL Will Quietly Eat Your Data If You Let It: Truncation, Charsets, and the 1366 You Never See

A WhatsApp button on a shop was dialling the wrong number. Not a formatting problem — a genuinely different number, three digits short of the one in the admin panel.

The admin panel had saved it. The page was reading it. Nothing errored. The column was varchar(10), the number arrived with a country code, and MySQL had done exactly what it was configured to do: chop the value to fit and carry on.

That column had been wrong for months. Nobody noticed because nothing anywhere in the stack raised its voice.

Here is the family of bugs that behaves this way, how to find them in a database you inherited, and why the obvious fix — "just turn on strict mode" — creates a different bug if you do only that.

Part 1: The truncation

Run this on any MySQL or MariaDB instance:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

If neither contains STRICT_TRANS_TABLES or STRICT_ALL_TABLES, then for every INSERT and UPDATE your server is in the business of making data fit, not of telling you it doesn't.

What that means concretely:

CREATE TABLE t (phone VARCHAR(10));
INSERT INTO t VALUES ('905551234567');
SHOW WARNINGS;
-- Level: Warning  Code: 1265  Message: Data truncated for column 'phone' at row 1

SELECT phone FROM t;
-- 9055512345

Note the level. Warning, not error. The statement succeeded. affected_rows is 1. PDO in exception mode throws nothing, because there is nothing to throw — MySQL considers this a completed statement. Your ORM reports success. Your integration test asserting "the row exists" passes. The value is wrong.

The same mechanism applies well beyond strings:

Every one of those is a silent, permanent difference between what your application believed and what your database holds. And unlike a crash, there is no timestamp to correlate against — you cannot tell from the row when it happened or how many rows before it went the same way.

Finding the damage in a database you inherited

You cannot recover truncated values — the tail is gone. But you can find the columns where it is happening, which is what matters going forward. Truncated values pile up at exactly the column limit, so the suspects are the columns where real rows sit at the maximum length:

-- 1. list the string columns and their limits
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH AS max_len
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND DATA_TYPE IN ('varchar', 'char')
  AND CHARACTER_MAXIMUM_LENGTH <= 64      -- short columns are where this bites
ORDER BY CHARACTER_MAXIMUM_LENGTH;

Then, per interesting column:

SELECT COUNT(*) AS at_the_limit
FROM settings
WHERE CHAR_LENGTH(phone) = 10;

A handful of rows sitting exactly at the limit can be a coincidence. A majority of rows sitting exactly at the limit is not a coincidence, it is a report. In our case the count was every row that had ever been saved with a country code.

Two details that matter when you run this:

• Use CHAR_LENGTH(), not LENGTH(). LENGTH() counts bytes; CHAR_LENGTH() counts characters. On multi-byte text they disagree, and VARCHAR(n) limits characters, not bytes.
• Do this on a replica or a restored dump if the table is large. CHAR_LENGTH() in a WHERE clause means a full scan.

Part 2: Strict mode is necessary and not sufficient

The obvious response is to turn strict mode on. Do it — but understand what you are buying.

Strict mode does not repair the mismatch between what your application sends and what the column accepts. It changes how you find out about it: instead of quietly storing a wrong value, MySQL raises error 1406 (Data too long for column) and your code, which never expected a write to fail, hands the user a 500.

I have seen exactly this on an admin form. Strict mode was correctly enabled; the title field in the form had no maxlength and no server-side length check; the column was varchar(150). Someone pasted a long headline. Instead of a validation message the editor got a blank error page, and — worse — a story they thought they had saved and hadn't.

That is still better than silent corruption, because it is loud and it is immediate. But the actual fix is at the boundary:

$rules = [
    'title' => ['max' => 150],   // same number as the column, in one place
    'phone' => ['max' => 20],
];

foreach ($rules as $field => $rule) {
    if (mb_strlen($input[$field] ?? '') > $rule['max']) {
        return back()->withError("{$field}: at most {$rule['max']} characters.");
    }
}

Three habits that keep this honest:

1. Validate before you write, with the column's real limit. Generate the limits from information_schema if you can — a hand-copied number drifts the first time someone runs an ALTER.
2. Turn strict mode on so the boundary check has a backstop. Validation you wrote can be bypassed; the database is the last honest party in the chain.
3. Widen the column when the data is legitimately bigger. VARCHAR(10) for a phone number was never right. Truncation is a symptom; a wrong schema is the disease.

Part 3: Charsets, where "it worked on the old server" lives

A database was copied to a new server. Everything imported. Row counts matched. A week later somebody noticed that Turkish characters in older records had turned into question marks in some tables and not others.

The mistake was trusting the database default:

SHOW CREATE DATABASE app;
-- CHARACTER SET utf8mb4  ← looks fine, means almost nothing

The database default applies to newly created tables that do not specify their own. It says nothing about the columns you already have. The columns are where the truth is:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4';

If that returns rows, you have a mixed-charset database, and every dump/restore across it is a chance to lose bytes.

Two more charset facts worth having in your head:

utf8 in MySQL is not UTF-8. The alias utf8 (historically utf8mb3) stores at most three bytes per character, so it cannot hold anything outside the Basic Multilingual Plane — emoji, some CJK extensions, some historic scripts. Insert one into a utf8 column and, in non-strict mode, MySQL truncates the value at the emoji and keeps whatever came before. utf8mb4 is real UTF-8. Use it everywhere, including the connection charset.

The connection charset is part of the pipeline. Your column can be utf8mb4, your data can be perfect, and your terminal or client can still show you mojibake because the session negotiated something else. Which leads to the most useful debugging habit in this whole article:

When you think data is corrupted, check the bytes

SELECT title, HEX(title), LENGTH(title), CHAR_LENGTH(title)
FROM articles WHERE id = 42;

HEX() is the ground truth. It is not affected by your client, your terminal font, or the connection charset. I have twice now "found" data loss that turned out to be a display artefact of the command-line client, and both times HEX() settled it in one query: the bytes were intact, the rendering was not.

The tell for genuine UTF-8 content is LENGTH() > CHAR_LENGTH(). If they are equal on text you know contains non-ASCII characters, something upstream already flattened it.

Part 4: The application-side version of the same bug

Databases are not the only layer that cuts strings. This is a summary excerpt in PHP:

$summary = substr($body, 0, 200);   // wrong

substr() counts bytes. On UTF-8 text, byte 200 lands in the middle of a multi-byte character about half the time, and you send MySQL a string ending in half a character. Then either:

• the column is utf8mb4 and strict mode is on → error 1366, Incorrect string value, and you get a 500 from what looked like a formatting line; or
• strict mode is off → MySQL drops the invalid tail silently, and your summaries are quietly one character shorter than you think, sometimes ending in a replacement glyph.

The fix is one letter and three characters:

$summary = mb_substr($body, 0, 200);   // counts characters

Whenever you see strlen, substr, strtoupper, or str_pad applied to user text, treat it as a bug report waiting to happen. Error 1366 in a log almost always traces back to one of them.

The checklist

Run these on any project you did not set up yourself. They take about ten minutes together.

1. SELECT @@GLOBAL.sql_mode; — is STRICT_TRANS_TABLES present? If not, plan to enable it after auditing lengths, not before.
2. List varchar columns with small limits, then count rows sitting exactly at the limit. That count is your truncation report.
3. List columns whose CHARACTER_SET_NAME is not utf8mb4. Fix them before the next migration, not during it.
4. Confirm the connection charset your application actually negotiates — the DSN, not the config file you hope it reads.
5. Grep the codebase for byte-based string functions applied to user content, and replace them with the mb_ versions.
6. When you suspect corruption, run HEX() before you conclude anything. Half the time the data is fine and the client is lying.

The theme across all of this: the database is willing to accept an approximation of what you gave it, and by default it will not argue. Make it argue. Then make sure your code is ready to hear it.

I build and run news and e-commerce platforms at alestaweb.com. Every example above cost somebody a real afternoon.