Updating recipe with mysql database to PHP 8.4 causes mysql command errors

[yorkshire-pudding]

I have a Backdrop recipe with MySQL database; I have tried with default and 8.0
It has a setup script that runs some mysql commands to create additional databases and grant all privileges to the default database user.
It was previously on PHP 8.3 and this script ran fine.

When it runs now it gives the following error:

mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain

I also get this on mysqldump:

/usr/bin/mysqldump: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb-dump' instead
mysqldump: Got error: 2026: "TLS/SSL error: Certificate verification failure: The certificate is NOT trusted." when trying to connect

I ssh'd into the app to see what is happening:

$ lando ssh -u root
root@fc220caa9207:/app# ls -al /usr/bin/mariadb
-rwxr-xr-x 1 root root 5308896 Oct 30  2024 /usr/bin/mariadb
root@fc220caa9207:/app# ls -al /usr/bin/mysql
lrwxrwxrwx 1 root root 7 Oct 30  2024 /usr/bin/mysql -> mariadb
root@fc220caa9207:/app#

This doesn't make sense when it is configured for MySQL. Here is the relevant part of the output from lando info:

  { service: 'database',
    urls: [],
    type: 'backdrop-mysql',
    healthy: true,
    internal_connection: { host: 'database', port: '3306' },
    external_connection: { host: '127.0.0.1', port: '44787' },
    healthcheck:
     'mysql --defaults-extra-file=/app/.lando/mysql.cnf --silent --execute "SHOW DATABASES;"',
    creds: { database: 'backdrop', password: 'backdrop', user: 'backdrop' },
    config: { database: '/home/shl/.lando/config/backdrop/mysql.cnf' },
    version: '5.7',
    meUser: 'www-data',
    hasCerts: false,
    api: 3,
    hostnames: [ 'database.bee.internal' ],
    hostname: [] },

Switching back to PHP 8.3 and they all work. I don't understand this as the command is a linux command not a PHP command.
I have tested with another Backdrop recipe and get the same.

[yorkshire-pudding]

I've also tested this in a lamp recipe and I get the same

[AaronFeledy]

It looks like you have two issues here. Your script is failing due to a certificate error, and you are also getting an unrelated deprecation warning.

On the certificate error: What does your connection string look like on the MySQL commands?

On the deprecation warning: Lando's PHP service doesn't know whether you're connecting to a MySQL or a MariaDB database. We allow cross-compatibility by using the MariaDB binaries for both, since MariaDB has historically been fully compatible with both MySQL and MariaDB, while the reverse was not compatible. Recently, MariaDB has started distancing itself from its MySQL roots, including deprecating the use of a mysql alias pointing at mariadb. We'll likely need to think of a new strategy for future versions, but for now, this is just a warning and they should still be compatible.

If your script doesn't rely on PHP, I would try running it on your database service, where it will have the proper matching mysql binaries.

[yorkshire-pudding]

Hi @AaronFeledy . Ok - I think I'll have to look at a long term solution for the binaries as a PHP command line tool I manage uses the mysql and mysqldump binaries.

Regarding the certificates:

The script that does the setup runs under:

services:
  appserver:
    run:

events:
  pre-rebuild:

events:
  pre-destroy:

and here is an example statement:

mysql -h database -u root -e "DROP DATABASE backdrop;"

I've tried switching the binaries in the script and that gets rid of the deprecation warning but not the certificate error.
The script works on multiple databases but all on the same database server.

It could be problematic to run the script on the database server as I would have to split it and I don't know if I can ensure the database scripts run before appserver scripts.

It all works on PHP 8.3 so I'm baffled

Is this significant in lando info: hasCerts: false under the service: 'database'

[AaronFeledy]

The older PHP 8.3 image probably has a older versions of the mariadb cli tools installed that don't require SSL validation. You might be able to work around it by using the fully qualified name for the host: database.bee.internal.

Lando should probably be doing something different here if you're getting errors like this in what should be a common use case. I'm unsure if this is a Lando bug generally, or if you've stumbled upon some errant state with certificates. Can you post some minimal representation of a landofile and a script that will trigger the error so I can see if I can replicate and identify a specific bug on the Lando side?

[yorkshire-pudding]

Hi @AaronFeledy - if you go to https://github.com/backdrop-contrib/bee you can see the repo with the lando file and setup script. The only differences I've got to locally is changing PHP to 8.4 on both main config and multisite service. Also I've been tweaking the setup script to try and fix issues.

I've got a way I think should fix the mysql executables in the tool but only if I can sort the certificate.

This is the current setup script:

.lando/setup.sh

#!/bin/sh
###
## Shell script to setup Bee's testing environment.
###

