SHOW CREATE USER syntax creates non-creatable users

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Ran show create user, and pasted the output back to the shell:

show create user 'root';
CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

The command show create user was added in #9240 , but unfortunately it creates a MySQL 5.7 compatible output which TiDB does not yet parse. See also: pingcap/parser#158 for matching parser bug.

2. What did you expect to see?

Pasting the output create user in MySQL 5.7 works fine:

mysql [localhost:5725]> CREATE USER 'abcd'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

3. What did you see instead?

mysql> CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 76 near "REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK" 

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.0-beta-118-g9afbda98d
Git Commit Hash: 9afbda98d0c52a964c4a920bc259e7b5b25bc7a4
Git Branch: master
UTC Build Time: 2019-02-26 04:19:57
GoVersion: go version go1.11.3 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

@lnhote are you interested in fixing? :-)

[lnhote]

@lnhote are you interested in fixing? :-)

Yes I will investigate and try to fix it.

[jackysp]

I think it is already been fixed.

mysql> show create user 'root';
+--------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@%                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
ERROR 1105 (HY000): Duplicate user
mysql> CREATE USER 'abc'@'%' IDENTIFIED WITH 'mysql_native_password' AS '' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
    -> ;
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

but there is another issue that TiDB require the password generated by password() function. See #5948

[jackysp]

Fixed by pingcap/parser#269.