MySQL SELECT Query Crash Analysis and Resolution

When a SELECT statement caused MySQL to crash, the error log showed signal 11 and a stack trace pointing to actual_key_parts in sql_class.h:1487 .

The offending query was:

SELECT DISTINCT T.CUST_NO FROM testDB.TABLE_TRANSACTION T WHERE EXISTS (SELECT 1 FROM testDB.Table1 T1 WHERE T.CUST_NO = T1.CUST_NO) AND T.AGENT_CERT_NO IS NOT NULL

Analysis revealed that the optimizer transformed the EXISTS sub‑query into a semi‑join with the DuplicateWeedout execution strategy, which creates a temporary table and accessed an invalid memory address, leading to the crash.

Further investigation using gdb confirmed that the function in_use returned a null pointer, causing an illegal address dereference.

Three practical solutions were proposed:

Set the optimizer switch to disable DuplicateWeedout: SET [GLOBAL|SESSION] optimizer_switch='duplicateweedout=off';

Upgrade MySQL to version 8.0.24 or later where the bug is fixed.

Ensure tables use a uniform charset (utf8mb4) and collation to avoid the semi‑join issue.

After applying one of these fixes, the SELECT query executes without crashing.