Bug #79706 | mysql_upgrade does not create missing user needed by sys if missing. | ||
---|---|---|---|
Submitted: | 18 Dec 2015 16:08 | Modified: | 23 Dec 2015 9:15 |
Reporter: | Simon Mudd (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
Version: | 5.7.10 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | missing, mysql.sys, sys, user |
[18 Dec 2015 16:08]
Simon Mudd
[23 Dec 2015 9:15]
MySQL Verification Team
Hello Simon, Thank you for the report. Thanks, Umesh
[23 Dec 2015 9:20]
MySQL Verification Team
How to repeat: 1. Setup 5.7.8 instance (sys schema exists but 'mysql.sys'@'localhost' doesn't exists in 5.7.8) 2. Confirm that sys schema exists, but no user 'mysql.sys'@'localhost' present 3. Upgrade 5.7.8->5.7.10 - bring down 5.7.8, start 5.7.10 mysqld pointing to 5.7.8 datadir - run mysql_upgrade - Confirm that 'mysql.sys'@'localhost' is created during the upgrade process 4. To reproduce missing user issue, drop user 'mysql.sys'@'localhost', and re-run mysql_upgrade with --force as recommended - this time 'mysql.sys'@'localhost' is not created
[23 Dec 2015 9:20]
MySQL Verification Team
test results
Attachment: 79706.results (application/octet-stream, text), 9.86 KiB.
[29 Apr 2016 23:06]
monty solomon
To create the mysql.sys user drop the sys database and run mysql_upgrade mysql> show create user 'mysql.sys'@localhost\G *************************** 1. row *************************** CREATE USER for mysql.sys@localhost: CREATE USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK 1 row in set (0.00 sec) mysql> drop user 'mysql.sys'@localhost; Query OK, 0 rows affected (0.01 sec) mysql> show create user 'mysql.sys'@localhost\G ERROR 1396 (HY000): Operation SHOW CREATE USER failed for 'mysql.sys'@'localhost' mysql> drop database sys; Query OK, 101 rows affected (0.02 sec) The output of the command mysql_upgrade --verbose --force --write-binlog will contain mysql.user OK Upgrading the sys schema. mysql> show create user 'mysql.sys'@localhost\G *************************** 1. row *************************** CREATE USER for mysql.sys@localhost: CREATE USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK 1 row in set (0.00 sec)