14

How To Fix Intermittent MySQL Errcode 13 Errors On Windows


Posted by Artem Russakovskii on January 5th, 2010 in Databases, MySQL, PHP, Programming

Updated: September 16th, 2012

13

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_posts

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:

Ignore Windows Temp dir in Microsoft Security Essentials

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:

Ignore *.MYI and *.MYD in Microsoft Security Essentials

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.



Share
  • ColdMan

    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.

    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.

  • 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.

  • Ezekiel Victor

    I just installed Microsoft Security Essentials and experienced this same thing. Brilliant post — thank you, solved my problem.

  • Allen A

    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!