User creation has changed in MySQL 8.0

Added tag.

Option 1. seems like the correct answer, assuming the BC in mysql goes as far back as we (D7) support. If the two are equivalent even in older versions, there should be no problem with just switching the syntax for everyone, right?

Option 1 seems to be the best ... I'm assuming that it's also MariaDB 10.3 compatible.

Thanks, I've committed your patch now to a 'test-focal-fossa-runalltests' branch on gitlab to see what the CI test does ... but it still fails very early during install with an sql related error.

See https://gitlab.com/aegir/provision/-/jobs/601841502

If someone has time to dig into this ... a good place to start would be in a test vm to run the ci-aegir-dev-install-apt-ubuntu-focal.sh script from that branch(the one on the main repo uses mariaDB for 20.04).

The error being thrown upon install from
https://gitlab.com/aegir/provision/-/jobs/601841502
appears to be coming from line 166 of aegir3-hostmaster.postinst. In my test environment, I broke it into CREATE USER and GRANT lines, but I didn't get something correct. I no longer get that error.

Now I get

Caught drush error, ending drush_provision_hostmaster_install [error]
Array [error]
(
[PROVISION_GRANT_DB_USER_FAILED] => Array
(
[0] => Unable to grant privileges to database users.
)

)

so it still looks to me like I didn't get the GRANT permissions right.

I'm getting this too. Commenting here to hopefully subscribe and share my full command history for reference.

Starting with a clean 20.04 server build:

$ sudo visudo  # reset timeout



$ ip a
$ sudo vi /etc/hosts
    127.0.0.1 localhost

    192.168.1.226 aegir-1.<my-domain>.org aegir-1 
    192.168.1.227 aegir-2.<my-domain>.org aegir-2
    192.168.1.228 aegir-3.<my-domain>.org aegir-3
    fe80::a00:27ff:febe:e851 aegir-1.<my-domain>.org aegir-1
    fe80::a00:27ff:fe93:e6d0 aegir-2.<my-domain>.org aegir-2
    fe80::a00:27ff:fc44:eed0 aegir-3.<my-domain>.org aegir-3

    # The following lines are desirable for IPv6 capable hosts
    ::1     ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters

$ hostname -f
    aegir-1.<my-domain>.org


$ sudo apt update
$ sudo apt upgrade -y


# bare minimum LAMP
$ sudo apt install -y mysql-server apache2 libapache2-mod-php php php-mysql php-cli



$ sudo vi /var/www/html/phpinfo.php
    <?php
    phpinfo();

    Visit http://aegir-1.<my-domain>.org/phpinfo.php
    <web works showing 7.4.3>

$ php -v
    PHP 7.4.3 (cli) (built: May 26 2020 12:24:22) ( NTS )
    Copyright (c) The PHP Group
    Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies


$ sudo apt-get install msmtp msmtp-mta
$ sudo vi /etc/msmtprc
    defaults
    tls on
    tls_trust_file /etc/ssl/certs/ca-certificates.crt
    logfile /var/log/msmtp.log

    account mailgun
    host smtp.mailgun.org
    port 587
    auth on
    user postmaster@mg.<my-domain>.org
    password <my pw>
    from info@<my-domain>.org

    account default : mailgun

