| 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: | |
| 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 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
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;