Install matching psql/pg_dump client inside ddev-webserver based on postgres version

[rfay]

In general, the mysql client shipped with the ddev-webserver works fine with all mariadb and mysql back-end databases.

However, there are a few cases where their behavior is slightly different.

And the fact that the mysql client reports a different version is often confusing to people.

For example, with database mysql:8.0 we see these outputs:

rfay@rfay-mbp-2021:~/workspace/d10$ ddev exec mysql --version
mysql  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper

rfay@rfay-mbp-2021:~/workspace/d10$ ddev exec -s db mysql --version
mysql  Ver 8.0.33-0ubuntu0.20.04.2 for Linux on aarch64 ((Ubuntu))

The web container has the mariadb version, and the db container has the mysql:8.0 version.

On Craft CMS and perhaps some other places, people have the habit of using the built-in tools; Craft actually calls out to the mysql client (on ddev-webserver) rather than connecting directly to the database server, so this can cause confusion there.

• https://discord.com/channels/664580571770388500/1227934688505630721/1227934688505630721
• https://discord.com/channels/664580571770388500/1189581731301429318
• https://discord.com/channels/664580571770388500/1160230833601392791/1160258585633247302

Proposal

Add a layer to the ddev-webserver adding a matching mysql client to the ddev-webserver, especially where the database type is mysql:8.0, which is the most likely place for concern.

It also might be possible to implement this as an add-on for the few people it matters to. It's tricky getting the proper match between the OS-provided mysql client and the ddev-webserver, and it could introduce instabilities.

[stasadev]

Answering to #6139 (comment) in the relevant issue:

Is there something like that where we can get the mysql version of the client also?

There is mysql-apt-config https://dev.mysql.com/doc/refman/8.4/en/linux-installation-apt-repo.html

The current latest version is 0.8.30 https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb

How to install mysql-server in non-interactive way https://stackoverflow.com/a/37267411/8097891

The SO example needs to be modified to change the server to client, but I think it's an achievable goal.

[rfay]

I'm not sure there's a client package, but if there is this can be great!

[rfay]

Now that MariaDB has released mariadb-dump/mysqldump versions that are completely incompatible with "traditional" versions, we're going to have to prioritize this.

From: https://mariadb.com/kb/en/mariadb-dump/

Note: From MariaDB 10.5.25, MariaDB 10.6.18, MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5, MariaDB 11.2.4 and MariaDB 11.4.2 mariadb-dump generates a command at the beginning of the dump to enable sandbox mode. This command cannot be interpreted by earlier versions of the MariaDB command line client or by MySQL's command line client, and the client will generate an error if used against the versions that do not support it. This does not affect other methods of importing the data.

Basically, all the latest supported patch versions of mysqldump from mariadb are completely incompatible (without workarounds) with the previous patch version. So mariadb-dump/mysqldump 10.6.18's version is incompatible with 10.6.17. What's up with them!!!

• MariaDB 10.6.18 seems to generate invalid SQL dumps
• mariadb-dump trusts the server and does not validate the data

Unfortunately, there isn't much we can do about it. This change came as a fix for CVE-2024-21096, and security fixes can sometimes break backward compatibility.

In this particular case, though, there are plenty of workarounds:

• you can use the new mariadb client to restore the backup on the old MariaDB/MySQL server
• you can use old mariadb-dump to make a backup of the new MariaDB server
• you can use mariadb-dump|tail +2 to remove the problematic line when making a backup
• you can use tail +2|mariadb to remove the problematic line when applying a backup

[manutepowa]

Indeed mariadb has added sandbox mode, and database backups fail.
https://jira.mariadb.org/browse/MDEV-34183

[rfay]

MariaDB Blog about the change to dump file format: https://mariadb.org/mariadb-dump-file-compatibility-change/

[cristiroma]

FYIW - We are mitigating with the following commands on the web container:

docker exec -ti ddev-PROJECT-web bash
sudo apt remove mariadb-client -y
sudo curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.11"
sudo apt install mariadb-client -y

[rfay]

Thanks @cristiroma - It would be a lot better as a .ddev/web-build/Dockerfile.mariaclient or something, you wouldn't have to do it all the time.

