The Problem

I've had MySQL on my Windows 7 laptop for a bit (as part of wampserver), mostly for local offline WordPress development.

However, even though MySQL is relatively stable, I've been observing a vast quantity of intermittent MySQL errors, as reported by WordPress in the PHP error log (C:\wamp\logs\php_error.log). Here are some examples:

The important part here is "Errcode: 13", which is a file access error. The MySQL daemon process (mysqld.exe) randomly cannot access temporary tables it itself creates, which causes these errors and failed queries.

Digging Around

After looking around and finding nothing obvious, I tracked down a few forum posts mentioning the same issue.

Here is the gist – the problem is caused by an anti-virus program that is clearly not working properly.

The one mentioned on the forums is McAfee (big surprise, right? McAfee is a piece of junk – probably the worst anti-virus I've ever tried). In my case, however, it was the recently installed freeware security program from Microsoft called Microsoft Security Essentials, highly praised but problematic in this case nonetheless.

After thinking about it, I am confident that these security programs don't actually purposely prohibit access to the files MySQL creates. Instead, they lock these files for the duration of the check, so that the system doesn't get infected before they are approved. If you notice, the problems are related to temporary tables, created by MySQL on the fly. MySQL probably has a very short access timeout for these files, for performance reasons, and because it doesn't get this access fast enough, it considers it a failure (file deletions are failing, as you can see in the log).

The Solution

Now onto fixing the problem. The solution is to have Microsoft Security Essentials, in my case, or whatever your security program may be ignore these files.

You can block the whole Temp directory from being checked. It is not a good idea, as viruses can trickle down to that location and bypass your anti-virus:

Instead, you should just ignore by file extension: *.MYI and *.MYD. MySQL uses files with these extensions for its MyISAM table types. Using this approach is obviously safer as it doesn't single out a directory and instead targets specific files:

After I applied either of these exclusions, all MySQL Error 13 problems went away immediately.

Conclusion

The intermittent Error 13 problem on Windows is caused by 2 otherwise legitimate processes which, when mixed together, end up breaking MySQL.

Is this entirely the fault of the antivirus programs? Inadvertently, perhaps so.

Could MySQL be a bit smarter in this scenario? Perhaps so as well.

What do you, MySQL pros, think?

● ● ●

Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.

In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.

I wonder if this problem happens with all antiviruses or just some. In other words can the bad ones do something to fix it or it won't be possible without mysql's own changes…

http://beerpla.net Artem Russakovskii

I am not sure, but my guess is it happens with the ones that lock the file until it's been checked, to avoid spreading the infection in case it IS a virus. Hopefully, most of that actually do that.

Vladislav Vaintroub

Well, in the described case MySQL is trying to delete the file, and I would expect AV to be smart enough and not block opening for deletion – a removed file can impossibly spread the virus:)

http://beerpla.net Artem Russakovskii

Vladislav, I don't know enough about Windows internals to be able to tell if there's a way to do a file lock without locking it from being deleted. Maybe you could tell us (and Microsoft, and McAfee :-] )

Vladislav Vaintroub

Windows has something called file sharing mode, which is documented in CreateFile. When opening a file, one specifies desired access (read,write, delete and there is a bunch of others like "read attributes") and file sharing mode (combination of FILE_SHARE_READ,FILE_SHARE_WRITE or FILE_SHARE_DELETE). when file is removed it is opened with desired access="read attributes + delete", sharing mode =read,write,delete sets DELETE_ON_CLOSE flag and closes the file.

So, if AV would open the file with FILE_SHARE_DELETE it would be possible to delete it even if AV still has it opened, though the file would not completely disappear from the file system until the last handle to it is closed, i.e until AV closes the file.

I think Microsft would know this already

http://beerpla.net Artem Russakovskii

Thanks for the details, Vlad. Perhaps someone from MS will see this and raise as an issue.

Lachlan Mulcahy

@ColdMan: It is generally recommended process to set all AV to skip over MySQL database files. If they aren't causing problems with locking like this then they can cause issues with performance.

If your server is dedicated to MySQL use, then the risk of introducing a virus to the system is greatly reduced anyhow.

S

Artem,

You don't need an AV. Create a limited guest account for all your work sans sysadmin tasks, and use your Administrator account for the latter. If you really need Administrator rights, you can "Run as Administrator" (right-click). Use cygwin for file permissions.

Xampp is *way* better than Wampserver. I could not use xdebug with the latter.

S

Chris webb

Thanks for sharing this really useful article; I have had a similar problem in the last few weeks.

Thanks for this post. I actually have CA (Computer Associates) Total Defense virus scanner (it came free with my laptop). Although it did not have an "exclude files/directory" feature, it did have a "Enable Snooze" feature. I set it to snooze for 5 minutes, ran my database scripts and it worked!

nobody

Try to change de directory of TempFiles of mysql

my.ini

key -> [tmpdir] and set another directory c:\windows\temp.

no-more-error-13s

Thanks very much! Just ran into this tonight and this certainly helped. I'm impatient and the file type exclusions probably required a reboot so I just wound up disabling real-time protection while I ran my SQL import and everything went smoothly. I'm using Microsoft Security Essentials.

Stevie

This post led me into the correct direction — I skipped the Database files in MSE but still got the error. The I discovered that my backup program (Genie Timeline) also locks files in the Background, even if they are not in a directory Genie watches. After excluding the Temp directory explicitly, the error disappeared!

About Me

beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who is currently pursuing his own projects and regularly enjoys hacking Android, PHP, CSS, Javascript, AJAX, Perl, and regular expressions, working on Wordpress plugins and tools, tweaking MySQL queries and server settings, administering Linux machines, blogging, learning new things, and other geeky stuff.

Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.