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:

<span class="pln">mysql</span><span class="pun">></span> <span class="kwd">CREATE</span> <span class="kwd">USER</span> <span class="str">'root'</span><span class="pun">@</span><span class="str">'%'</span><span class="pln"> IDENTIFIED </span><span class="kwd">BY</span> <span class="str">'root'</span><span class="pun">;</span><span class="pln">
mysql</span><span class="pun">></span> <span class="kwd">GRANT</span> <span class="kwd">ALL</span><span class="pln"> PRIVILEGES </span><span class="kwd">ON</span> <span class="pun">*.*</span> <span class="kwd">TO</span> <span class="str">'root'</span><span class="pun">@</span><span class="str">'%'</span> <span class="kwd">WITH</span> <span class="kwd">GRANT</span> <span class="kwd">OPTION</span><span class="pun">;</span>

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)


Share this article

Leave a Reply

Your email address will not be published.

Scroll to Top