| Bug #51763 | Can't delete rows from MEMORY table with HASH key | ||
|---|---|---|---|
| Submitted: | 5 Mar 2010 10:11 | Modified: | 10 Dec 2012 19:07 |
| Reporter: | Valery Mosyagin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Memory storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.31, 5.1.32, 5.1.37-2-log, 5.1.43, 5.1.45-bzr, 5.5.26 | OS: | Linux (Debian, Fedora) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | delete, hash, Memory, timestamp | ||
[5 Mar 2010 11:32]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.43-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP TABLE IF EXISTS `table1`;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> CREATE TABLE `table1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `color` enum('GREEN', 'WHITE') DEFAULT NULL,
-> `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`),
-> KEY `color` (`color`) USING HASH
-> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO table1 VALUES("1","GREEN","2010-03-02 20:53:35");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO table1 VALUES("2","GREEN","2010-03-02 20:55:29");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table1 VALUES("3","GREEN","2010-03-02 20:55:29");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table1 VALUES("4","GREEN","2010-03-02 20:55:31");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table1 VALUES("5","GREEN","2010-03-02 20:55:34");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table1 VALUES("6","GREEN","2010-03-02 20:55:35");
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM table1 WHERE id = 1; # 1,2,3 - error, 4,5,6 - no errr
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO table1 VALUES("7","GREEN","2010-03-02 20:57:31");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from table1
-> WHERE ts <= DATE_SUB('2010-03-02 20:59:29', INTERVAL 121 SECOND) AND colo
r = 'GREEN';
+----+-------+---------------------+
| id | color | ts |
+----+-------+---------------------+
| 6 | GREEN | 2010-03-02 20:55:35 |
| 5 | GREEN | 2010-03-02 20:55:34 |
| 4 | GREEN | 2010-03-02 20:55:31 |
| 3 | GREEN | 2010-03-02 20:55:29 |
| 2 | GREEN | 2010-03-02 20:55:29 |
+----+-------+---------------------+
5 rows in set (0.05 sec)
mysql> DELETE FROM table1
-> WHERE ts <= DATE_SUB('2010-03-02 20:59:29', INTERVAL 121 SECOND) AND colo
r = 'GREEN';
Query OK, 4 rows affected (0.00 sec)
mysql> DELETE FROM table1
-> WHERE ts <= DATE_SUB('2010-03-02 20:59:29', INTERVAL 121 SECOND) AND colo
r = 'GREEN';
Query OK, 1 row affected (0.00 sec)
[5 Mar 2010 13:35]
Valeriy Kravchuk
Ignore my previoius comment. I made copy/paste mistake. Recent 5.1.45 from bzr is also affected.
[22 May 2010 22:14]
Larry Adams
This issue happens for primary keys that are BTREE too. In my example, if the primary key is created using BTREE for a memory table and you goto delete several rows, only the last is delete. OMFG! This is awfull. Going to open a bug report for version: mysql> show variables like '%version%'; +-------------------------+-------------------+ | Variable_name | Value | +-------------------------+-------------------+ | protocol_version | 10 | | version | 5.0.51a-24+lenny2 | | version_comment | (Debian) | | version_compile_machine | powerpc | | version_compile_os | debian-linux-gnu | +-------------------------+-------------------+ 5 rows in set (0.04 sec)
[3 Aug 2010 7:12]
John Nagle
I'm seeing this too. Table definition is CREATE TABLE ratingqueue ( domain VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY, requestor_ip_hash INT, rating_state_int INT UNSIGNED NOT NULL, -- INT, not ENUM (Bug #37439 workaround) server VARCHAR(63) NULL, priority SMALLINT DEFAULT 0, update_timestamp TIMESTAMP NOT NULL, request_timestamp TIMESTAMP NOT NULL, INDEX(requestor_ip_hash), INDEX USING BTREE(rating_state_int, request_timestamp) ) ENGINE=MEMORY; DELETE FROM ratingqueue WHERE ... only deletes one record per request, even when many match the WHERE clause. (We've been running this in production for two years, but our production code doesn't delete more than one entry at a time. Today we tried to manually delete 16,000 entries, because someone had flooded our queue. We can't.) (MySQL 5.0.27-log, Red Hat Linux (Fedora))
[11 Jul 2012 14:43]
Arnaud Adant
This bug is reproduced in 5.5.14, 5.5.25 and 5.5.26.
[10 Dec 2012 19:07]
Paul DuBois
Noted in 5.1.68, 5.5.30, 5.6.9, 5.7.0 changelogs. For MEMORY tables with HASH indexes, DELETE sometimes failed to delete all applicable rows.
Description: After insert some rows to MEMORY table with HASH key some rows can't be deleted in one step. CREATE TABLE `table1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `color` enum('GREEN', 'WHITE') DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `color` (`color`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8; There is no bug using BTREE key for 'color'. How to repeat: Create memory table: DROP TABLE IF EXISTS `table1`; CREATE TABLE `table1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `color` enum('GREEN', 'WHITE') DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `color` (`color`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Insert and delete some values: INSERT INTO table1 VALUES("1","GREEN","2010-03-02 20:53:35"); INSERT INTO table1 VALUES("2","GREEN","2010-03-02 20:55:29"); INSERT INTO table1 VALUES("3","GREEN","2010-03-02 20:55:29"); INSERT INTO table1 VALUES("4","GREEN","2010-03-02 20:55:31"); INSERT INTO table1 VALUES("5","GREEN","2010-03-02 20:55:34"); INSERT INTO table1 VALUES("6","GREEN","2010-03-02 20:55:35"); DELETE FROM table1 WHERE id = 1; # 1,2,3 - error, 4,5,6 - no errr INSERT INTO table1 VALUES("7","GREEN","2010-03-02 20:57:31"); Show rows with our condition: SELECT * from table1 WHERE ts <= DATE_SUB('2010-03-02 20:59:29', INTERVAL 121 SECOND) AND color = 'GREEN'; # Will show 5 rows: +----+-------+---------------------+ | id | color | ts | +----+-------+---------------------+ | 6 | GREEN | 2010-03-02 20:55:35 | | 5 | GREEN | 2010-03-02 20:55:34 | | 4 | GREEN | 2010-03-02 20:55:31 | | 3 | GREEN | 2010-03-02 20:55:29 | | 2 | GREEN | 2010-03-02 20:55:29 | +----+-------+---------------------+ 5 rows in set (0.00 sec) Delete rows using same condition: DELETE FROM table1 WHERE ts <= DATE_SUB('2010-03-02 20:59:29', INTERVAL 121 SECOND) AND color = 'GREEN'; It says "Query OK, 4 rows affected (0.00 sec)" but must be 5! Delete rows using same condition again: DELETE FROM table1 WHERE ts <= DATE_SUB('2010-03-02 20:59:29', INTERVAL 121 SECOND) AND color = 'GREEN'; It says Query OK, 1 row affected (0.00 sec)