新年、サーバー移動に伴うMySQL文字化けで困っていた人の手助けをしたときのメモ

これは2019年早々早朝から受けた相談

文字コードについて詳しくないけど割と楽しんで形にしたときのメモ

◆条件(相談を受けて途中から介入したので正しい情報か不明)

・移動元/移動先共にcharacter_set_serverはbinary

・移動元DBの文字コード:Shift-jis、しかし1つのテーブルのdefaultで登録してる文字はUTF-8

◆問題点

・移動元DBで伝統的に使ってるであろうdumpスクリプトを使用して移動先でリストアしたら文字化けや日本語データの抜け落ちが発生していた。
(そのスクリプトはテーブルの文字コードをnkfでUTF-8へ変換してるらしい)
移動元でmysqldumpコマンドを実行してそれを移動先でリストアしたら
ERROR 1067 (42000) at line NN: Invalid default value for 'ZZZZZZ'
と出てリストアに失敗する。

◆結論

dumpコマンドで作成したdumpファイルの中で問題となっているテーブルのDEFAULT CHARSETをsjisからbinaryへ変更した後、restoreを実施した。

◆過程

テーブル構造だけでも見たかったのでdumpファイルの文字化けしてる日本語を削ってrestoreした。

そこからshow create tablesをすると下記のような構造だった。

※文字化けしてたオリジナルの内容はその人の秘密を守るって意味で変更

table_name | CREATE TABLE `table_name` (
`ID` int(11) NOT NULL,

`XXXXXX` varchar(255) NOT NULL,
`YYYYYY` varchar(255) NOT NULL,

`ZZZZZZ` varchar(512) DEFAULT '【日本語】',　<-元はこんな感じで入っていた日本語を削除した

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=sjis |

ちなみに、desc table_nameした結果

| ID | int(11) | NO | PRI | NULL | |

| ZZZZZZ | varchar(512) | YES | | 【日本語】| |

次にselect * from table_name limit 1;すると中身は文字化けしていたがTeraTermの文字コードをUTF-8からShift-JISへ変更すると中身は見えた。

この状態でshow create tablesをすると当然、defaultの「【日本語】」が文字化けで見えなかった。

このことからテーブルのデフォルトで入力される日本語はUTF-8、テーブルの中のデータはShift-JISであることを予想。

昔、先輩が困ったときはdumpのときにnkfでUTF-8に全部変換してそれをbinaryで展開すれば良いと助言貰ったことがあったがそれで解決する根拠がよく想像できなかったので良い機会だったので実際試してみた。

多分、日本語が化けるか最悪無視されてリストアされるんじゃないだろうかと思っていた。

結果は、show create tableをUTF-8で見る分には問題なかったが、selectしたらやはり日本語の部分だけデータが飛ばされていた。

色々考えて出たのが結論にもあるとおりdumpファイルのDEFAULT CHARSETをbinaryにしてrestoreすると形式的には入るのではないだろうかということ。

sed -e "NN s/sjis/binary/g" /home/momo/tmp/table_name_dump.sql > /home/momo/tmp/table_name_dump_change.sql

mysql -p --default-character-set=binary table_name < /home/momo/tmp/table_name_dump_change.sql

すると狙い通り、データの移動を元と同じ状態で見えるようになった。

あとはテーブルの型がvarbinaryになってるのをvarcharに変更すると完全に元通りになりそう。

テーブルdumpしてそのファイルをsedでvarbinaryからvarcharへ変換。

mysqldump db_name -p --default-character-set=binary --tables table_name > hoge.sql

sed -i 's/varbinary/varchar/g' hoge.sql

これをリストアしたらいけるかなと思ったがvarbinaryのままだった。ここで時間切れとなってしまった。

残る課題として型はvarbinaryとなってるのでそれをどうvarcharへ戻すかというとこである。

momoの手帳