MySQL 8.4 LTS compatibility tracking

[petecooper]

MySQL 8.4 LTS is now GA: https://dev.mysql.com/doc/relnotes/mysql/8.4/en/

It is likely that hosting providers will deploy MySQL in due course, so we need to make sure where we stand regarding Textpattern compatibility. More info on support here: https://www.mysql.com/support/supportedplatforms/database.html

I'll make some time in the coming weeks for some outline testing, it's a good reason for me to finally work out how to compile it from source, anyway.

[Bloke]

Nice one, thank you. Of the big ticket items, the main thing that might hurt us is the password authentication.

See A cautionary tale for SQL statements we may need to advise users to run, or we may need to offer as part of the 'change password' feature to ensure account passwords are updated cleanly.

I'm a little unclear as to how I, as an admin, will now log into mysql from the command line, given they intimate that from 8.4 the mysql -u username -p no longer works, unless you power up the server with the native pluggable authentication switch on. Maybe that method of logging in still works but will only succeed if the password is migrated to caching_sha2_password first?

As usual, the docs are fairly impenetrable and unclear on whether there is now no way to log into 8.4 from the command line without some other form of authentication system in place (passwordless? tokens? mysql_config_editor?) or if it's simply a case that the mechanism of using -u -p still works, but the password itself will fail unless the hashing scheme is first altered.

[petecooper]

I'm a little unclear as to how I, as an admin, will now log into mysql from the command line, given they intimate that from 8.4 the mysql -u username -p no longer works, unless you power up the server with the native pluggable authentication switch on.

Using the default encryption option…

…straight in:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE "%version%";
+-----------------------------+------------------------------+
| Variable_name               | Value                        |
+-----------------------------+------------------------------+
| admin_tls_version           | TLSv1.2,TLSv1.3              |
| explain_json_format_version | 1                            |
| immediate_server_version    | 999999                       |
| innodb_version              | 8.4.0                        |
| original_server_version     | 999999                       |
| protocol_version            | 10                           |
| replica_type_conversions    |                              |
| slave_type_conversions      |                              |
| tls_version                 | TLSv1.2,TLSv1.3              |
| version                     | 8.4.0                        |
| version_comment             | MySQL Community Server - GPL |
| version_compile_machine     | x86_64                       |
| version_compile_os          | Linux                        |
| version_compile_zlib        | 1.2.13                       |
+-----------------------------+------------------------------+
14 rows in set (0.01 sec)

mysql> 

Remote connection works just fine with SequelAce - same route as MySQL 8.0 in that respect.

Using the legacy auth method, it breaks:

Setting up mysql-community-server (8.4.0-1debian12) ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
dpkg: error processing package mysql-community-server (--configure):
 installed mysql-community-server package post-installation script subprocess returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-community-server (= 8.4.0-1debian12); however:
  Package mysql-community-server is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
Processing triggers for man-db (2.11.2-2) ...
Processing triggers for libc-bin (2.36-9+deb12u7) ...
Errors were encountered while processing:
 mysql-community-server
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

…and doesn't create the socket, so when I try to connect it (expectedly) bombs out:

$ mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

NTS

• fix legacy auth as outlined above

[petecooper]

It lives! Textpattern 4.8.8 on MySQL 8.4.0 + PHP 8.3:

Textpattern version: 4.8.8 (a5969b336c096d872a7ecab9ee25914b)
Last update: 2024-05-09 19:55:20
Site URL: mysql84stronk.textpattern.co
Admin URL: mysql84stronk.textpattern.co/textpattern
Document root: /var/www/servers/textpattern.co/mysql84stronk/live
$path_to_site: /var/www/servers/textpattern.co/mysql84stronk/live
Textpattern path: /var/www/servers/textpattern.co/mysql84stronk/live/textpattern
Article URL pattern: messy
Production status: testing
Temporary directory path: /tmp
PHP version: 8.3.7
GD Graphics Library: 2.3.3; Supported formats: GIF, JPEG, PNG, WebP, AVIF.
Intl extension: 8.3.7
Multibyte String extension: 8.3.7
Server time zone: UTC
Server local time: 2024-05-09 20:00:29
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds):  (+3600)
MySQL: 8.4.0 (MySQL Community Server - GPL) 
Database server time: 2024-05-09 20:00:29
Database server time offset: 0 s
Database server time zone: SYSTEM
Database session time zone: SYSTEM
Locale: C
Site / Admin language: en / en
Web server: nginx/1.25.5
PHP server API: fpm-fcgi
PHP SSL version: OpenSSL 3.0.11 19 Sep 2023
RFC 2616 headers: 0
Server OS: Linux 6.1.0-21-amd64
Admin-side theme: hive 4.8.8

[petecooper]

First pass on a vanilla Textpattern dev with debugging enabled works fine - no visible warnings or errors when viewing pages.

[petecooper]

@Bloke - Here's a more user-friendly précis of MySQL from a Percona point of view: https://www.percona.com/blog/mysql-8-4-first-peek/

[petecooper]

