MySQL 8.0でLOAD DATA LOCAL INFILEが "ERROR 1148 (42000): The used command is not allowed with this MySQL version" または "Error: 3948. Loading local data is disabled; this must be enabled on both the client and server sides" で失敗する時

TL;DR

【2020/02/18 14:23】

MySQL 8.0.19とそれ以降ではエラーメッセージが "Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides" に変わりました（それ以前は "ERROR 1148 (42000): The used command is not allowed with this MySQL version" ）

エラーメッセージの出力例以外はこの記事の内容そのままで対処できると思います。たぶん。

• --local-infile のデフォルトが0になったので明示的に1を指定してやる必要がある
• mysqld, mysql コマンドラインクライアント それぞれ別に --local-infile オプションを持っているので、両方1に指定しないとダメ。
• MySQL :: MySQL 8.0 Reference Manual :: 6.1.6 Security Issues with LOAD DATA LOCAL

---

吊るしのMySQL 8.0で mysql コマンドラインクライアントから LOAD DATA LOCAL INFILE を実行すると転けます。

mysql80 125> LOAD DATA LOCAL INFILE '/tmp/aaa' INTO TABLE t1;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

LODA DATA LOCAL INFILE を実行するには2つの条件が必要で、

1. LOAD DATA LOCAL INFILE を実行するコネクションに CLIENT_LOCAL_FILES ケーパビリティー（オプションだと思って）が設定されていること
2. サーバー側で opt_local_infile が設定されていること

mysql-server/sql_load.cc at mysql-8.0.11 · mysql/mysql-server

2. は mysqld の local_infile オプションなのでわかりやすい。単にデフォルトが5.7とそれ以前の “1” から8.0では　“0” に変わったというだけ。
再起動しなくても SET GLOBAL や SET PERSIST で設定できる。

mysql80 125> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql80 125> SET PERSIST local_infile= 1;
Query OK, 0 rows affected (0.00 sec)

mysql80 125> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

サーバー側（ 2. ）だけ満たされていても、コネクションに CLIENT_LOCAL_FILES ケーパビリティー（ 1. ）はついてないのでやっぱり転ける。
同じエラーなので見分けにくい。

mysql80 125> LOAD DATA LOCAL INFILE '/tmp/aaa' INTO TABLE t1;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

1. が満たされているかどうかをgdbを使わずに確かめる方法が見当たらなかったのだけれど、 mysql コマンドラインクライアントであれば —local-infile オプションを有効にするとこのケーパビリティーのフラグが立つ。ただし接続しながら変えることはできないのでこっちは切断してから再接続する。

$ mysql80 --local-infile=1
mysql80> use d1
mysql80 132> LOAD DATA LOCAL INFILE '/tmp/md5' INTO TABLE t1;

サーバーサイドのlocal_infileとクライアントサイドのlocal_infileがそれぞれ別で、それぞれ暗黙のデフォルトが0になったから両方で指定しないといけないよ、というお話でした

ちなみにConnector/Cなら mysql_real_connect を呼ぶときに client_flag に CLIENT_LOCAL_FILESを立てるか、 mysql_options で MYSQL_OPT_LOCAL_INFILE を有効にしてやればおｋ。

​

CREATE USER .. DEFAULT ROLE .. で指定すると一発でROLEも許可される

TL;DR

mysql> CREATE USER hoge@xxx.xxx.xxx.xxx IDENTIFIED BY 'password';
mysql> GRANT hoge_role TO hoge@xxx.xxx.xxx.xxx;
mysql> ALTER USER hoge@xxx.xxx.xxx.xxx DEFAULT ROLE hoge_role;

と

mysql> CREATE USER hoge@xxx.xxx.xxx.xxx IDENTIFIED BY 'password' DEFAULT ROLE hoge_role;

は同じ状態になる。
当たり前といえば当たり前なんだけど、ちょっと感動したのでメモ。

---

MySQL 8.0で追加されたROLEの話は↓の記事。

日々の覚書: MySQL 8.0.0で追加されたROLEの仕組み

ロールを作ってから

1. ユーザー作る
2. ロールを許可する
3. デフォルトロール設定する
   でやらないといけないのかなと思ってたら一発で指定できた。

5.7とそれ以前

mysql> CREATE USER hoge@xxx.xxx.xxx.xxx IDENTIFIED BY 'password';
mysql> GRANT ALL ON hogedb.* TO hoge@xxx.xxx.xxx.xxx;
mysql> GRANT ALL ON fugadb.* TO hoge@xxx.xxx.xxx.xxx;

mysql> CREATE USER hoge@xxx.xxx.xxx.yyy IDENTIFIED BY 'password';
mysql> GRANT ALL ON hogedb.* TO hoge@xxx.xxx.xxx.yyy;
mysql> GRANT ALL ON fugadb.* TO hoge@xxx.xxx.xxx.yyy;

• APサーバーが増えるたびに CREATE USER + GRANT * スキーマ数がめんどい
  • スキーマが増減した時とか死にたくなる
    • そして mysql.db の直接変更に手を出す…
• 「おとなしく hoge@xxx.xxx.% にしたらいいのでは？」「それな」

8.0

mysql> CREATE ROLE hoge_role;
mysql> GRANT ALL ON hogedb.* TO hoge_role;
mysql> GRANT ALL ON fugadb.* TO hoge_role;

mysql> CREATE USER hoge@xxx.xxx.xxx.xxx IDENTIFIED BY 'password' DEFAULT ROLE hoge_role;
mysql> CREATE USER hoge@xxx.xxx.xxx.yyy IDENTIFIED BY 'password' DEFAULT ROLE hoge_role;

• 楽だ…

