Bug #30221 log_tables.test fails on Windows
Submitted: 3 Aug 2007 7:44 Modified: 20 Nov 2010 19:48
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.2.6 OS:Microsoft Windows
Assigned to: Sergey Petrunya
Tags: pbfail
Triage: D3 (Medium)

[3 Aug 2007 7:44] Ingo Strüwing
Description:
main.log_tables                [ fail ]

Errors are (from e:/var-ps_row-101/log/mysqltest-time) :
mysqltest: At line 471: query 'ALTER TABLE mysql.slow_log ENGINE = CSV' failed: 7: Error on rename of '.\mysql\slow_log.MYI' to '.\mysql\#sql2-24c0-2.MYI' (Errcode: 13)
(the last lines may be the most important ones)

Stopping All Servers

How to repeat:
See https://intranet.mysql.com/secure/pushbuild/showdir.pl?dir=mysql-5.2-engines
[17 Oct 2007 20:29] Damien Katz
The problem seems to be myisam. Here is test script to reproduce:

use test;

CREATE TABLE `foo` (
  a INT NOT NULL,
  seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

FLUSH LOGS;

INSERT into foo values(1, DEFAULT);
INSERT into foo values(1, DEFAULT);
INSERT into foo values(1, DEFAULT);
INSERT into foo values(1, DEFAULT);
SELECT * FROM foo WHERE seq >= 2 LIMIT 3;

FLUSH LOGS;

ALTER TABLE foo DROP COLUMN seq;
ALTER TABLE foo ENGINE = CSV;
[17 Oct 2007 20:38] Damien Katz
Here is updated sql. The flushes in the previous example are not necessary.

CREATE TABLE `foo` (
  a INT NOT NULL,
  seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT into foo values(1, DEFAULT);
INSERT into foo values(1, DEFAULT);
INSERT into foo values(1, DEFAULT);
INSERT into foo values(1, DEFAULT);
SELECT * FROM foo WHERE seq >= 2 LIMIT 3;

ALTER TABLE foo DROP COLUMN seq;
ALTER TABLE foo ENGINE = CSV;

The last line fails with this error:

mysqltest: At line 28: query 'ALTER TABLE foo ENGINE = CSV' failed: 7: Error on rename of '.\test\foo.MYI' to '.\test\#sql2-d98-1.MYI' (Errcode: 13)
[17 Oct 2007 21:18] Konstantin Osipov
Hello Igor, Sergey,

the bug is reproducible without use of log tables (please see the test case attached by Damien above).

The bug is not reproducible if the amount of rows in the table is changed and SELECT specification is changed/simplified.
This suggests that the bug originates in the SELECT implementation itself.

Since the bug is reproducible in 5.2 only, one got to be looking at 5.2-specific changes, and MyISAM has only one new feature: Ds_Mrr

Indeed, this access method is used in 5.2 for the query in question:
mysql> explain extended SELECT * FROM foo WHERE seq >= 2 LIMIT 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition; Using MRR
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`foo`.`a` AS `a`,`test`.`foo`.`seq` AS `seq` from `test`.`foo` where (`test`.`foo`.`seq` >= 2) limit 3

My suspicion is that this access method does not close the index read on completion and this leads to a file descriptor leakage (error 13 on Windows indicates that there is an open file descriptor and thus the file can not be
dropped or moved).

Your help is much appreciated.
[29 Jan 2008 14:52] Sergey Petrunya
The problem can be observed on Linux also:

 * Start the server
 * Run the query that uses MRR and has LIMIT clause
 * Run lsof and watch the the number of opened MYD files grow with each query execution.
[29 Jan 2008 17:26] 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/41371

ChangeSet@1.2792, 2008-01-29 20:25:33+03:00, sergefp@mysql.com +4 -0
  BUG#30221 "log_tables.test fails on Windows"
  The problem was that for LIMIT queries handler->index_end() was not called,
  and DS-MRR scan was not properly de-initialized, which caused leaks of file
  descriptors in MyISAM.
  
  Fix: Call ds_mrr.dsmrr_close() also in handler->reset().
[5 Feb 2008 11:02] Ingo Strüwing
Set back to "in progress". No response to review.
[14 Feb 2008 3:53] 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/42254

ChangeSet@1.2804, 2008-02-14 06:53:02+03:00, sergefp@mysql.com +7 -0
  BUG#30221 "log_tables.test fails on Windows"
  The problem was that dsmrr_close() was not called for LIMIT queries that didn't
  finish the DS-MRR scan. This failure to deinitialize the scan caused leaks of 
  file descriptors in MyISAM.
  
  Fix: Call ds_mrr.dsmrr_close() in handler->reset().
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[18 Apr 2008 15:35] Paul Dubois
Noted in 6.0.5 changelog.

For multi-read-range scans used to resolve LIMIT queries, failure to
close the scan caused file descriptor leaks for MyISAM tables.
[16 Aug 2010 6:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:21] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 19:48] Paul Dubois
Noted in 5.6.1 changelog.