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)