Can't create/write to file MySQL

Question

Server Configuration:

MySQL 5.051a-community-nt-log Community Edition installed on Windows Server 2003 Standard Edition SP1.

This MySQL Server is part of a client's WAMP installation

Problem:

MySQL was originally using the C:\WINDOWS\TEMP directory for temp files. From time to time, the client would get the following error when trying to access data in a table in phpMyAdmin.

"#1 - Can't create/write to file 'C:\WINDOWS\TEMP\#sql_1760_0.MYD' (Errcode: 17)".

Which would require a restart of MySQL to fix.

Thinking this might be a problem with using the C:\WINDOWS\TEMP directory, I created another directory that the MySQL service had read/write access to and pointed tmpdir to that directory in my.cnf.

tmpdir=e:/Program Files/wamp/bin/mysql/mysql5.0.51a/tmp

I restarted MySQL and accessing tables from phpMyAdmin worked fine for one entire day and then the following morning, phpMyAdmin started complaning about the same error.

Investigation:

There is more than enough disk space on both the C: and E: drives

Not seeing any other errors from MySQL.

Window's application logs don't mention anything about the error.

No information in MySQL's logs

Database in question is a mix of MyISAM and InnoDB tables.

I have not yet veririfed, if the inaccessible tables are only the ones in MyISAM.

Questions:

Has anyone seen this problem before with a WAMP MySQL 5.0 installation and know what might be causing it and or how to resolve this error?

• Philᵀᴹ: Have you tried excluding the directory from virus scanning? Seems to be the main cause of this
• user4659: Hi Phil, that's probably the answer as a virus scanner was recently installed. If you add your answer below I will check it off as correct once I've confirmed.
• user4659: Thanks Phil, I'll let you know as soon as I'm sure excluding the directory resolved the problem.

Answer 1

The most common cause of this is virus scanning, as virus scanners tend to lock files.

Try excluding the directory from scanning.

• RolandoMySQLDBA: I have heard of this legend. Beware of legends that are true. +1 !!!
• user4659: Thanks again Phil, no reports of problems this morning.