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...
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...