[v11ncent]

I was able to import a file dumped with the new mariadb-dump by deleting the line at the top of the dump that says /*!999999\- enable the sandbox mode */ in my MariaDB dump file.

[rfay]

The affected DDEV database images have been pushed with updated mariadb tools, please see

• ddev import-db fails with "Error: Unknown command '\-'" because of new directive in mariadb-dump output #6249 (comment) if you're dealing with the broken mysqldump issues.

[Defcon0]

FYIW - We are mitigating with the following commands on the web container:

docker exec -ti ddev-PROJECT-web bash
sudo apt remove mariadb-client -y
sudo curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.11"
sudo apt install mariadb-client -y

Doesn't work anymore:

$ sudo apt remove mariadb-client -y
sudo curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.11"
sudo apt install mariadb-client -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl libmariadb3 libterm-readkey-perl mariadb-client-core mariadb-common mysql-common
Use 'sudo apt autoremove' to remove them.
The following packages will be REMOVED:
  mariadb-client
0 upgraded, 0 newly installed, 1 to remove and 9 not upgraded.
After this operation, 64,4 MB disk space will be freed.
(Reading database ... 59191 files and directories currently installed.)
Removing mariadb-client (1:10.11.6-0+deb12u1) ...
bash: line 1: error: command not found
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
  mariadb-client
0 upgraded, 1 newly installed, 0 to remove and 9 not upgraded.
Need to get 2.931 kB of archives.
After this operation, 64,4 MB of additional disk space will be used.
Get:1 http://deb.debian.org/debian bookworm/main amd64 mariadb-client amd64 1:10.11.6-0+deb12u1 [2.931 kB]
Fetched 2.931 kB in 0s (7.489 kB/s)      
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package mariadb-client.
(Reading database ... 59089 files and directories currently installed.)
Preparing to unpack .../mariadb-client_1%3a10.11.6-0+deb12u1_amd64.deb ...
Unpacking mariadb-client (1:10.11.6-0+deb12u1) ...
Setting up mariadb-client (1:10.11.6-0+deb12u1) ...

[stasadev]

@Defcon0,

It's working, https://r.mariadb.com/downloads/mariadb_repo_setup was down, but it's fine now.

[rfay]

The mariadb:10.11 approach described above (from https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/) does work currently.

However, 10.6 does not work, on either amd64 or arm64 with Debian Bookworm:

$ sudo curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.6"
# [info] Checking for script prerequisites.
# [error] MariaDB Server version 10.6 is not working.
#         Please verify that the version is correct.
#         Not all releases of MariaDB are available on all distributions.
#
#         The latest MariaDB Server versions are:
#             10.4.34 10.5.25 10.6.18 10.11.8 11.0.6 11.1.5 11.2.4 11.4.2 11.5.1
#
#         More information on MariaDB releases is available at:
#             https://mariadb.com/kb/en/release-notes/

And of course, 10.6 is the exact example they provide in their docs.

Opened https://jira.mariadb.org/browse/MDEV-34339

[rfay]

However, I'll bet that the mariadb 10.11 client will work for most of the mariadb situations. That's a pretty simple approach.

[rfay]

mysql has never provided arm64 packages for Debian/Ubuntu, and they still don't. Info about their apt repo is at https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

Ubuntu does package mysql server for arm, it's conceivable that we can steal the client and its dependencies from a related Ubuntu version. This is how we do mysql server for DDEV, see https://ddev.readthedocs.io/en/stable/developers/release-management/#maintaining-ddev-dbserver-mysql-57-and-80-arm64-images

[rfay]

It looks like if we want to do the mysql client, we'll have to build from source, https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/source-installation.html, as we do for xtrabackup.

[rfay]

One thing we could do for mariadb is to replace mysqldump and mysql with wrappers that strip /*!999999\- enable the sandbox mode */. That sure seems intrusive to me.

[rfay]

I think

• fix: Use mariadb repositories for mariadb-client, for #6083 #6290 (comment) may solve this problem for those who have application-defined backup/restore packages that happen in the web container. I'd sure appreciate it if you'd test.

