Bug #31098 HANDLER names 5.0.45.
Submitted: 19 Sep 2007 14:23 Modified: 1 Oct 2007 10:26
Reporter: Peter Johansson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: handler, regression
Triage: Triaged: D3 (Medium)

[19 Sep 2007 14:23] Peter Johansson
Description:
Handler is not working when you do an "update" and then a "Handler .. next" you lose position when update table and Handler has the same name.

This differ from 4.1 Server.

How to repeat:
the Data
dim1,charge,recid
202,10,1
203,10,2
203,10,3
203,10,4
208,10,5
208,10,6 

Ex code1. 

HANDLER TESTH OPEN               
HANDLER TESTH READ INDEX_1 (203) // pos record 2
HANDLER TESTH READ NEXT          // pos record 3
HANDLER TESTH READ NEXT          // pos record 4
// make an update on record at positon 4.
UPDATE `TESTH` SET dim1 = 203,charge = 20 WHERE RECID = 4
HANDLER TESTH READ NEXT          // pos record 1

Ex code2 this works if you open the handler whith a different name then the update table name. 

HANDLER TESTH  OPEN AS OKHAND                
HANDLER OKHAND READ INDEX_1 = (203) // pos record 2
HANDLER OKHAND READ NEXT          // pos record 3
HANDLER OKHAND READ NEXT          // pos record 4
// make an update on record at positon 4.
UPDATE `TESTH` SET dim1 = 203,charge = 20 WHERE RECID = 4
HANDLER OKHAND READ NEXT          // pos record 5

Suggested fix:
use different name on handler.
[19 Sep 2007 14:35] Paul Dubois
http://dev.mysql.com/doc/refman/5.0/en/handler.html says:

"HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by the current thread or other threads) and these modifications might be only partially visible to HANDLER ... NEXT or HANDLER ... PREV scans."
[20 Sep 2007 10:55] Peter Johansson
The code1 should be this. sorry.

HANDLER TESTH OPEN               
HANDLER TESTH READ INDEX_1 =  (203)      // pos record 2
HANDLER TESTH READ INDEX_1 NEXT          // pos record 3
HANDLER TESTH READ INDEX_1 NEXT          // pos record 4
// make an update on record at positon 4.
UPDATE `TESTH` SET dim1 = 203,charge = 20 WHERE RECID = 4
HANDLER TESTH READ INDEX_1 NEXT          // pos record 1

The handler is not traversing the index after the update if the handlers name is the same as the table name and even if the records effected is 0 and only one thread.

When a Ex. “HANDLER  NAME read index_1 next” is executed after an update you get the first record in that index. 

Tested on a 4.1 server and it is working OK.
Tested on a 5.0.19 server and it is working OK.
On 5.0.45 community server this function differs.
[21 Sep 2007 8:25] Peter Johansson
Now I have tested this on windows XP.
Version 5.0.20a OK.
Version 5.0.27  OK
Version 5.0.37 The error/bug or change appears.

The bug seem to appear in version 5.0.37.
[23 Sep 2007 10:24] Valeriy Kravchuk
Verified just as described. Indeed, explicitely named handler works differently.