Ubuntu 16.04(and Debian 9) ships with and MariaDB 10. The new default authentication mechanism for these is the UNIX_SOCKET Authentication Plugin:


Create a database account for Aegir and set the database username before installation. The installation will ask twice for the password.

/usr/bin/mysql -e "GRANT ALL ON *.* TO 'aegir_root'@'localhost' IDENTIFIED BY 'mysecretPASSWORD' WITH GRANT OPTION"
echo "aegir3-hostmaster aegir/db_user string aegir_root" | debconf-set-selections
apt-get install aegir3

Original issue

This plugin allows the user to use operating system credentials when connecting to MariaDB via Unix socket. It works by retrieving uid of the process that has connected to the socket [...] and allowing to connect to the MariaDB account with the corresponding user name.

So no password should be set for the root user. Here's the pertinent information from /usr/share/doc/mariadb-server-10.0/README.Debian.gz:

On new installs no root password is set and no debian-sys-maint user is
created anymore. Instead the MariaDB root account is set to be authenticated
using the unix socket, e.g. any mysqld invocation by root or via sudo will
let the user see the mysqld prompt.

You may never ever delete the mysql user "root". Although it has no password
is set, the unix_auth plugin ensure that it can only be run locally as the root

The credentials in /etc/mysql/debian.cnf specify the user which is used by the
init scripts to stop the server and perform logrotation. This used to be the
debian-sys-maint user which is no longer used as root can run directly.

I'm assuming that this is why Debian package installation is failing on Ubuntu 16.04:

Caught drush error, ending drush_provision_hostmaster_install
            [0] => SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'
            [0] => Unable to connect to database server.

I tried entering nothing when the two password prompts came up, but this had no effect. So maybe the attempt to use a password (even though it's empty) is causing the problem.

I also tried disabling the plug-in for root, and then setting a traditional password. This got Aegir up & running, but broke the database package's cron job as it now assumes no password. We probably shouldn't mess with it.

The above-mentioned README also says:

Scripts should run as a user have have the required grants and be identified via unix_socket.

So given that we have an aegir user, I'm thinking that we should grant it the necessary permissions before it starts doing its work, and then it can connect without a password.

How does this process work now? Where is the code that handles it?

I'd like to see if it's possible to come up with a quick solution right away.

#16 install_fails_on-2770819-16.patch4.57 KBhelmo
Members fund testing for the Drupal project. Drupal Association Learn more


colan created an issue. See original summary.

helmo’s picture

On install the drush_provision_hostmaster_install() function saves the db credentials to e.g. ~/.drush/server_localhost.alias.drushrc.php

Code in db/Provision/Service/db/pdo.php is used to connect ... you could try a mini test script to just test a connection.

In a vagrant box with xenial64 it still has the debian-sys-maint user so I'm not sure how default that is...

helmo’s picture

Issue summary: View changes
Issue tags: -Ubuntu 14.04 LTS +Ubuntu 16.04 LTS

I assume 14.04 was a typo :)

colan’s picture

Title: Install fails on MySQL 5.7+ / MariaDB 10+ without password (new default) » Install fails on MariaDB 10 without password (new default)
Issue summary: View changes
Priority: Major » Normal
Issue tags: -MySQL


I just confirmed that everything works fine with MySQL 5.7. Seems that package is still doing things the old way.

So this is just a MariaDB 10 problem. I updated the issue title & description. As I'm not sure if we're actually supporting MariaDB, I demoted the priority to Normal. As this appears to be an upstream bug (given that MySQL works), let's link to it if/when we find it.

Related: MariaDB 10.1 support in BOA

memtkmcc’s picture

The problem is related to MariaDB 10.1 or newer, I think? It doesn't affect MariaDB 10.0, since we are using 10.0 for a very long time and it never caused such problems.

colan’s picture

Looks like it actually is 10, not 10.1, as that's what's in Xenial. However, it could be the Debian package and not the database server application itself, or something we're doing in Provision that no longer works (or something specific to our Debian package). More investigation will be required to track this one down.

At the very least, it would be great if someone else could confirm by installing the default mariadb-server and then aegir3 on Xenial (Ubuntu 16.04). I was using the unstable Aegir channel, but stable channel tests are welcome.

memtkmcc’s picture

@colans -- Maybe it's Xenial specific issue? We never experienced any issues with MariaDB 10.0 in BOA, but we use and recommend Debian, while Ubuntu is minimally supported, and we didn't add Xenial support, yet.

memtkmcc’s picture

No idea why, but maybe on Xenial you need this trick?

shell$ sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q
colan’s picture

Thanks for that, but as I said earlier:

I also tried disabling the plug-in for root, and then setting a traditional password. This got Aegir up & running, but broke the database package's cron job as it now assumes no password. We probably shouldn't mess with it.

The above-mentioned README also says:

Scripts should run as a user have have the required grants and be identified via unix_socket.

So for now, I'm running MySQL instead, but it's less than ideal because of the memory leak. Running on a tiny VM is not possible; 2 GBs of memory or more seems fine.

memtkmcc’s picture

@colan Oh,sorry for not paying enough attention to previous comments. I think I should check if we didn't experience this in BOA because we force the mysql root stuff, etc.

ergonlogic’s picture

I've managed to install Aegir on Debian Stretch, using MariaDB (default-mysql-server), by creating a new user (e.g., 'aegir_root') with the proper privileges, and providing that user to the hostmaster-install process.

helmo’s picture

I now have a testcase standing by for stretch ... https://gitlab.com/aegir/provision/blob/feature/gitlab-testing/.gitlab-c...

Can you also fix it there?

  • helmo committed 18a411d on 7.x-3.x
    Issue #2770819 by helmo: Install fails on MariaDB 10 without password (...
helmo’s picture

That commit fixed the stretch build ... https://gitlab.com/aegir/provision/builds/18202028

helmo’s picture

The gist is to seed the debian package with the username 'aegir_root' and create and account e.g. sudo /usr/bin/mysql -e "GRANT ALL ON *.* TO 'aegir_root'@'localhost' IDENTIFIED BY 'mysecretPASSWORD' WITH GRANT OPTION"

The normal installation will ask for the password but not the username, so we either have to make it ask or change the default.

helmo’s picture

Issue summary: View changes
Status: Active » Needs work
4.57 KB

Here's a first draft patch.

It creates an account like in #15.

  • detect that we have mariadb 10.1 ... or whatever is the best property to check
  • remove the account in a postrm script?
  • what happens with existing installations is we change the default username here?
  • can it be possible to still override the username/password using debconf-set-selections?

PS: added a workaround to the summary

colan’s picture

Why not always use aegir_root everywhere, not just in this case? Would be simpler.

Make that the new default. If the user doesn't exist on upgrades, create it, and just ignore root.

helmo’s picture

Well, before there always was a root user with a password.

leenx’s picture

I would just like to report that I am having similar problems on mysql 5.7.18 (Percona deb installs). Looks like a change in mysql 5.7.6 seen in the following release notes - https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html

helmo’s picture

Issue summary: View changes
Status: Needs work » Active

Simplified workaround.

leenx’s picture

Greetings ... Is there an update patch to try?

I have mysql aergir details in ~/.drush/server_localhost.alias.drushrc.php and also double checked that the account works. Used mysql -u aergir_root -p

Also grant the suggests rights too.

Still having issues with creating new sites - Database import failed: ERROR 1210 (HY000) at line 21: Incorrect arguments to EXECUTE

Can any one assist?