### Clean-up tasks that get the system back to a clean slate.
clean_up() {
  cd /app

  # Remove Backdrop installations.
  rm -rf backdrop/ multisite/ current/ directory/ defined_release/

  # Drop databases.
  mariadb -h database.bee.internal -u root -e "DROP DATABASE backdrop;"
  mariadb -h database.bee.internal -u root -e "DROP DATABASE multi_one;"
  mariadb -h database.bee.internal -u root -e "DROP DATABASE multi_two;"
  mariadb -h database.bee.internal -u root -e "DROP DATABASE install_test;"
}

### Setup tasks that need to run on a clean slate.
set_up() {
  cd /app

  # Create databases.
  mariadb -h database.bee.internal -u root -e "CREATE USER IF NOT EXISTS 'backdrop'@'%' IDENTIFIED BY 'backdrop';"
  mariadb -h database.bee.internal -u root -e "CREATE DATABASE IF NOT EXISTS backdrop; GRANT ALL PRIVILEGES ON backdrop.* TO 'backdrop'@'%';"
  mariadb -h database.bee.internal -u root -e "CREATE DATABASE IF NOT EXISTS multi_one; GRANT ALL PRIVILEGES ON multi_one.* TO 'backdrop'@'%';"
  mariadb -h database.bee.internal -u root -e "CREATE DATABASE IF NOT EXISTS multi_two; GRANT ALL PRIVILEGES ON multi_two.* TO 'backdrop'@'%';"
  mariadb -h database.bee.internal -u root -e "CREATE DATABASE IF NOT EXISTS install_test; GRANT ALL PRIVILEGES ON install_test.* TO 'backdrop'@'%';"
  mariadb -h database.bee.internal -u root -e "FLUSH PRIVILEGES;"

  # Configure Backdrop installation.
  unzip -q backdrop.zip && mv backdrop-1.x backdrop
  echo 'if (isset($_SERVER["BACKDROP_SETTINGS"])) unset($_SERVER["BACKDROP_SETTINGS"]);' >> backdrop/settings.php

  # Disable sending of telemetry data from GitHub Action runners. Override if
  # testing telemetry locally.
  echo '$settings["telemetry_enabled"] = FALSE;' >> backdrop/settings.php

  # Configure multisite installation.
  cp -r backdrop multisite
  cd multisite
  echo '$sites["multi-1.lndo.site"] = "multi_one";' >> sites/sites.php
  echo '$sites["multi-2.lndo.site"] = "multi_two";' >> sites/sites.php
  echo '$sites["install-test.lndo.site"] = "install_test";' >> sites/sites.php
  mkdir sites/multi_one && cp settings.php sites/multi_one/settings.php
  mkdir sites/multi_two && cp settings.php sites/multi_two/settings.php
  mkdir sites/install_test && cp settings.php sites/install_test/settings.php

  # Install sites.
  cd ../backdrop
  ./core/scripts/install.sh --account-pass=password --db-url=mysql://backdrop:backdrop@database/backdrop
  cd ../multisite
  ./core/scripts/install.sh --url=multi-1.lndo.site --account-pass=password --site-name="Multisite 1" --db-url=mysql://backdrop:backdrop@database/multi_one
  ./core/scripts/install.sh --url=multi-2.lndo.site --account-pass=password --site-name="Multisite 2" --db-url=mysql://backdrop:backdrop@database/multi_two
  # The `install_test` site is only installed during test runs, not here.
}

### Run different tasks depending on any arguments passed to the script.
if [ -z $1 ]; then
  # No argument, run everything.
  clean_up
  set_up
else
  if [ $1 = 'clean' ]; then
    # Just clean-up.
    clean_up
  else
    # Just setup.
    set_up
  fi
fi

There is no difference using the fully qualified host name.

I also get the certificate error on a much simpler recipe that just has one backdrop service (+phpmyadmin and mailpit)

[yorkshire-pudding]

Hi @AaronFeledy - have you managed to replicate yet? Is there anything else I could try on my side to address this? Not being able to develop and test in PHP 8.4 is a bit of a problem for me.

[yorkshire-pudding]

Is this issue related to #162 ?

[AaronFeledy]

This might work as a workaround to the cert issue:

services:
  appserver:
    build_as_root:
      - echo "[client]\nssl-verify-server-cert = off" > /etc/mysql/mariadb.conf.d/zz-lando.cnf

[yorkshire-pudding]

Thanks @AaronFeledy

That workaround does fix the issue for the rebuild tasks. I'm giving some thought to how best to add this to the command line tool; maybe I just need it in Lando or maybe it needs to be a wider option.

