MySQL Bugs: #1810: InnoDB rollback on delete from indexed table -> strange query results

Bug #1810	InnoDB rollback on delete from indexed table -> strange query results
Submitted:	11 Nov 2003 17:53	Modified:	13 Nov 2003 5:25
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.11	OS:	Linux (Linux Mandrake 9.1)
Assigned to:		CPU Architecture:	Any

Description:
This report actually shows up a couple of issues, though the first may be a 
feature (I'm relatively new to MySQL). These operations are done on the 
indexed table as given in the "how to repeat" section of this report: 
 
1. selects on varchar columns where the contents are a number will succeed 
   if the select where clause value is a number and not a string, eg:: 
 
     select * from ids where id=1; 
 
   will work where the ids column is a varchar. As I say, this may be 
   intentional. 
2. When a delete operation is rolled back, a row may be accessed using the 
   above query, but not:: 
 
     select * from ids where id='1'; 
 
   ie. accessing the column using the correct data type. 
 

How to repeat:
Run the following:: 
 
    set autocommit=0; 
    CREATE TABLE ids (id varchar(255)) TYPE=InnoDB; 
    CREATE INDEX ids_id_idx on ids(id); 
    insert into ids values ('1'); 
    commit; 
    delete from ids where id='1'; 
    rollback; 
    select * from ids where id='1'; 
    select * from ids where id=1; 
    drop table ids; 
 
That will list an empty table on the first select, and the contents of the 
table on the second select. Note the id column is clearly a varchar. Now the 
fun part. Commenting out the "CREATE INDEX" statement makes BOTH selects work! 
 
It's a bug in the InnoDB implementation. Switching to BDB makes it work.

Hi!

I cannot repeat this with 4.0.17.

Are you using the query cache? What charset? What do EXPLAIN SELECT print?

Regards,

Heikki

mysql>
mysql>      set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql>     CREATE TABLE ids (id varchar(255)) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>     CREATE INDEX ids_id_idx on ids(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql>     commit;
Query OK, 0 rows affected (0.00 sec)

mysql>     delete from ids where id='1';
Query OK, 1 row affected (0.00 sec)

mysql>     rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>     select * from ids where id='1';
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>     select * from ids where id=1;
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>     explain select * from ids where id=1;
+-------+-------+---------------+------------+---------+------+------+----------
----------------+
| table | type  | possible_keys | key        | key_len | ref  | rows | Extra
                |
+-------+-------+---------------+------------+---------+------+------+----------
----------------+
| ids   | index | ids_id_idx    | ids_id_idx |     256 | NULL |    0 | Using whe
re; Using index |
+-------+-------+---------------+------------+---------+------+------+----------
----------------+
1 row in set (0.00 sec)

mysql>     explain select * from ids where id='1';
+-------+------+---------------+------------+---------+-------+------+----------
----------------+
| table | type | possible_keys | key        | key_len | ref   | rows | Extra
                |
+-------+------+---------------+------------+---------+-------+------+----------
----------------+
| ids   | ref  | ids_id_idx    | ids_id_idx |     256 | const |    1 | Using whe
re; Using index |
+-------+------+---------------+------------+---------+-------+------+----------
----------------+
1 row in set (0.00 sec)

mysql>

I'm not sure I'm in a position to be able to upgrade to 4.0.17 unless someone 
releases Mandrake-9.1-compatible RPMs. 
 
I couldn't find how to check the charset. The EXPLAIN output follows: 
 
mysql> explain select * from ids where id=1;  
+-------+-------+---------------+------------+---------+------+------+--------------------------+  
| table | type  | possible_keys | key        | key_len | ref  | rows | Extra                     
|  
+-------+-------+---------------+------------+---------+------+------+--------------------------+  
| ids   | index | ids_id_idx    | ids_id_idx |     256 | NULL |    0 | Using  
where; Using index |  
+-------+-------+---------------+------------+---------+------+------+--------------------------+  
1 row in set (0.00 sec)  
  
mysql> explain select * from ids where id='1';  
+-------+-------+---------------+------+---------+------+------+-------+  
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra |  
+-------+-------+---------------+------+---------+------+------+-------+  
| ids   | const | ids_id_idx    | NULL |    NULL | NULL |    1 |       |  
+-------+-------+---------------+------+---------+------+------+-------+  
1 row in set (0.00 sec)

I have installed the 4.0.16 RPM from your download area, and that fixes the 
bug.

Hi!

I was able to repeat the bug with 4.0.8, and noticed that this bug was fixed in 4.0.12.

Best regards,

Heikki

MySQL/InnoDB-4.0.12, March 18, 2003 

Fixed a bug: MySQL could erroneously return 'Empty set' if InnoDB estimated an index range size to 0 records though the range was not empty; MySQL also failed to do the next-key locking in the case of an empty index range.