MySQL 5.7+のJSONカラムで発生する「ERROR 3140: Invalid JSON text」の解決方法

問題の概要

MySQL 5.7でネイティブの JSON データ型が導入され、自動バリデーションと高速な検索が可能になりました。しかし、そのバリデーションは非常に厳格です。データがRFC 7159に一文字でも準拠していない場合、MySQLは即座に操作を中断します。その際、次のような素っ気ないエラーメッセージが表示されます。

ERROR 3140 (22032): Invalid JSON text: "Invalid value." in value for column 'meta' at row 1

このエラーは通常、TEXT から JSON カラムへのデータ移行中や、バックエンドサービスがデータベースで許容されない僅かに不正確な形式の文字列を送信したときに発生します。

なぜMySQLはデータを拒否するのか？

JSON カラムに書き込む際、MySQLの内部パーサーは入力をバイト単位でスキャンします。予期しない文字に遭遇すると停止します。本番環境でよく見られる5つの主な原因は以下の通りです。

シングルクォート: JSONでは厳格にダブルクォート (") が要求されます。{'id': 1} はJavaScriptとしては有効ですが、JSONとしては不正です。
末尾のカンマ: [10, 20, 30,] のように、リストの最後にある余分なカンマ1つでパーサーはエラーになります。
空文字列: 空の文字列 '' は有効なJSONではありません。代わりに null または空のオブジェクト {} を使用する必要があります。
エスケープされていない制御文字: 文字列値の中に隠れたタブやリテラルの改行が含まれていると失敗します。これらは \t や \n としてエスケープする必要があります。
スマートクォート（全角引用符）: WordやSlackからコピー＆ペーストすると、しばしば「しなり」のある引用符 (“ ”) が混入します。MySQLは標準の直線的なクォート (" ") のみを認識します。

クイックフィックス: 無効な構文の特定

どこにエラーがあるか推測するのはやめましょう。アプリケーションコードを修正する前に、MySQLの JSON_VALID() 関数を使用して生のクエリをテストしてください。有効なデータには 1 を、エラーには 0 を返します。

-- SQLクライアントでこれをテストしてください
SELECT JSON_VALID('{ "status": "active", "tags": ["web", "prod",] }'); 
-- 結果: 0 ("prod" の後の末尾のカンマが原因です)

結果が 0 の場合、構文が壊れています。1 が返されるのにエラーが出る場合は、文字エンコーディングを確認してください。パーサーが認識できないマルチバイト文字でつまづいている可能性があります。

恒久的な解決策

1. JSONの構築をMySQLに任せる

アプリケーション側でJSON文字列を手動で繋ぎ合わせる（ここで引用符のエラーの90%が発生します）代わりに、JSON_OBJECT や JSON_ARRAY を使ってMySQLに重労働を任せましょう。

INSERT INTO users (meta) 
VALUES (JSON_OBJECT("last_login", NOW(), "ip", "192.168.1.1"));

2. 空の値のサニタイズ

アプリケーションのロジックで時々空文字列が送信される場合は、それをインターセプトしてください。JSON カラムは NULL にはなれますが、'' にはなれません。SQLドライバーが空の入力を適切なデフォルト値に変換するようにしてください。

-- これは失敗します:
UPDATE settings SET config = '' WHERE id = 1;

-- 代わりにこうします:
UPDATE settings SET config = '{}' WHERE id = 1;

3. バックエンドのエンコーディングの標準化

バックエンドでJSON文字列を手動でフォーマットしないでください。PHPの json_encode()、Pythonの json.dumps()、Node.jsの JSON.stringify() といったネイティブ関数を使用してください。これらのライブラリはRFC標準に従って構築されており、特殊文字を自動的にエスケープします。

デバッグのヒント

5,000文字のネストされた巨大なデータを目を細めて20分間眺めるのは時間の無駄です。大規模なJSONオブジェクトが失敗したときは、まずバリデーターを通しましょう。ToolCraftのJSON Validatorのようなツールは、クォートの欠落やカンマの配置ミスがある正確な行と文字をハイライトしてくれます。ブラウザベースなので、機密データをサードパーティのサーバーに送信することなく、本番環境のスニペットをデバッグできます。

最終確認

修正が確実であることを確認するために、次の3つのチェックを実行してください。

妥当性の確認: SELECT COUNT(*) FROM table WHERE JSON_VALID(meta) = 0; (結果は0であるべきです)。
型の確認: SELECT DISTINCT JSON_TYPE(meta) FROM table; (OBJECT または ARRAY が返されるべきです)。
抽出のテスト: SELECT meta->>"$.status" FROM table LIMIT 1;。

最後に一点：接続が utf8mb4 を使用していることを確認してください。クライアントが latin1 データを JSON カラムに送信すると、パーサーが有効な文字を構文エラーと誤認することがあります。