Description:
A functional index randomly becomes invisible to the optimizer under concurrent load. For a query whose WHERE expression is byte-identical to the index expression, EXPLAIN sometimes returns type=ALL with possible_keys=NULL — the index is not even a candidate. FORCE INDEX is silently ignored in that state (no error 1176). OPTIMIZER_TRACE shows the substitute_generated_columns step NOT rewriting the condition, ref_optimizer_key_uses empty, and rows_estimation containing only table_scan.
Characteristics:
(1) The failure is decided per cached-table-object creation — a race when TABLE instances are instantiated while other sessions concurrently open/use/flush the table. A "bad" instance stays bad until evicted, so behavior is consistent within a connection but random across connections. On a busy production table (~550k rows) 0/20 EXPLAINs used the index; on a quiet table in the same server, 16/20, oscillating as instances get recreated.
(2) Independent of session state: reproduces under latin1 and utf8mb4 clients with any collation, any default_collation_for_utf8mb4, any sql_mode, text and binary protocol.
(3) Single-threaded use never triggers it; only a concurrent mix (parallel EXPLAIN/SELECT + FLUSH TABLES churn + INSERT churn) does — typically 30–60 occurrences per ~17,000 EXPLAINs per 60s on an 8-core host.
(4) Version matrix (same harness, 2×60s runs each): 8.0.46 — 46 and 60 failures/run; 8.4.10 — 33 and 45; 8.4.5 and Percona 8.4.10-10 — affected; 9.7.1 — 0 failures in ~35,000 probes, so a 9.x change likely fixed it and may be backportable.
Production impact: JSON-array functional-index lookups randomly degrade from ~ms to multi-second full table scans with no way to force the index.
How to repeat:
Start a server (Docker shown; any Linux 8.0/8.4 server reproduces; 8+ cores recommended):
```bash
docker run -d --name bugrepro -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3310:3306 \
mysql:8.4 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
```
Create schema and data:
```sql
CREATE DATABASE bugtest DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bugtest;
CREATE TABLE t (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`from` VARCHAR(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`to` JSON DEFAULT NULL,
ccEmails JSON DEFAULT NULL,
subject TEXT COLLATE utf8mb4_unicode_ci,
createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_from (`from`),
KEY idx_to_first ( ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`to`, _utf8mb4'$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)) ),
KEY idx_cc_first ( ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`ccEmails`, _utf8mb4'$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)) ),
FULLTEXT KEY ft_subject (subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO t (`from`, `to`, ccEmails, subject)
SELECT CONCAT('sender', MOD(n,500), '@example.com'),
JSON_ARRAY(CONCAT('user', MOD(n,500), '@example.com')),
JSON_ARRAY(CONCAT('cc', MOD(n,500), '@example.com')),
CONCAT('subject ', n)
FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 5000) SELECT n FROM s) x;
ANALYZE TABLE t;
```
Sanity check, single-threaded (always uses the index when run alone):
```sql
EXPLAIN SELECT * FROM t WHERE (CAST(JSON_UNQUOTE(JSON_EXTRACT(`to`, _utf8mb4'$[0]'))
AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) = 'user42@example.com';
-- type=ref, key=idx_to_first
```
Save as repro.sh and run (bash, needs the mysql CLI client; runs 60 seconds):
```bash
#!/bin/bash
P=3310
M="mysql -h127.0.0.1 -P$P -uroot bugtest"
EXPR_TO="(CAST(JSON_UNQUOTE(JSON_EXTRACT(\`to\`, _utf8mb4'\$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)"
EXPR_CC="(CAST(JSON_UNQUOTE(JSON_EXTRACT(\`ccEmails\`, _utf8mb4'\$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)"
END=$((SECONDS+60))
# 6 concurrent EXPLAIN probes on the `to` index
for w in 1 2 3 4 5 6; do
( while [ $SECONDS -lt $END ]; do
$M -N -e "SET SESSION explain_format=TRADITIONAL; EXPLAIN SELECT * FROM t WHERE $EXPR_TO = 'user42@example.com';" 2>/dev/null | awk -F'\t' '{print $5"/"$6}'
done > probe_$w.log ) &
done
# 2 concurrent EXPLAIN probes on the ccEmails index
for w in 7 8; do
( while [ $SECONDS -lt $END ]; do
$M -N -e "SET SESSION explain_format=TRADITIONAL; EXPLAIN SELECT * FROM t WHERE $EXPR_CC = 'cc42@example.com';" 2>/dev/null | awk -F'\t' '{print $5"/"$6}'
done > probe_$w.log ) &
done
# 2 workers churning FLUSH TABLES
for f in 1 2; do
( while [ $SECONDS -lt $END ]; do $M -e "FLUSH TABLES;" 2>/dev/null; done ) &
done
# 2 workers churning INSERTs
for i in 1 2; do
( while [ $SECONDS -lt $END ]; do $M -e "INSERT INTO t (\`from\`, \`to\`, ccEmails, subject) VALUES ('x@example.com', JSON_ARRAY('y@example.com'), JSON_ARRAY('z@example.com'), 'chaos');" 2>/dev/null; done ) &
done
# 1 latin1-charset session churning EXPLAINs, 1 session churning SELECTs
( while [ $SECONDS -lt $END ]; do mysql -h127.0.0.1 -P$P -uroot --default-character-set=latin1 bugtest -N -e "SET SESSION explain_format=TRADITIONAL; EXPLAIN SELECT * FROM t WHERE $EXPR_TO = 'user43@example.com';" >/dev/null 2>&1; done ) &
( while [ $SECONDS -lt $END ]; do mysql -h127.0.0.1 -P$P -uroot bugtest -N -e "SELECT id FROM t WHERE $EXPR_TO = 'user44@example.com' LIMIT 1;" >/dev/null 2>&1; done ) &
wait
cat probe_*.log | sort | uniq -c
rm -f probe_*.log
```
Expected result: every probe line is ref/idx_to_first or ref/idx_cc_first.
Actual result on 8.0.46 and 8.4.10 (one 60 s run, 8-core host):
```
33 ALL/NULL <-- BUG: full scan, index not even a candidate
4175 ref/idx_cc_first
12512 ref/idx_to_first
```
A failing EXPLAIN row looks like:
```
1 SIMPLE t NULL ALL NULL NULL NULL NULL 5000 100.00 Using where
```
Notes:
- Incidence varies run to run (30–60 hits/min on a busy 8-core host; an idle box can produce a zero run — repeat 2–3 times).
- Adding FORCE INDEX (idx_to_first) to a failing statement does not restore the index and raises no error.
- The same harness on 9.7.1 produces zero failures across ~35,000 probes.
- Removing any ingredient class (the FLUSH churn, the INSERT churn, or the probe concurrency) makes failures disappear; single-threaded execution never fails.
Suggested fix:
Use the index instead of ignoring it
Description: A functional index randomly becomes invisible to the optimizer under concurrent load. For a query whose WHERE expression is byte-identical to the index expression, EXPLAIN sometimes returns type=ALL with possible_keys=NULL — the index is not even a candidate. FORCE INDEX is silently ignored in that state (no error 1176). OPTIMIZER_TRACE shows the substitute_generated_columns step NOT rewriting the condition, ref_optimizer_key_uses empty, and rows_estimation containing only table_scan. Characteristics: (1) The failure is decided per cached-table-object creation — a race when TABLE instances are instantiated while other sessions concurrently open/use/flush the table. A "bad" instance stays bad until evicted, so behavior is consistent within a connection but random across connections. On a busy production table (~550k rows) 0/20 EXPLAINs used the index; on a quiet table in the same server, 16/20, oscillating as instances get recreated. (2) Independent of session state: reproduces under latin1 and utf8mb4 clients with any collation, any default_collation_for_utf8mb4, any sql_mode, text and binary protocol. (3) Single-threaded use never triggers it; only a concurrent mix (parallel EXPLAIN/SELECT + FLUSH TABLES churn + INSERT churn) does — typically 30–60 occurrences per ~17,000 EXPLAINs per 60s on an 8-core host. (4) Version matrix (same harness, 2×60s runs each): 8.0.46 — 46 and 60 failures/run; 8.4.10 — 33 and 45; 8.4.5 and Percona 8.4.10-10 — affected; 9.7.1 — 0 failures in ~35,000 probes, so a 9.x change likely fixed it and may be backportable. Production impact: JSON-array functional-index lookups randomly degrade from ~ms to multi-second full table scans with no way to force the index. How to repeat: Start a server (Docker shown; any Linux 8.0/8.4 server reproduces; 8+ cores recommended): ```bash docker run -d --name bugrepro -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3310:3306 \ mysql:8.4 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci ``` Create schema and data: ```sql CREATE DATABASE bugtest DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; USE bugtest; CREATE TABLE t ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `from` VARCHAR(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `to` JSON DEFAULT NULL, ccEmails JSON DEFAULT NULL, subject TEXT COLLATE utf8mb4_unicode_ci, createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_from (`from`), KEY idx_to_first ( ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`to`, _utf8mb4'$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)) ), KEY idx_cc_first ( ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`ccEmails`, _utf8mb4'$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)) ), FULLTEXT KEY ft_subject (subject) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET SESSION cte_max_recursion_depth = 10000; INSERT INTO t (`from`, `to`, ccEmails, subject) SELECT CONCAT('sender', MOD(n,500), '@example.com'), JSON_ARRAY(CONCAT('user', MOD(n,500), '@example.com')), JSON_ARRAY(CONCAT('cc', MOD(n,500), '@example.com')), CONCAT('subject ', n) FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 5000) SELECT n FROM s) x; ANALYZE TABLE t; ``` Sanity check, single-threaded (always uses the index when run alone): ```sql EXPLAIN SELECT * FROM t WHERE (CAST(JSON_UNQUOTE(JSON_EXTRACT(`to`, _utf8mb4'$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) = 'user42@example.com'; -- type=ref, key=idx_to_first ``` Save as repro.sh and run (bash, needs the mysql CLI client; runs 60 seconds): ```bash #!/bin/bash P=3310 M="mysql -h127.0.0.1 -P$P -uroot bugtest" EXPR_TO="(CAST(JSON_UNQUOTE(JSON_EXTRACT(\`to\`, _utf8mb4'\$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)" EXPR_CC="(CAST(JSON_UNQUOTE(JSON_EXTRACT(\`ccEmails\`, _utf8mb4'\$[0]')) AS CHAR(512) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)" END=$((SECONDS+60)) # 6 concurrent EXPLAIN probes on the `to` index for w in 1 2 3 4 5 6; do ( while [ $SECONDS -lt $END ]; do $M -N -e "SET SESSION explain_format=TRADITIONAL; EXPLAIN SELECT * FROM t WHERE $EXPR_TO = 'user42@example.com';" 2>/dev/null | awk -F'\t' '{print $5"/"$6}' done > probe_$w.log ) & done # 2 concurrent EXPLAIN probes on the ccEmails index for w in 7 8; do ( while [ $SECONDS -lt $END ]; do $M -N -e "SET SESSION explain_format=TRADITIONAL; EXPLAIN SELECT * FROM t WHERE $EXPR_CC = 'cc42@example.com';" 2>/dev/null | awk -F'\t' '{print $5"/"$6}' done > probe_$w.log ) & done # 2 workers churning FLUSH TABLES for f in 1 2; do ( while [ $SECONDS -lt $END ]; do $M -e "FLUSH TABLES;" 2>/dev/null; done ) & done # 2 workers churning INSERTs for i in 1 2; do ( while [ $SECONDS -lt $END ]; do $M -e "INSERT INTO t (\`from\`, \`to\`, ccEmails, subject) VALUES ('x@example.com', JSON_ARRAY('y@example.com'), JSON_ARRAY('z@example.com'), 'chaos');" 2>/dev/null; done ) & done # 1 latin1-charset session churning EXPLAINs, 1 session churning SELECTs ( while [ $SECONDS -lt $END ]; do mysql -h127.0.0.1 -P$P -uroot --default-character-set=latin1 bugtest -N -e "SET SESSION explain_format=TRADITIONAL; EXPLAIN SELECT * FROM t WHERE $EXPR_TO = 'user43@example.com';" >/dev/null 2>&1; done ) & ( while [ $SECONDS -lt $END ]; do mysql -h127.0.0.1 -P$P -uroot bugtest -N -e "SELECT id FROM t WHERE $EXPR_TO = 'user44@example.com' LIMIT 1;" >/dev/null 2>&1; done ) & wait cat probe_*.log | sort | uniq -c rm -f probe_*.log ``` Expected result: every probe line is ref/idx_to_first or ref/idx_cc_first. Actual result on 8.0.46 and 8.4.10 (one 60 s run, 8-core host): ``` 33 ALL/NULL <-- BUG: full scan, index not even a candidate 4175 ref/idx_cc_first 12512 ref/idx_to_first ``` A failing EXPLAIN row looks like: ``` 1 SIMPLE t NULL ALL NULL NULL NULL NULL 5000 100.00 Using where ``` Notes: - Incidence varies run to run (30–60 hits/min on a busy 8-core host; an idle box can produce a zero run — repeat 2–3 times). - Adding FORCE INDEX (idx_to_first) to a failing statement does not restore the index and raises no error. - The same harness on 9.7.1 produces zero failures across ~35,000 probes. - Removing any ingredient class (the FLUSH churn, the INSERT churn, or the probe concurrency) makes failures disappear; single-threaded execution never fails. Suggested fix: Use the index instead of ignoring it