[META] Support Ubuntu 20.04 LTS

That's a good idea. While I'd prefer to use MariaDB, Canonical supports only the MySQL package. I may not be the only one preferring it for that reason.

Here are the errors:

 Unpacking aegir3-hostmaster (3.185+582055384.2dcc33a) ...
dpkg: dependency problems prevent configuration of aegir3-provision:
 aegir3-provision depends on php5 | php7.0-xml | php7.1-xml | php7.2-xml | php-xml; however:
  Package php5 is not installed.
  Package php7.0-xml is not installed.
  Package php7.1-xml is not installed.
  Package php7.2-xml is not installed.
  Package php-xml is not installed.
 aegir3-provision depends on php5-mysql | php7.0-mysql | php7.1-mysql | php7.2-mysql | php-mysql; however:
  Package php5-mysql is not installed.
  Package php7.0-mysql is not installed.
  Package php7.1-mysql is not installed.
  Package php7.2-mysql is not installed.
  Package php-mysql is not installed.
dpkg: error processing package aegir3-provision (--install):
 dependency problems - leaving unconfigured

php-xml and php-mysql are listed in there. Why aren't they being pulled in automatically as dependencies?

Chiming in here with my experience just now with Ubuntu 20.04 (focal).

I'll paste my EXACT install steps below but the problem that I can't seem to get over with the usual mystical workarounds is:

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.
        )

)

Aegir 7.x-3.186 automated install script

I tried all the usual suspects such as not running the "mysql_secure_installation" command and also making sure that the default mysql 8.0 default_authentication_plugin= mysql_native_password.

Still seem to be stuck here. I tried pre-installing drush 8, drush 9 and drush 10 all with the same error. There was some drush chatter in a bug submission about this but not sure if it is related. Drush does not grant access to SQL user

Here are my exact steps from a fresh 20.04 install

$ 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

That certainly looks like #3145881: User creation has changed in MySQL 8.0.

If that can help:

I cloned an Ubuntu 18.04 VM with a functioning aegir 3.186 installation and upgraded it to Ubuntu 20.04 using $ sudo do-release-upgrade -d.

The upgrade ran smoothly without any error or warning I could see.

When I restart the upgraded VM:

1) it sends me every minute by email the following error message:

Subject: Cron <aegir@sistema> /usr/bin/env php /usr/local/bin/drush '@hostmaster' hosting-dispatch 
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games>
X-Cron-Env: <HOME=/var/aegir>
X-Cron-Env: <LOGNAME=aegir>

Additional uncaught exception thrown while handling exception.

Original

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in drupal_is_denied() (line 2204 of /var/aegir/hostmaster-7.x-3.186/includes/bootstrap.inc).

Additional

TYPO3\PharStreamWrapper\Exception: Unexpected file extension in "phar:///usr/local/bin/drush/commands/core/drupal" in Drupal\Core\Security\PharExtensionInterceptor->assert() (line 38 of /var/aegir/hostmaster-7.x-3.186/misc/typo3/drupal-security/PharExtensionInterceptor.php).

---

Drush command terminated abnormally due to an unrecoverable error. [error]
Error: Array and string offset access syntax with curly braces is
deprecated in phar:///usr/local/bin/drush/includes/sitealias.inc,
line 176

2) When I direct a browser to the AEGIR hostmaster web UI, I get the following error message:

Error
The website encountered an unexpected error. Please try again later.
Error message
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in lock_may_be_available() (line 167 of /var/aegir/hostmaster-7.x-3.186/includes/lock.inc).

Drupal 7 needs a patch to run on MySQL 8. This patch also resolves last issue of #7

Other option could be to override sql_mode options in settings, see i.e.: https://www.drupal.org/project/drupal/issues/2939760

I've done couple of tests on this branch, and getting to run Aegir3 on Ubuntu 20.04 with this patch.

MySQL8 support of Drupal 7 still needs to be incorporated here, see my previous comments.

Contents of the patch:
• Merged patch of https://www.drupal.org/project/provision/issues/3145881#comment-13750463 into this branch
• debian/aegir3-hostmaster.postinst has to be changed, to adapt DB user creation also here, using code from https://www.drupal.org/project/provision/issues/3145881#comment-13750463
• scripts/ci-aegir-dev-install-apt-ubuntu-focal.sh needed also some clean-up, in particular to add sudo to debconf-set-selections, and set a more recent version of drush, to support for PHP4

Aegir3 will need a more recent drush version to run on PHP 7.4. Consider this patch here.

Workround is to manually set drush version via debconf, before installing the aegir packages:

sudo debconf-set-selections <<EOF
aegir3-provision aegir/drush_version string 8.3.4
EOF

Drupal 7 needs a core patch to support MySQL 8. The patch is there, but not yet committed in core. Without this patch Aegir cannot be installed with MySQL 8, or on a default Ubuntu 20.04 setup.

Consider this patch to get a workaround in Aegir, until the core patch gets into Drupal 7. It pins Drupal core and deploys core patch via makefiles of Provison.

Since the last update to this ticket, Drupal has added support for MySQL 8.

I've now successfully tested the Provision patch for User creation on #3145881: User creation has changed in MySQL 8.0 with:

• Ubuntu 20.04
• PHP 7.4.3
• MySQL 8.0.23
• Drush 8.3.4
• Drupal 7.78 (hostmaster)
• Drupal 9.1.4 (hosted site)

Should/could someone also update #3168480: Include Drupal 7 core support for MySQL 8, since no workaround for MySQL 8 is necessary once the above patch is accepted?
Also relevant, this ticket about updating drush: #3166952: update drush version in debian package

Added child issues and updated their states, and removed duplicate related ones. Let's keep child-ticket discussions in their respective tickets from now on.

Once those are closed, we can close this one too.

Added #3209815: Check that socket file actually exists when determining FPM mode re. FPM socket/port mode detection. Please review.

Added #3246691: Create an Aegir 3.20 release for recent stable Debian & Ubuntu LTS as a child issue.

For documentation and when planning Aegir migration paths: Aegir won't work with older PHP versions (7.3 and below) together with MySQL 8, since first PHP 7.4 has support for default MySQL 8 authentication method. See: https://stackoverflow.com/a/50027581/1509771