"Index column size too large. The maximum column size is 767 bytes." への対応

外に借りているVPSサーバーにmariadbが入れてある(何を隠そう、このサイトは、このVPSに載せてある。格安でありながら、結構使い勝手がいいので気に入っている)。ただ、バージョンは古く、MariaDB-server-5.5.60である。10.3とかにすればいいのかもしれないが、問題は、先に作ったtwitterの頻度データをそこに載せる必要があったのだが、パソコンで作ったデータベースをdumpして、移行しようとしたら、リストアするときに、

Index column size too large. The maximum column size is 767 bytes

というエラーで止まってしまうのだ。インデクスのサイズが小さすぎるらしいが、ネットにあるいろいろな対応を試みたが、うまくいかなかった。

最終的にどうしたかだけ、いや、多分この操作が良かったのだろうというところだけ書いておく。

ちなみにVPSは、CentOs の6.9あたりが入っている。これもあまり新しくない。今更、バージョンアップするのも面倒だ。

さしあたって、https://blog.cles.jp/item/9965に書いていただいているような対応をする(参考にさせていただいて感謝します)。要は、/etc/my.cnf.d/server.cnfに、

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = on
innodb_default_row_format = DYNAMIC # この設定が効かない場合には以下を参照

を書き加えるのだ。が、私の場合、最後の文章を入れてmariadbを再起動すると、失敗した。したがって、これは、入れることができない。ありがたいことに、それがダメな場合はというコメントで、以下のようにするといいと書いてある。

CREATE TABLE `hogetable` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

ところが、私の場合、テーブルを作るのではなく、ダンプしたものをリストアするのであるから、このような、テーブルを作るプロセスそのものはない。ダンプの仕方を色々変えてもやってみた。indexを抜いた形でdumpして、リストアして、その後、indexを作成したりもしたが、結局同じエラーが出る。

indexを抜けば、普通にリストアできるが、index抜きで使ったら数百万のデータを検索する時間は、ほぼ使い物にならないくらいかかってしまう。

結局、150メガもあるだぷファイルの中身を見ることにした。すると、それはテキストファイルで、全てのデータベース操作がそこに書いてあるものなのだ。そして冒頭に、次のような、テーブルを作成するコマンドが書かれていた。

CREATE TABLE `tweetedwords` (  `word` varchar(200) NOT NULL,  `part` varchar(5) NOT NULL,  `frequency` int(15) NOT NULL,  KEY `twitterword` (`word`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

なんだ、そうか!という感じだ。ここに、先に教えてもらった ROW_FORMAT=DYNAMICを書き込んだら、見事、リストアできた。つまり

CREATE TABLE `tweetedwords` (  `word` varchar(200) NOT NULL,  `part` varchar(5) NOT NULL,  `frequency` int(15) NOT NULL,  KEY `twitterword` (`word`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

と変更しただけである。

良かった、良かった。