MySQL8 autotests are failing due to "Temporary failure in name resolution"

[rdimaio]

(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'mysql8' ([Errno -3] Temporary failure in name resolution)")

Example: https://github.com/rucio/rucio/actions/runs/8918099209/job/24492187039

Related issue: docker-library/mysql#1048

[rdimaio]

Deprecation and Removal Notes
Important Change: The deprecated mysql_native_password authentication plugin is now disabled by default. It can be enabled by starting MySQL with the new --mysql-native-password=ON server option, or by adding mysql_native_password=ON to the [mysqld] section of your MySQL configuration file.
For more information, see Native Pluggable Authentication. (Bug #36337893)

• https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html

[rdimaio]

I tried rolling back to MySQL 8.3 in this commit: rdimaio@595afe9

With this change, I get this error (which is likely related to me not having specified the MySQL version properly):

 rucio.common.exception.ConfigNotFound: Configuration not found.
Details: Could not load Rucio configuration file. Rucio looked in the following paths for a configuration file, in order:
	/opt/rucio/etc/rucio.cfg

(see: https://github.com/rdimaio/rucio/actions/runs/8925397636/job/24514035997)

Then I tried this rdimaio@326fb8f, and I get this error:

WARNING:root:Still waiting for database: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'mysql8' ([Errno 111] Connection refused)")
...
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'SOURCES_REPLICA_FK' in the referenced table 'replicas'")
[SQL: ALTER TABLE rucio.sources ADD CONSTRAINT `SOURCES_REPLICA_FK` FOREIGN KEY(scope, name, rse_id) REFERENCES rucio.replicas (scope, name, rse_id)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Failed to run alembic migration!

(see https://github.com/rdimaio/rucio/actions/runs/8925833658/job/24515450220)

It's a bit unclear to me how we're defining the tests, specifically the versions for the DB systems in our tests. I think the changes that I made here rdimaio@326fb8f should be correct, and it might be an issue that the test pulls the cached image?

[rdimaio]

By specifying MySQL 8.3 correctly, this way rdimaio@61d00a3:

• The MySQL autotests pass: https://github.com/rdimaio/rucio/actions/runs/8929411714
• However, the integration tests fail at the conveyor stage: https://github.com/rdimaio/rucio/actions/runs/8929411712/job/24528778021 (this is the second test run, and multiple tests are failing, so I don't think this is due to flaky tests)

I think ideally we should use MySQL 8.4 in tests, and fix the deprecated command mentioned here #6747 (comment) (like I attempted in this commit: rdimaio@326fb8f)

Edit: the conveyor stage tests for MySQL 8.3 are unrelated to this issue. See this issue for more: #6751

So we can fix this by pinning to MySQL8.3.

[rdimaio]

Discussed with @bari12 and we agreed to pin to mysql:8.3 for now, and update in this follow-up issue: #6759.

[bari12]

Backport 32.8.3