MySQL Bugs: #25872: mysqldump

Bug #25872	mysqldump
Submitted:	26 Jan 2007 16:35	Modified:	16 Feb 2007 10:22
Reporter:	Philip Owen
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.26	OS:	Microsoft Windows (Windows Server 2003)
Assigned to:		Target Version:
Tags:	error17, mysqldump

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

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.

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.

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?

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.

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;"?

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.

Thank you for the feedback. Could you please provide the output of show
variables?. Thanks in advance.

Output from "show variables" provided.

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

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?

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.

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.

So, this can be closed as not a result of bug in MySQL's code.

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

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)

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.

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.

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