MySQL Bugs: #29474: 21587 Regression Ever increasing open tables/fd when using HANDLER READ + DELETE

Bug #29474	21587 Regression Ever increasing open tables/fd when using HANDLER READ + DELETE
Submitted:	2 Jul 2007 4:27	Modified:	24 Aug 2007 9:53
Reporter:	Trent Lloyd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.32+, 5.1	OS:	Any
Assigned to:	Timothy Smith	CPU Architecture:	Any
Tags:	bfsm_2007_07_19, regression

Description:
When using a combination of HANDLER... READ and a DELETE statement on a table, MySQL continues to open new copies of the table every time, leading to an exhaustion of file descriptors.

This has been caused by the fix to Bug #21587 introduced in 5.0.32, reversing that patch to a 5.0.32 source fixes the problem, as does using 5.0.30 and the bug is still present in 5.0.42

How to repeat:
The following commands will reproduce this bug, if you then continually repeat the last 2 commands (HANDLER..READ, DELETE FROM) the FD count will go up by 1 every time, this will keep going to use up over 12,000 FDs (as far as I tested)

DROP DATABASE IF EXISTS `test_database`;
CREATE DATABASE `test_database`;
USE `test_database`;
CREATE TABLE `test_table` (`test_field` INT);
ALTER TABLE `test_table` ADD INDEX `test_field` (`test_field`);
HANDLER `test_table` OPEN;
HANDLER `test_table` READ `test_field` =(12930) LIMIT 1;
DELETE FROM `test_table` WHERE `test_field`=12930;

I also noticed this, which may or may not be related;

DROP DATABASE IF EXISTS `test_database`;
CREATE DATABASE `test_database`;
USE `test_database`;
CREATE TABLE `test_table` (`test_field` INT);
ALTER TABLE `test_table` ADD INDEX `test_field` (`test_field`);
HANDLER `test_table` OPEN;
# Try to READ from an index that doesn't exist
HANDLER `test_table` READ `test_fieldx` =(12930) LIMIT 1;
DELETE FROM `test_table` WHERE `test_field`=12930;

Which then causes;
#ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Thank you for the report.

Verfied as described.

Version 4.1 is not affected.

test case

Attachment: 29474.test (application/octet-stream, text), 442 bytes.

Davi, please fix by reverting the patch for 21587 and adding the offending test case to the test suite.
21587 will be fixed differently without show stopper priority.

Timothy agreed to do it since his team tree will be merged into 5.0.48 sooner than ours.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32602

ChangeSet@1.2496, 2007-08-15 19:34:54-06:00, tsmith@ramayana.hindu.god +3 -0
  Bug #29474: 21587 Regression Ever increasing open tables/fd when using HANDLER READ + DELETE
  
  Revert the fix for bug 21587.  That bug will be re-opened, and a new
  fix must be created.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32655

ChangeSet@1.2496, 2007-08-16 10:56:09-06:00, tsmith@ramayana.hindu.god +3 -0
  Bug #29474: 21587 Regression Ever increasing open tables/fd when using HANDLER READ + DELETE
  
  Revert the fix for bug 21587.  That bug will be re-opened, and a new
  fix must be created.

Pushed into 5.0.48

Pushed into 5.1.22-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix/reversion of earlier fix in 5.0.48 and 5.1.22 changelogs.

This was pushed to 5.1.23, not 5.1.22.