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:
None 
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
Description:
For a system that has 5.7.10 on I noticed the missing mysql.sys@localhost user.

The cause of this "system" user being missing is not clear but I wanted to fix this so ran mysql_upgrade again and was told that I'd need to use -f as it had already been run.

However, running mysql_upgrade -f did not create the missing user. (The sys schema does exist).

How to repeat:
See above.

Suggested fix:
Given sys is part of the default mysql 5.7 installation (though it can not be installed if necessary) I would suggest that if the database exists and the expected views/stored procedures etc exist then if the user does not exist it should be recreated with default permissions.

If a DBA does not want this user to be reachable (it's locked already) then any existing grants can be dropped leaving just USAGE, and these grants probably should not be changed (though it's wise not to touch this sort of thing for a system user.
[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)