Bug #54860 detect when HANDLER was implicitly reset and looses position

• Submitted: 28 Jun 2010 10:33
• Reporter: Andrii Nikitin
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: all
• OS: Any
• CPU Architecture: Any

[28 Jun 2010 10:33] Andrii Nikitin

Description:
HANDLER may loose position if any session executes DDL on the table or flushes it.

http://dev.mysql.com/doc/refman/5.1/en/handler.html
"
An open handler can be closed and marked for reopen, in which case the handler loses its position in the table. 
"

On practice there is no way to detect that handler was reset:

How to repeat:
mysql> create table t(id int) engine=myisam;
Query OK, 0 rows affected (0.16 sec)

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

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

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

mysql> handler ttt read first;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

mysql> handler ttt read next;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

At this point execute "alter table t add u int;" in second session.
Now HANDLER was reset and READ will return again first row without any warning:

mysql> handler ttt read next;
+------+------+
| id   | u    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

Suggested fix:
Generate warning or error.

There may be some workarounds, e.g. perform analysis of SHOW STATUS like "handler%"; before and after HANDLER command.