MySQL Bugs: #39041: Document HANDLER closed and marked for reopen on SELECT in same connection

Bug #39041	Document HANDLER closed and marked for reopen on SELECT in same connection
Submitted:	26 Aug 2008 10:36	Modified:	9 Dec 2008 19:56
Reporter:	Jørgen Austvik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	6.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Please document that an open handler can ble closed and marked for reopen if there are another other statements on the same connection which touches tables.

From Dmitry Lenev:

What probably happens here is that selects from I_S which you perform in
connection locker" are implicitly close and mark for reopen open HANDLER
thus allowing ALTER to proceed (execution of any statement that uses 
tables will close and mark for reopen all HANDLERs in this connection
against which there are pending metadata locks or for which are marked
for flush).

How to repeat:
CREATE TABLE t2(a INT) ENGINE=$engine_type;
INSERT INTO t2 VALUES (1);

# Connections we will use
connect (locker,localhost,root,,);
connect (waiter,localhost,root,,);

connection locker;

HANDLER t2 OPEN;
HANDLER t2 READ FIRST;

connection waiter;
send ALTER TABLE t2 ADD COLUMN (c INT);

connection locker;
let $wait_condition=
  SELECT COUNT(*) = 1 FROM information_schema.processlist
  WHERE state="Table lock" AND info = "ALTER TABLE t2 ADD COLUMN (c INT)";
--source include/wait_condition.inc

HANDLER t2 CLOSE;
SELECT * FROM t2;

connection waiter;
reap;

# Clean up
connection default;

disconnect waiter;
disconnect locker;

DROP TABLE t2; 

If the connection before let $wait_condition had been default, the test had passed.

Suggested fix:
Update documentation chapter 12.2.3. HANDLER Syntax. Add information saying that executing statements that use tables in the same connection as an open HANDLER will close and mark the handler for reopen. Please ask runtime/Dmitry for specifics.

Thank you for the bug report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added this to the manual:

An open handler can be closed and marked for reopen, in which case
the handler loses its position in the table. This occurs when both of
the following circumstances are true: 

Any session executes FLUSH TABLES or DDL statements on the handler's
table. 

The session in which the handler is open executes non-HANDLER
statements that use tables.