Migration of custom configuration to my.cnf.migrated is not explicit enough

[Markus]

After upgrading from 14.10 to 15.04, I cannot access mariadb anymore on the local system.

root@system:~# mysql -p
Enter password:
ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

System is on Ubuntu 15.04 vivid, mariadb-server is

mariadb-server:
  Installiert: 10.0.17-0ubuntu1
  Installationskandidat: 10.0.17-0ubuntu1

[Launchpad Janitor (janitor)]

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

[Georg Abenthung (georg-0)]

+1

[BjoernR (bjoern-rafreider)]

after an update to 15.04. my system was affected too;
after some research I added

plugin-load=auth_socket.so

ty my.cnf, now authenticated users (like root) can access the databases without entering a database password.

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

[Markus (maier-m)]

In /etc/mysql/mariadb.conf.d/mysqld.conf there is an something like

#
# * Unix socket authentication plugin
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
plugin-load-add = auth_socket.so

But that doesn't seem to work for me.

I also tried adding

plugin-load=auth_socket.so

to my.cnf, also with no success. Section [mysqld] should be correct, right?

Apart from that, accessing the databases without password is the new default way here?

[ppvolto (volto-phoenix)]

Config fix for local root user access
from: plugin-load-add=auth_socket.so
to: plugin-load-add=unix_socket=auth_socket.so

after that
mysql as root is posible

[Fred Azevedo (fkeyster)]

My friends, I have the same problem. Have added the plugin-load code = auth_socket.so in my.cnf and not solved.

When trying to log into phpMyAdmin is giving the following error:

Plugin unix_socket 'is not loaded

mysql_connect (): Plugin unix_socket 'is not loaded

Can anyone help me?

[Markus (maier-m)]

None of the above mentioned fixes worked for me.
In which config file and which section do I have to add the entries? There are much more in /etc/mysql now than it was before.

[zhxq (zhxq)]

I solved this problem.

Thanks for http://ubuntuforums.org/showthread.php?t=2275033

I wrote a script and it is here: http://zhxq.io/?p=99

It contains a script and a manually way to solve the problem.

I used it, and it is functional.

Hope this helps.

[Markus (maier-m)]

!WARNING! After the script mentionend above has been executed, root was able to login without a password!

Basically, the script by zhxq does the trick and removes the plugin authentication for root. But on my system, it left MariaDB with no password set for root. So in terms of security, this is even worse than having no access at all.

Anyway, this brings us closer to the root cause of the problem. From my understanding, after upgrading to vivid, passwords for user root are removed and authentication is set to unix socket authentication. This authentication should be using a plugin which doesn't work properly.

So we should either make sure that the plugin works as intended or, my preference, don't change the authentication method for user root.

[BjoernR (bjoern-rafreider)]

I think, that there is some missunderstanding of what the unix_socket authentication does...

A user, who is loged in on his system (shell, ssh...) is able to access mariadb with a user that is named like his unix user (if it exists as db user) without password.

So logged in as root, I'm able to use "mysql -u root".
logged in as bjoern, I get an access denied, trying to do "mysql -u root"
If there was a user bjoern in MariaDB, I could use "mysql -u bjoern2 to login without password.

phpmyadmin will never work with root and unix_socket auth!

the script mentionned above will remove the auth plugin from the user root and return to the old DB-based auth... What I'm not sure about, is if the old password is still valid, so ry it or update root password...

[Markus (maier-m)]

Yes, correct. What I intended to say is that after zhxq's script was applied, MariaDB is back on the old authentication mode without any password for root. So "mysql -u root" just works for any user!

Focussing back on the issue: we should then get unix_socket running right after the upgrade. Can anyone provide a proper solution (which file, which section, which config parameter)? This should be added to the original package then.

[Alexander Monk (krenair)]

I was running into this issue and found that installing mariadb-server-10.0 created the plugin needed to fix the issue.

[zhxq (zhxq)]

Thanks for replying for the functionality of my script.

Because I'm the only one that uses the server, so I don't care others using MariaDB as root.

However, with this you can create another user by root, and re-enable unix socket by
 update user set plugin="unix-socket" where User="root";flush privileges;

then all is set.

[zhxq (zhxq)]

ah by the way I just tried to login using root without password.

and mariadb told me access denied.

using root with password can login normally.

so it shouldn't be a problem, I think.

[zhxq (zhxq)]

@Markus

Thanks a lot, and I had noticed the problem in http://zhxq.io/?p=99

Please take a look, and if you are available, please leave a comment under my website.

