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

Đang mở
  • Có gì không ổn?
#1,780 1 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 10 ngày

@kotigor đang làm issue này rồi.

Từ ngày 5/10/2026.

  • #1782 của @kotigor — đang mở

Đánh giá

Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức phù hợp với người mới
52/100
Loại issue
Lỗi
Độ rõ ràng
Đặc tả rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
go, mysql, postgresql
Lĩnh vực
databases

Hướng nghiên cứu

Start with ExpectProcess and IsOpenMetadataLockInstruments to understand how the applier detects available process-list sources. Check the existing tests and replica-tests.yml for database-version coverage and compatibility patterns. Done means the process check can use performance_schema.processlist with a fallback, reports check errors, and is tested for supported server variants.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

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):

Server Concurrent load Atomic cut-over
Percona Server 9.7.1 _binary non-UTF-8 fails: every attempt 1205, migration aborted
Percona Server 9.7.1 emoji in a text column only fails: every attempt 1205
Oracle MySQL 9.7.2 _binary non-UTF-8 fails: every attempt 1205
Oracle MySQL 8.4 _binary non-UTF-8 succeeds on the first attempt
Percona Server 9.7.1 ASCII only succeeds on the first attempt

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

Server information_schema.processlist with a non-BMP / non-UTF-8 statement in flight
Oracle MySQL 9.7.2, 9.1.0; Percona Server 9.7.1 ERROR 3854
Oracle MySQL 9.0.1, 8.4.11, 8.0.44; Percona Server 8.4.11 OK (warning 1366 at most)
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.

Ngôn ngữ chính
Go
Star
13.6k
Fork
1.4k
Merge trung bình
9 ngày 19 giờ
Pull request đã merge (30 ngày)
1

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của github/gh-ost

Tất cả issue của github/gh-ost

Issue tương tự

Thêm issue về Go

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.