2018/07/25

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" ）

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

吊るしの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 が設定されていること
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 を有効にしてやればおｋ。
​
時刻: 3 件のコメント :

2018/07/12

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の話は↓の記事。
ロールを作ってから
  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とそれ以前を使っている場合は注意。。

​
時刻: 0 件のコメント :

2018/07/02

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

TL;DR

MySQLの中の人が最近（？）ちょくちょく推してる Upgrade Checker だけど、MySQL Shellの機能なのでX Plugin必須かなと思ってちょっと敬遠していたのだけれど。
checkForServerUpgrade() can use either an X Protocol connection or a classic MySQL protocol connection.
ってなことで実は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
...
よいこはちゃんとドキュメントを読もう、という話で終わっちゃいそうだけれど、折角書き下したのでちょっと紹介。
シンタックスが気にくわなくて直したのはいくつかあるけれど、割と素直に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");
予約語が使われているオブジェクトがないか、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 だけ可変だけど他は固定）が貼ってあるのでそれでも雰囲気は感じ取ってもらえるのではないか。
なおこの get_reserved_keywords_check は「新しく予約語になったもの」が使われていないかを探すけれど、「キーワードから予約語になって俺のスキーマを殺した row 」とかは入っていないので注意。
プルリクチャンス？

 【2018/07/06 14:59】
フツーに入ってました。。
​
時刻: 0 件のコメント :