Bug #18387 MySQL Crash on long "NOT IN" Expression
Description:
MySQL 5.0.17, 5.0.18 and 5.0.19 crash on long "NOT IN" statements. These long statements mean SQL's greater than 300.000 Bytes. The same SQL but with a "IN" statement will not crash the Server.
The SQL that will crash the Server:
SELECT * FROM FOO WHERE BAR NOT IN('01.24.01.0001', '01.24.01.0002', '01.24.01.0003', ... );
The SQL that will be executed without any errors:
SELECT * FROM FOO WHERE BAR IN('01.24.01.0001', '01.24.01.0002', '01.24.01.0003', ... );
The Field BAR is indexed and the index is used in the statement.
Our Servers have 4GB RAM, 2 - 4 Intel Xeon or Opteron Prozessors and 2GB Swap enabled. During the Statement no Swap memory is used, load is 0.07 0.04 0.00 .
MySQL is configured with 1GB Keybuffer, Max allowed Packet 32M.
How to repeat:
Create a table with > 10.000 entries. Create a SELECT statement as above with a character count > 300.000. All my tests with a "NOT IN" results in a Server Crash.
Suggested fix:
I created the same statement, but used a more complex syntax, that is slower...
SELECT * FROM FOO WHERE BAR NOT LIKE '01.24.01.0001' AND BAR NOT LIKE '01.24.01.0002' AND BAR NOT LIKE '01.24.01.0003' ...
The hole SQL statement is 644.321 Bytes long, but works without and errors...
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL 5.0.17, 5.0.18 and 5.0.19 crash on long "NOT IN" statements. These long statements mean SQL's greater than 300.000 Bytes. The same SQL but with a "IN" statement will not crash the Server. The SQL that will crash the Server: SELECT * FROM FOO WHERE BAR NOT IN('01.24.01.0001', '01.24.01.0002', '01.24.01.0003', ... ); The SQL that will be executed without any errors: SELECT * FROM FOO WHERE BAR IN('01.24.01.0001', '01.24.01.0002', '01.24.01.0003', ... ); The Field BAR is indexed and the index is used in the statement. Our Servers have 4GB RAM, 2 - 4 Intel Xeon or Opteron Prozessors and 2GB Swap enabled. During the Statement no Swap memory is used, load is 0.07 0.04 0.00 . MySQL is configured with 1GB Keybuffer, Max allowed Packet 32M. How to repeat: Create a table with > 10.000 entries. Create a SELECT statement as above with a character count > 300.000. All my tests with a "NOT IN" results in a Server Crash. Suggested fix: I created the same statement, but used a more complex syntax, that is slower... SELECT * FROM FOO WHERE BAR NOT LIKE '01.24.01.0001' AND BAR NOT LIKE '01.24.01.0002' AND BAR NOT LIKE '01.24.01.0003' ... The hole SQL statement is 644.321 Bytes long, but works without and errors...