MySQL Bugs: #30221: log_tables.test fails on Windows

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:	Windows
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	pbfail

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

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;

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)

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.

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.

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().

Set back to "in progress". No response to review.

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().

Pushed into 6.0.5-alpha

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.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Noted in 5.6.1 changelog.