10年以上前のホームページは、得てしてMYSQL4.0を使用しているケースがあります。
最近サーバーから「そろそろMYSQLのバージョンを上げてください。」というようなメッセージがあり、MYSQL4.0→MYSQL5.7へデータを移動させることにしました。
最初は簡単だろうと思いましたが、思いのほかはまりましたので、忘備録として記録いたします。
まずは通常通り4.0エクスポート→5.7インポートをしてみたらエラーになりました。
MYSQLのデータを移動させる手段として、私はphpMyAdminでエクスポートし、対象のMYSQL側のphpMyAdminでインポートします。
そもそもこれだけで済めばらくちんです。
ということで、特に何もさわらずやってみました。
MYSQL5.7のphpMyAdminにはインポートに互換指定があったので、MYSQL40を指定してインポートしてみました。
すると・・
読み込みできませんという旨のエラーメッセージが表示されました。
具体的なエラーが出ているので、ひとまずエクスポートしたSQLの該当箇所を見てみることにしました。
エクスポートしたSQLをテキストエディタで開こうとすると文字コードエラーで文字化け
私は、テキストエディターは秀丸を使っています。
文字コード（Shift-jis、UTF-8等）に対応しているので、MYSQLなど文字コードがシビアなデータには向いています。
MYSQL4.0からエクスポートしたSQLを秀丸で開いてみたところ、次のようになりました。
エクスポートしたデータはUTF-8なので、デフォルトShift-jisの秀丸ではエラーになります。
そこで、UTF-8に設定したところ、さらに文字コードエラーになりました。
つまり、文字化け状態でしか開けないということです。
無視してやればいいような気もしますが、もし肝心のデータ自体が文字化けしていたら、うまく読めたとしても文字化け状態でデータが入ります。
それは具合が悪いと思い、次の手順でMYSQL4.0のデータを再度エクスポートしなおしました。
注意！
この現象は、MYSQLデータの中に「Shift-JIS文字」があったために起こった現象です。
全ての文字がUTF-8であれば、この部分は省略してもOKです。
MYSQL4.0はSJISでエクスポートする
phpMyAdminには、エクスポートする際のオプション設定があります。
そこで、SJISを選択してエクスポートしました。
これならば、エディタで文字化け問題が発生しません。
SJISでエクスポートしたデータはすぐにUTF-8に再変換する
あえてSJISで出力しておいてなんですが、MYSQL5.7は大抵UTF-8ベースです。
そのため、エディタを使ってSJIS→UTF-8変換しておきます。
テキストエディタでToo big precision 14 specified for ‘colum名’ Maximum is 6を修正する
文字化けしない状態にしたエクスポートSQLをエディタで開き、エラーメッセージの該当箇所を直そうと思いました。
そのときに、そもそも「Too big precision 14 specified for ‘colum名’ Maximum is 6」って何エラー？
と思い、翻訳してみました。
エラーメッセージって、翻訳すると意外に問題がわかりやすくなります。
エラーメッセージを翻訳してみると、timestamp(14)の記述がMYSQL5.7ではエラーになるようです。
そこで、timestampだけにしました。
timestamp(6)にする方法もあるようですが、かっこがなくてもOKでした。
今度は謎エラーが発生
文法エラー的なものを修正後、MYSQL5.7のphpMyAdminで再度インポートしてみました。
すると、今度はこのようなエラーがでました。
何エラーかわからないエラーが出ました…。
詰んだ・・・。
そんな感じがしましたが、ここでかつての経験から気になる個所を見つけました。
それがこちらです。
コメント行だから一見関係ないように思えましたが、以前EC-CUBE4の対応で、コメント行を直すと反映される事例があったため、ピンときました。
これは単にphpMyAdmin内で文字コード不一致による誤動作が発生しているのではないかと。
つまり、UTF-8の文字コードでShift-JISとして登録しようとしているのではないかと思いました。
事実、この部分を修正したことで謎エラーは消えました。
一見コメントのようで、重要な意味のある記述でした。
TYPE=MyISAMの記述をなおす
SET NAMESをUTF-8に変更し再度MYSQL5.7にインポートしてみました。
すると今度は次のようなエラーがでました。
MyISAMは、今となっては古いMYSQLの型になります。
いまではInnoDBを指定するのが主流です。
ということで、この部分の記述を次のように変更しました。
この部分の記述は、テーブル分あったので、全てのテーブルの記述をなおしました。
ついでに、DEFAULT CHARSETも追加しました。
これは、MYSQL5.7のphpMyAdminでエクスポートしたところ、SQLにDEFAULT CHARSETが記述されていたため、それにならい追記しました。
今度こそと思いインポートしてみました。
ようやくうまくインポートできました。
インポートして、エラーが出て、調べて、修正してを繰り返しようやく正常にインポートできました。
なかなか思うようにいきませんでしたが、MYSQL4.0→MYSQL5.7へのデータ移動実績を作ることができました。
もしかしたらSQL内容によっては、さらなるSQL修正が必要になると思います。
またMYSQL5.7へインポートできても、ＰＨＰ等のプログラム側が古く、MYSQL5.7の読み書きに対応するために、プログラム側も直す必要があるかもしれません。
いずれにせよ、古い代物は互換性が低いということがわかりました。
MYSQL4.0→5.7へデータ移動するためのまとめ
今回の要点を以下に記載いたします。
- MYSQL4.0のエクスポートは「SJIS」で行う
- エクスポートしたSQLはテキストエディタ等で「UTF-8」に変換する
- SET NAMESの記述を「utf8mb4」等データベースの指定コードにする
- timestamp(14)はtimestampに書き換える
- 「TYPE=MyISAM」は「TYPE=InnoDB」に書き換える
場合によっては、これだけで対応できないこともあると思われますが、今回はこの内容で移し替えすることができました。
またご参考になれば幸いです。