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 11:16	Modified:	28 Nov 2006 13:28
Reporter:	Ian Brooks
Status:	Closed
Category:	Server: Memory	Severity:	S2 (Serious)
Version:	5.0.26 5.0.27	OS:	Linux (FC5)
Assigned to:		Target Version:

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