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: | |
Category: | MySQL Server: Memory storage engine | Severity: | S2 (Serious) |
Version: | 5.0.26 5.0.27 | OS: | Linux (FC5) |
Assigned to: | CPU Architecture: | Any |
[30 Oct 2006 10:16]
Ian Brooks
[28 Nov 2006 11:28]
Valeriy Kravchuk
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.
[28 Nov 2006 11:53]
Ian Brooks
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]>
[28 Nov 2006 12:28]
Valeriy Kravchuk
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