MySQL Bugs: #9400: Errors deleting rows from heap tables with btree indexes

Bug #9400	Errors deleting rows from heap tables with btree indexes
Submitted:	25 Mar 2005 7:42	Modified:	25 Apr 2005 16:23
Reporter:	Sergey Naletov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10a	OS:
Assigned to:		CPU Architecture:	Any

Description:
Looks like mysql doesnt work properly  with btree indexes in memory tables.
I have such table:

CREATE TABLE `visits` (
  `ttime` int(11) unsigned NOT NULL default '0',
  `ip` int(11) unsigned NOT NULL default '0',
  `visited` smallint(5) unsigned NOT NULL default '0',
  `from_id` smallint(5) unsigned NOT NULL default '0',
  `from_special` enum('normal','noref','notrade','unknown','index') NOT NULL default 'normal',
  INDEX USING BTREE (`ip`),
  INDEX USING BTREE (`ttime`)
) ENGINE=HEAP;

When I try to delete expired records with this query:

DELETE FROM visits where ttime<UNIX_TIMESTAMP()-21600; 

I always get message - "Query OK, 1 row affected (0.00 sec)";
But table has thousands of expired records. 
If I query:

SELECT COUNT(*) FROM visits where ttime<UNIX_TIMESTAMP()-21600;

I get:
+----------+
| count(*) |
+----------+
|    10604 |
+----------+
1 row in set (0.00 sec)

So, delete query deletes only one record!

If I drop ttime index - all start working fine. 

How to repeat:
execute:
CREATE TABLE `visits` (
  `ttime` int(11) unsigned NOT NULL default '0',
  `ip` int(11) unsigned NOT NULL default '0',
  `visited` smallint(5) unsigned NOT NULL default '0',
  `from_id` smallint(5) unsigned NOT NULL default '0',
  `from_special` enum('normal','noref','notrade','unknown','index') NOT NULL default 'normal',
  INDEX USING BTREE (`ip`),
  INDEX USING BTREE (`ttime`)
) ENGINE=HEAP;

insert some rows and try to delete rows using ttime index in where clause.
Only one row will be deleted.

Hi,

Thank you for the report, but I wasn't able reproduce it with my test data using 4.1.11-debug-log:

mysql> insert into  visits values
    -> (9999999,0,1,2,'normal'),
    -> (9999999,0,1,2,'normal');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM visits where ttime<UNIX_TIMESTAMP()-21600;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.06 sec)

mysql> DELETE FROM visits where ttime<UNIX_TIMESTAMP()-21600;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM visits where ttime<UNIX_TIMESTAMP()-21600;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Can you provide some data for testing?

Hmm... Very strange. 
I also couldnt reproduce this on another database with the same data.
Even when I have copied full database with data (where bug appears) to new database - I dont see this bug on new database. On old database I still have this bug.
Looks like this bug appears on high load or something like that and somehow corrupt particular database files. Now I have no idea how to reproduce it...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".