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)