[rfay]

Thanks for testing!

If you can please be specific about what the problem is and how to reproduce it, I may be able to help. Otherwise, not.

MySQL 8.0 is not compatible with earlier and simpler versions of mysql or mariadb. MySQL 5.6 is mostly.

Please describe exactly what the problem is and how to reproduce it.

[rfay]

@timnolte if you have time, could you make an appointment with me so we could work on this together? We're planning a release for the next day or two, and would love to know what your problem is. https://cal.com/randyfay/30min

[timnolte]

@rfay so here are some more details that I also posted in the HyperDB support forums.

---

To follow up with either MySQl 5.7 or MySQL 8.0 with HyperDB active the following is an example of the sort of error when trying to install a site the very first time.

WordPress database error: [Invalid default value for 'user_registered']
CREATE TABLE wp_users ( ID bigint(20) unsigned NOT NULL auto_increment, user_login varchar(60) NOT NULL default '', user_pass varchar(255) NOT NULL default '', user_nicename varchar(50) NOT NULL default '', user_email varchar(100) NOT NULL default '', user_url varchar(100) NOT NULL default '', user_registered datetime NOT NULL default '0000-00-00 00:00:00', user_activation_key varchar(255) NOT NULL default '', user_status int(11) NOT NULL default '0', display_name varchar(250) NOT NULL default '', PRIMARY KEY (ID), KEY user_login_key (user_login), KEY user_nicename (user_nicename), KEY user_email (user_email) ) DEFAULT CHARACTER SET utf8

Without HyperDB active this error doesn't occur. After reactivating HyperDB then there are issues with creating new posts with the following sort of error:

Fatal error: Uncaught Error: Attempt to assign property "post_content" on null in /var/www/html/web/wp/wp-admin/includes/post.php:808 Stack trace: #0 /var/www/html/web/wp/wp-admin/post-new.php(66): get_default_post_to_edit('page', true) #1 {main} thrown in /var/www/html/web/wp/wp-admin/includes/post.php on line 808

From everything we've seen this doesn't occur with MariaDB versions or AWS Aurora DB versions.

---

When we use MySQL 5.6 none of those above issues occur, however it rendered DB operations with the WP-CLI unusable.

We can't use MariaDB locally either because when we attempt to pull/restore from a server backup(which is an AWS Aurora DB backup) we hit errors related to collation.

[rfay]

I hope you'll make an appointment.

• Collation-type problems are one of the key differences between Mysql 8.x and MariaDB
• According to Wikipedia, "As of December 2021, Amazon Aurora is compatible with MySQL 5.6, 5.7, and 8.0". So you'll want to be checking which version you're using on Amazon Aurora. Could you please check?

It seems like you're probably on MySQL 5.6 on Aurora and that's the fundamental issue.

[rfay]

You can find out what you're using on a server with the query SELECT VERSION();

[rfay]

(I just tried building the mysql 5.6 client for Debian Bookworm and it failed. I think a workaround might be to install the mysql 5.7 client in ddev-webserver when mysql 5.5 or 5.6 is configured.)

[rfay]

@timnolte I also assume that if you're using mysql 5.6, you must be on an Intel processor, right? DDEV hasn't ever supported Mysql 5.6 on arm64 because Mysql images have never had arm64 for 5.6.

[rfay]

@timnolte Could you please test the artifacts in

• fix: Support MySQL 5.5 and 5.6 on ddev-webserver by using MySQL 5.7 clients #6309 (comment) ?

[timnolte]

@timnolte I also assume that if you're using mysql 5.6, you must be on an Intel processor, right? DDEV hasn't ever supported Mysql 5.6 on arm64 because Mysql images have never had arm64 for 5.6.

