Add MySQL 8.0 support

[jerico]

This PR adds MySQL 8.0 support. It also makes 8.0 the default version because 5.7 has reached end-of-life.

https://github.com/humanmade/altis-local-server/issues/671

[mikelittle]

@jerico I cannot get this to run locally. I get the following error:

service "db" has neither an image nor a build context specified: invalid compose project

I tried adding the config in composer.json, but that didn't change anything. I see the Travis build is failing for roughly the same reason. Though with a slightly different message.

[6260] Failed to execute script docker-compose
Traceback (most recent call last):
  File "docker-compose", line 3, in <module>
  File "compose/cli/main.py", line 81, in main
  File "compose/cli/main.py", line 203, in perform_command
  File "compose/metrics/decorator.py", line 18, in wrapper
  File "compose/cli/main.py", line 1186, in up
  File "compose/cli/main.py", line 1182, in up
  File "compose/project.py", line 664, in up
  File "compose/service.py", line 352, in ensure_image_exists
  File "compose/service.py", line 378, in image
  File "docker/utils/decorators.py", line 17, in wrapped
docker.errors.NullResource: Resource ID was not provided
Services failed to start successfully.

[mikelittle]

I got it working with my suggested change. The upgrade process from 5.7 to 8.0 worked ok and transparently

product-dev-db  | 2024-04-04 16:35:02+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.36-1.el8 started.
product-dev-db  | 2024-04-04 16:35:03+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
product-dev-db  | 2024-04-04 16:35:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.36-1.el8 started.
product-dev-db  | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
product-dev-db  | 2024-04-04T16:35:04.111371Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
product-dev-db  | 2024-04-04T16:35:04.122336Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36) starting as process 1
product-dev-db  | 2024-04-04T16:35:04.143010Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
product-dev-db  | 2024-04-04T16:35:04.143202Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
product-dev-db  | 2024-04-04T16:35:04.947386Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
product-dev-db  | 2024-04-04T16:35:08.704545Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
product-dev-db  | 2024-04-04T16:35:11.974230Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' started.
product-dev-db  | 2024-04-04T16:35:21.731513Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' completed.
product-dev-db  | 2024-04-04T16:35:21.930494Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
p

We may still need to make some changes to the configuration as there are some warnings in the log file.

product-dev-db  | 2024-04-04T16:35:04.111371Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
...
product-dev-db  | 2024-04-04T16:35:22.940954Z 11 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
product-dev-db  | 2024-04-04T16:35:24.622775Z 12 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
product-dev-db  | 2024-04-04T16:35:26.508446Z 13 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

This last one is repeated every 5 seconds. That's gonna fill up the logs and make it hard to spot real issues.

[jerico]

@mikelittle

product-dev-db | 2024-04-04T16:35:26.508446Z 13 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

My understanding is this is a deprecation of mysql_native_password in MySQL 8.0.35. This should not happen on a fresh Altis install.

The fix looks to be executing ALTER USER 'user'@'%' IDENTIFIED WITH caching_sha2_password BY 'password'; https://repost.aws/questions/QUNe4_oNNuQQuB2pyBo8XGsw/getting-rid-of-warning-plugin-mysql-native-password-reported-mysql-native-password-is-deprecated

Do you have a suggestion where to put this fix?

[mikelittle]

We do have the altis migrate wp cli command. It triggers an action altis.migrate. https://github.com/humanmade/altis-core/blob/6e6dc30b4419d5ede173678c6d228f0e51042fb0/inc/class-command.php#L40 So you could hook onto that and trigger an SQL call.

OR there is a WP CLI hook you can use to trigger another wp cli command to do it see https://github.com/humanmade/altis-cloud/blob/12f0c70673e6b0b82cffb378f4cd8a244e6116ff/inc/namespace.php#L574

[rmccue]

Probably need to make sure that only runs on Local Server too, not in the cloud envs.

[jerico]

We do have the altis migrate wp cli command. It triggers an action altis.migrate. https://github.com/humanmade/altis-core/blob/6e6dc30b4419d5ede173678c6d228f0e51042fb0/inc/class-command.php#L40 So you could hook onto that and trigger an SQL call.

OR there is a WP CLI hook you can use to trigger another wp cli command to do it see https://github.com/humanmade/altis-cloud/blob/12f0c70673e6b0b82cffb378f4cd8a244e6116ff/inc/namespace.php#L574

I was looking for a way to keep the changes within local-server and I was not able to work out how to do it with migrate scripts that will only run once.

There were a couple of users that needs to be altered too:

mysql> select Host,User,plugin FROM  mysql.user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| %         | wordpress        | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)

The safest way I found was suppressing this specific warning using log_error_suppression_list. mysql_native_password is deprecated but there's no fixed date or release when it will be removed.

A fresh install using MySQL 8.0 should not have this warning. Only affected users are those coming from 5.7 upgraded to 8.0.

[mikelittle]

We do have the altis migrate wp cli command. It triggers an action altis.migrate. https://github.com/humanmade/altis-core/blob/6e6dc30b4419d5ede173678c6d228f0e51042fb0/inc/class-command.php#L40 So you could hook onto that and trigger an SQL call. OR there is a WP CLI hook you can use to trigger another wp cli command to do it see https://github.com/humanmade/altis-cloud/blob/12f0c70673e6b0b82cffb378f4cd8a244e6116ff/inc/namespace.php#L574

I was looking for a way to keep the changes within local-server and I was not able to work out how to do it with migrate scripts that will only run once.

There were a couple of users that needs to be altered too:

mysql> select Host,User,plugin FROM  mysql.user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| %         | wordpress        | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)

The safest way I found was suppressing this specific warning using log_error_suppression_list. mysql_native_password is deprecated but there's no fixed date or release when it will be removed.

A fresh install using MySQL 8.0 should not have this warning. Only affected users are those coming from 5.7 upgraded to 8.0.

Hmmm.. when you are running under local server Altis sets the constant HM_ENV_ARCHITECTURE to local-server So I think you should be able to tell in the altis-migrate hook that you are running locally and only run the sql command then.

By the way, I ran composer server cli config list to find that constant.

[jerico]

@mikelittle Added additional comments to explain why we are suppressing warning. It only affects local-server and those who do in-place upgrade from 5.7 to 8.0.