MySQLで画像を格納時にエラーが発生する3つの要注意ポイント

最近、データベースもお手軽に利用できるようになって、私の周りでも画像をMySQLのようなデータベースに格納する事例が増えてきました。

そこで、MySQLに画像を保存していく場合にエラーになりやすいポイントを3つ挙げておきます。

１．max_allowed_packet の容量をオーバーする

1度にクエリとして送信することができるパケットサイズをMySQLでは「max_allowed_packet」という設定オプションで規定しています（何も指定しない場合は1MBになっています）。

この値をオーバーするパケットを送ると「Packet too large」というエラーが発生します。　MySQL4.1以上では理論値で2GBまで可能になっています。

標準でこの値が「my.cnf」に書いてない場合があるので一度確認してみるといいでしょう。

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

たとえば、この値を16MBにするには以下の記述を「my.cnf」に記述してMySQLサーバーを再起動させます。

[mysqld]
...(中略)...
max_allowed_packet = 16MB

画像を登録する際にbase64エンコードしてテキストデータとして挿入する場合などは、実際のファイルサイズよりも137%増加するため、やや多めに設定しておくと良いと思います。

２．フィールド型の容量を超えてしまう

画像をbase64エンコードして挿入する場合、フィールドの型を「TEXT」「MEDIUMTEXT」「LONGTEXT」のどれにしようか迷う人もいるでしょう。　以下の容量に照らし合わせて適切なサイズの型を選ぶようにしてください。

これらの容量を超えてデータベースにデータを挿入すると、挿入エラーにはならないものの、たとえばJPEG画像だと画像の下部分が削れて登録されてしまうなど、ある意味エラーよりもやっかいな結果になりかねません。

３．別テーブルのIDと「DELETE CASCADE」な関係がない画像がある

これは滅多にないことですが、別テーブルに画像に関するメタデータを保持していて、画像ファイルをバッチでINSERTする場合に、そのテーブルのIDと「DELETE CASCADE」なIDもいっしょに登録する場合、別テーブルのIDにない画像を登録しようとすると次のようなエラーになります。

#1452 - Cannot add or update a child row: a foreign key constraint fails
(`画像テーブル`, CONSTRAINT `画像テーブル_ibfk_1` FOREIGN KEY (`外部キー`)
REFERENCES `別テーブル` (`外部キー`) ON DELETE CASCADE)

これは画像登録に限った話ではないのですが、既に画像説明などのメタデータを別テーブルに持っていて、画像リストをファイルからDB内に移す必要があった場合に陥りがちなミスなので、ここに挙げておきました。

これを回避するには、メタデータに存在しない画像ファイルを事前に取り除いておくか、または画像テーブルの「DELETE CASCADE」を一度解除してからINSERTし、別テーブルにないIDが付けられた行を削除するようにします。

DELETE FROM 画像テーブル
LEFT JOIN 別テーブル ON (画像テーブル.id=別テーブル.id)
WHERE 画像テーブル.id IS NULL

Webシステムなど、既に画像をファイルベースで運用しているサイトは多いと思いますが、データベースで全て運用する方針にした場合にこのようなシチュエーションになることが考えられます。

本番環境でアタフタしないようにしっかりと対策しておきましょうね。