Several months ago I reported on Default Users in MySQL 5.7. With the addition of the sys schema the server needs an extra user to secure operations. The problem with extra users is that, if you are not aware of their existence and why they are needed, you may easily mismanage them. For example, you may have a cleanup routine that you run when you use a new server, and the routine may have a command like
DELETE FROM mysql.user WHERE user != 'root';
This was good up to MySQL 5.6. Then the sys schema was added, and with it the user
mysql.sys, which may cause errors if you try to re-create views in the sys schema.
The latest user sneaking below the radar is
mysqlxsys. Like its predecessor, it comes well documented, and if only I had read all the manual I'd had known that this user must exist, or things won't work.
Here is how they did not work for me:
$ make_sandbox 5.7.12 -- -c plugin-load=mysqlx=mysqlx.so --no_show The MySQL Sandbox, version 3.1.05 (C) 2006-2016 Giuseppe Maxia loading grants .. sandbox server started Your sandbox server was installed in $HOME/sandboxes/msb_5_7_12
Check that the plugin is loaded
$ ~/sandboxes/msb_5_7_12/use -e "show plugins" |grep mysqlx mysqlx ACTIVE DAEMON mysqlx.so GPL
Check that the port is open
$ sudo netstat -atn |grep LISTEN|grep 33060 tcp4 0 0 *.33060 *.* LISTEN
Try a connection:
$ mysqlsh -h 127.0.0.1 -umsandbox -pmsandbox test mysqlx: [Warning] Using a password on the command line interface can be insecure. Creating an X Session to firstname.lastname@example.org:33060/test MySQL Error (1045): Unable to switch context to user mysqlxsys
This was my fault, of course. MySQL-Sandbox uses an aggressive cleanup routine, which was necessary up to MySQL 5.6, and less so in MySQL 5.7, but just for the sake of completeness it's still there. The routine removes all users that are neither
mysql.sys. In this case the installation failed because I was removing an user that was needed.
I am not alone in this affair. Also the MySQL Docker image does the same thing.
A fix is ready for both these issues. MySQL-Sandbox 3.1.08 is already available.