Bug #31781 multi-table UPDATE with temp-pool enabled fails with errno 17
Submitted: 23 Oct 2007 10:57 Modified: 10 Apr 2008 14:08
Reporter: Sergey Vojtovich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:4.1,5.0 OS:Windows
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: bfsm_2007_12_06

[23 Oct 2007 10:57] Sergey Vojtovich
Description:
Can't create/write to file 'C:\WINDOWS\TEMP\#sql_618_0.MYD' (Errcode: 17)

How to repeat:
N/A
[21 Nov 2007 7:49] Sveta Smirnova
Thank you for the report.

Verified as described. How to repeat see private comments.
[21 Nov 2007 9:03] MySQL Verification Team
the above testcase causes the error when running 5.0.50 like this:
E:\mysql-enterprise-gpl-5.0.50-win32\bin>mysqld-debug --no-defaults --console --skip-grant-tables -P3307  --tmpdir=e:/tmp
[21 Nov 2007 9:13] MySQL Verification Team
this is the complete testcase including drop/create table .. sorry :0

Attachment: bug31781_testcase.sql (application/octet-stream, text), 27.51 KiB.

[2 Dec 2007 8:37] Sveta Smirnova
Bug #32905 was marked as duplicate of this one.
[29 Feb 2008 15:07] Alexey Kopytov
Shorter test case. Start mysqld on Windows with --max-connections=1 --table-cache=1 --open-files-limit=0. Then execute SQL queries written below. The first execution gives:

ERROR 23 (HY000) at line 31: Out of resources when opening file 'C:\DOCUME~1\kaamos\LOCALS~1\Temp\#sql_4bc_0.MYD' (Errcode: 24)

The second one gives:

ERROR 1 (HY000) at line 31: Can't create/write to file 'C:\DOCUME~1\kaamos\LOCALS~1\Temp\#sql_4bc_0.MYD' (Errcode: 17)

SQL test case:

drop table if exists t1;
set storage_engine=myisam;
create table t1(b text);
insert into t1(b) values ('1');
create temporary table tmp01(a int);
create temporary table tmp02(a int);
create temporary table tmp03(a int);
create temporary table tmp04(a int);
create temporary table tmp05(a int);
create temporary table tmp06(a int);
create temporary table tmp07(a int);
create temporary table tmp08(a int);
create temporary table tmp09(a int);
create temporary table tmp10(a int);
create temporary table tmp11(a int);
create temporary table tmp12(a int);
create temporary table tmp13(a int);
create temporary table tmp14(a int);
create temporary table tmp15(a int);
create temporary table tmp16(a int);
create temporary table tmp17(a int);
create temporary table tmp18(a int);
create temporary table tmp19(a int);
create temporary table tmp20(a int);
create temporary table tmp21(a int);
create temporary table tmp22(a int);
create temporary table tmp23(a int);
create temporary table tmp24(a int);
create temporary table tmp25(a int);
create temporary table tmp26(a int);
update t1,t1 as t2 set t2.b='a';
[2 Mar 2008 16:16] 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/43272

ChangeSet@1.2609, 2008-03-02 19:15:41+03:00, kaa@kaamos.(none) +1 -0
  Fix for bug #31781: multi-table UPDATE with temp-pool enabled fails 
                      with errno 17
  
  my_create() did not perform any checks for the case when a file is
  successfully created by a call to open(), but the call to
  my_register_filename() later fails because the number of open files
  has exceeded the my_open_files limit. This can happen on platforms 
  which do not have getrlimit(), and hence we do not know the real limit
  for open files. In such a case an error was returned to a caller
  although the file has actually been created. Since callers assume
  my_create() to return an error only when it failed to create a file,
  they did not perform any cleanups, leaving an 'orphaned' file on the
  file system.
  
  Fixed by adding a check for the above case to my_create() and ensuring
  the newly created file is deleted before returning an error.
  
  Creating a deterministic test case in the test suite is impossible,
  because the exact steps required to reproduce the above situation
  depend on the platform and/or environment (OS per-user limits, queries
  executed by previous tests, startup parameters). The patch was
  manually tested on Windows using examples posted in the bug report.
[3 Mar 2008 14:34] 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/43318

ChangeSet@1.2609, 2008-03-03 17:34:06+03:00, kaa@kaamos.(none) +1 -0
  Fix for bug #31781: multi-table UPDATE with temp-pool enabled fails 
                      with errno 17
  
  my_create() did not perform any checks for the case when a file is
  successfully created by a call to open(), but the call to
  my_register_filename() later fails because the number of open files
  has exceeded the my_open_files limit. This can happen on platforms 
  which do not have getrlimit(), and hence we do not know the real limit
  for open files. In such a case an error was returned to a caller
  although the file has actually been created. Since callers assume
  my_create() to return an error only when it failed to create a file,
  they did not perform any cleanups, leaving an 'orphaned' file on the
  file system.
  
  Fixed by adding a check for the above case to my_create() and ensuring
  the newly created file is deleted before returning an error.
  
  Creating a deterministic test case in the test suite is impossible,
  because the exact steps required to reproduce the above situation
  depend on the platform and/or environment (OS per-user limits, queries
  executed by previous tests, startup parameters). The patch was
  manually tested on Windows using examples posted in the bug report.
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:43] Bugs System
Pushed into 5.0.60
[10 Apr 2008 14:08] Paul DuBois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

If an error occurred during file creation, the server sometimes did
not remove the file, resulting in an unused file in the filesystem.
[15 Apr 2008 12:43] Alexey Kopytov
The patch for this bug is valid only for 5.0. 5.1/6.0 still have this problem, reported as bug #36091.