MySQL 5.7.6でCREATE USERせずにGRANTステートメントを叩くとワーニング

ワーニングが出るようになってますね。


「sql_modeのデフォルトにNO_AUTO_CREATE_USERを設定しようと思う」っていうネタがMorgan Tockerのブログにあがってましたのでその布石でしょうか。

mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode                                                    |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO replicator IDENTIFIED BY 'replicator';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR replicator;
+----------------------------------------------------+
| Grants for replicator@%                            |
+----------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' |
+----------------------------------------------------+
1 row in set (0.00 sec)

出来てるには出来てるけど、SHOW GRANTSの結果にパスワードハッシュ出さなくなったのか。。

mysql> SELECT user, host, authentication_string FROM mysql.user WHERE user= 'replicator';
+------------+------+-------------------------------------------+
| user       | host | authentication_string                     |
+------------+------+-------------------------------------------+
| replicator | %    | *E6FA4B5E283079702D46597DFCD2766E7BF96B0E |
+------------+------+-------------------------------------------+
1 row in set (0.22 sec)

mysql.userテーブルにはちゃんと入ってる。

しかし 同じノリでproposalに上がってる NO_AUTO_VALUE_ON_ZEROの方は

mysql> CREATE TABLE t1 (num serial);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+-----+
| num |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

特に何のお咎めもなし。
どちらかというとこっちワーニング吐いてほしいんだけどな。。

NO_AUTO_VALUE_ON_ZEROが有効な状態だと

mysql> TRUNCATE t1;
Query OK, 0 rows affected (0.18 sec)

mysql> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'NO_AUTO_VALUE_ON_ZERO');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------+
| @@sql_mode                                                                          |
+-------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+-----+
| num |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

とまあこんな風にauto_incのつもりでNULLじゃなく0を当てると本当に0が入ってしまうという(そんな風に書かれたコードが実在したとしたらこっちの方が余程影響でかい)
昔はauto_incrementなカラムに敢えてNULLじゃなくて0を渡すコードを 書いていた ことがあるんだけど、最近の人はどうなんだろう。

ちなみにNO_AUTO_CREATE_USERな状態でもIDENTIFIED BYがない場合にだけがエラーになるはずだから…と思ってたら。

mysql> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'NO_AUTO_CREATE_USER');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                              |
+---------+------+------------------------------------------------------------------------------------------------------+
| Warning | 3090 | Setting sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be made read-only in a future release. |
+---------+------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT ALL on *.* to yoku;
ERROR 1133 (42000): Can't find any matching row in the user table

mysql> GRANT ALL on *.* to yoku@localhost identified by '0825';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

今のところ動作は変わってないけど、NO_AUTO_CREATE_USER自体が廃止されて、GRANTステートメントでCREATE USERされちゃうのは完全に廃止になるぽい。