【2020/07/30 20:23】

ただし残念ながらこんなバグがあった…8.0.17とそれ以前を使っている場合は注意。。

日々の覚書: MySQL 8.0.17とそれ以前では、CREATE USER .. DEFAULT ROLE ..構文を使うと、ロールの情報が正しくレプリケーションされない

​

MySQL ShellのUpgrade CheckerをPerl 5に書き下してみたけどそんなことする必要はなかったようだ

TL;DR

• lefred や MySQL Server Team もすなる checkForServerUpgrade といふものを、yoku0825もしてみむとしてするなり。
• オリジナルはMySQL Shellのjsモードで使うものらしく、 X Pluginをインストールしなければいけない X PluginなしのClassic Protocol + jsモードでもイケた 。これを書いている最中に気が付いた…。
• それもアレだし、どんなことをチェックして良し悪しを判断してるのかも気になったのでPerl 5で書き下してみたのがこちら。
  • p5-mysql-upgrade-checker/p5-mysql-upgrade-checker.pl at master · yoku0825/p5-mysql-upgrade-checker

---

MySQLの中の人が最近（？）ちょくちょく推してる Upgrade Checker だけど、MySQL Shellの機能なのでX Plugin必須かなと思ってちょっと敬遠していたのだけれど。

• How to safely upgrade to MySQL 8.0 ? – lefred’s blog: tribulations of a MySQL Evangelist
• MySQL Shell 8.0.4: Introducing “Upgrade checker” utility | MySQL Server Blog
• INPLACE upgrade from MySQL 5.7 to MySQL 8.0 | MySQL Server Blog
• Upgrading to MySQL 8.0? Here is what you need to know… | MySQL Server Blog

checkForServerUpgrade() can use either an X Protocol connection or a classic MySQL protocol connection.

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

ってなことで実はPerlに打ち直す必要なんてなかったことがこの記事を書いている最中に判明した。かなしい。

$ mysqlsh -S /usr/mysql/5.7.22/data/mysql.sock -uroot --mysql
Creating a Classic session to 'root@/usr%2Fmysql%2F5.7.22%2Fdata%2Fmysql.sock'
Enter password:
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 77
Server version: 5.7.22-log Source distribution
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.11

 MySQL  localhost  JS > util.checkForServerUpgrade();
The MySQL server at /usr%2Fmysql%2F5.7.22%2Fdata%2Fmysql.sock will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.22-log - Source distribution
...

よいこはちゃんとドキュメントを読もう、という話で終わっちゃいそうだけれど、折角書き下したのでちょっと紹介。

p5-mysql-upgrade-checker/p5-mysql-upgrade-checker.pl at master · yoku0825/p5-mysql-upgrade-checker

シンタックスが気にくわなくて直したのはいくつかあるけれど、割と素直にSQLを移植しただけなのでSQLごとにちょっと感じが違うのはある。
あと、本家はワーニングとエラーと分けられているけれど面倒だったので全部 Test::More でOKかNGかしか返さなくしちゃった。

チェック全体の流れとしてはざっとこんな感じ。

  ok(get_reserved_keywords_check(),
     "Usage of db objects with names conflicting with reserved keywords in 8.0");
  ok(get_utf8mb3_check(), "Usage of utf8mb3 charset");
  ok(get_zerofill_check(), "Usage of use ZEROFILL/display length type attributes");
  ok(Check_table_command(), "Issues reported by 'check table x for upgrade' command");
  ok(get_mysql_schema_check(), "Table names in the mysql schema conflicting with new tables in 8.0");
  ok(get_old_temporal_check(), "Usage of old temporal type");
  ok(get_foreign_key_length_check(), "Foreign key constraint names longer than 64 characters");
  ok(get_maxdb_sql_mode_flags_check(), "usage of obsolete MAXDB sql_mode flag");
  ok(get_obsolete_sql_mode_flags_check(), "get_obsolete_sql_mode_flags_check");
  ok(get_partitioned_tables_in_shared_tablespaces_check(), "Usage of partitioned tables in shared tablespaces");
  ok(get_removed_functions_check(), "get_removed_functions_check");

https://github.com/yoku0825/p5-mysql-upgrade-checker/blob/master/p5-mysql-upgrade-checker.pl#L110-L121

予約語が使われているオブジェクトがないか、utf8mb3（3バイトUTF-8）が使われているオブジェクトはないか、zerofill属性を期待している（ように思われる）データ型はないか、 CHECK TABLE .. FOR UPGRADE は通るか、8.0で新しく追加されたテーブルと競合するオブジェクトはないか、5.5とそれ以前までで使用されていた古い形式のDATETIME/TIMESTAMP型（マイクロ秒非対応の古いデータ型）はないか、64文字を超える外部キー制約はないか、古い複合sql_modeが使われているオブジェクトはないか、ibdata1に格納されているパーティションはないか、削除された関数を使っているストアドやgenerated columnはないか、をチェックしている。

具体的なSQLは、 p5-mysql-upgrade-checker を --execute なしで実行するか、general_log = ON にしてMySQL Shellから実行などすると良いと思われる。

あとはREADMEにサンプル（ CHECK TABLE .. FOR UPGRADE だけ可変だけど他は固定）が貼ってあるのでそれでも雰囲気は感じ取ってもらえるのではないか。

https://github.com/yoku0825/p5-mysql-upgrade-checker#example

なおこの get_reserved_keywords_check は「新しく予約語になったもの」が使われていないかを探すけれど、「キーワードから予約語になって俺のスキーマを殺した row 」とかは入っていないので注意。

プルリクチャンス？

【2018/07/06 14:59】
フツーに入ってました。。

​