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

[2 Jul 2007 4:27] Trent Lloyd
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;
[2 Jul 2007 4:29] Trent Lloyd
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
[2 Jul 2007 8:56] Sveta Smirnova
Thank you for the report.

Verfied as described.

Version 4.1 is not affected.
[12 Jul 2007 19:26] Gleb Shchepa
test case

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

[15 Aug 2007 20:42] Konstantin Osipov
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.
[15 Aug 2007 20:44] Konstantin Osipov
Timothy agreed to do it since his team tree will be merged into 5.0.48 sooner than ours.
[16 Aug 2007 1:36] Bugs System
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.
[16 Aug 2007 16:59] Bugs System
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.
[20 Aug 2007 10:05] Bugs System
Pushed into 5.0.48
[20 Aug 2007 10:20] Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 9:53] Jon Stephens
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.
[31 Aug 2007 17:01] Paul Dubois
This was pushed to 5.1.23, not 5.1.22.