Atomic cut-over always fails on MySQL 9.1+: information_schema.processlist errors out (3854) while another session runs a non-BMP utf8mb4 statement #1780

Summary

On MySQL 9.1 and later (Oracle MySQL and Percona Server alike), the atomic cut-over never sees its own RENAME
and every attempt fails with Error 1205 (HY000): Lock wait timeout exceeded, as soon as any other session runs a
statement whose text is not representable in utf8mb3 — an emoji in a string literal is enough, and so is a binary
parameter that is not valid UTF-8 (_binary'...' as sent by go-sql-driver with interpolateParams=true, or a
[]byte over the binary protocol). Under write load the table lock of the cut-over queues exactly such statements,
so the check fails on every attempt and the migration aborts after --default-retries.

ExpectProcess reads information_schema.processlist. Since 9.1.0 that query fails as a whole with

Error 3854 (HY000): Cannot convert string '\xF0\x9F\x98\x80' ...' from utf8mb4 to utf8mb3

while such a statement is in flight in another session. The error is swallowed by retryOperation, so the gh-ost
log shows only the 1205 of the RENAME and cannot find process. Hints: metadata lock, rename — nothing points at
the real cause.

Reproduction

Server only (no gh-ost involved), MySQL 9.7.2 official image:

docker run -d --name m97 -e MYSQL_ROOT_PASSWORD=x mysql:9.7

# session 1: a statement with an emoji, kept running
mysql -uroot -px --default-character-set=utf8mb4 -e "SELECT SLEEP(10), '😀'" &

# session 2, within the 10 s — the shape of ExpectProcess
mysql -uroot -px --default-character-set=utf8mb4 -e \
  "select id from information_schema.processlist where id != connection_id() and 0 in (0, id)
   and state like '%metadata lock%' and info like '%rename%'"
-> ERROR 3854 (HY000): Cannot convert string '\xF0\x9F\x98\x80' ...' from utf8mb4 to utf8mb3

The same on 8.0.44 succeeds with warning 1366 only. performance_schema.processlist, performance_schema.threads
and SHOW FULL PROCESSLIST are not affected on 9.7.

(A client with character_set_client=latin1 does not reproduce it — latin1 always converts to utf8mb3 — which
is why a quick check from a non-interactive mysql client may look fine.)

End to end with gh-ost v1.1.11 (--allow-on-master --cut-over=atomic, a table of 60k–10M rows, ~200 inserts/s
carrying either a _binary non-UTF-8 parameter or only an emoji in a text column):

The query alone, per server version (non-UTF-8 parameter, emoji, ASCII control):

Root cause

MySQL 9.1.0 made character set conversions into temporary tables strict (sql/sql_tmp_table.cc: "All character
set conversions into temporary tables are strict", table->m_charset_conversion_is_strict = true). Fill_process_list
stores every session's statement text into the INFO column of the temporary information_schema table, whose
character set is utf8mb3; a 4-byte character or invalid bytes now raise ER_CANNOT_CONVERT_STRING instead of the
old warning — the long-standing MySQL Bug #87579 (information_schema.processlist should handle utf8mb4 characters,
Verified since 2017) turned from a warning into an error. information_schema.INNODB_TRX fails the same way.
INFORMATION_SCHEMA.PROCESSLIST is deprecated in favour of performance_schema.processlist since 8.0.22.

Workaround

Run gh-ost under an account without the PROCESS privilege. Such an account sees only its own sessions in
information_schema.processlist, so foreign statement texts are never converted; at the moment of ExpectProcess
gh-ost's own sessions carry ASCII only. In our tests, stock v1.1.11 under such an account completed the atomic
cut-over on Percona Server 9.7.1 under the loads above, 3 out of 3 on the first attempt. This relies on server
behaviour that is not documented, though, so a fix in gh-ost seems worth having.

Proposed fix

In ExpectProcess, read performance_schema.processlist with the same three conditions (the INFO condition still
works with performance_schema_max_sql_text_length=0), and fall back to information_schema.processlist when the
former is not usable — performance_schema off, the table absent (5.7, 8.0 before 8.0.22), or no SELECT on it —
decided once when the applier initialises, the way IsOpenMetadataLockInstruments is. And log the error of the check
instead of swallowing it: that silence is what hid the cause here.

MySQL 9.x is not in the CI matrix today (replica-tests.yml runs 5.7, 8.0, 8.4, Percona 8.0 and MariaDB), so this
would go unnoticed by CI; adding a 9.x image may be worth considering.

I'd be happy to open a PR with the fix and a test if this approach looks acceptable.