MySQL Bugs: #31781: multi-table UPDATE with temp-pool enabled fails with errno 17

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

Description:
Can't create/write to file 'C:\WINDOWS\TEMP\#sql_618_0.MYD' (Errcode: 17)

How to repeat:
N/A

Thank you for the report.

Verified as described. How to repeat see private comments.

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

this is the complete testcase including drop/create table .. sorry :0

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

Bug #32905 was marked as duplicate of this one.

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';

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

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.

The patch for this bug is valid only for 5.0. 5.1/6.0 still have this problem, reported as bug #36091.