MySQL Bugs: #23764: BTREE index deletes only 1 item on memory table

Bug #23764	BTREE index deletes only 1 item on memory table
Submitted:	30 Oct 2006 10:16	Modified:	28 Nov 2006 12:28
Reporter:	Ian Brooks	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.0.26 5.0.27	OS:	Linux (FC5)
Assigned to:		CPU Architecture:	Any

Description:
Deleting from a memory table that uses btree index algorithm results in only 1 records being deleted regardless of how many records are in the table

Joing the table to its-self using a primary key and deleteing from one side works as a temporary fix but has performance implications.

How to repeat:
Create a table with a btree index on 1 field then popultate it. run the query 
delete from table where field = 'value'

only deletes 1 row

Thank you for a problem report, and sorry for a delay with its processing. I was not able to repeat the behaviour described with 5.0.32-BK on Linux:

openxs@suse:~/dbs/5.0> 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 1
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table lookup(id INT, c1 char(100), index using btree(id)) engine=
Memory;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lookup values (1, 'a'), (1, 'b'), (2, 'a'), (2, 'b');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from lookup;
+------+------+
| id   | c1   |
+------+------+
|    1 | a    |
|    1 | b    |
|    2 | a    |
|    2 | b    |
+------+------+
4 rows in set (0.00 sec)

mysql> delete from lookup where id=1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from lookup;
+------+------+
| id   | c1   |
+------+------+
|    2 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

mysql> delete from lookup where id='2';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from lookup;
Empty set (0.00 sec)

Please, sends your complete test case, if mine is substantially different.

Only difference is version. were only on .27 I have uploaded a zip file containg a table that causes the error.

zip file http://www.scran.ac.uk/downloads/searchresults.zip (408K) 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1400742 to server version: 5.0.27-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(****@****) [scran]> SELECT * FROM `_searchresults0` WHERE searchid = '2fvapxhu75' LIMIT 0 , 30
    -> ;
+------------+--------+-------------------+-------------------+
| searchid   | id     | usi               | rusi              |
+------------+--------+-------------------+-------------------+
| 2fvapxhu75 | 140432 | 000-000-594-769-C | 000-000-594-770-R |
| 2fvapxhu75 | 139984 | 000-000-549-274-C | 000-000-549-274-R |
| 2fvapxhu75 | 140496 | 000-000-510-944-C | 000-000-510-944-R |
| 2fvapxhu75 | 140832 | 000-000-463-482-C | 000-000-463-482-R |
| 2fvapxhu75 | 140048 | 000-000-624-289   | 000-000-624-289-R |
| 2fvapxhu75 | 139612 | 000-000-469-258-C | 000-000-469-258-R |
| 2fvapxhu75 | 140560 | 000-000-580-295-C | 000-000-580-295-R |
| 2fvapxhu75 | 140896 | 000-000-578-011-C | 000-000-578-011-R |
| 2fvapxhu75 | 140112 | 000-000-096-986-C | 000-000-096-986-R |
| 2fvapxhu75 | 139676 | 000-000-136-675-C | 000-000-136-675-R |
| 2fvapxhu75 | 140624 | 000-000-599-516-C | 000-000-599-516-R |
| 2fvapxhu75 | 140960 | 000-000-095-787-C | 000-000-095-787-R |
| 2fvapxhu75 | 140176 | 000-000-135-095-C | 000-000-135-095-R |
| 2fvapxhu75 | 139740 | 000-100-103-874-C | 000-100-103-874-R |
| 2fvapxhu75 | 140688 | 000-000-631-622-C | 000-000-634-330-R |
| 2fvapxhu75 | 141024 | 000-000-507-054-C | 000-000-507-054-R |
| 2fvapxhu75 | 140240 | 000-000-132-978-C | 000-000-132-978-R |
| 2fvapxhu75 | 139804 | 000-000-003-684-C | 000-000-003-684-R |
| 2fvapxhu75 | 140752 | 000-000-034-761-C | 000-000-034-761-R |
| 2fvapxhu75 | 140304 | 000-000-135-082-C | 000-000-135-082-R |
| 2fvapxhu75 | 139867 | 000-000-005-095-C | 000-000-005-095-R |
| 2fvapxhu75 | 140379 | 000-000-465-783-C | 000-000-465-783-R |
| 2fvapxhu75 | 139931 | 000-000-135-282-C | 000-000-135-282-R |
| 2fvapxhu75 | 140443 | 000-190-002-208-C | 000-190-002-208-R |
| 2fvapxhu75 | 139995 | 000-000-513-443-C | 000-000-513-443-R |
| 2fvapxhu75 | 140507 | 000-000-568-941-C | 000-000-568-941-R |
| 2fvapxhu75 | 140843 | 000-000-637-006-C | 000-000-642-184-R |
| 2fvapxhu75 | 140059 | 000-000-527-300-C | 000-000-527-300-R |
| 2fvapxhu75 | 139623 | 000-000-136-654-C | 000-000-136-654-R |
| 2fvapxhu75 | 140571 | 000-000-580-368-C | 000-000-580-368-R |
+------------+--------+-------------------+-------------------+
30 rows in set (0.00 sec)

(****@****) [scran]> DELETE FROM `_searchresults0` WHERE searchid = '2fvapxhu75';
Query OK, 1 row affected (0.00 sec)

(****@****) [scran]>

So, as you can see from my test case, this problem has been fixed somehow in our source repository, and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html