@rfay so I am on Linux Intel, however, we do have Devs on arm64(macOS M#). So there is that aspect that is going to be at play.

[timnolte]

I hope you'll make an appointment.

• Collation-type problems are one of the key differences between Mysql 8.x and MariaDB
• According to Wikipedia, "As of December 2021, Amazon Aurora is compatible with MySQL 5.6, 5.7, and 8.0". So you'll want to be checking which version you're using on Amazon Aurora. Could you please check?

It seems like you're probably on MySQL 5.6 on Aurora and that's the fundamental issue.

I know for a fact actually that our Aurora DB is running the MySQL 8.0 engine, which is certainly why we were having issues with attempting to use MariaDB locally. Generally, perform imports from our Aurora DB is working with 5.6/5.7/8.0.

[rfay]

I'm baffled why you aren't able to use MySQL 8.0 as a database in HEAD then. If you can give explicit results of importing/exporting with database set to mysql:8.0 and using DDEV HEAD it will be appreciated.

I don't understand why you were trying to use MariaDB locally in DDEV.

[rfay]

Testing techniques for validating mysql 8.0 with a db dump from mysql 8.0 @timnolte :

• Use current HEAD
• Use database type mysql:8.0
• Download a db dump and just put it in your project root
• ddev ssh
• mysql --version should show the matching version
• Use mysql db < db.sql or gzip -dc db.sql.gz | mysql db to do an import
• See the results

If you can create a db dump that demonstrates the problem (can be a trivial dump) I'll be happy to work with it.

[timnolte]

I'm baffled why you aren't able to use MySQL 8.0 as a database in HEAD then. If you can give explicit results of importing/exporting with database set to mysql:8.0 and using DDEV HEAD it will be appreciated.

I don't understand why you were trying to use MariaDB locally in DDEV.

@rfay so using MariaDB was an initial failure in local project setup, since MariaDB is the default when initializing a new DDev project. This was also for a completely new infrastructure with a new Aurora DB instance that is using the MySQL 8.0 engine. Our legacy infrastructure was using an older Aurora DB instance that was in fact using a MySQL 5.6 engine. We had been quietly succeeding to use MariaDB locally even though we should have been using MySQL locally in DDev all along. It's only this new infrastructure recent local project setups where this issue surfaced.

If you want to try and reproduce these issues if you setup a WordPress site, using say MySQL 8.0 as the DB in DDev. Setup the HyperDB plugin which also involves setting up some configuration and either copies or symlink of DB drop-ins for WordPress. At which point you try to run the WordPress web-based install you'll see the errors and failures I mentioned in #6083 (comment)

All that being said. I have found that there is a fork of the HyperDB plugin(LudicrousDB) that is more recently maintained by the community and it actually appears to be working with MySQL 8.0 without the issues we've been seeing. I'm using the nightly with the MySQL 5.7/8.0 fixes. So, while I think ultimately DDev using matching clients it a good thing, and things look on track with that, there is a part of this that I think is an issue with HyperDB and not strictly a DDev issue.

[timnolte]

Testing techniques for validating mysql 8.0 with a db dump from mysql 8.0 @timnolte :

• Use current HEAD
• Use database type mysql:8.0
• Download a db dump and just put it in your project root
• ddev ssh
• mysql --version should show the matching version
• Use mysql db < db.sql or gzip -dc db.sql.gz | mysql db to do an import
• See the results

If you can create a db dump that demonstrates the problem (can be a trivial dump) I'll be happy to work with it.

@rfay to clarify the issues we were having with MySQL weren't related to loading in our database dumps, only if we didn't initialize or convert our project from MariaDB. The problem was though that when using MySQL 5.7 or 8.0 we could not start a new project or successfully use WordPress content editing without major issues while having the HyperDB plugin active. We didn't have these problems with MySQL 5.6 or our AuroraDB with MySQL 8.0 instances.

[rfay]

So from your description your problem has nothing to do with this issue? It's all about you using a non-matching database type in DDEV?

If you're using MySQL for server side project, please use MySQL in DDEV. And please use matching version number.

[timnolte]

So from your description your problem has nothing to do with this issue? It's all about you using a non-matching database type in DDEV?

If you're using MySQL for server side project, please use MySQL in DDEV. And please use matching version number.

@rfay so there were 4 issues at play, with 3 of them(2-4) being related to this issue.

1. If using MariaDB locally, then import collation issues with our AuroraDB imports. (That was resolved by making sure we use MySQL locally in DDev.)
2. Initial WordPres site setup with MySQL 5.7/80 preventing the ability to properly setup a new WordPress site in the database. (see: Install matching psql/pg_dump client inside ddev-webserver based on postgres version #6083 (comment))
3. If starting with MySQL 5.6 to get a successful install, converting to MySQL 5.7 or 8.0, this causes WordPress content editing failures as the databases would seem to be converted in a corrupted/incompatible way. (see: Install matching psql/pg_dump client inside ddev-webserver based on postgres version #6083 (comment))
4. The WordPress HyperDB plugin seems to be incompatible with MySQL 5.7/8.0 in DDev, but works fine with MariaDB and AuroraDB(with MySQL engine 8.0).

[timnolte]

@rfay Ultimately, I think we will be in better shape if for our projects we move to LudicrousDB and MySQL 8.0, however in order for us to be able to continue to use the WP-CLI for DB operations we will need the fixes related to this issue. So far testing with the MySQL 8.0 fixes here seems to resolve things. I can test the MySQL 5.6 related fix, but to your point MySQL 5.6 is EOL so should we really focus on that right now?

[rfay]

2. Initial WordPres site setup with MySQL 5.7/80 preventing the ability to properly setup a new WordPress site in the database.
   Do you mean creating a project from scratch on DDEV when using Mysql 5.7 or 8.0 fails? Does it work anywhere else using Mysql 5.7 or 8.0? I wouldn't think that it would use the mysql client on the webserver for anything in initial setup.

3. If starting with MySQL 5.6 to get a successful install, converting to MySQL 5.7 or 8.0, this causes WordPress content editing failures

You get a successful install and then you fail converting? That seems like a normal problem with trying to upgrade from an obsolete version to a current one? Doesn't seem to have to do with DDEV?

4. The WordPress HyperDB plugin seems to be incompatible with MySQL 5.7/8.0 in DDev, but works fine with MariaDB and AuroraDB(with MySQL engine 8.0)

I don't think there's anything we can do about that. DDEV's mysql 5.7 and 8.0 are simply running database engines as provided by MySQL.

So far testing with the MySQL 8.0 fixes here seems to resolve things. I can test the MySQL 5.6 related fix, but to your point MySQL 5.6 is EOL so should we really focus on that right now?

I'm glad to hear about resolution. Yes, if I were you I wouldn't be making mysql 5.6 a part of the problem or solution, just adds complexity and probably additional pain.

I have no idea how any of this could have to do with the client versions in the ddev-webserver though.

[timnolte]

Do you mean creating a project from scratch on DDEV when using Mysql 5.7 or 8.0 fails? Does it work anywhere else using Mysql 5.7 or 8.0? I wouldn't think that it would use the mysql client on the webserver for anything in initial setup.

@rfay so yes. Starting a new project with MySQL 5.7 or 8.0. The WordPress install doesn't work and fails to create tables. This is somehow related to HyperDB and collation issues it seems. That being said performing the initial WordPress install(the 5-minute install where you enter in the site name/admin user/etc) while using HyperDB and AuroraDB, or when using HyperDB with MariaDB or MySQL 5.6 locally in DDev, works just fine. However, if we try to initialize a project with DDev and use MySWL 5.7 or 8.0 it won't work.

[rfay]

HyperDB appears to be very lightly maintained, or perhaps not maintained, and not even tested with current WP versions... We definitely can't solve that.

[timnolte]

HyperDB appears to be very lightly maintained, or perhaps not maintained, and not even tested with current WP versions... We definitely can't solve that.

@rfay yeah, I mean it's from Automattic, and supposedly used on WordPress.com. Up until now, and at least with MariaDB, it hasn't been an issue.

[rfay]

I think the final step in this issue is to try to install the matching pg_dump and psql in the web image.

[rfay]

Renamed this to focus on the remaining psql issues.

[stasadev]

I think the final step in this issue is to try to install the matching pg_dump and psql in the web image.

And it's fixed, closing.

• fix: Install matching postgresql client versions in ddev-webserver, fixes #6139 #6385