$ echo "Subject: Testing msmtp" | sendmail -v <me>@gmail.com
    loaded system configuration file /etc/msmtprc
    ignoring user configuration file /home/<me>/.msmtprc: No such file or directory
    falling back to default account
    using account default from /etc/msmtprc
    host = smtp.mailgun.org
    port = 587
    source ip = (not set)
    proxy host = (not set) 
    proxy port = 0
    timeout = off
    protocol = smtp
    domain = localhost
    auth = choose
    user = postmaster@mg.<my-domain>.org
    password = *
    passwordeval = (not set)
    ntlmdomain = (not set)
    tls = on
    tls_starttls = on
    tls_trust_file = /etc/ssl/certs/ca-certificates.crt
    tls_crl_file = (not set)
    tls_fingerprint = (not set)
    tls_key_file = (not set)
    tls_cert_file = (not set)
    tls_certcheck = on
    tls_min_dh_prime_bits = (not set)
    tls_priorities = (not set)
    auto_from = off
    maildomain = (not set)
    from = info@<my-domain>.org
    add_missing_from_header = on
    add_missing_date_header = on
    remove_bcc_headers = on
    dsn_notify = (not set)
    dsn_return = (not set)
    logfile = /var/log/msmtp.log
    logfile_time_format = (not set)
    syslog = (not set)
    aliases = (not set)
    reading recipients from the command line
    <-- 220 Mailgun Influx ready
    --> EHLO localhost
    <-- 250-smtp-out-n02.prod.us-east-1.postgun.com
    <-- 250-AUTH PLAIN LOGIN
    <-- 250-SIZE 52428800
    <-- 250-8BITMIME
    <-- 250-ENHANCEDSTATUSCODES
    <-- 250-SMTPUTF8
    <-- 250-PIPELINING
    <-- 250 STARTTLS
    --> STARTTLS
    <-- 220 Go ahead
    TLS session parameters:
        (TLS1.3)-(ECDHE-X25519)-(RSA-PSS-RSAE-SHA256)-(AES-128-GCM)
    TLS certificate information:
        Owner:
            Common Name: *.mailgun.org
            Organization: MAILGUN TECHNOLOGIES\, INC
            Organizational unit: MAILGUN TECHNOLOGIES\, INC
            Locality: San Francisco
            State or Province: California
            Country: US
        Issuer:
            Common Name: Thawte TLS RSA CA G1
            Organization: DigiCert Inc
            Organizational unit: www.digicert.com
            Country: US
        Validity:
            Activation time: Wed Feb 19 00:00:00 2020
            Expiration time: Tue Apr 19 12:00:00 2022
        Fingerprints:
            SHA256: 9E:5F:9B:27:BB:26:14:6F:3E:2F:50:75:FE:BF:64:1C:4B:8D:E0:A6:B7:EA:4F:27:13:05:FD:81:3F:57:52:26
            SHA1 (deprecated): 54:36:F6:D1:44:0A:B4:62:F0:94:1B:21:7A:1B:82:5C:DF:FD:FF:57
    --> EHLO localhost
    <-- 250-smtp-out-n02.prod.us-east-1.postgun.com
    <-- 250-AUTH PLAIN LOGIN
    <-- 250-SIZE 52428800
    <-- 250-8BITMIME
    <-- 250-ENHANCEDSTATUSCODES
    <-- 250-SMTPUTF8
    <-- 250 PIPELINING
    --> AUTH PLAIN     AHBvc3RtYXN0ZXJAbWcuZmx5bm5ob21lLm9yZwA2Y2U4ZmIxMzhkYWMzYWY4MmNkYjU4NjYyNDQ2MmRkYy1hODNhODdhOS1jYTRiOGYzMA==
    <-- 235 Authentication successful
    --> MAIL FROM:<info@<my-domain>.org>
    --> RCPT TO:<<me>@gmail.com>
    --> DATA
    <-- 250 Sender address accepted
    <-- 250 Recipient address accepted
    <-- 354 Continue
    --> From: info@<my-domain>.org
    --> Date: Thu, 16 Jul 2020 13:25:17 +0000
    --> Subject: Testing msmtp
    --> .
    <-- 250 Great success
    --> QUIT
    <-- 221 See you later. Yours truly, Mailgun
    sendmail: cannot log to /var/log/msmtp.log: cannot open: Permission denied
    sendmail: log info was: host=smtp.mailgun.org tls=on auth=on user=postmaster@mg.<my-domain>.org from=info@<my-domain>.org r    ecipients=<me>@gmail.com mailsize=86 smtpstatus=250 smtpmsg='250 Great success' exitcode=EX_OK

<   Virtual Machine SNAPSHOT 1   >
$ sudo shutdown now

$ sudo apt-get install -y php-dom php-mbstring     php-xml    php-gd   php-json    php-curl    php-common    php-intl  php-zip unzip zip

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf   # take out bind to localhost 
$ sudo vi /etc/mysql/my.cnf
    [mysqld]  
    default_authentication_plugin= mysql_native_password
$ sudo systemctl restart mysql
$ sudo mysql
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
    FLUSH PRIVILEGES;
    SELECT user,authentication_string,plugin,host FROM mysql.user;

+------------------+---------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                | plugin                | host      |
+------------------+---------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | $A$005$w2b&^}~:)Pb{&WWiu}h3MfcdkCJHdTHGAQWFGpxIU4 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDBRBEUSED | caching_sha2_password | localhost |
| root             | *8EFFB34E53A24141BD9BFA10C3sdfd970Cxxxx9C470017DE | mysql_native_password | localhost |
+------------------+---------------------------------------------------+-----------------------+-----------+

$ cd ~
$ curl -sS https://getcomposer.org/installer -o composer-setup.php
$ HASH=`curl -sS https://composer.github.io/installer.sig`
$ php -r "if (hash_file('SHA384', 'composer-setup.php') === '$HASH') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;"
$ sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer
$ composer global require drush/drush:"9.*"   # go with 9 for a try
$ vi ~/.profile  
    if [ -d "$HOME/.config/composer/vendor/bin" ] ; then
        PATH="$HOME/.config/composer/vendor/bin:$PATH"
    fi

