When PDO Silent Truncation Cost Us A Week Of Bad Data

We run PHP 8.2 with pdo_mysql against MySQL 8.0 on RDS. Our write path pulls roughly 40k events/hour into a events table where one column, payload, is TEXT. One Tuesday the analytics team asked why a specific event type had empty payloads for about 0.3% of rows. The application logged no errors. The inserts returned true. Just missing trailing bytes.

The cause was not PDO. It was sql_mode. We had migrated the schema from a staging box where STRICT_TRANS_TABLES was set, but production RDS had been created from an older parameter group where strict mode was off. For TEXT columns, MySQL truncates silently when strict mode is disabled, and emits a warning, not an error. PDO with ATTR_ERRMODE => ERRMODE_EXCEPTION does not throw on warnings, only on errors.

To confirm, I ran this against production from a read replica first:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);
$pdo->exec("SET SESSION sql_mode = ''");
$pdo->exec("CREATE TEMPORARY TABLE t (id INT PRIMARY KEY, p TEXT)");
$pdo->exec("INSERT INTO t VALUES (1, REPEAT('a', 70000))");
var_dump($pdo->query("SHOW WARNINGS")->fetchAll(PDO::FETCH_ASSOC));

We got Warning: 1265 Data truncated for column 'p' at row 1. In our real insert path, that warning was discarded because we never checked SHOW WARNINGS after each statement.

The fix was two parts. First, set sql_mode strictly in the connection init command so it cannot drift per host:

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'"
];

Second, we added a cheap length check before binding: strlen($payload) > 65535 gets rejected at the application boundary with a real exception, rather than trusting the database to tell us. The trade-off is one strlen call per row and a hard limit that is slightly conservative for multibyte content if we ever switch to utf8mb4 with 4-byte chars, because TEXT is 65,535 bytes, not characters. We accepted that.

Where this fails: MYSQL_ATTR_INIT_COMMAND does not run on reconnect in every PDO build, so long-lived workers can silently fall back to the server default after a dropped connection. Test reconnect behavior explicitly. Also, if you rely on PDO::ATTR_EMULATE_PREPARES => true, warnings attached to emulated statements may surface differently. And setting strict mode can break legacy inserts that previously relied on implicit zero dates or truncation, so roll it out per-database, not globally.

What are people using now to catch row-level MySQL warnings in PHP without wrapping every query in a SHOW WARNINGS round trip?