It does seem that there are issues with both the certificate and the use of symlinks (mysql -> mariadb) that with PHP 8.4 cause issues that need to be addressed.

For the aliases, I would of thought there would be a way that the PHP plugin could know that the Lando machine was running with either MySQL or MariaDB and include the correct executables without needing symlinks.

I'm not sure what the best approach long term will be for the certificate issue.

[yorkshire-pudding]

@AaronFeledy Would it be feasible to install both clients and make which client is used a config setting for the recipe?

[AaronFeledy]

This should set you up with the MySQL cli tools:

services:
  appserver:
    build_as_root:
      - cd /var/www && wget https://dev.mysql.com/get/mysql-apt-config_0.8.34-1_all.deb
      - export DEBIAN_FRONTEND=noninteractive && apt update && apt install -y /var/www/mysql-apt-config_0.8.34-1_all.deb
      - apt update && apt install -y mysql-client

[yorkshire-pudding]

Thanks @AaronFeledy
This is a definite improvement though one thing I'm struggling with: using mysqldump within the appserver I get the following:

-- Warning: column statistics not supported by the server.

I want to disable this warning at the lando level (it works on other servers) as having output like this can break the tests.

I tried:

services:
  appserver:
    build_as_root:
      - echo "[mysqldump]\ncolumn-statistics=0\n" > /etc/mysql/conf.d/zz-lando.cnf

and

services:
  appserver:
    build:
      - echo "[mysqldump]\ncolumn-statistics=0\n" > ~/.my.cnf

In both cases, the files are created but the error persists. I think there is now a dodgy mix of mariadb and mysql configuration that is stopping it working:

root@6ef2abdbf29f:/app/backdrop# cd /etc/mysql/
root@6ef2abdbf29f:/etc/mysql# ls -al
total 28
drwxr-xr-x 1 root root 4096 Jun 23 09:23 .
drwxr-xr-x 1 root root 4096 Jun 23 09:23 ..
drwxr-xr-x 1 root root 4096 Jun 23 09:23 conf.d
-rw-r--r-- 1 root root 1126 Oct 30  2024 mariadb.cnf
drwxr-xr-x 2 root root 4096 Jan  8 18:52 mariadb.conf.d
lrwxrwxrwx 1 root root   24 Jan  8 18:52 my.cnf -> /etc/alternatives/my.cnf
root@6ef2abdbf29f:/etc/mysql# ls -al conf.d/
total 16
drwxr-xr-x 1 root root 4096 Jun 23 09:23 .
drwxr-xr-x 1 root root 4096 Jun 23 09:23 ..
-rw-r--r-- 1 root root   32 Jun 23 09:23 zz-lando.cnf
root@6ef2abdbf29f:/etc/mysql# cat my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# 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.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Any ideas how to get the config working correctly?

[AaronFeledy]

Try this:

services:
  appserver:
    build_as_root:
      - cd /var/www && wget https://dev.mysql.com/get/mysql-apt-config_0.8.34-1_all.deb
      - export DEBIAN_FRONTEND=noninteractive && apt update && apt install -y /var/www/mysql-apt-config_0.8.34-1_all.deb
      - apt update && apt install -y mysql-client
      - echo "[mysqldump]\nskip-column-statistics" > /etc/mysql/conf.d/zz-lando.cnf

You can verify that the config is correct with lando exec appserver -- mysqldump --print-defaults.

[yorkshire-pudding]

Thanks again @AaronFeledy
I suddenly realised why it wasn't working for my application (PHP CLI based):

• To avoid MySQL warnings about supplying credentials on the command line I set it up (some time ago) to write the db credentials to a temporary .cnf file
• The application then read this with --defaults-file=$temp_filename
• This was cancelling out any config at the server or account level. I verified this by successfully running mysqldump within lando ssh

I changed it to --defaults-extra-file=$temp_file name and now the added default is applied. I confirmed this works with both column-statistics=0 and skip-column-statistics and note there is a bug reported about the inconsistency between the documentation and the client application help text.
I also note this also applies to a secondary PHP service, which is good.

For the benefit of anyone coming to this issue, with the solution above, I no longer have a certificate issue so can drop the line added here.

This is a suitable workaround to allow me to proceed with testing my CLI tool on PHP 8.4, but this feels like it could be very onerous to do for any recipe that I (or others) want to upgrade to PHP 8.4. Given the continued divergence of MariaDB from MySQL is there a case to address this use of symlinks so it works out of the box? The Backdrop recipe comes with the tool I maintain and Drush is shipped with Drupal recipes, which also includes mysql and mysqldump. WordPress also uses mysqldump in wp-cli. I don't know about other recipes