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 10:46]
Lei Wang
[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