| Bug #121044 | mysqld crashes (signal 11) building multi-valued index via ALTER TABLE on JSON column holding both empty and non-empty a | ||
|---|---|---|---|
| Submitted: | 31 Jul 10:50 | Modified: | 31 Jul 10:56 |
| Reporter: | lin vo | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 8.0.44, 8.0.45, 8.4.7, 8.4.8 | OS: | Linux (Official mysql Docker image (Oracle Linux 9); also Azure Database for MySQL Flexible Server) |
| Assigned to: | CPU Architecture: | ARM (aarch64 (local repro); production/test instances affected are x86_64) | |
| Tags: | ALTER TABLE, crash, innodb, json, multi-valued index | ||
[31 Jul 10:56]
lin vo
Correction to the version matrix in my original description, and it resolves the inconsistency I raised there.
My original matrix was produced with the 2-row minimal test case only. Re-testing with realistic data shows 8.0.45 is NOT safe: it still crashes.
Data set used for the real-world test: 260,824 rows, of which 54,025 (about 20%) hold an empty array, the rest hold 1 to 212 elements (string-typed snowflake IDs). Same statement in all cases:
ALTER TABLE c_communication_record
ADD INDEX idx_participant_contacts ((CAST(participant_contact_ids AS CHAR(20) ARRAY))),
ALGORITHM=INPLACE, LOCK=SHARED;
version 2-row minimal case 260,824-row real data
8.0.44 CRASH CRASH
8.0.45 OK, index created CRASH <-- corrected
8.0.46 OK, index created OK, index created
8.4.8 CRASH (not tested)
8.4.9 OK, index created (not tested)
8.4.11 OK, index created OK, index created
All results verified by checking both that the server survived and that the index really exists afterwards (SHOW CREATE TABLE). Disk space was confirmed non-exhausted in every run (19 GB free), and no ENOSPC entries appear in the error logs. Default server configuration, official Docker images.
Consequences:
1. 8.0.45 appears to carry at best a partial fix. The minimal case is fixed there, the real-data case is not.
2. This removes the discrepancy I flagged in the original description. I had noted that 8.0.45 and 8.4.8 shipped on the same date yet behaved differently, and that 8.0.45 carried no related release note. With real data the 8.0 boundary is 8.0.46, which is exactly where the "Fixed an issue relating to multi-value indexes. (Bug #39040128)" note appears. The note position and the observed behaviour are consistent after all.
3. Since MySQL 8.0 has reached end of extended support and 8.0.46 is its final release, 8.0.46 is the only 8.0 version where this is safe.
Crash signature is identical in the 8.0.45 real-data run:
mysqld got signal 11 ;
Query (f567c801fab0): ALTER TABLE c_communication_record ADD INDEX idx_participant_contacts
((CAST(participant_contact_ids AS CHAR(20) ARRAY))), ALGORITHM=INPLACE, LOCK=SHARED
Status: NOT_KILLED
I can supply the generator for the real-data set or the full error logs if useful.
[31 Jul 11:01]
arthas ye
太过顶尖
Description: Creating a multi-valued index with ALTER TABLE ... ADD INDEX ((CAST(col AS CHAR(n) ARRAY))) crashes mysqld with signal 11 (SIGSEGV) when the JSON column already holds a mix of empty arrays ('[]') and non-empty arrays. Two rows are enough to reproduce it deterministically. It is NOT related to table size, array width, disk space, concurrency or metadata locking. Verified negative controls (same server, none crash): - non-empty arrays only - empty arrays only - empty array last in primary key order - 260,000 rows / 520,000 array elements, no empty arrays - 260,000 rows including a 212-element array, no empty arrays Crashes: - (1,'[]'),(2,'["123"]') -- 2 rows - 260,000 rows with 18.5% empty arrays So the trigger is an empty array followed by a non-empty array in primary key order. The crash is specific to the online index BUILD path. Declaring the same index in CREATE TABLE and then inserting identical data does NOT crash. Observed by version (official mysql Docker images; each result verified by checking BOTH that the server stayed alive AND that the index really exists afterwards via SHOW CREATE TABLE): 8.0.44 (2025-10-21) CRASH 8.0.45 (2026-01-20) OK, index created 8.0.46 (2026-04-21) OK, index created 8.4.7 (2025-10-21) CRASH (seen on Azure Database for MySQL Flexible Server) 8.4.8 (2026-01-20) CRASH 8.4.9 (2026-04-21) OK, index created 8.4.11 (2026-07-28) OK, index created The 8.4 boundary matches "InnoDB: Fixed an issue relating to multi-value indexes. (Bug #39040128)", which appears in the 8.4.9 and 8.0.46 release notes. One inconsistency worth clarifying: 8.0.45 and 8.4.8 shipped on the same date (2026-01-20), yet 8.0.45 does NOT crash while 8.4.8 does, and the 8.0.45 release notes contain no multi-valued index entry at all. It is unclear whether 8.0.45 received Bug #39040128 ahead of the 8.4 branch or a separate undocumented change. Real-world impact: this crashed a production Azure MySQL 8.0.44 instance twice (all connections dropped, instance restarted) and separately an Azure MySQL 8.4.7 test instance holding only 7,641 rows. Because it is a crash and not a lock wait, none of the usual online-DDL precautions (off-peak window, stopping concurrent readers, lock_wait_timeout, ALGORITHM/LOCK hints) offer any protection. Note: ALGORITHM=INPLACE, LOCK=NONE is correctly rejected up front with "ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: ADD COLUMN col...VIRTUAL, ADD INDEX(col). Try LOCK=SHARED." The crash happens on the subsequent LOCK=SHARED attempt. Error log excerpt (mysql:8.0.44, default configuration): mysqld got signal 11 ; BuildID[sha1]=25b5b47f3acaaabaaa0241110dff5efaf218c959 stack_bottom = e63f704ee528 thread_stack 0x100000 #0 0xe63f7f3b68f7 <unknown> #1 0xe63f7e880980 <unknown> #2 0x16c99fb <unknown> ... Query (e63ee4d81a80): ALTER TABLE c_communication_record ADD INDEX idx_participant_contacts ((CAST(participant_contact_ids AS CHAR(20) ARRAY))), ALGORITHM=INPLACE, LOCK=SHARED Status: NOT_KILLED Server: version 8.0.44, MySQL Community Server - GPL, version_compile_machine aarch64, version_compile_os Linux, innodb_version 8.0.44. sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Symbols are not resolvable from the release build, so the stack is unresolved. Happy to provide a resolved dump or anything further on request. How to repeat: Server: official Docker image mysql:8.0.44, DEFAULT configuration, no options changed. Reproduced identically on mysql:8.4.8. docker run -d --name repro -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=t mysql:8.0.44 # wait until the server is ready docker exec repro mysql -uroot -proot t -e " CREATE TABLE m (id INT PRIMARY KEY, j JSON NOT NULL); INSERT INTO m VALUES (1,'[]'),(2,'[\"123\"]');" docker exec repro mysql -uroot -proot t -e " ALTER TABLE m ADD INDEX idx_j ((CAST(j AS CHAR(20) ARRAY)));" docker logs repro | grep -A5 "got signal" Equivalent plain SQL (mysql test < file): CREATE DATABASE t; USE t; CREATE TABLE m (id INT PRIMARY KEY, j JSON NOT NULL); INSERT INTO m VALUES (1,'[]'),(2,'["123"]'); ALTER TABLE m ADD INDEX idx_j ((CAST(j AS CHAR(20) ARRAY))); Expected result: index idx_j is created. Actual result: mysqld dies with signal 11; the client reports ERROR 2013 (HY000): Lost connection to MySQL server during query and the error log contains a backtrace naming the ALTER TABLE statement. Negative controls on the same server, none of which crash: INSERT INTO m VALUES (1,'["123"]'); -- non-empty only INSERT INTO m VALUES (1,'[]'); -- empty only INSERT INTO m VALUES (1,'[]'),(2,'[]'); -- all empty INSERT INTO m VALUES (1,'["123"]'),(2,'[]'); -- empty last Also does NOT crash when the index exists before the data is inserted: CREATE TABLE m2 (id INT PRIMARY KEY, j JSON NOT NULL, KEY idx_j ((CAST(j AS CHAR(20) ARRAY)))); INSERT INTO m2 VALUES (1,'[]'),(2,'["123"]'); Suggested fix: Appears already fixed on the 8.4 branch in 8.4.9, most likely by Bug #39040128. Two things worth confirming: 1. Whether 8.0.45 (which does not crash despite carrying no related release note) received the same fix or a different one. MySQL 8.0 has now reached end of extended support, so anyone still on 8.0.44 or earlier has no documented in-branch remedy for a full server crash. 2. Whether the empty-array special-case handling in the multi-valued index build path shares an origin with Bug #118299 (composite multi-valued index silently dropping rows whose multi-valued key part is an empty array; Verified, S2, no fix version). That report is still open and appears to touch the same code area. Per WL#8955 an empty array intentionally contributes no index entry, which is reasonable on its own, but the interaction looks like the common factor. The deterministic 2-row reproducer above may also be useful for Bug #109542, where the earlier crash reports in this area were closed as "Can't repeat" for lack of a test case.