Thanks for noticing, and I also tried, this is not a problem.

This will start a mysql with no root password for seconds (this is for doing things under emergency, such as forgetting root password), and restart the mysql will get back to the old, "root with password" mode.

[Markus (maier-m)]

On my system, root passwords are being removed during the upgrade from utopic to vivid (and before zhxq's script is run). I guess this is done because root should now be authenticated using unix_socket.

So after removing the plugin entry from the user table, the security issue remains.

[zhxq (zhxq)]

@Markus

It is weird that Ubuntu didn't find this problem before releasing the new upgrade.

I think this problem will affect tens of thousands of users.

However, I hope my script helps to solve the problem temporary ;-)

[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)]

Errors about unix_socket usually mean that your configuration in /etc/mysql/ does not contain the plugin loading stanza. Something is messed up in your configuration that the automatic upgraded wasn't able to resolve. What does 'ls -la /etc/mysql' show? What is the contents of your main configuration file /my.cnf or mariadb.cnf)?

[Markus (maier-m)]

ls -la /etc/mysql gives

insgesamt 44
drwxr-xr-x 4 root root 4096 Apr 24 09:26 .
drwxr-xr-x 152 root root 12288 Apr 24 22:44 ..
drwxr-xr-x 2 root root 4096 Apr 24 09:09 conf.d
-rw------- 1 root root 277 Apr 24 09:26 debian.cnf
-rwxr-xr-x 1 root root 1312 Feb 17 19:24 debian-start
-rw-r--r-- 1 root root 744 Feb 17 19:24 mariadb.cnf
drwxr-xr-x 2 root root 4096 Apr 24 09:09 mariadb.conf.d
lrwxrwxrwx 1 root root 24 Apr 24 09:09 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root 839 Feb 11 16:10 my.cnf.fallback
-rw-r--r-- 1 root root 4077 Apr 26 16:51 my.cnf.migrated

my.cnf is

# This file has been automatically moved from your previous
# /etc/mysql/my.cnf, with just this comment added at the top, to maintain MySQL
# operation using your previously customised configuration.

# To switch to the new packaging configuration for automated management of
# /etc/mysql/my.cnf across multiple variants:
#
# 1. Move your customisations from this file to /etc/mysql/conf.d/ and
# to /etc/mysql/<variant>.conf.d/ as appropriate.
# 2. Run "update-alternatives --remove my.cnf /etc/mysql/my.cnf.migrated"
# 3. Remove the file /etc/mysql/my.cnf.migrated

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log...

[Alexander Monk (krenair)]

If you stop the mysql service and just run `mysql` directly from the command line, does it mention the plugin file in the log?

[Markus (maier-m)]

which log? If I stop mysqld and just run mysql, /var/log/mysql/error.log is not touched.

[Richard (richar-heng)]

For me it fails to stop mysql

[Alexander Monk (krenair)]

Oops. That should have been `mysqld`, and I meant the terminal output rather than /var/log/mysql/error.log.

[Markus (maier-m)]

Hm, I just realized that after bootup, I see mysqld and mysqld_safe running:

root 1016 0.0 0.1 22572 3688 ? S 20:45 0:00 /bin/bash /usr/bin/mysqld_safe
root 1017 0.0 0.1 24508 2588 ? S 20:45 0:00 logger -p daemon.err -t /etc/init.d/mysql -i
mysql 1155 0.4 5.1 609876 104696 ? Sl 20:45 0:03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

This looks a little strange to me. Was different on 14.10 before. Are these remainders of a unsuccessful upgrade?

After killling mysqld_safe and mysqld, then `mysqld` on the terminal it just gives me... nothing! No output/log is given here.

[Alexander Monk (krenair)]

Interesting. For me I'm pretty sure that displayed an error about the plugin file being missing. Sorry, I can't help.

[Markus (maier-m)]

Maybe someone could post a working configuration here which might point us to a solution?

[Richard (richar-heng)]

Hello guys,

Do you think it is possible to downgrade MariaDB or in anyway go back to the previous versions of ubuntu and mariaDB ?

