Bug #25872 mysqldump
Submitted: 26 Jan 2007 15:35 Modified: 16 Feb 2007 9:22
Reporter: Philip Owen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26 OS:Windows (Windows Server 2003)
Assigned to: CPU Architecture:Any
Tags: error17, mysqldump

[26 Jan 2007 15:35] Philip Owen 
Description:
mysqldump intermittently aborts with error messages like:

-- Connecting to localhost...
-- Retrieving table structure for table table1...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table table2...
-- Sending SELECT query...
-- Retrieving rows...
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'table2'': Can't create/write to file 'C:\WINDOWS\TEMP\#sql_2b8_0.MYD' (Errcode: 17) (1)

and

mysqldump: Couldn't execute 'show fields from `archive`': Can't create/write to file 'C:\WINDOWS\TEMP\#sql_2b8_0.MYD' (Errcode: 17) (1)

Comamnd: mysqldump -u backup --password=XXXX --lock-tables --disable-keys --add-locks --extended-insert --quick --all --verbose --databases XXXX

The server has 18 databases, and several will dump fine while others will fail with this message. Affected databases change, but are usually the larger ones (10s of thousands of rows so not exactly huge).

The file C:\WINDOWS\TEMP\#sql_2b8_0.MYD is left in the directory after abort, deleting it and re-dumping gives the same error.

Nothing in the Windows Event Log to indicate disk read or write failures.

Same databases on 5.0.16 have the same issue but seems to happen less frequently. Filename there is #sql_d5c_0.MYD

Sorry this report is so vague, but the behaviour is not consistent.

How to repeat:
Unknown
[26 Jan 2007 15:54] MySQL Verification Team 
Thank you for the bug report. Could you please try the latest release version
and also clean the temp directory of files like showed in the error messages.
Thanks in advance.
[26 Jan 2007 16:07] Philip Owen 
Miguel,

If I clean the directory of temp files, several small dumps will work before a larger one fails part way through with this error, leaving the file in place again.

According to the changelog for 5.0, 5.0.27 only reverted an ABI problem. Do you want me to try that version anyway? I can't install 5.0.33 as there is no Windows distribution.

Thanks.
[26 Jan 2007 16:23] Philip Owen 
I just got the same error (including the path to C:\WINDOWS\TEMP) using mysqldump from a RedHat box when dumping the Windows server. So I presume this is a problem with the server rather than mysqldump. Should I change the category for the bug?
[26 Jan 2007 16:44] Philip Owen 
Can now reproduce:

create a file with 1000 lines of:
"show fields from user;"
save it as show.sql
mysql -u whoever -p
use mysql;
source show.sql

Several show statements will work before some start failing with a .MYI file existing errors:

ERROR 1 (HY000): Can't create/write to file 'C:\WINDOWS\TEMP\#sql_2b8_0.MYI' (Errcode: 17)

This happens intermittently with some working, some not, then after the first:

ERROR 1 (HY000): Can't create/write to file 'C:\WINDOWS\TEMP\#sql_2b8_0.MYD' (Errcode: 17)

they all produce that error.

After the script is run, the MYD file exists but the MYI doesn't. This is as you would expect as the error relating to the MYI is only fatal to the statement whereas the error with the MYD causes all future show commands to fail.
[26 Jan 2007 17:01] Valeriy Kravchuk 
This behaviour may have something to do with up to only 2048 open files limit on Windows! Read http://dev.mysql.com/doc/refman/5.0/en/windows-vs-unix.html.

Can you repeat this with a file with 900 (not 1000) lines "show fields from user;"?
[29 Jan 2007 8:47] Philip Owen 
The 1000 was an arbitrary large number. It seems to happen after approximately 50 "show" commands.

There is nothing to indicate file handle exhaustion in the windows event log.
[29 Jan 2007 19:10] MySQL Verification Team 
Thank you for the feedback. Could you please provide the output of show
variables?. Thanks in advance.
[1 Feb 2007 10:27] Philip Owen 
Output from "show variables" provided.
[6 Feb 2007 11:38] Simon Hodgson 
I'm using version 5.0.34 and getting the same problem. All applications working fine util the daily backup runs, then after that some queries work, but some are displaying this error "Can't create/write to file 'C:\WINDOWS\TEMP\#sql_20cc_0.MYD' (Errcode: 17)"

OS is also Windows Server 2003 SP 1
[15 Feb 2007 13:18] Valeriy Kravchuk 
Tried to repeat many times according to you comment dated [26 Jan 17:44]. With 1000, 1000+ and even 2053 rows of SHOW FIELDS FROM user;. No luck. But I use Windows XP SP2 and 5.0.27.

So, if not a problem with number of open handles (obvious one, in case of mysqldump and Windows), it can be only a platfrom-specific problem, or a lack of disk space. 

All reporters:

Can you check if this is repeatable on Windows XP, and that you have free space for temporary files on disk?
[15 Feb 2007 14:11] Simon Hodgson 
I think we've narrowed the cause of this being McAfee VirusScan Enterprise 8.5. Moved the MySQL Temp folder to a folder seperate from the Windows Temp folder, then excluded this folder from the on access virus scanner and the problem had gone away.
[15 Feb 2007 14:13] Philip Owen 
Sufficient disk space on affected servers for the temporary files (several GB free).

Cannot replicate problem on my Windows XP SP2 desktop using 5.0.26. Desktop is a single processor hyper-threaded whereas affected server is a dual processor hyper-threaded FWIW.

Saw faster performance of the SHOW FIELDS script on XP. Lead me to suspect that something was interfering with the temporary files. XP desktop had no virus scanner, server was running McAfee 8.5. Excluded Windows\Temp and problem was resolved.

Sorry to have wasted your time.

Thanks for all the help.
[16 Feb 2007 9:22] Valeriy Kravchuk 
So, this can be closed as not a result of bug in MySQL's code.
[29 May 2007 0:57] jason martin 
Actually this wasn't a waste of time because I had the same issue and you just saved me and probably others hours of investigation
[14 Feb 2008 0:00] Alex Gonzalez 
It was also necessary to add the MySQL\data directory to the McAfee exclusion list.  That On-Access scanner causing mysqldump to fail with;

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'namedquery\_group\_map'': Can't create/write to file 'C:\MySQL\Data\#sql_1164_0.MYI' (Errcode: 17) (1)
[8 Sep 2008 14:31] David Heath 
We are experiencing this problem at some of our client sites – these are large multi-national organizations that are not prepared to disable their anti-virus software – even for a couple of directories.

Hence, please could this bug be re-opened and a work around be found within the MySQL code.
[17 Feb 2009 15:45] Jacob Daly 
I came across this issue using Windows XP SP2, with McAfee VirusScan Enterprise Workstation v8.5.

It seems the McAfee wasn't allowing mysqldump access to the Windows temp directory.  Running the following command to change the %TEMP% environment variable did the trick for us:

set temp=c:\temp

Then run mysqldump as normal.
[20 Mar 2009 22:45] Trent Lloyd 
I have no idea if this is the actual fix/same issue or not.

But you may wish to try disabling temp-pool, per this bug:
http://bugs.mysql.com/bug.php?id=39750