Having upgraded to Ubuntu 15.04, one of the many changes is the upgrade from MariaDB 5.5 to MariaDB 10. Unfortunalty the upgrade didn't go smoothly and left me dealing with an unusual new error.

After upgrading and trying open phpMyAdmin on the localhost, I was greeted with the error "MariaDB Plugin 'unix_socket' is not loaded". A quick Google search shows this is a new feature for MariaDB. Basically rather than needing to set a password for the database root account, it will instead use the unix login session to determine authentication. This only applies to selected database users, by default the root user, and means if I want to login the the database as the root user I have to be logged in to a root session or be using sudo.

For me because this is a development setup I use the root database login all the time, it's easier when working with phpMyAdmin. However, that is not possible with unix_socket auth, because the php process would never be run as root.

The Fix

To fix this issue there are two things we need to do, enable unix_socket auth, so we can login the the database root account and then to disable unix authentication for the root user.

First you need open my.cnf found in /etc/mysql/ with your favourite editor, and add the following after the [mysqld] header.

plugin-load-add = auth_socket.so

Then restart mysqld using the new systemd syntax

sudo systemctl restart mysql.service

You should now be able to access the mysql server like this

sudo mysql -u root

And look no password prompt, that's because it is using unix socket authentication.

If you attempt to access phpMyAdmin now you should see a different error ERROR 1698 (28000): Access denied for user 'root'@'localhost'

At this point if this is a production system we should create a new user account and use that instead for our PHP applications, but to get around all the security features lets just disable socket auth for the root user.

I must stress that this is not recommended for a production environment.

At the mysql prompt enter the following:

use mysql;
update user set plugin='' where User='root';
flush privileges;
exit

This will disable socket authentication and switch back to standard authentication.