Bug #53917 Deleting Rows on MEMORY table with a BTREE PRIMARY KEY Breaks
Submitted: 22 May 2010 22:34 Modified: 31 May 2010 22:07
Reporter: Larry Adams Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.51a-24 OS:Linux
Assigned to: CPU Architecture:Any
Tags: MEMORY BREEE PRIMARY KEY DELETE

[22 May 2010 22:34] Larry Adams 
Description:
When a MEMORY table is created using a BTREE PRIMARY KEY, a DELETE from this table for a range of values results in only the last value being deleted, even though several records may be eligible.

How to repeat:
Create the following table:

CREATE TABLE `poller_output_boost` (
  `local_data_id` mediumint(8) unsigned NOT NULL default '0',
  `rrd_name` varchar(19) NOT NULL default '',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `output` varchar(50) NOT NULL default '',
  PRIMARY KEY USING BTREE (`local_data_id`,`rrd_name`,`time`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

Insert some rows into it.

INSERT INTO `poller_output_boost` VALUES (5,'','2010-05-22 23:05:09','1min:0.08 5min:0.12 10min:0.19'),(5,'','2010-05-22 22:55:09','1min:0.19 5min:0.27 10min:0.28'),(6,'users','2010-05-22 22:55:09','2'),(7,'proc','2010-05-22 22:55:09','72'),(8,'ping','2010-05-22 22:55:10','79.5'),(9,'cpu_nice','2010-05-22 22:55:10','0'),(10,'cpu_system','2010-05-22 22:55:10','408699'),(11,'cpu_user','2010-05-22 22:55:10','306596'),(12,'load_1min','2010-05-22 22:55:10','0.01'),(20,'hdd_used','2010-05-22 23:00:09','20197376'),(27,'hdd_total','2010-05-22 23:05:10','509809664'),(20,'hdd_total','2010-05-22 23:00:09','63311872'),(30,'traffic_in','2010-05-22 23:05:10','218161'),(24,'hdd_used','2010-05-22 23:00:09','7208960'),(24,'hdd_total','2010-05-22 23:00:09','7208960'),(18,'hdd_used','2010-05-22 23:00:09','59457536'),(28,'cpu','2010-05-22 23:05:10','1'),(18,'hdd_total','2010-05-22 23:00:09','63311872'),(14,'load_5min','2010-05-22 23:00:09','0.00'),(15,'mem_buffers','2010-05-22 23:00:09','19724'),(22,'hdd_used','2010-05-22 23:00:09','32768'),(22,'hdd_total','2010-05-22 23:00:09','1085652992'),(6,'users','2010-05-22 23:00:08','2'),(7,'proc','2010-05-22 23:00:08','72'),(26,'hdd_used','2010-05-22 23:00:09','20740096'),(26,'hdd_total','2010-05-22 23:00:09','509809664'),(30,'traffic_out','2010-05-22 23:00:09','218161'),(3,'mem_buffers','2010-05-22 23:00:08','10044'),(10,'cpu_system','2010-05-22 23:00:09','408847'),(11,'cpu_user','2010-05-22 23:00:09','306692'),(28,'cpu','2010-05-22 23:00:09','1'),(29,'traffic_in','2010-05-22 23:00:09','361212185'),(13,'load_15min','2010-05-22 22:55:10','0.00'),(14,'load_5min','2010-05-22 22:55:10','0.02'),(15,'mem_buffers','2010-05-22 22:55:10','19724'),(16,'mem_cache','2010-05-22 22:55:10','10248'),(30,'traffic_out','2010-05-22 23:05:10','218161'),(3,'mem_buffers','2010-05-22 23:05:09','9680'),(17,'mem_free','2010-05-22 22:55:10','3776'),(18,'hdd_used','2010-05-22 22:55:10','59445248'),(18,'hdd_total','2010-05-22 22:55:10','63311872'),(19,'hdd_used','2010-05-22 22:55:10','59478016'),(19,'hdd_total','2010-05-22 22:55:10','1148964864'),(20,'hdd_used','2010-05-22 22:55:10','20197376'),(20,'hdd_total','2010-05-22 22:55:10','63311872'),(21,'hdd_used','2010-05-22 22:55:10','10493952'),(21,'hdd_total','2010-05-22 22:55:10','10493952'),(22,'hdd_used','2010-05-22 22:55:10','32768'),(22,'hdd_total','2010-05-22 22:55:10','1085652992'),(23,'hdd_used','2010-05-22 22:55:10','12718080'),(23,'hdd_total','2010-05-22 22:55:10','18273280'),(24,'hdd_used','2010-05-22 22:55:10','7208960'),(24,'hdd_total','2010-05-22 22:55:10','7208960'),(25,'hdd_used','2010-05-22 22:55:10','685020577792'),(25,'hdd_total','2010-05-22 22:55:10','1474368057340'),(26,'hdd_used','2010-05-22 22:55:10','20740096'),(26,'hdd_total','2010-05-22 22:55:10','509809664'),(27,'hdd_used','2010-05-22 22:55:10','10240'),(27,'hdd_total','2010-05-22 22:55:10','509809664'),(28,'cpu','2010-05-22 22:55:10','1'),(29,'traffic_in','2010-05-22 22:55:10','361204403'),(29,'traffic_in','2010-05-22 23:05:10','361221975'),(29,'traffic_out','2010-05-22 23:05:10','244313317'),(26,'hdd_total','2010-05-22 23:05:10','509809664'),(27,'hdd_used','2010-05-22 23:05:10','10240'),(29,'traffic_out','2010-05-22 22:55:10','244301243'),(30,'traffic_in','2010-05-22 22:55:10','218161'),(30,'traffic_out','2010-05-22 22:55:10','218161'),(21,'hdd_used','2010-05-22 23:00:09','10493952'),(21,'hdd_total','2010-05-22 23:00:09','10493952'),(25,'hdd_used','2010-05-22 23:00:09','685020577792'),(4,'mem_swap','2010-05-22 23:05:09','473988'),(25,'hdd_total','2010-05-22 23:00:09','1474368057340'),(19,'hdd_used','2010-05-22 23:00:09','59490304'),(19,'hdd_total','2010-05-22 23:00:09','1148964864'),(16,'mem_cache','2010-05-22 23:00:09','10248'),(17,'mem_free','2010-05-22 23:00:09','3764'),(23,'hdd_used','2010-05-22 23:00:09','12718080'),(23,'hdd_total','2010-05-22 23:00:09','18273280'),(8,'ping','2010-05-22 23:00:09','80.3'),(9,'cpu_nice','2010-05-22 23:00:09','0'),(27,'hdd_used','2010-05-22 23:00:09','10240'),(27,'hdd_total','2010-05-22 23:00:09','509809664'),(4,'mem_swap','2010-05-22 23:00:08','473988'),(5,'','2010-05-22 23:00:08','1min:0.08 5min:0.17 10min:0.23'),(12,'load_1min','2010-05-22 23:00:09','0.00'),(13,'load_15min','2010-05-22 23:00:09','0.00'),(29,'traffic_out','2010-05-22 23:00:09','244307414'),(30,'traffic_in','2010-05-22 23:00:09','218161'),(3,'mem_buffers','2010-05-22 22:55:09','10268'),(4,'mem_swap','2010-05-22 22:55:09','473988'),(26,'hdd_used','2010-05-22 23:05:10','20740096'),(25,'hdd_total','2010-05-22 23:05:10','1474368057340'),(25,'hdd_used','2010-05-22 23:05:10','685020577792'),(24,'hdd_total','2010-05-22 23:05:10','7208960'),(24,'hdd_used','2010-05-22 23:05:10','7208960'),(23,'hdd_total','2010-05-22 23:05:10','18273280'),(23,'hdd_used','2010-05-22 23:05:10','12718080'),(22,'hdd_total','2010-05-22 23:05:10','1085652992'),(22,'hdd_used','2010-05-22 23:05:10','32768'),(21,'hdd_total','2010-05-22 23:05:10','10493952'),(21,'hdd_used','2010-05-22 23:05:10','10493952'),(20,'hdd_total','2010-05-22 23:05:10','63311872'),(20,'hdd_used','2010-05-22 23:05:10','20197376'),(19,'hdd_total','2010-05-22 23:05:10','1148964864'),(19,'hdd_used','2010-05-22 23:05:10','59478016'),(18,'hdd_total','2010-05-22 23:05:10','63311872'),(18,'hdd_used','2010-05-22 23:05:10','59445248'),(17,'mem_free','2010-05-22 23:05:10','3776'),(16,'mem_cache','2010-05-22 23:05:10','10248'),(15,'mem_buffers','2010-05-22 23:05:10','19724'),(14,'load_5min','2010-05-22 23:05:10','0.00'),(13,'load_15min','2010-05-22 23:05:10','0.00'),(12,'load_1min','2010-05-22 23:05:10','0.00'),(11,'cpu_user','2010-05-22 23:05:10','306780'),(10,'cpu_system','2010-05-22 23:05:10','409001'),(9,'cpu_nice','2010-05-22 23:05:10','0'),(8,'ping','2010-05-22 23:05:10','33.3'),(7,'proc','2010-05-22 23:05:09','72'),(6,'users','2010-05-22 23:05:09','2'),(4,'mem_swap','2010-05-22 23:10:09','473988'),(3,'mem_buffers','2010-05-22 23:10:09','8644'),(30,'traffic_out','2010-05-22 23:10:10','218161'),(30,'traffic_in','2010-05-22 23:10:10','218161'),(29,'traffic_out','2010-05-22 23:10:10','244319810'),(29,'traffic_in','2010-05-22 23:10:10','361234956'),(28,'cpu','2010-05-22 23:10:10','1'),(27,'hdd_total','2010-05-22 23:10:10','509809664'),(27,'hdd_used','2010-05-22 23:10:10','10240'),(26,'hdd_total','2010-05-22 23:10:10','509809664');

mysql> select * from poller_output_boost where local_data_id=30;
+---------------+-------------+---------------------+--------+
| local_data_id | rrd_name    | time                | output |
+---------------+-------------+---------------------+--------+
|            30 | traffic_in  | 2010-05-22 22:55:10 | 218161 |
|            30 | traffic_in  | 2010-05-22 23:00:09 | 218161 |
|            30 | traffic_in  | 2010-05-22 23:05:10 | 218161 |
|            30 | traffic_in  | 2010-05-22 23:10:10 | 218161 |
|            30 | traffic_out | 2010-05-22 22:55:10 | 218161 |
|            30 | traffic_out | 2010-05-22 23:00:09 | 218161 |
|            30 | traffic_out | 2010-05-22 23:05:10 | 218161 |
|            30 | traffic_out | 2010-05-22 23:10:10 | 218161 |
+---------------+-------------+---------------------+--------+
8 rows in set (0.00 sec)

mysql> delete from poller_output_boost where local_data_id='30' and time<='2010-05-22 23:10:10';
Query OK, 1 row affected (0.00 sec)

Suggested fix:
For now don't use BTREE for PRIMARY KEYS in MEMORY tables.  This is such a bad bug.  Really pissed me off.

I understand the opposite is true in MySQL 5.1.  OMG!
[22 May 2010 22:35] Larry Adams 
I can also say that this is not consistent.  For example, when I just add a few records to the table, the records delete just fine.  It's only when you introduce variability.  So, it looks like the linked lists are hosed.

TheWitness
[23 May 2010 0:41] Larry Adams 
Looks like it's fixed in FC 12 5.1.46-1.
[23 May 2010 11:03] Valeriy Kravchuk 
Thank you for the problem report. Please, check with a newer version, 5.0.91, and inform about the results. 

Look:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.92-debug Source distribution

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

mysql> select * from poller_output_boost where local_data_id=30;
+---------------+-------------+---------------------+--------+
| local_data_id | rrd_name    | time                | output |
+---------------+-------------+---------------------+--------+
|            30 | traffic_in  | 2010-05-22 22:55:10 | 218161 | 
|            30 | traffic_in  | 2010-05-22 23:00:09 | 218161 | 
|            30 | traffic_in  | 2010-05-22 23:05:10 | 218161 | 
|            30 | traffic_in  | 2010-05-22 23:10:10 | 218161 | 
|            30 | traffic_out | 2010-05-22 22:55:10 | 218161 | 
|            30 | traffic_out | 2010-05-22 23:00:09 | 218161 | 
|            30 | traffic_out | 2010-05-22 23:05:10 | 218161 | 
|            30 | traffic_out | 2010-05-22 23:10:10 | 218161 | 
+---------------+-------------+---------------------+--------+
8 rows in set (0.00 sec)

mysql>  delete from poller_output_boost where local_data_id='30' and time<='2010-05-22
    '> 23:10:10';
Query OK, 8 rows affected (0.00 sec)
[29 May 2010 19:26] Larry Adams 
Ok, all tests good on 5.0.91

Thanks.
[31 May 2010 22:07] MySQL Verification Team 
Closing according last comment.