MySQL 8 removes shorthand for creating user + permissions

Oh Dear monitors your entire site, not just the homepage. We crawl and search for broken pages and mixed content, send alerts when your site is down and notify you on expiring SSL certificates.

Start your free 10 day trial! »

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.