Creating a new user in MariaDB

I’ve never used MariaDB before, but today I needed a MySQL database for the first time since installing Arch Linux and figured that I’d stick with the system defaults.

The installation went fine (sudo pacman -S mariadb) and I could login with mysql -u root as expected. An issue appeared when I tried to create a new user though

MariaDB [(none)]> GRANT ALL ON somedb.* TO [email protected]'localhost' IDENTIFIED BY '';
ERROR 1133 (28000): Can't find any matching row in the user table

It took a little while, but I realised that MySQL has an option to disable the automatic creation of users with GRANT. You can check if you have it enabled by running SELECT @@GLOBAL.sql_mode;.

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

There’s two possible fixes. The first is to disable NO_AUTO_CREATE_USER for the current session with SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; then run your GRANT statement again.

Alternatively, you can create the user before granting permissions

MariaDB [(none)]> CREATE USER 'myuser'@'localhost' IDENTIFIED BY '';
MariaDB [(none)]> GRANT ALL ON somedb.* TO [email protected]'localhost';

Michael is a polyglot software engineer, committed to reducing complexity in systems and making them more predictable. Working with a variety of languages and tools, he shares his technical expertise to audiences all around the world at user groups and conferences. You can follow @mheap on Twitter

Thoughts on this post

Hai Tu 2017-04-12

This solution worked. Thank you.

I was following this tutorial for open stack https://pdfs.semanticscholar.org/d740/d99efca17de8a90f2b9a12fea1608934dbdd.pdf

Got to the part where I had to
$ mysql -u root -p -e “GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;”

Leave a comment?

Leave a Reply