Access denied for user 'root'@'localhost' after upgrading to Ubuntu 15.04

[Jesús Abelardo Saldívar Aguilar]

Cannot access as root user after a system upgrade, from Ubuntu 14.10 to Vivid Vervet 15.04

Normally I could access as root user to the database, from phpmyadmin and from comand line client. After ugrading the system, I can't:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I have tried resseting the root password in the user table and flushing privileges, but it doesn't work; I still cant access as root user.

lsb_release:
Description: Ubuntu 15.04
Release: 15.04

mariadb-server:
  Instalados: 10.0.17-0ubuntu1
  Candidato: 10.0.17-0ubuntu1
  Tabla de versión:
 *** 10.0.17-0ubuntu1 0
        500 http://mx.archive.ubuntu.com/ubuntu/ vivid/universe amd64 Packages
        100 /var/lib/dpkg/status

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Ruben Lopez (ruben-u)]

I can also verify that this is an issue. Except mine is a fresh install of Ubuntu-Gnome 15.04.

I have also reset the password and flushed permission. I can only login if skip-grant-tables flag is given.

[Jesús Abelardo Saldívar Aguilar (jasaldivara)]

I found that the root user are now set up to login using unix_socket authentication plugin instead of a password, so one can login as root when using sudo command:

sudo mysql -u root

Also, what I did to solve my problem was to create another user, with log in via password and grant all permissions to that user.

[alokmahor (alokmahor)]

I am also facing the same problem after freshly installing mariadb in Kubuntu 15.04. I am able to get mysql shell with root account but not with my user account.

root@alok-PORTEGE-R30-A:~# mysql -u root -proot #is owrking

alok@alok-PORTEGE-R30-A:~$ mysql -u root -proot #not wokring
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

also my java jdbc driver also not able to connect

[alokmahor (alokmahor)]

I am not able to connect using python too

import MySQLdb
db = MySQLdb.connect("localhost","alok","alok","messages")

is giving error
OperationalError: (1698, "Access denied for user 'root'@'localhost'")

[Jesús Abelardo Saldívar Aguilar (jasaldivara)]

I think, this problem is because the root mysql account is now setup to aunthenticate via unix_socket instead of a password:

https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/

So, you can enter to mysql as root user if you are logged in to the system using the root unix user (or using sudo) and you don't need to type the mysql password:

sudo mysql -u root

(this only will ask you for your root's system password, not your root's mysql password

This is the output from mysql when I query for the root account grants (SHOW GRANTS FOR 'root'@'localhost';):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION

So, if you want to connect to mysql using a password, I suggest to create another mysql user, and identify it using a password

[Otto Kekäläinen (otto)]

The new MariaDB 10.0 do no longer require users to set a root password. This is a security feature. You don't need root passwords (or debian-maint-user passwords) simply to run and maintain your database anymore. If you have root on the system you will get in as root to the database (or using sudo).

For use cases where you need to access the database as some user from somewhere, just create a user and grant it the required permissions, and then use that user in your apps and other places. There is no need to have a general MySQL/MariaDB root user.

[Otto Kekäläinen (otto)]

Can you log in from command line with simply running 'sudo mysql' ?

Do that and create yourself your own MySQL/MariaDB user account, give it the neeeded permissions and use it. For security reasons it is not advised to use the root account all the time and in particular not remotely.

[nadavkav (nadavkav)]

Workaround/fix:
http://www.versatilewebsolutions.com/blog/2015/05/dealing-with-mariadb-plugin-unixsocket-is-not-loaded.html

[Ralph Corderoy (ralph-inputplus)]

Upgrade from 14.10 to 15.04 broke programs' access to MariaDB because
they were all expecting to give a username of root and the appropriate
password. To repair, I set user.plugin back to empty for user='root'
and *also* had to set user.password as the upgrade had cleared it.

It's not nice to discover the problem after the reboot upgrade; I
didn't notice it in 15.04's release notes.

Suggesting a new user be created with the desired permissions and code
switch across to using that isn't practical when it's a legacy system
with much code in various languages and root/password scattered to the
winds by time. The client's system was being upgraded to get security
updates, not be pushed into changing their codebase for a security
improvement when no change wouldn't have worsened security.

[Ralph Corderoy (ralph-inputplus)]

Another change required having done the above is to alter
/etc/mysql/debian.cnf to have user = debian-sys-maint, instead of root,
with a new random password that's put into the existing row for that
user. Otherwise, /etc/cron.daily/logrotate generates an email because
it has problems rotating /var/log/mysql.log and friends.

[Otto Kekäläinen (otto)]

The version 10.0.23-1 uploaded to Debian and soon synced to Ubuntu has now a reworked auth_socket configuration, where auth_socket is used by default only on new installs and on upgrades the existing mysqld root accounts are kept as-is.

Therefore marking 'fix committed'.

[Faustin (fauust)]

Ubuntu 15.04 is no more supported and fix has been released.

https://www.ubuntu.com/info/release-end-of-life