Thanks (I'm going crazy by the way, I am very new in all server administration thing..)

[Markus (maier-m)]

Okay guys, slowly getting there... Inspired by https://lists.launchpad.net/maria-developers/msg08315.html I figured out that my plugin table in the mysql db was empty.

So

/etc/init.d/mysql stop
killall mysqld_safe
killall mysqld
mysqld_safe --skip-grant-tables &
INSERT INTO mysql.plugin (name, dl) VALUES ('unix_socket', 'auth_socket');
exit
/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql start

did the trick for me, unix_socket is now enabled. Please don't ask why I needed to start mysqld twice...

I guess that the installation of the plugin just did not work during the upgrade. Could someone of the package maintainers cross-check this (reference: https://lists.launchpad.net/maria-developers/msg08315.html)

After all, I still have the issue that after rebooting mysqld_safe and mysqld are running. I think this is related to https://bugs.launchpad.net/ubuntu/+source/mariadb-10.0/+bug/1445549

user@system:~$ ps axu|grep -i mysql
root 1023 0.0 0.1 22572 3644 ? S 12:07 0:00 /bin/bash /usr/bin/mysqld_safe
root 1024 0.0 0.1 24508 2560 ? S 12:07 0:00 logger -p daemon.err -t /etc/init.d/mysql -i
mysql 1162 0.9 5.0 611932 102640 ? Sl 12:07 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

[Jing Zhang (wanderer.wetware)]

The bug also occurred on my PC:

# cat /etc/issue
Ubuntu 15.04 \n \l

# uname -a
Linux domzjing 3.19.0-16-generic #16-Ubuntu SMP Thu Apr 30 16:09:58 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

@otto Any news about this problem?

[Otto Kekäläinen (otto)]

@maier-m Thanks for providing more information.

It seems your my.cnf points to /etc/alternatives/my.cnf. What does that file point to then? Please post output of 'ls -la /etc/alternatives/my.cnf'

I suspect that the update-alternatives didn't work and your my.cnf does not point to mariadb.cnf.

What it you try issuing the command mentioned in the fallback config file you posted:

  2. Run "update-alternatives --remove my.cnf /etc/mysql/my.cnf.migrated"

What does 'update-alternatives --list my.cnf' show?

[Markus (maier-m)]

Well, you're right...

root@system:/etc# ls -la /etc/mysql/my.cnf
lrwxrwxrwx 1 root root 24 Mai 21 11:21 /etc/mysql/my.cnf -> /etc/alternatives/my.cnf

root@system:/etc# ls -la /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 26 Mai 21 11:21 /etc/alternatives/my.cnf -> /etc/mysql/my.cnf.migrated

Then after

root@system:/etc# update-alternatives --remove my.cnf /etc/mysql/my.cnf.migrated
update-alternatives: /etc/mysql/mariadb.cnf wird verwendet, um /etc/mysql/my.cnf (my.cnf) im automatischen Modus bereitzustellen

root@system:/etc# update-alternatives --list my.cnf
/etc/mysql/mariadb.cnf
/etc/mysql/my.cnf.fallback

root@system:/etc# ls -la /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 22 Mai 21 11:49 /etc/alternatives/my.cnf -> /etc/mysql/mariadb.cnf

And after a reboot, we're good :-)

So I guess you are right, the update-alternatives did not work out during migration. Does that point you to a solution?

[Otto Kekäläinen (otto)]

2015-05-21 12:53 GMT+03:00 Markus <email address hidden>:
> And after a reboot, we're good :-)
>
> So I guess you are right, the update-alternatives did not work out
> during migration. Does that point you to a solution?

I'll subscribe the Robie who engineered the update-alternatives for
MySQL/MariaDB if he can think of why the install script didn't work as
expected.

[Robie Basak (racb)]

Ending up with /etc/mysql/my.cnf.migrated is currently expected and intended behaviour if you had previously customised configuration. In this case you are required to either manually update your customisations or explicitly choose to drop your customisations and switch back to packaging configuration defaults.

So the symlink will end up pointing to /etc/mysql/my.cnf.migrated if you had previously customised /etc/mysql/my.cnf (which gets renamed to /etc/mysql/my.cnf.migrated to make way for the symlink). This is because Debian policy says that if the user customises configuration in /etc, then that should not be overridden. So in that case packaging assumes that you know what you're doing, leaving it up to you to update your customised configuration for changes in the new release. There are comments at the top of /etc/mysql/my.cnf.migrated (IIRC) that explain how to switch back to the packaging default configuration if you wish. I think it's just a one line call to update-alternatives?

I welcome ideas on improving this experience though. I certainly didn't expect anything to regress by simply copying forward the same my.cnf. Perhaps we should provide a debconf notice telling the user what has happened when my.cnf.migrated is used? Or otherwise, what else can we do in the case of a previously customised configuration?

[Robie Basak (racb)]

> I think it's just a one line call to update-alternatives?

Ah - I see that you found that. Does my explanation make sense? Should the behaviour be any different?

[Otto Kekäläinen (otto)]

Without the update-alternatives system debconf would automatically notify the user that the config is changed, and then ask if the user wants to keep their file, install the packager version or view a diff of the changes. It would be nice if that user experience would be present here.

At least we've seen that people don't read the first lines in the my.cnf.migrated even though the solution is there.

Maybe the simplest solution would be for the update-alternatives wrapper to emit a debconf screen which notifies what it was automatically done to the config file.

The point of decoupling the config files was to allow variants to have their own config options. That does not work in reality if variants cannot have reasonable trust that their config file is actually active.

[Robie Basak (racb)]

On Thu, May 21, 2015 at 11:34:47AM -0000, Otto Kekäläinen wrote:
> Without the update-alternatives system debconf would automatically
> notify the user that the config is changed, and then ask if the user
> wants to keep their file, install the packager version or view a diff of
> the changes. It would be nice if that user experience would be present
> here.

Agreed.

> At least we've seen that people don't read the first lines in the
> my.cnf.migrated even though the solution is there.

Agreed.

> Maybe the simplest solution would be for the update-alternatives wrapper
> to emit a debconf screen which notifies what it was automatically done
> to the config file.

Should this be in the wrapper, or in the one-time migration code in
mysql-common when my.cnf.migrated is generated? I'm happy with the
latter, especially as you point out a regular conffile prompt would
happen anyway. I'm concerned about "too many debconf prompts" for the
former.

We could additionally provide the notice on mysql-common.postinst every
time that my.cnf.migrated is present and active, perhaps filtered by
version. This might help users who have upgraded already.

[Markus (maier-m)]

> At least we've seen that people don't read the first lines in the
> my.cnf.migrated even though the solution is there.

Honestly I read that part of the conffile (by chance while scrolling through the file) but did not even understand that this was the issue here. The whole link/alternatives thing is well covered from the users (thanks for that!) so it's kind of tricky to find out what's really going on.

[Otto Kekäläinen (otto)]

> Should this be in the wrapper, or in the one-time migration code in
> mysql-common when my.cnf.migrated is generated? I'm happy with the
> latter, especially as you point out a regular conffile prompt would
> happen anyway. I'm concerned about "too many debconf prompts" for the
> former.
>
> We could additionally provide the notice on mysql-common.postinst every
> time that my.cnf.migrated is present and active, perhaps filtered by
> version. This might help users who have upgraded already.

Either or both are OK to me, choose whatever you think is simpler to
implement or makes more sense.

The helper text prepended to the migrated my.cnf could also be
improved a bit. It does not tell the user _why_ the
update-alternatives is used and what the alternatives are.
Unfortunately I can't come up with a smart and short text to put there
from the top of my head.

@maier-m: Do you feel you could contribute to improve the documentation part?

[Markus (maier-m)]

Well, I could help on the documentation part (although I have very little experience with alternatives) but I think we should tackle this issue somewhere else: we're currently implementing new config files and the user sees an error message which does not guide him to the config file immediately. Not an easy way to find out that you need to check the config file, migrate it manually and remove the old one...

Could we somehow show a message during the upgrade process itself?

[Tobias Frei (tobefree)]

This happened to me after installing mariadb, deciding to go back to mysqld and installing mysqld again. The fix by Markus (maier-m) (#29, 2015-04-30) solved the problem for me. Thank you very much! :)

[Tobias Frei (tobefree)]

Sorry for the double-post, but I have to correct my previous statement. #29 did not help.
What helped was logging in via the mysql console as debian-sys-maint and doing the following:

use mysql;
update user set plugin='' where User='root';
\q

After restarting the service using "service mysql restart", everything worked again. And yes, I do have to enter a password for root. It works perfectly. Maybe I'll run into other issues later, but I hope that this fixed the problem permanently. Phew.

Kudos to #8 and http://ubuntuforums.org/showthread.php?t=2275033

[gonssal (gonssal)]

So I just had the terrible to idea to try and switch from mysql to mariadb (drop-in replacement, yeah sure...) and hit this bug.

After a couple of hours trying different things, I went back to mysql 5.7 and used the solution in #29. I think we should stop calling mariadb a drop-in replacement, at least in the state its ubuntu's packaging is.

Also during the upgrade process, the system told me existing databases could not be migrated and they were being copied somewhere else.

Probably the worst user experience I've had with ubuntu in years.