| Share |
Updated: September 16th, 2012
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:
[05-Jan-2010 09:47:51] WordPress database error Error on delete of
'C:\Windows\TEMP\#sql17e0_1a2_6.MYD' (Errcode: 13) for query SELECT t.*, tt.*
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id =
tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (3)
ORDER BY t.name ASC made by require, require_once, include, get_footer,
locate_template, load_template, require_once, dynamic_sidebar,
call_user_func_array, widget_rrm_recent_posts, RecentPosts->execute,
ppl_expand_template, otf_categorylinks, get_the_category, wp_get_object_terms
[05-Jan-2010 09:50:42] WordPress database error Error on delete of
'C:\Windows\TEMP\#sql17e0_1b0_0.MYD' (Errcode: 13) for query SELECT
SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON
(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND
wp_term_taxonomy.term_id IN ('3') AND wp_posts.post_type = 'post' AND
(wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY
wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10 made by require, wp,
WP->main, WP->query_posts, WP_Query->query, WP_Query->get_postsThe 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.
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.
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…
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.
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:)
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 :-] )
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
Thanks for the details, Vlad. Perhaps someone from MS will see this and raise as an issue.
@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.
I'd suggest skipping scanning of your entire datadir and all *.frm, *.MYI, *.MYD and *.ibd files in your MySQL temp dir.
If your server is dedicated to MySQL use, then the risk of introducing a virus to the system is greatly reduced anyhow.
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
Thanks for sharing this really useful article; I have had a similar problem in the last few weeks.
I just installed Microsoft Security Essentials and experienced this same thing. Brilliant post — thank you, solved my problem.
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!
Try to change de directory of TempFiles of mysql
my.ini
key -> [tmpdir] and set another directory c:\windows\temp.
For newest news you have to pay a visit internet and on the
web I found this web site as a best site for newest updates.
Look into my weblog :: Byron