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

bash
MariaDB [(none)]> GRANT ALL ON somedb.* TO myuser@'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;.

bash
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

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