Root Cause Analysis of MySQL 5.7 JSON NOT NULL Rollback Failure and Its Fix

The author, a DBA engineer at Sina Weibo, encountered a data‑loss incident caused by a DELETE operation that required a rollback. When executing the generated rollback SQL, MySQL 5.7.21 returned an error: ERROR 3140 (22032) ... Invalid JSON text: "The document is empty."

Initial suspicion fell on escaped backslashes, but further inspection revealed that the JSON column search_stats was being rolled back with an empty string (''), which violates the JSON NOT NULL constraint.

Analysis of the binlog showed that the original value was indeed an empty string, while the live table stored a NULL (or lower‑case null ) value, indicating a compatibility issue between the binlog representation and the column definition.

To reproduce the problem, the author set SQL_MODE='' , created a table with a JSON NOT NULL column, inserted a NULL value, and altered the column to JSON NOT NULL . Subsequent updates caused the binlog to record the JSON field as '' instead of NULL . The same behavior was observed on MySQL 5.7.35 and 5.7.44, while MySQL 8.0 correctly records NULL .

The resolution involved creating a new database with an identical schema, converting the problematic JSON column to TEXT , importing the rollback SQL (changing empty strings to {} ), and then converting the column back to JSON . After verifying the data, the corrected rows were inserted back into the original table.

Additional tests showed that a VARCHAR column behaved similarly, turning NULL into an empty string after the same alteration.

Key recommendations include:

Always back up data and test schema changes in a staging environment before applying them to production.

Pay special attention to type changes for JSON, BLOB, and other special columns.

Enable strict SQL_MODE in production to prevent silent data truncation and constraint violations.

Be aware that the MySQL 5.7 mysqlbinlog tool may record NULL JSON values as empty strings, which can cause rollback failures.

The issue does not affect replication or online data integrity; it is limited to the rollback process in MySQL 5.7.