BitmapCake!

Today I installed MariaDB on my laptop and faced this weird issue. After the installation, I tried to login to the DB shell using the user 'root' and the blank password. Then it returned me this error message stating:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'
My first guess was that they might have changed the default password and set it to something else. So I did some research and realized it was not the case here. The actual problem was related to the authentication process of MariaDB.

MariaDB uses a plugin called UNIX_SOCKET to authenticate users, which allows them to use the operting system credentials to login. If I wanted to login to MariaDB using the root user, it first requires me to logged into my system as root (which I can temporarily take access using the sudo command.)

But since I'm logged in as the user nimeshka, I cannot request MariaDB to login as another user (in this case, root). So if I enter the command,

mysql -uroot -p
it would tell me that I have no permissions to login. Why? because I'm authenticated to the Ubuntu system as nimeshka, so MariaDB won't trust me when I ask it to login as root :P So it is clear that if I enter the following command, it would allow me to log in.

sudo mysql
You should also note that If I had created a user in mysql with the username nimeshka, I could have logged in to the shell by just entering the mysql command (it will take my current system username and will log me in as the particular mysql user)

Another thing to note: As I was concerned why I didn't face this issue when I installed MariaDB earlier, I looked for a bit more details in their documentation and found a satisfying answer. It says that they use the UNIX_SOCKET plugin by default in new installations of Ubuntu 15 and later (I used to install MariaDB in Ubuntu 14 earlier and today I tried it in Ubuntu 16, which makes sense!).

So here is how to disable the UNIX_SOCKET plugin for root user.

# First we will login with sudo (means root)
$ sudo mysql -u root

# Then switch to the mysql DB.
MariaDB [(none)]> use mysql

# Then we will update the authentication plugin for root user by running the following query.

MariaDB [(none)]> update user set plugin='' where User='root';

# We need to flush privilages and exit the session.
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit


That's it guys, hope this will be useful for someone :)