Fix atomic cut-over on MySQL 9.1+ under utf8mb4 load - #1782

A Pull Request should be associated with an Issue.

Related issue: #1780

Description

This PR fixes the atomic cut-over on MySQL 9.1 and later (Oracle MySQL and Percona Server). Today it fails on every attempt with Error 1205 while any other session runs a statement that cannot be represented in utf8mb3, for example an emoji in a string literal or a binary parameter that is not valid UTF-8. Under write load, the cut-over's table lock queues exactly such statements.

Cause. ExpectProcess reads information_schema.processlist. To fill that table, the server converts every session's statement text into a utf8mb3 INFO column. Since 9.1, character set conversions into temporary tables are strict (sql_tmp_table.cc), so the long-standing MySQL Bug #87579 now raises Error 3854 for the whole read instead of a warning. The error arrives after the result set has started, and sqlutils.QueryRowsMap does not check rows.Err(). As a result, the failed read looked like an empty result, and the log said only cannot find process. Hints: metadata lock, rename. (In #1780 I wrote that retryOperation swallows the error. It is actually dropped earlier, in QueryRowsMap.)

Change (go/logic/applier.go):

• ExpectProcess reads performance_schema.processlist (MySQL 8.0.22+) with the same three conditions. That table keeps the statement text in the session's own character set, so it is not affected.

• Whether that table is usable is decided once, in Applier.InitDBConnections(): the applier must find its own session in it. Otherwise information_schema.processlist stays the fallback. This happens when the table is absent (MySQL 5.7, 8.0 before 8.0.22, MariaDB), when it is empty because performance_schema is off (which, since Before the successful renaming, a session accessed the ghost table, w… #1536, requires --skip-metadata-lock-check), or when it is not readable. The log says which table is used. On MySQL 9.1+ the fallback logs a warning that names the risk and the remedy.

• ExpectProcess reads through QueryRow().Scan(), which returns the error of a failed read. The error is logged at ERROR level, as the other retried cut-over checks are (ExpectMetadataLock, the RENAME), instead of being reported as a missing process.

• contributed code is using same conventions as original code

• script/cibuild returns with no formatting errors, build errors or unit test errors.

How it was tested

• New TestApplierMySQL9 suite (testcontainers, mysql:9.7.2). An INSERT carrying an emoji and then a RENAME wait behind a table lock. As a control, the old information_schema.processlist query fails with 3854. ExpectProcess finds the waiting RENAME. Forced to the fallback table, ExpectProcess returns the 3854 error instead of cannot find process.

  • With the upstream ExpectProcess body put back, the test fails with cannot find process. Hints: metadata lock, rename, which is the production symptom.
  • With only the table switch disabled, it fails with failed reading information_schema.processlist: Error 3854 (HY000): Cannot convert string '\xF0\x9F\x98\x80')' from utf8mb4 to utf8mb3.
  • With only the read put back on sqlutils.QueryRowsMap, the fallback assertion fails because the error is again reported as cannot find process.

• ApplierTestSuite.TestExpectProcess (mysql:8.0.42): both tables find a RENAME waiting on a metadata lock, and a non-matching hint gives cannot find process. TestInitDBConnections now also asserts that performance_schema.processlist is chosen.

• TestHasStrictTmpTableCharsetConversion: the version gate for the warning, covering Oracle, Percona, -commercial, MariaDB and unparsable versions.

• script/cibuild with Go 1.25.12 (macOS arm64, Docker): exit 0. All packages ok, 0 failures. The new 9.7.2 suite adds about 20 s to go/logic.

• golangci-lint v2.11.4 with .golangci.yml: 0 issues.

• script/docker-gh-ost-replica-tests locally, with host ports remapped:

  • mysql:8.4.3 takes the performance_schema.processlist path: 90 passed, 0 failed.
  • mariadb:11.4.12 takes the fallback: 90 passed, 0 failed.
  • In both runs, sysbench was skipped because it is not installed locally.

• End to end with the built binary on mysql:9.7.2. Setup: a 60k-row table, 4 connections inserting continuously, --allow-on-master --cut-over=atomic --cut-over-lock-timeout-seconds=2 --default-retries=3.

  Binary	Concurrent inserts carry	Result
  master	an emoji in a text column	aborted: every attempt 1205, cannot find process, no 3854 in the log
  master	a []byte that is not valid UTF-8	aborted, same
  this PR	an emoji in a text column	done, the RENAME found on the first attempt
  this PR	a []byte that is not valid UTF-8	done, the RENAME found on the first attempt
  this PR, performance_schema=OFF (--skip-metadata-lock-check)	ASCII only	done, via information_schema.processlist
  this PR, performance_schema=OFF (--skip-metadata-lock-check)	an emoji	aborted, as expected for the fallback. The log now shows the start-up warning and ERROR failed reading information_schema.processlist: Error 3854 ... on every attempt

  On MariaDB 11.4.12 the applier logs will use information_schema.processlist on applier: performance_schema.processlist is not usable (Error 1146 ...) and proceeds as before.

Notes

• MySQL 9.x is not in the replica-tests.yml matrix. I'd be happy to add a 9.x image in a separate PR. Two caveats:
  • The harness needs a script/docker/mysql-9.x config, and its replica terminology check matches only 8.4.
  • The localtests cut over with --test-on-replica after stopping replication. That means no foreign statements are in flight during the cut-over, so they would not catch this bug without a dedicated load on the replica.
• The new unit suite starts a mysql:9.7.2 container within script/cibuild. If you'd rather keep the unit run to one image, I can gate that suite differently.
• my.cnf.test is not used for the 9.x container, because MySQL 9.7 rejects innodb_log_file_size.