Upgrade mysql tests to use 8.4 or higher

[thompson-tomo]

When I was working on #2019 I had attempted to consolidate the mysql version on to 8.4 but I couldn't get the CI to pass.

The error which I was getting without any changes was and affected both mysql2 and trilogy:

Error: 2026-02-25T04:20:16.191787Z 0 [ERROR] [MY-000067] [Server] unknown variable 'default-authentication-plugin=mysql_native_password'.

This error can be addressed by changing the servicd command from

exec docker-entrypoint.sh mysqld --default-authentication-plugin=mysql_native_password

To

exec docker-entrypoint.sh mysqld --mysql-native-password=ON

The resolves the ci issues for mysql2 but not trilogy which was producing the following error:

Trilogy::BaseConnectionError: trilogy_auth_recv: caching_sha2_password requires either TCP with TLS or a unix socket: TRILOGY_UNSUPPORTED

To attempt to address this I have tried the following but no success or any change in the error:

• mounting an Init script see appendix 1 as a volume using ./mysql-init:/docker-entrypoint-initdb.d. Issue is volumes are apparently not supported for github action services?
• Adding a post install step to change permissions. See appendix 2
• read through Having caching_sha2_password authentication issues with mysql 8.0.30 trilogy-libraries/trilogy#26

In summary the issues are:

• cannot mount init scripts
• cannot use Unix sockets
• cannot inject TLS certs

Which all appear related to https://dev.mysql.com/doc/refman/8.4/en/caching-sha2-pluggable-authentication.html

My suggested solution would be to switch to mariadb.

---

Appendix 1

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;

Appendix 2

- name: Fix MySQL auth plugin for Trilogy
  run: |
    sudo apt-get update && sudo apt-get install -y mysql-client
    until mysql -h 127.0.0.1 -uroot -proot -e "SELECT 1"; do
      echo "Waiting for MySQL..."
      sleep 2
    done
    mysql -h 127.0.0.1 -uroot -proot <<'EOF'
      ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
      ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
      FLUSH PRIVILEGES;
    EOF