$ . ~/.profile
$ which drush
$ sudo ls
$ sudo apt-get install phpmyadmin

$ echo "deb [signed-by=/usr/share/keyrings/aegir-archive-keyring.gpg] https://debian.aegirproject.org stable main" | sudo tee -a /etc/apt/sources.list.d/aegir-stable.list
$ sudo wget -O /usr/share/keyrings/aegir-archive-keyring.gpg https://debian.aegirproject.org/aegir-archive-keyring.gpg


$ sudo su
# cat > /etc/apt/preferences.d/aegir <<EOD
# Package: *
# Pin: origin debian.aegirproject.org
# Pin-Priority: 100
# EOD
# exit

$ sudo apt-get update
$ sudo apt-get install -y aegir3 aegir-archive-keyring

It actually changed in 5.7, but wasn't strictly enforced. But it looks like despite MySQL saying otherwise, you can't set password while creating the user or adding grants. There will be warning reported (this was tested with Percona 5.7):

mysql> CREATE USER IF NOT EXISTS 'aegir_root'@'localhost' IDENTIFIED BY 'Foo+Bar=';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL ON *.* TO 'aegir_root'@'localhost' IDENTIFIED BY 'Foo+Bar=' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

You actually have to split this into CREATE/GRANT/ALTER chain like this to het it actually done:

mysql> CREATE USER IF NOT EXISTS 'xaegire'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'xaegire'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'xaegire'@'localhost' IDENTIFIED BY 'Foo+Bar=';
Query OK, 0 rows affected (0.00 sec)

These two patches should fix the problem, tested with MariaDB and Percona, which is MySQL equivalent, but more testing is welcome.

Why two patches? Is one for Provision and one for Hostmaster? The filenames don't help here at all.

Also, do you have an interdiff for #8?

I have split this into two patches, because I'm not sure about the Debian scripts, so left it in the second patch.

I don't have interdiff because I couldn't use/test the proposed previous patch with my Provision copy (we have a lot more there for mydumper support), so had to make all changes manually anyway.

Patch #13 is not working with MariaDB 10.1, since "ALTER USER" was introduced in MariaDB 10.2. As a consequence, aegir install won't work on Ubuntu Bionic, unless the user upgrades MariaDB before Aegir install.

I've tested the first patch in #13 successfully (on Ubuntu 20, using MySQL 8 with Drupal 7.78 and 9.1.4) but not the second.

Is there a way for me to RTBC just the first patch but not the second one?

@helmo How would you like this to be handled? Just commit the Provision fix here and move the deb postinst script fix to a separate ticket?

Also: should the MariaDB issue be broken out as a separate ticket? This one is ostensibly about Mysql 8, for which I am confirming the patch above works.

After reviewing:

1. We should commit the first patch in #13.
2. The second one needs testing still.
3. For the MariaDB problem (#16) , I think we can add that to the release notes.

I created a new issue fork using the new GitLab integration (see the top of the issue for details) so far pushing only the first patch in #13. I didn't use @helmo's test-focal-fossa-runalltests branch (#9) because I couldn't find it.

As there were no objections, I merged the first patch, and updated the IS with remaining tasks.

Is the workaround for Ubuntu 20.04 to manually install? Anything I can do to help get this fix in production?

If anyone lands here after trying to install Aegir on Ubuntu 20.04, I recommend using Ubuntu 18.04 instead. I had no issues installing on 18.04 and it still has a few years before end-of-life.

For what it's worth, we're got it working on 20.04 with our Ansible role using dev branches.

Once there's a new official release, the Debian package should work on 20.04.

@colan, we could set the debian package requirements for mariadb >= 10.2 in addition to the documentation notice. it will make it clear that the installation won't work unless it is updated.

👍

Do we have an idea of when a debian package working on 20.04 may be available?

Here's a patch that sets minimum versions on the mysql-server and mariadb-server recommends. This won't stop someone from upgrading the package when using a database (especially if the database is not local).

#30: You may want to track #3246691: Create an Aegir 3.20 release for recent stable Debian & Ubuntu LTS.

One missing piece to get Aegir package install running on MySQL (and to get it running on Ubuntu 20.04 as well) is to change user creation in debian/aegir3-hostmaster.postinst.

Even the #3145881-13: User creation has changed in MySQL 8.0 patch has had this code earlier, but has not been committed. This patch changes the method of aegir database user creation the same way as implemented in database service.

Beyond this patch, the debian/aegir3-hostmaster.postinst file needs also a more general rework.

This patch contributes to #3145927: [META] Support Ubuntu 20.04 LTS as well.

Looks like this got committed to 7.x-3.x already.