MySQL 8 removes shorthand for creating user + permissions

Profile image of Mattias Geniar

Mattias Geniar, October 10, 2018

Follow me on Twitter as @mattiasgeniar

I used to run a one-liner for creating a new database and adding a new user to it, with a custom password. It looked like this:

mysql> GRANT ALL PRIVILEGES ON ohdear_ci.*
       TO 'ohdear_ci'@'localhost'
       IDENTIFIED BY 'ohdear_secret';

In MySQL 8 however, you’ll receive this error message.

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 'ohdear_secret'' at line 1

The reason appears to be that MySQL dropped support for this short-hand version and now requires the slightly longer version instead.

mysql> CREATE USER 'ohdear_ci'@'localhost' IDENTIFIED BY 'ohdear_secret';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL ON ohdear_ci.* TO 'ohdear_ci'@'localhost';
Query OK, 0 rows affected (0.15 sec)

If you have scripting in place that uses the short, one-liner version, be aware those might need changing if you move to MySQL 8.

Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.