MySQL Bugs: #26996: Update of a Field in a Memory Table ends with wrong result

Bug #26996	Update of a Field in a Memory Table ends with wrong result
Submitted:	9 Mar 2007 12:44	Modified:	31 Mar 2007 23:11
Reporter:	Bj?rn-Arne Jensen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S1 (Critical)
Version:	5.0.38-BK, 5.0.27	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	bfsm_2007_03_15, BTREE, memory table, UPDATE

Description:
If i have a Memory Table and make an update on an integer Field which is in a BTREE Index and the Field in the where Clause is in a BTREE Index too, the update ends with a wrong result (allways max Integer or max TypeDef).

update table btree_test set counter=counter+1 where field_1='A';

If i change the index on colum counter from btree to hash it is working.

I tested it with mysql-5.0.20 as well as mysql-5.0.15. Same result.

How to repeat:
mysql> create table btree_test ( field_1 char(3) , counter integer , key f using btree(field_1) , key c using btree(counter) ) engine=memory default charset=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into btree_test values ('ABC',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into btree_test values ('A',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into btree_test values ('B',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into btree_test values ('C',0);
Query OK, 1 row affected (0.00 sec)

mysql> update  btree_test set counter=counter+1 where field_1='A';
Query OK, 2147483647 rows affected, 1 warning (1 hour 3 min 45.51 sec)
Rows matched: -2147483648  Changed: 2147483647  Warnings: 1

mysql> select * from btree_test;

+---------+------------+
| field_1 | counter    |
+---------+------------+
| ABC     |          0 | 
| A       | 2147483647 | 
| B       |          0 | 
| C       |          0 | 
+---------+------------+
4 rows in set (0.00 sec)

mysql> alter table btree_test drop key c;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table btree_test add key c using hash (counter);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update  btree_test set counter=counter+1 where field_1='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from btree_test;
+---------+---------+
| field_1 | counter |
+---------+---------+
| ABC     |       0 | 
| A       |       1 | 
| B       |       0 | 
| C       |       0 | 
+---------+---------+
4 rows in set (0.00 sec)

Suggested fix:

If i change the Index from BTREE to HASH it is working. But i ran in Performance Problems.

Thank you for a bug report. Verified just as described with latest 5.0.38-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.38 Source distribution

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

mysql> create table btree_test ( field_1 char(3) , counter integer , key f usin
g
    -> btree(field_1) , key c using btree(counter) ) engine=memory default
    -> charset=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into btree_test values ('ABC',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into btree_test values ('A',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into btree_test values ('B',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into btree_test values ('C',0);
Query OK, 1 row affected (0.00 sec)

mysql> update  btree_test set counter=counter+1 where field_1='A';
Query OK, 2147483647 rows affected, 1 warning (57 min 46.00 sec)
Rows matched: -2147483648  Changed: 2147483647  Warnings: 1

mysql> select * from btree_test;
+---------+------------+
| field_1 | counter    |
+---------+------------+
| ABC     |          0 |
| A       | 2147483647 |
| B       |          0 |
| C       |          0 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> alter table btree_test drop key c;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table btree_test add key c using hash (counter);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update  btree_test set counter=0 where field_1='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update  btree_test set counter=counter+1 where field_1='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from btree_test;
+---------+---------+
| field_1 | counter |
+---------+---------+
| ABC     |       0 |
| A       |       1 |
| B       |       0 |
| C       |       0 |
+---------+---------+
4 rows in set (0.01 sec)

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/22273

ChangeSet@1.2608, 2007-03-19 15:56:53+01:00, istruewing@chilla.local +3 -0
  Bug#26996 - Update of a Field in a Memory Table ends with wrong result
  
  Using a MEMORY table BTREE index for scanning for updatable rows
  could lead to an infinite loop.
  
  Everytime a key was inserted into a btree index, the position
  in the index scan was cleared. The search started from the
  beginning and found the same key again.
  
  Now we do not clear the position on key insert an more.

Approved.

Queued to 5.1-engines, 5.0-engines, 4.1-engines.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Pushed to 4.1.23

Noted in 4.1.23, 5.0.40, 5.1.18 changelogs.

In a MEMORY table, using a BTREE index to scan for updatable rows
could lead to an infinite loop.