MySQL JSON NOT NULL Constraint Fails with Empty Values

Fault Phenomenon

A business operation executed a DELETE command, causing accidental data loss requiring rollback. During the rollback process using tools, an error occurred:
ERROR 3140 (22032): Invalid JSON text: "The document is empty."
Initial assumptions pointed to escaped characters truncating data, but reprocessing with sed failed. Further analysis revealed the root cause: the rolled-back SQL generated ​empty string ('') values​ for the search_stats JSON column, violating its NOT NULL constraint.

• ​MySQL Version:​​ 5.7.21
• ​SQL_MODE:​​ Empty

Fault Analysis

Binlog analysis confirmed the rolled-back SQL logged empty strings (''), but the live table exhibited unexpected NULL values under the JSON NOT NULL constraint. Normally, inserting NULL would trigger a constraint violation. This discrepancy suggested historical DDL changes or MySQL 5.7-specific bugs.

Fault Reproduction

1. Create a table and insert NULL:

CREATE TABLE q (id INT, age VARCHAR(200) DEFAULT NULL);  
INSERT INTO q VALUES (1, NULL);  

‍

2. Alter column to JSON NOT NULL:

ALTER TABLE q MODIFY age JSON NOT NULL;  
-- Warning: Data truncated (expected error but got warning)  

‍

3. Verify data corruption:

SELECT * FROM q WHERE JSON_EXTRACT(age, '$.age') IS NULL;  
-- Returns row with `age = null` (lowercase, invalid JSON)  

‍

4. Binlog records NULL as empty strings (''):

-- Binlog entry for UPDATE shows:  
### @2='' /* JSON meta=4 nullable=0 is_null=0 */  

‍

Resolution

1. ​Recreate the table with a compatible schema:​​
   • Replace JSON with TEXT, import rolled-back SQL.
   • Update '' to '{}', then migrate back to JSON.
2. ​Validate data integrity​ via INSERT ... SELECT to restore original data.

Post-Testing

• ​MySQL 5.7.35/5.7.44​ exhibit identical behavior.
• ​MySQL 8.0​ logs null (lowercase) instead of empty strings.
• ​VARCHAR fields​ convert NULL to empty strings under similar constraints.

​Key Insight:​​
Strict SQL_MODE prevents silent truncation. Always enforce STRICT_TRANS_TABLES in production.

Best Practices

1. ​Backup data​ before altering tables.
2. ​Test schema changes​ in staging environments.
3. ​Avoid JSON/BLOB constraints​ unless strictly necessary.
4. ​Enable strict SQL_MODE​ (STRICT_ALL_TABLES) to catch truncation early.