MariaDB - cannot login as root

Question

I am trying to setup MariaDB (10.0.29) on Ubuntu (16.04.02). After I installed it and started the process (sudo service mysql start), I cannot login as root even though I originally set the password to blank.

Ie mysql -u root will deny me access. I logged in through sudo mysql and checked the user table, ie. select user, password, authentication_string from mysql.user and as expected:

+---------+----------+-----------------------+ 
| User    | password | authentication_string |
+---------+----------+-----------------------+
| root    |          |                       |
+---------+----------+-----------------------+

I also created a new user, ie. create user 'test'@'localhost' identified by ''; and when I try to do mysql -u test (empty password), it works as expected and logs me in.

The user table looks like this:

+---------+----------+-----------------------+
| User    | password | authentication_string |
+---------+----------+-----------------------+
| root    |          |                       |
| test    |          |                       |
+---------+----------+-----------------------+

So, can anyone tell me why I cannot login as root with empty password but I can login as test?

Answer 1

Unlike native MariaDB packages (those provided by MariaDB itself), packages generated by Ubuntu by default have unix_socket authentication for the local root. To check, run

SELECT user, host, plugin FROM mysql.user;

If you see unix_socket in the plugin column, that's the reason.

To return to the usual password authentication, run

UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';
FLUSH PRIVILEGES;

(choose the WHERE clause which fits your purposes, the one above is just an example)