Bug #45741 Memory of server increases abnormally for sql with mass <> condition
Submitted: 25 Jun 2009 10:46 Modified: 29 Jun 2009 9:47
Reporter: Lei Wang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.83, 5.0, 5.1, 5.5.8 OS:Linux
Assigned to: CPU Architecture:Any
Tags: Memory

[25 Jun 2009 10:46] Lei Wang
Description:
When I execute the following SQL by JDBC, the memory of mysql server increases abnormally.

  select * from table where col1<> value1 and col1<> value2 and ... and col1 <> valueN

For N=10000,the server's memory usage can increase 2~5 GB in a minute.

How to repeat:
Create a table :

 create table test (id int primary key)engine = innodb;

Insert data:

  for (i=0;i<10000;i++)
    insert into test values(1),(2),...,(10000);

Execute sql with JDBC:
  select * from test where id<>1 and id<>2 and ... and id<>10000;
[25 Jun 2009 12:56] MySQL Verification Team
Thank you for the bug report. Could you please provide the JDBC test case and its version. Thanks in advance,
[25 Jun 2009 13:10] Lei Wang
JDBC test case

Attachment: MyTest.java (text/x-java), 1.63 KiB.

[25 Jun 2009 21:41] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior. Please provide output of SHOW CREATE TABLE test1;
[26 Jun 2009 2:22] Lei Wang
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.83 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table test.test1;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Pay attention to the table name. The table name in jdbc program is test1, not test.
[29 Jun 2009 9:47] Sveta Smirnova
Thank you for the feedback.

Verified as described:

mysql> show processlist;
+------+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host            | db   | Command | Time | State      | Info                                                                                                 |
+------+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| 2907 | root | localhost:58891 | test | Query   | 1080 | statistics | select * from test1 where id<>0 and id<>1 and id<>2 and id<>3 and id<>4 and id<>5 and id<>6 and id<> | 
| 2908 | root | localhost       | NULL | Query   |    0 | NULL       | show processlist                                                                                     | 
+------+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

while memory usage grows.

Workaround: drop primary key.
[30 Jun 2009 7:19] MySQL Verification Team
testcase using a stored procedure instead of java

Attachment: bug45741_stored_procedure_testcase.sql (application/unknown, text), 1015 bytes.

[4 Mar 2010 9:16] Manyi Lu
Workaround: Use "field not in (...)" instead of "col1<> value1" to get rid of the memory consumption problem.
[10 Nov 2010 2:36] MySQL Verification Team
5.5.8 allocated >4G of ram with my SP