Bug #96062 Show create user changed after flush privileges
Submitted: 2 Jul 2019 9:12 Modified: 2 Jul 2019 9:55
Reporter: dave do Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.16, 8.0.11, 5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[2 Jul 2019 9:12] dave do
Description:
USER_RESOURCES related fields in the show create user statement results get changed after run a flush privileges command.

How to repeat:
mysql> CREATE USER IF NOT EXISTS CREATE_USER_148 IDENTIFIED BY 'Baidu+123' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTIONS 5 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> show create user CREATE_USER_148 \G
*************************** 1. row ***************************
CREATE USER for CREATE_USER_148@%: CREATE USER 'CREATE_USER_148'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$KU|ei[ADHx|3saGUBM4ADUk/7jLhR3WBwCfYPIda.dL1sBWK6Dc.bNL9' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTIONS 5 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> show create user CREATE_USER_148 \G
*************************** 1. row ***************************
CREATE USER for CREATE_USER_148@%: CREATE USER 'CREATE_USER_148'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$KU|ei[ADHx|3saGUBM4ADUk/7jLhR3WBwCfYPIda.dL1sBWK6Dc.bNL9' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTIONS 5 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.01 sec)

------------

MAX_UPDATES_PER_HOUR disappeared in the second result.
[2 Jul 2019 9:55] Umesh Shastry
Hello Dave do,

Thank you for the report and test case.
Observed that 8.0.16 release build is affected.

regards,
Umesh