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

[9 Mar 2007 12:44] Bj?rn-Arne Jensen
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.
[9 Mar 2007 14:06] Valeriy Kravchuk
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)
[19 Mar 2007 14:57] Bugs System
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.
[21 Mar 2007 12:56] Sergey Vojtovich
Approved.
[23 Mar 2007 9:11] Ingo Strüwing
Queued to 5.1-engines, 5.0-engines, 4.1-engines.
[30 Mar 2007 17:28] Bugs System
Pushed into 5.1.18-beta
[30 Mar 2007 17:30] Bugs System
Pushed into 5.0.40
[30 Mar 2007 18:04] Ingo Strüwing
Pushed to 4.1.23
[31 Mar 2007 23:11] Paul DuBois
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.