fbpx

phpMyAdmin stops working after upgrade to MySQL version 8

After an upgrade to MySQL version 8 the users can’t login to phpMyAdmin anymore.

In troubleshooting the problem, you issue the following command:

mysql> grant all privileges on *.* to 'db_user'@'localhost' identified by "password" with grant option;

Instead of getting a success message, the following message is produced:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "password" with grant option' at line 1

This doesn’t make sense – that command always worked.

The reason why this happens is because starting with MySQL 8 you no longer can implicitly create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement. The two statements required to do this are:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

If you get this:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'localhost' WITH GRANT OPTION;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

Change localhost to %(but be aware of the security risks)

Reference

Share this article

Share on facebook
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top