Upgrade to MySQL 9.7 LTS

[RobHooper]

A new LTS version of MySQL is available 9.7 (We will use Percona MySQL).

One change that affects us is the deprecation and removal of mysql_native_password, instead users should move to caching_sha2_password.

However there is an open issue with SaltStack as support for this new option is not fully integrated yet:
saltstack/salt#66859

Once Salt is updated we can move forward with this.

The warning we are currently seeing:

[Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

Servers:

• CMS

[jpmckinney]

I think the limitations are:

• You can't change the auth_plugin via Salt. Need to run manually.
• If you change the password with auth_plugin set to caching_sha2_password, Salt will not update or verify the password.

But there's a workaround for getting the password to update and verify:

• Use password_hash instead of password (leave the plaintext password in a comment)

caching_sha2_password should already work with MySQL 8 so we can try it.

[jpmckinney]

I changed coalition's auth_plugin over.

[jpmckinney]

Claude instructions:

Details

Rotating MySQL passwords on ocp21 (CMS server)

Covers the corporate and digitalbuying accounts, which both use mysql_native_password with a plaintext password: in the pillar.

Before you start

Salt issues the ALTER USER for you, but it does not update the application's copy of the password. Neither wp-config.php nor a running container is reconciled by a highstate. Every rotation is therefore a two-part change, with a window in between where the app cannot reach the database. Use the zero-downtime variant if that window is unacceptable.

Generate an alphanumeric password. Both existing passwords are 24-char alphanumeric, and this avoids SQL quoting problems and — for digitalbuying — URL encoding problems:

LC_ALL=C tr -dc 'A-Za-z0-9' < /dev/urandom | head -c 24; echo

All salt-ssh commands run from the repository root. All mysql commands run as root on the server, which authenticates via auth_socket, so no password is needed:

ssh root@ocp21.open-contracting.org

Run the verification steps in an interactive SSH session rather than as ssh root@… "…", so the new password does not land in your local shell history.

---

A. corporate (www.open-contracting.org)

The password lives in two places:

Location	Managed by Salt?
pillar/private/corporate.sls → mysql.users.corporate.password	Yes — drives the ALTER USER
/home/corporate/public_html/wp-config.php → DB_PASSWORD (line 67)	No — edit manually

Steps

1. Edit the pillar:

   # pillar/private/corporate.sls
   mysql:
     users:
       corporate:
         password: <NEW_PASSWORD>

2. Preview. Expect Password for user corporate@localhost is set to be changed:

   salt-ssh cms state.apply mysql test=True

3. Apply. The site is down from this point.

   salt-ssh cms state.apply mysql

4. Update WordPress. On the server:

   cp -a /home/corporate/public_html/wp-config.php \
         /home/corporate/public_html/wp-config.php.bak-$(date +%F)
   sed -i "s/^define('DB_PASSWORD'.*/define('DB_PASSWORD', '<NEW_PASSWORD>');/" \
         /home/corporate/public_html/wp-config.php
   grep -n "DB_PASSWORD" /home/corporate/public_html/wp-config.php

   Note corporate uses define('DB_PASSWORD', '…'); with no inner spaces, whereas coalition uses define( 'DB_PASSWORD', '…' );. The sed above is written for corporate.

5. Verify:

   php -r 'require "/home/corporate/public_html/wp-config.php";
           echo DB_USER, " ", strlen(DB_PASSWORD), PHP_EOL;'
   curl -sI https://www.open-contracting.org/ | head -1
   curl -s https://www.open-contracting.org/ | grep -ci "error establishing a database connection"

   Expect HTTP/2 200 and a count of 0.

Cautions

• Do not change DB_HOST. It is 'localhost', which mysqlnd resolves to the Unix socket. That is the only reason the corporate@localhost grant matches — the server runs with skip-name-resolve, so a TCP connection would present as 127.0.0.1 and be rejected with ERROR 1130.
• A plugin rewrites wp-config.php. On 2026-08-05 it and .htaccess were both modified at 19:25:04 with no SSH session at that time; ithemes-security-pro and w3-total-cache are both installed. Take the backup in step 4 and re-check your edit landed.

---

B. digitalbuying (digitalbuying.open-contracting.org)

The password appears twice in the same pillar file. Editing only one of them leaves the database and the application out of sync, and nothing validates that they match.

Location	Managed by Salt?
pillar/private/digitalbuying.sls → mysql.users.digitalbuying.password	Yes — drives the ALTER USER
pillar/private/digitalbuying.sls → docker_apps.digitalbuying.env.DATABASE_URL	Yes — rendered into .env
/data/deploy/digitalbuying/.env	Yes, via salt/docker_apps/init.sls:30

Steps

1. Edit both pillar entries:

   # pillar/private/digitalbuying.sls
   mysql:
     users:
       digitalbuying:
         password: <NEW_PASSWORD>
         host: "172.16.0.0/12"
   
   docker_apps:
     digitalbuying:
       env:
         DATABASE_URL: "mysql://digitalbuying:<NEW_PASSWORD>@host.docker.internal:3306/digitalbuying"

   The password is embedded in a URL, so it must be URL-safe — a further reason to keep it alphanumeric.

2. Preview both states:

   salt-ssh cms state.apply mysql test=True
   salt-ssh cms state.apply digitalbuying test=True

3. Apply. The app is broken from this point.

   salt-ssh cms state.apply mysql
   salt-ssh cms state.apply digitalbuying

4. Recreate the containers. On the server:

   sudo -u deployer bash -c 'cd /data/deploy/digitalbuying && docker compose up -d'

5. Verify:

   sudo -u deployer bash -c 'cd /data/deploy/digitalbuying && docker compose logs web --tail=50'
   curl -sI https://digitalbuying.open-contracting.org/ | head -1

Cautions

• Step 4 is not optional. salt/docker_apps/init.sls writes .env with no watch_in on the containers, and compose consumes it via env_file:, which is read only at container creation. A highstate alone leaves digitalbuying-web-1 running with the old password. It keeps working until something restarts it, and then fails — with no warning at rotation time.
• The scheduled publish_scheduled cron (salt/digitalbuying/init.sls) uses docker compose run --rm cron, which creates a fresh container and so reads the new .env immediately. Between steps 3 and 4 the two therefore disagree: the cron succeeds on the new password while the long-running web container still holds the old one and fails.

---

Zero-downtime variant

The server is MySQL 8.0.46, so dual passwords are available. Salt has no RETAIN CURRENT PASSWORD support, so the first and last steps are manual.

1. On the server, add the new password while keeping the old one valid:

   ALTER USER '<user>'@'<host>' IDENTIFIED BY '<NEW_PASSWORD>' RETAIN CURRENT PASSWORD;

   Use 'corporate'@'localhost' or 'digitalbuying'@'172.16.0.0/12'.

2. Update the application config — step 4 of runbook A, or steps 1, 3 (state.apply digitalbuying only) and 4 of runbook B. Verify the app works.

3. Update the pillar to the new password, then confirm Salt sees no drift:

   salt-ssh cms state.apply mysql test=True

   Salt compares against authentication_string, which already holds the new hash, so this should report the user as already present with the desired password.

4. Retire the old password:

   ALTER USER '<user>'@'<host>' DISCARD OLD PASSWORD;

---

Rollback

Keep the old password until verification passes. To revert, restore it in the pillar and the application config, then:

salt-ssh cms state.apply mysql

For corporate, also restore the wp-config.php backup taken in step 4. For digitalbuying, also re-run state.apply digitalbuying and recreate the containers.

---

Appendix: known state and gotchas

• Verification only works for native passwords. Salt's user_exists compares authentication_string to the native hash of the pillar password. If an account is migrated to caching_sha2_password, that comparison is skipped and the pillar password is silently no longer enforced — which is why coalition stores a password_hash: instead.
• auth_plugin is create-only. salt/mysql/init.sls passes it through, but mysql.user_chpass derives the plugin from the current database state, so it cannot migrate an existing account. That requires a manual ALTER USER ... IDENTIFIED WITH <plugin>.
• Migrating digitalbuying to caching_sha2_password needs testing first. It is the only one of the three accounts not on the Unix socket — it is granted to 172.16.0.0/12 and connects over TCP from the Docker bridge. The caching_sha2 full-authentication path requires a secure channel, so it depends on mysqlclient negotiating TLS. The server does offer TLS (TLSv1.3, via the auto-generated certificates), and libmysqlclient defaults to ssl-mode=PREFERRED, so it should work — but unlike the socket accounts it is not guaranteed by the transport. Test on a throwaway account before committing.
• Backups are unaffected. salt/mysql/backup.sls runs as root, which uses auth_socket, not any of these passwords.

Appendix: connecting remotely

The app accounts are granted to localhost only, and the server runs with skip-name-resolve, so ssh -L 3307:localhost:3306 fails with ERROR 1130 (HY000): Host '::1' is not allowed. Forward the port to the Unix socket instead, which the server sees as a localhost connection:

ssh -N -L 3307:/var/run/mysqld/mysqld.sock <user>@ocp21.open-contracting.org
mysql --host 127.0.0.1 --port 3307 --protocol TCP --user <user> -p <database>

For corporate, which is still mysql_native_password, this needs a pre-9.0 client, since MySQL 9 dropped that plugin — brew install mysql-client@8.0. For coalition, the stock modern client works.

[jpmckinney]

Done for digitalbuying.

[jpmckinney]

Done for corporate.