MySQL Bugs: #30590: delete from memory table with composite btree primary key

Bug #30590	delete from memory table with composite btree primary key
Submitted:	23 Aug 2007 12:26	Modified:	23 Oct 2007 0:08
Reporter:	Shane Bester
Status:	Closed
Category:	Server: Memory	Severity:	S1 (Critical)
Version:	5.0.48,5.1.22	OS:	Any
Assigned to:	Sergey Vojtovich	Target Version:	5.1.23

Description:
deleting from a memory table where the PK is defined as a composite key over two columns
results in wrong matches.  select works fine.

mysql> drop table if exists `t`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t` (`a` int,`b` int,`c` int,primary key using btree
(`a`,`b`))engine=memory;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into `t` values (1,1,1),(2,1,1),(2,2,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `t`;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 1 |    1 |
| 2 | 2 |    1 |
+---+---+------+
3 rows in set (0.00 sec)

mysql> delete from `t` where `a`=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from `t`;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    1 |<---- we expect this to be deleted!!
+---+---+------+
2 rows in set (0.00 sec)

How to repeat:
drop table if exists `t`;
create table `t` (`a` int,`b` int,`c` int,primary key using btree
(`a`,`b`))engine=memory;
insert into `t` values (1,1,1),(2,1,1),(2,2,1);
select * from `t`;
delete from `t` where `a`=2;
select * from `t`;

Suggested fix:
as a workaround, using hash indexes seems to work

this leads to wrong data!  related might be bug #23764

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33471

ChangeSet@1.2680, 2007-08-30 23:47:04+05:00, svoj@april.(none) +4 -0
  BUG#30590 - delete from memory table with composite btree primary key
  
  DELETE query against memory table with btree index may remove
  not all matching rows. This happens only when DELETE uses
  index read method to find matching rows. E.g. for queries
  like DELETE FROM t1 WHERE a=1.
  
  Fixed by reverting fix for BUG9719 and applying proper solution.

IMHO a second reviewer is not absolutely required. May Calvin decide.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34168

ChangeSet@1.2680, 2007-09-13 15:39:16+05:00, svoj@mysql.com +5 -0
  BUG#30590 - delete from memory table with composite btree primary key
  
  DELETE query against memory table with btree index may remove
  not all matching rows. This happens only when DELETE uses
  index read method to find matching rows. E.g. for queries
  like DELETE FROM t1 WHERE a=1.
  
  Fixed by reverting fix for BUG9719 and applying proper solution.

Pushed into 5.1.23-beta

Pushed into 5.0.52

Pushed into 4.1.24

Noted in 4.1.24, 5.0.52, 5.1.23 changelogs.

For MEMORY tables, DELETE statements that remove rows based on an
index read could fail to remove all matching rows.

I have this issue always on memory table with unique composite btree key. But 
also SOMETIMES I have similar issue on memory table with simple btree key:
...
KEY `key` USING BTREE (`key`)
...

SELECT COUNT(*) AS rows_count FROM `table` WHERE `key` = <SOME_VALUE>;
+------------+
| rows_count |
+------------+
|<ROWS_COUNT>|
+------------+
1 row in set (0.00 sec)

DELETE FROM `table` WHERE `key` = <SOME_VALUE>;
Query OK, <X> rows affected (0.00 sec)

0 <= X <= ROWS_COUNT

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65096

2768 Anurag Shekhar	2009-02-04
      Bug#30590 delete from memory table with composite btree primary key
      
      While evaluating self join with delete server uses two instance of handler 
      (with both holding reference to same file). During query evaluation last_pos 
      is cached in handler to improve search performance. But in the mean while 
      second handler is used to delete the node last_pos is holding. This makes 
      the last_pos an invalid and when accessed results in segmentation fault.
      
      To fix this I have introduced a new variable in HP_INFO structure (deleted) 
      which remembers the number of deleted (initialized while opening the file) 
      nodes since last_pos is updated. If this value differs from the value of 
      deleted nodes in shared structure last_pos is invalidated (set to zero).

My patch wasn't meant for this bug. I had given wrong id in the comment which caused this
confusion sorry about that.