MySQLで「ERROR 1030 (HY000): Got error 139 from storage engine」というエラーが出た時の対処法を検討する

はじめに

　現在オレが担当してる案件は既にリリースから数年経っているのですが、MySQLについても相応に古く、5.1系を利用しています。当然毎日mysqldumpを取得し、データバックアップを行っていますが、今更ながら「ちゃんとレストア出来るか確認しよう！」と検証したところ、なんと掲題のようなエラーが出て、データ取込に失敗してしまいました。マジか………… orz

　このエラーに関する詳細を記事から引用します。

http://mysql.stu.edu.tw/doc/refman/5.1-olh/ja/innodb-restrictions.html

可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除く行の最大長は、データベースページの半分よりも少し短くなります。これは、最大行長は約 8000 バイトであるということです。LONGBLOB と LONGTEXT カラムは 4G バイト以下である必要があり、BLOB と TEXT カラムを含んだ合計行長は 4G バイト以下でなければいけません。

漢(オトコ)のコンピュータ道: MySQL 5.5新機能徹底解説

今でもこの8KBの制限については非常に多くの問い合わせを受けるのだが、特にBLOB/TEXT/VARCHARを多用している場合にこの制限に引っかかりやすい。と言っても単に8KB以上の大きなサイズのカラムが使えないというわけではない。大きなサイズのBLOBやTEXTは先頭の768バイトを除いて別の領域に格納されるので、8KB以上のカラムを格納するのは問題ないのだが、先頭の768バイトは通常の領域に残るため、その部分がたくさんあると8KBの制限に引っかかることになる。例えば11個以上のBLOB/TEXT/VARCHAR（768バイト以上）を作成すると合計で8KBを超えてしまうため、この制限に引っかかってしまうのである。
ただし、この問題が顕在化するのはデータを格納したときであり、テーブルの作成は問題なく出来てしまう。しかも、データを満たんに詰めると8KBを超えるテーブル定義であっても、実際の行サイズが合計で8KBを超えないと問題が顕在化しないので潜在的な問題に気づかず、ある日突然「データが格納できない！」ということになってしまうわけだ。

　対象のテーブルがストレージエンジンにInnoDBを採用してる場合、1レコードのデータ長合計が8000バイトを超えると怒られるわけですな。ちなみに、この8000バイト(8KB)というのは、ページが持つ最大サイズ16KBの約半分まで、というルールによって決められているようです。
　というわけで、この「ERROR 1030 (HY000): Got error 139 from storage engine」というエラーの解消方法を探してみました。

対処案１「MyISAM」の利用

　MySQLでは元々「MyISAM」というストレージエンジンが主流でした。トランザクション機能が無い代わりにシンプルで高速、というのがこのストレージエンジンの売り。最大行長が8000バイトという制限もありません。
　なので、対象テーブルで採用するストレージエンジンを、「InnoDB」→「MyISAM」に移行してやる、というアイディアです。

【MySQL】 Got error 139 from storage engine → text, blobのカラムが多すぎ at softelメモ

なお、InnoDBでなくてもよい場合では、テーブルをMyISAMに変換することで回避できる。
> ALTER TABLE TABLE_NAME ENGINE=MyISAM;

　業務システムでトランザクションが使えないのは、あまりにリスキー過ぎるので、この案は却下！

対処案２コンパイルオプションを指定してページサイズを拡張

mysql:12769

１．DDLを変更して8000バイト以上のデータがPage内に入らないようにする。
　　例：テーブルを分割する、行を統合するなど
２．コンパイルオプションを指定してInnoDBのPageサイズをデフォルトの16KBから、もっと大きな値にする（最大で64KBまで指定可能）。
　　（64KBにすることで制限は8KBから32KBに緩和される。）

　MySQLを自身でコンパイルして、その際のコンパイルオプションの指定で、8000バイトの上限設定の元となっているページサイズを増やそうとするアイディア。
　悪くないようにも思えるんですが、あまり難しいことをやるとインフラ担当の人(別会社)に怒られるし、そもそもオレがよう分からんので、一旦却下でお願いしたいっす。

対処案３「可変ページサイズ」の利用

　MySQL5.6からページサイズを設定できるようになりました。これで現在の16KBからもっと大きなサイズに指定できれば！！

MySQL 5.6での機能強化点（その1） - パフォーマンスと使い勝手を大きく向上 | Think IT（シンクイット）

可変ページサイズ
従来はInnoDBのページサイズは16KBに固定されていました。InnoDBはページ単位でI/Oするため、ページサイズはパフォーマンスに影響を与える要因の1つとなっています。例えば、大量の行を一度に更新するようなUPDATE処理では、ページサイズが大きい方が多くの行にまとめてアクセスすることができるためI/O効率が良くなりますが、1行だけを更新するUPDATE処理では、ページサイズが小さい方が余分なデータが少なくなるためアクセス効率が良くなります。また、I/Oの特性はハードウェアによっても異なります。
HDDはランダムアクセスが苦手ですが、SSDはランダムアクセスが得意です。16KBというページのサイズは、ランダムアクセスが苦手なHDDを考慮し、1回のアクセスである程度まとまったサイズのデータをやり取りできるように設定されていました。しかし、ランダムアクセスが得意なSSDの環境では、ページサイズが小さい方が全体のスループットが向上する可能性もあります。そこで、MySQL 5.6ではページサイズをパラメータで変更できるようにしました。
ページサイズを変える場合は、インスタンス作成時に以下のパラメータを設定しておきます。

