The Problem
MySQL 5.7 introduced the native JSON data type, bringing automatic validation and faster lookups. But that validation comes with a hair-trigger. If your data doesn't follow RFC 7159 to the letter, MySQL kills the operation instantly. Youāll be met with this blunt error message:
ERROR 3140 (22032): Invalid JSON text: "Invalid value." in value for column 'meta' at row 1
This usually happens during a data migration from TEXT to JSON columns, or when a backend service sends a slightly malformed string that your database just won't swallow.
Why is MySQL rejecting your data?
Every time you write to a JSON column, MySQL's internal parser scans the input byte-by-byte. If it hits an unexpected character, it stops. In production environments, I see these five culprits most often:
- Single quotes: JSON strictly requires double quotes (
"). While{'id': 1}is valid JavaScript, it is illegal JSON. - Trailing commas: A single extra comma at the end of a list, like
[10, 20, 30,], will break the parser. - Empty strings: A blank string
''is not valid JSON. You must usenullor an empty object{}instead. - Unescaped control characters: Hidden tabs or literal newlines inside a string value will cause a failure. They must be escaped as
\tor\n. - Smart quotes: Copy-pasting from Word or Slack often introduces "curly" quotes (
ā ā). MySQL only recognizes standard straight quotes (" ").
Quick Fix: Identifying the invalid syntax
Don't guess where the error is. Before updating your application code, test your raw query using MySQL's JSON_VALID() function. It returns 1 for valid data and 0 for errors.
-- Test this in your SQL client
SELECT JSON_VALID('{ "status": "active", "tags": ["web", "prod",] }');
-- Result: 0 (The trailing comma after "prod" is the killer)
If you get a 0, the syntax is broken. If you get a 1 but still see the error, check your character encoding; the parser might be tripping over a multi-byte character it doesn't recognize.
Permanent Solutions
1. Let MySQL build the JSON
Instead of manually stitching JSON strings together in your applicationāwhich is where 90% of quoting errors startālet MySQL handle the heavy lifting with JSON_OBJECT or JSON_ARRAY.
INSERT INTO users (meta)
VALUES (JSON_OBJECT("last_login", NOW(), "ip", "192.168.1.1"));
2. Sanitize Empty Values
If your application logic sometimes sends an empty string, intercept it. A JSON column can be NULL, but it cannot be ''. Ensure your SQL driver converts empty inputs to a proper default.
-- This will fail:
UPDATE settings SET config = '' WHERE id = 1;
-- Do this instead:
UPDATE settings SET config = '{}' WHERE id = 1;
3. Standardize your Backend Encoding
Never manually format JSON strings in your backend. Use native functions like json_encode() in PHP, json.dumps() in Python, or JSON.stringify() in Node.js. These libraries are built to follow RFC standards and will automatically escape special characters for you.
Debugging Tips
Don't waste 20 minutes squinting at a 5,000-character nested blob. When a massive JSON object fails, I pipe it through a validator first. Tools like ToolCraft's JSON Validator highlight the exact line and character where a quote is missing or a comma is misplaced. Itās browser-based, so you can debug production snippets without sending sensitive data to a third-party server.
Final Verification
Run these three checks to confirm your fix is solid:
- Check validity:
SELECT COUNT(*) FROM table WHERE JSON_VALID(meta) = 0;(Should be 0). - Check type:
SELECT DISTINCT JSON_TYPE(meta) FROM table;(Should returnOBJECTorARRAY). - Test extraction:
SELECT meta->>"$.status" FROM table LIMIT 1;.
One last tip: ensure your connection uses utf8mb4. If your client sends latin1 data to a JSON column, the parser might misinterpret valid characters as syntax errors.