Percona MySQL 8.4.0 now running on the demo server, installed with an in-place upgrade, keeping previous configs.

As shipped, MySQL 8.4.0 does not have the mysql_native_password plugin enabled (see https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html for background). Accordingly, an in-place upgrade throws this error:

Fatal error: Uncaught mysqli_sql_exception: Plugin 'mysql_native_password' is not loaded in /var/www/servers/textpattern.co/release-demo/live/textpattern/lib/txplib_db.php:247
Stack trace: #0 /var/www/servers/textpattern.co/release-demo/live/textpattern/lib/txplib_db.php(247): mysqli_real_connect()
#1 /var/www/servers/textpattern.co/release-demo/live/textpattern/lib/txplib_db.php(288): DB->__construct()
#2 /var/www/servers/textpattern.co/release-demo/live/textpattern/index.php(63): include('...')
#3 {main} thrown in /var/www/servers/textpattern.co/release-demo/live/textpattern/lib/txplib_db.php on line 247

Adding mysql_native_password=ON to mysql.cnf then restarting resolves the above error.

Basic diagnostics report MySQL 8.4.0 correctly:

Textpattern version: 4.8.8 (a5969b336c096d872a7ecab9ee25914b)
Last update: 2024-08-28 12:00:02
Site URL: release-demo.textpattern.co
Admin URL: release-demo.textpattern.co/textpattern
Document root: /var/www/servers/textpattern.co/release-demo/live
$path_to_site: /var/www/servers/textpattern.co/release-demo/live
Textpattern path: /var/www/servers/textpattern.co/release-demo/live/textpattern
Article URL pattern: messy
Production status: testing
Temporary directory path: /var/www/servers/textpattern.co/release-demo/live/textpattern/tmp
PHP version: 8.2.22
GD Graphics Library: bundled (2.1.0 compatible); Supported formats: GIF, JPEG, PNG, WebP, AVIF.
Intl extension: 8.2.22
Multibyte String extension: 8.2.22
Server time zone: UTC
Server local time: 2024-08-28 13:10:31
Daylight Saving Time enabled?: 0
Automatically adjust Daylight Saving Time setting?: 0
Time zone (GMT offset in seconds):  (+3600)
MySQL: 8.4.0-1 (Percona Server (GPL), Release '1', Revision '238b3c02') 
Database server time: 2024-08-28 13:10:31
Database server time offset: 1 s
Database server time zone: SYSTEM
Database session time zone: SYSTEM
Locale: C
Site / Admin language: en / en
Web server: nginx/1.27.1
PHP server API: fpm-fcgi
PHP SSL version: OpenSSL 3.0.13 30 Jan 2024
RFC 2616 headers: 0
Server OS: Linux 6.1.0-23-amd64
Admin-side theme: hive 4.8.8

Pre-flight check: 
------------------------
New Textpattern pre-release version 4.9.0-beta.1 available for download. Please visit the Textpattern website for more information.
Image directory is not writable: /var/www/servers/textpattern.co/release-demo/live/images
File directory path is not writable: /var/www/servers/textpattern.co/release-demo/live/files
Theme directory is not writable: /var/www/servers/textpattern.co/release-demo/live/themes
------------------------

[Bloke]

Right, so this is what I don't get. Ignore this and go here instead.

Presumably - and this is a guess - in the dim and distant past, the user accounts defaulted to mysql_native_password, perhaps created via the MySQL PASSWORD() function. So if I made a user 'stef' in an ancient Txp installation, I could carry on authenticating throughout the various versions and iterations because when the myslqi_real_connect()is encountered, it looked at the supplied password/cookie and it was of the format that identified it as a native password (is that $2y$10$? I'm not sure). It shrugged its shoulders and thought "silly old fool" and just logged me in using the old native method.

Fast forward to MySQL 8.4. That's now not baked in by default. So mysqli_real_connect() comes along, it's given the password string and realises it's of a format not supported, and then gives up.

Is that about right? Or have I missed something?

If so, we have two options:

1. Capture the plaintext password that's supplied on login and check if the hashed pass we're comparing it to is of the 'old' type (how?!) If so, apply something like ALTER USER '<dbuser>'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<OLD PSWD here>'; which should recreate the user's password using a newer algorithm. And hope they don't deprecate that one in future.
2. Invalidate (null) all entries in the pass column of txp_users in our upgrade script and thus force everyone to invoke Forgot Password to regenerate a new one using whatever method is baked into your MySQL version. We'd have to check that if a password is 'empty' that Txp doesn't allow login by supplying no password. It would have to see the 'null' and refuse login, triggering a password_reset_notification email somehow.

I might be way off, so if anyone who knows more about this can shed some light on why this is happening and what we can sanely do about it, I'd love to find out.

[Bloke]

Ignore this and keep scrolling....

What I truly don't get, if the above is true, is that on the demo server there are no 'old' passwords because they're regenerated fresh every 3 hrs.

So how can there be an exception saying that the native method isn't available when there's nothing I can see in our code base that even says we should use that ancient method to create or authenticate user passwords.

I must be missing something obvious in txplib_db.php.