MySQL user privileges lost on reboot

Question

On occasion our MySQL 5.6 server loses some user privileges when restarted. The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running. After a restart of MySQL, the user record still exists but the privileges are missing. In phpMyAdmin User Accounts, the user appears with global privileges of "Usage". The expected SELECT permission is missing.

Users and privileges are created using:

CREATE USER 'userName'@'hostName' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'userName'@'hostName';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `dbName` . * TO 'userName'@'hostName';

What could be causing the loss of privileges? Is there something else that should be executed in addition to these commands to force MySQL to persist the permissions?

• v.oddou: same issue here. I'm thinking of a missing flush permissions;? or some commit ? we're not alone in this: stackoverflow.com/questions/41645309/…

Answer 1

This is a strange behaviour! Once user is created you can make sure of that by reviewing the grants for it.

show grants for 'username'@'hostname';

Privileges are persistent, you don't need any additional step to make them stay.

Check if my.cnf has something like --init-file or --skip-grant-tables. Also, how mysql is started? Does your root password get reset as well? Are you sure you're connected to the right mysql?

• Corin: The root password is not reset and most of the other users are also unaffected. To the best of my knowledge I am connecting to the correct server.
• mysql_user: Can you create a user > check show grants for it > Connect from another prompt > do show grants again > restart mysql > verify if issue repeats? > Do you see anything in error log? Can you repeat and provide steps? Does this happen if you do it outside phpmyadmin and using say mysql commandline client? I'd post bug report in this case!!!
• Corin: Nothing in the my.cnf looks like the init-file or skip-grant-tables options. We're starting mysql using mysqld_safe.
• Corin: I'll try to remember to do something with show grants before we restart the service next time. So far this only occurs sometimes on the production server and never in test environments. :(
• simplifiedDB: This is strange. Have you tried above steps ? Is this happening only when you create user via phpadmin ? Can you please create user via mysqlcli and restart mysqld and share results