Bug #58254 Handler reset on temporary table
Submitted: 17 Nov 2010 13:25 Modified: 7 Jul 2011 17:44
Reporter: Adrian Barna Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.54 OS:Microsoft Windows
Assigned to: Paul Dubois CPU Architecture:Any
Tags: handler, reset, temporary
Triage: Needs Triage: D2 (Serious)

[17 Nov 2010 13:25] Adrian Barna
Description:
When opening 2 handlers, one on a permanent table and the other on a temporary table, the record index in the temporary table handler is reset after certain SQL statements.

When it's the only one handler open in current connection, the temporary table handler does not reset its position.
Also, the permanent table handler(s) do not lose their record index.

I tested on both innodb and myisam tables, and both are showing the issue.

The exemple below shows the issue after a SELECT statement, but other statements are also generating the same problem: insert, update, delete, show create table, handler open (for example table AAA below with different alias; or a table from another database), create/drop index (on a third table or table in different database).

How to repeat:
mysql> create database TTT;
Query OK, 1 row affected (0.00 sec)

mysql> use TTT;
Database changed

mysql> create table AAA (aaa varchar(3));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into AAA (aaa) values ('aaa'),('aab');
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create temporary table TTT (ttt varchar(3));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into TTT (ttt) values ('tta'),('ttb'),('ttc'),('ttd');
Query OK, 4 rows affected (0.10 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> handler AAA open;
Query OK, 0 rows affected (0.00 sec)

mysql> handler AAA read first;
+------+
| aaa  |
+------+
| aaa  |
+------+
1 row in set (0.00 sec)

mysql> handler TTT open;
Query OK, 0 rows affected (0.00 sec)

mysql> handler TTT read first;
+------+
| ttt  |
+------+
| tta  |
+------+
1 row in set (0.00 sec)

mysql> select * from AAA;
+------+
| aaa  |
+------+
| aaa  |
| aab  |
+------+
2 rows in set (0.00 sec)

mysql> handler AAA read next;
+------+
| aaa  |
+------+
| aab  |
+------+
1 row in set (0.00 sec)

mysql> handler TTT read next;
+------+
| ttt  |
+------+
| tta  |
+------+
1 row in set (0.00 sec)
[17 Nov 2010 15:17] Valeriy Kravchuk
Verified with current mysql-5.1 tree on Mac OS X:

macbook-pro:5.5 openxs$ 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 2
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table AAA (aaa varchar(3));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into AAA (aaa) values ('aaa'),('aab');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create temporary table TTT (ttt varchar(3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into TTT (ttt) values ('tta'),('ttb'),('ttc'),('ttd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> handler AAA open;
Query OK, 0 rows affected (0.00 sec)

mysql> handler AAA read first;
+------+
| aaa  |
+------+
| aaa  |
+------+
1 row in set (0.00 sec)

mysql> handler TTT open;
Query OK, 0 rows affected (0.00 sec)

mysql> handler TTT read first;
+------+
| ttt  |
+------+
| tta  |
+------+
1 row in set (0.01 sec)

mysql> select * from AAA;
+------+
| aaa  |
+------+
| aaa  |
| aab  |
+------+
2 rows in set (0.00 sec)

mysql> handler AAA read next;
+------+
| aaa  |
+------+
| aab  |
+------+
1 row in set (0.00 sec)

mysql> handler TTT read next;
+------+
| ttt  |
+------+
| tta  |
+------+
1 row in set (0.00 sec)
[5 Jul 2011 17:31] Paul Dubois
The last HANDLER statement in the example shown by the bug reporter returns "ttb" (not "tta") in MySQL 4.1, 5.0, 5.5, and 5.6.

In MySQL 5.1, it returns "ttb" though 5.1.22. It begins returning "tta" in 5.1.23.
[5 Jul 2011 17:49] Davi Arnaut
Fixed in 5.5 in the context of Bug#46224:

"Fix a bug in mysql_ha_flush() when we would always flush a temporary HANDLER when mysql_ha_flush() is called (actually mysql_ha_flush() never needs to flush temporary tables)."
[5 Jul 2011 18:04] Paul Dubois
For 5.5.x: 5.5.0 through 5.5.2 return "tta", 5.5.3 and up return "ttb".

See Bug#46224.
[7 Jul 2011 17:44] Omer Barnir
This issue was fixed in 5.5 and will not be back ported to 5.1 code (see bug#46224)