innodb_page_size=4k

※設定可能な値は4k、8k、16k（デフォルト値は16k）詳細はこちらを参照下さい。

※設定可能な値は4k、8k、16k（デフォルト値は16k）詳細はこちらを参照下さい。
※設定可能な値は4k、8k、16k（デフォルト値は16k）詳細はこちらを参照下さい。
※設定可能な値は4k、8k、16k（デフォルト値は16k）詳細はこちらを参照下さい。
※設定可能な値は4k、8k、16k（デフォルト値は16k）詳細はこちらを参照下さい。

　最大で16KBしか指定できないなら意味無いじゃん！！！

対処案４「Barracuda」の利用

　MySQL5.5から導入されている「Barracuda」というフォーマットは、ページの外に情報を保存するため、8000バイトという制限に引っ掛かりません。

漢(オトコ)のコンピュータ道: MySQL 5.5新機能徹底解説

ちなみに、InnoDB Plugin 1.0で追加された新しいフォーマットであるBarracudaでは「最大行サイズ8KBまで」という制限が緩和されている。
(中略)
InnoDB Plugin 1.0のBarracudaフォーマットでは、通常の領域の残るのがヘッダの20バイトだけであり、データは全て別の領域に格納されるようになった。従って、8KB÷20=400個のBLOBを利用しなければ8KBの制限に引っかかることはない。実質的に問題が解消したと言っても良いだろう。ただし、Barracudaは既存の（過去のバージョンの）InnoDBとファイルの互換性がなくなるし、利用するときはinnodb_file_per_tableオプションを利用しなければならないので注意が必要だ。使い方は先程紹介したエントリに載っているので、そちらを参照して頂きたい。

　非常に良いなぁと思うんですが、現在のMySQLのバージョンは5.1系。バージョン移行には当然のように壮絶な準備と計画が必要なので、一朝一夕に採用！というわけにはいかないです。

対処案５地道に対象テーブルを見つけ出してメンテナンス

　「銀の弾丸は無い」の格言にあるように、手軽な対応策を探すのを諦めて、地道にテーブルメンテナンスで対処する、という方法です。

　具体的には

エラーが発生したレコードのあるテーブルを特定 (※ 実はまだエラーが発生したテーブルを特定できてない。テーブル数は100〜200くらい、のはず)
対象テーブルを参照・更新しているプログラムを特定
テーブルを分割し、それに合わせてプログラムも改修
エラーが無くなるまで上記1〜3を繰り返す

ってなかんじのことをやります。

　……めんどくさいけど、しょーがないっすね。

対処案６ mysqldumpでのバックアップを止める

　今はmysqldumpで出力したものをバックアップとして残しているのですが、それを止めて、MySQLのデータファイル自体を圧縮してキープする、という方法です。この方法は担当案件では実績があり、本番環境→検証環境にデータコピーする時なんかは、rsyncでディレクトリごとコピーしてゴニョゴニョする、なんてことをやっています。
　ただ、この方法はコピー元とコピー先が同じMySQLバージョンだからこその方法なので、途中で採用するMySQLのバージョンを上げた時に使い物になるかどうかが定かではありません。バックアップファイルのサイズも大きくなりそうだし、あんまり現実的じゃないかなぁ。

結論

　上記で列挙した方法のうち、４と５の複合で対処していくのがいいかなぁ、と今は考えています。とにかくバックアップが役に立たない現在のような状態を早く解決しなくちゃいけないんですが、そうは言ってもスレーブDBも複数あるし、焦らず確実な方法を選択したいです。まずは現状調査からですね、そもそも今現在データがMySQLにちゃんと入っているのに、mysqldumpに出して再度データ突っ込もうとしただけでエラーが出るのも気になるし。実は対象テーブルがMyISAMなんじゃねーの？と疑ってもいます。

　実は元々MySQLのバージョンアップは年内にやらなくてはいけなかったんですが、今回の件でMySQLの5.1→5.6のバージョンアップの方法も色々調査できたので、ちょうど良かったかなぁとは思っています。これを機にバージョンアップも含めて進めていきたいです。

参考資料

　今回調査するにあたって、以下の記事を参考にさせて頂きました。本件に直接関係ないものもありますが、オレ自身MySQLへの理解が非常に乏しかったので、とても勉強になりました。多謝。

ミッションたぶんPossible

どこにでもいるシステムエンジニアのなんでもない日記です。たぶん。