user password options not compatible with MySQL

[dveeden]

Bug Report

To migrate from MySQL 8.x to TiDB one might copy user accounts by running SHOW CREATE USER ... on MySQL and executing the result on TiDB. However this fails due to a mismatch in what MySQL produces and what TiDB accepts.

More info:

• MySQL 8.0 Compatibility #7968 (See the entry for 8.0.13)
• https://dev.mysql.com/doc/refman/8.0/en/create-user.html#create-user-password-management
• https://dev.mysql.com/worklog/task/?id=11544
• https://dev.mysql.com/blog-archive/mysql-8-0-13-change-current-password-policy/

1. Minimal reproduce step (Required)

On MySQL 8.0.37

CREATE USER 'test1'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'secret';
SHOW CREATE USER 'test1'@'%';

Then on TiDB execute what MySQL produced:

CREATE USER `test1`@`%` IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

2. What did you expect to see? (Required)

Query OK, 0 rows affected (0.00 sec)

3. What did you see instead (Required)

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 266 near "REQUIRE CURRENT DEFAULT" 

4. What is your TiDB version? (Required)

mysql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv

And also today's master branch.