| Bug #33114 | Can't create/write to file 'C:\WINDOWS\TEMP\#sql_aa0_0.MYD' (Errcode: 13) | ||
|---|---|---|---|
| Submitted: | 10 Dec 2007 13:27 | Modified: | 25 Aug 2009 15:20 |
| Reporter: | Olaf van der Spek (Basic Quality Contributor) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.22-rc-community | OS: | Windows (2003) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[10 Dec 2007 13:27]
Olaf van der Spek
[10 Dec 2007 13:29]
Olaf van der Spek
Oops, wrong table.
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(40) NOT NULL DEFAULT '',
`old_password` varchar(40) NOT NULL DEFAULT '',
`passhash` varchar(32) NOT NULL DEFAULT '',
`secret` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`email` varchar(80) NOT NULL DEFAULT '',
`status` enum('pending','confirmed') NOT NULL DEFAULT 'pending',
`added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_access` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_visit` int(11) DEFAULT NULL,
`editsecret` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`privacy` enum('low','normal','strong') DEFAULT 'low',
`stylesheet` int(10) DEFAULT '1',
`info` text,
`acceptpms` enum('yes','power','friends','no') DEFAULT 'yes',
`ip` varchar(15) NOT NULL DEFAULT '127.0.0.1',
`class` tinyint(3) unsigned NOT NULL DEFAULT '1',
`avatar` varchar(100) NOT NULL DEFAULT '',
`uploaded` bigint(20) unsigned NOT NULL DEFAULT '0',
`downloaded` bigint(20) unsigned NOT NULL DEFAULT '0',
`title` varchar(100) NOT NULL DEFAULT '',
`country` int(10) unsigned NOT NULL DEFAULT '74',
`notifs` varchar(100) NOT NULL DEFAULT '',
`modcomment` longtext NOT NULL,
`enabled` enum('yes','no') NOT NULL DEFAULT 'yes',
`avatars` enum('yes','no') NOT NULL DEFAULT 'yes',
`donor` enum('yes','no') NOT NULL DEFAULT 'no',
`warned` enum('yes','no') NOT NULL DEFAULT 'no',
`warneduntil` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`torrentsperpage` int(3) unsigned DEFAULT '25',
`topicsperpage` int(3) unsigned NOT NULL DEFAULT '0',
`postsperpage` int(3) unsigned NOT NULL DEFAULT '0',
`deletepms` enum('yes','no') NOT NULL DEFAULT 'yes',
`savepms` enum('yes','no') NOT NULL DEFAULT 'no',
`anonymous` enum('yes','no') NOT NULL DEFAULT 'no',
`gender` enum('N/A','Male','Female') NOT NULL DEFAULT 'N/A',
`kade` varchar(255) NOT NULL DEFAULT '',
`supportfor` varchar(100) NOT NULL,
`support` enum('yes','no') NOT NULL DEFAULT 'no',
`parked` enum('yes','no') NOT NULL DEFAULT 'no',
`completed_t` varchar(255) NOT NULL DEFAULT '',
`connectpos` enum('enabled','disabled') NOT NULL DEFAULT 'enabled',
`signatures` enum('yes','no') NOT NULL DEFAULT 'yes',
`signature` varchar(225) NOT NULL DEFAULT '',
`uploadpos` enum('yes','no') NOT NULL DEFAULT 'yes',
`forumpost` enum('yes','no') NOT NULL DEFAULT 'yes',
`downloadpos` enum('yes','no') NOT NULL DEFAULT 'yes',
`client` varchar(255) NOT NULL DEFAULT '6',
`invited_by` int(10) NOT NULL DEFAULT '0',
`invites` smallint(4) NOT NULL DEFAULT '0',
`invitedate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`showsmiley` enum('yes','no') NOT NULL DEFAULT 'yes',
`last_browse` int(11) NOT NULL DEFAULT '0',
`ups` varchar(255) NOT NULL DEFAULT '',
`downs` varchar(255) NOT NULL DEFAULT '',
`leechwarn` enum('yes','no') NOT NULL DEFAULT 'no',
`last_check` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`maxtorrents` int(3) NOT NULL DEFAULT '5',
`passkey` varchar(32) NOT NULL DEFAULT '',
`invitees` varchar(100) NOT NULL DEFAULT '',
`forum_access` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`forumsor` enum('DESC','ASC') NOT NULL DEFAULT 'ASC',
`avatarkep` varchar(100) NOT NULL DEFAULT '',
`lastinvite` bigint(20) unsigned NOT NULL DEFAULT '0',
`seedbonus` bigint(20) NOT NULL DEFAULT '0',
`name` varchar(40) NOT NULL DEFAULT '',
`speedup` int(3) unsigned NOT NULL DEFAULT '0',
`speeddown` int(3) unsigned NOT NULL DEFAULT '0',
`isp` varchar(100) NOT NULL DEFAULT '',
`pass` blob NOT NULL,
`fid_end` int(11) NOT NULL DEFAULT '0',
`peers_limit` int(11) NOT NULL DEFAULT '0',
`torrents_limit` int(11) NOT NULL DEFAULT '0',
`torrent_pass` varchar(32) NOT NULL DEFAULT '',
`torrent_pass_secret` bigint(20) NOT NULL DEFAULT '0',
`upmeasure` varchar(10) NOT NULL DEFAULT '1',
`downmeasure` varchar(10) NOT NULL DEFAULT '1',
`downloaded2` bigint(20) NOT NULL DEFAULT '0',
`staffmenu` enum('yes','no') NOT NULL DEFAULT 'no',
`catset` enum('default','dark','old','original') DEFAULT 'dark',
`contract` enum('yes','no') NOT NULL DEFAULT 'no',
`fast` enum('yes','no') NOT NULL DEFAULT 'no',
`county` varchar(255) NOT NULL DEFAULT '0',
`commentpm` enum('yes','no') NOT NULL DEFAULT 'no',
`sendinvite` enum('yes','no') NOT NULL DEFAULT 'yes',
`can_leech` tinyint(4) NOT NULL DEFAULT '1',
`wait_time` int(11) NOT NULL,
`voted` enum('yes','no') DEFAULT 'no',
`invites_uj` bigint(20) NOT NULL DEFAULT '0',
`dollar` enum('yes','no') NOT NULL DEFAULT 'no',
`torrent_pass_version` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `status_added` (`status`,`added`),
KEY `ip` (`ip`),
KEY `uploaded` (`uploaded`),
KEY `downloaded` (`downloaded`),
KEY `country` (`country`),
KEY `last_access` (`last_access`),
KEY `enabled` (`enabled`),
KEY `warned` (`warned`),
KEY `email` (`email`),
KEY `torrrent_pass` (`torrent_pass`),
KEY `fast` (`fast`),
KEY `class` (`class`),
KEY `idenableds` (`id`,`enabled`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=86345 ;
[10 Dec 2007 13:55]
MySQL Verification Team
Error 13 means access denied.
[10 Dec 2007 13:57]
Olaf van der Spek
In that case I'm wondering why it only happens sometimes. Aren't those files normally created in the MySQL data dir anyway?
[10 Dec 2007 16:17]
Peter Laursen
some related issues and solutions were reported here: http://bugs.mysql.com/bug.php?id=9551 ... and we at Webyog have a report that this happened in one occasion (but I do not know if it happened randomly in this situation) after the configuration file accidentially got deleted! Maybe a problem with resolving the path to the TMP dir in this particular situation?
[10 Dec 2007 16:19]
Peter Laursen
btw .. this report that we had, was also a with server running on Win 2003!
[10 Dec 2007 16:57]
Sveta Smirnova
Thank you for the report. It looks like old reported problem. Please provide output of SHOW VARIABLES LIKE 'tmpdir'. Also check if PATH returned by this command is exists and writable for user MySQL server running as.
[10 Dec 2007 17:01]
Olaf van der Spek
mysql> SHOW VARIABLES LIKE 'tmpdir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tmpdir | C:\WINDOWS\TEMP | +---------------+-----------------+ 1 row in set (0.00 sec) MySQLd runs as System, so that path should be writeable.
[31 Jan 2008 23:39]
Sveta Smirnova
Thank you for the feedback. Please right-click directory C:\WINDOWS\TEMP, find "Permissions" tab and check permissons for sure. Screenshot will be appreciated.
[31 Jan 2008 23:45]
Olaf van der Spek
Temp Properties
Attachment: Temp Properties.png (image/png, text), 8.32 KiB.
[31 Jan 2008 23:45]
Olaf van der Spek
Screenshot uploaded...
[26 Mar 2008 13:47]
Jim Baker
This is happening to me, and I'm not creating any complex tables or SQL at all (as far as I know). What IS interesting is that it does NOT happen on my production server (windows Server 2003), but does happen on my development server (Windows XP). I can't figure out what the difference is.
[21 May 2008 15:15]
John Nutting
This problem happens on my Windows Vista instance running MySQL 5.0.45-community-nt. It happens intermittently, and seems to only happen on queries that have a DISTINCT clause. Is there any update on whether there is a patch, or an upgrade path that fixes this problem?
[21 May 2008 15:31]
MySQL Verification Team
Mr. John Nutting thank you for the comment. Are you able to isolate a test case (table structure, data and queries) which provoke the behavior reported?. If yes could you please provide it using private attach files is needed?. Thanks in advance.
[11 Jun 2008 2:22]
MySQL Verification Team
I could not repeat this issue, something is missed here to be repeatable ( I tried with queries with DISTINCT clause how was commented by Mr. Nutting and not luck). Could you please try with the newer released version and verify if the TEMP directory is clean of temporary files created by the MySQL server. Thanks in advance.
[11 Jun 2008 12:57]
John Nutting
Thanks for the update. Unfortunately I have not been able to isolated a test case. Each time I run my tests, it seems to happen only intermittently, and never on the same query twice. However, I suspect that it has something to do with multiple select queries with distinct clauses being run in rapid succession. I have currently worked around it by replacing the distinct clauses with group by clauses. We will be upgrading eventually, and at that point I will retest with distinct clauses to see if the problem is fixed. Thanks again.
[18 Jun 2008 19:45]
Valeriy Kravchuk
Please, try to check with a newer version, 5.1.25, and inform about the results.
[18 Jun 2008 20:10]
Olaf van der Spek
Is there any reason to believe it has been fixed? I can't test for about a month.
[20 Jun 2008 14:47]
Susanne Ebrecht
MySQL version 5.1 is not our stable release. It's still "release candidate". Our stable release at the moment is MySQL 5.0.51b Of course there will still happen lots of changes in MySQL 5.1. And we can't reproduce your problem. So we need to know from you if you will still have problems by using the newest version. Please also monitor the load of your system. It could be that Windows have a load problem here.
[23 Jun 2008 17:17]
Aaron Burke
I'm experiencing the exact same problem. I have all the symptoms previously described/hypothesized by various posters: * Only happens on my development machine (XP SP2), not on the production server (also XP SP2) * Happens consistently on one page which executes several queries, and appears to crash on the only query using a DISTINCT clause -- other queries on that page complete successfully. * "Goes away" if I restart the service, but comes back a few minutes later if I try to execute another DISTINCT query (either on the same page or other pages) * I'm running 5.0.51b However, I have a completely reproducible test case: Below is the table schema and the crashing query: CREATE TABLE `unit_history` ( `db_product_id` int(6) NOT NULL default '0' COMMENT 'NOT Dynon Product ID', `Comment_Source` varchar(25) NOT NULL default '' COMMENT 'IE Initials of commenter or Chamber1 or Chamber 2 . . .', `Serial_Num` varchar(10) default NULL, `rma_num` int(10) default NULL, `cal_id` int(10) default NULL, `Comment` blob, `id` bigint(20) unsigned NOT NULL auto_increment, `ncr_num` int(10) default NULL, `show_level` tinyint(3) unsigned NOT NULL default '0', `log_time` datetime NOT NULL default '0000-00-00 00:00:00', `action` varchar(45) default NULL, `other_name` varchar(45) default NULL, `shipment_id` bigint(20) unsigned default NULL, PRIMARY KEY (`id`), KEY `db_product_id` (`db_product_id`,`Serial_Num`) ) ENGINE=MyISAM AUTO_INCREMENT=142049 DEFAULT CHARSET=latin1 CREATE TABLE `shipments` ( `rma_num` int(10) unsigned default NULL, `db_product_id` int(10) NOT NULL default '0' COMMENT 'NOT Dynon Product ID', `serial_num` varchar(10) NOT NULL default '0' COMMENT 'Filled in when the RMA comes in', `recv_date` datetime default NULL, `ship_date` datetime default NULL, `other_name` varchar(45) NOT NULL default '', `received_condition` varchar(100) default NULL, `received_speed` varchar(45) default NULL, `tracking_num` varchar(45) default NULL, `id` int(10) unsigned NOT NULL auto_increment, `courier` varchar(45) default NULL, `newship_cust` varchar(45) default NULL, `invoice_number` varchar(45) default NULL, `po_number` varchar(85) default NULL, PRIMARY KEY (`id`), KEY `auto_id` (`id`), KEY `db_product_id` (`db_product_id`,`serial_num`), KEY `rma_num` (`rma_num`) ) ENGINE=MyISAM AUTO_INCREMENT=18040 DEFAULT CHARSET=latin1 SELECT DISTINCT unit_history.id , DATE_FORMAT( unit_history.log_time, '%m.%d.%Y %r' ) AS `cal_time` , unit_history.Comment_Source , unit_history.action , unit_history.Comment , unit_history.show_level , unit_history.cal_id, unit_history.log_time AS log FROM `shipments` RIGHT JOIN unit_history ON shipments.serial_num = unit_history.Serial_Num AND shipments.db_product_id = unit_history.db_product_id WHERE unit_history.cal_id IS NOT NULL AND shipments.rma_num = '3181' UNION SELECT `id` , DATE_FORMAT( `log_time` , '%m.%d.%Y %r' ) AS `cal_time` , `Comment_Source` , `action` , `Comment` , `show_level` , `cal_id`, log_time AS log FROM `unit_history` WHERE `rma_num` = '3181' ORDER BY `log` desc Error: SQL Error: Can't create/write to file 'C:\WINDOWS\TEMP\#sql_75c_0.MYD' (Errcode: 17) Other requested information from other posts: SHOW VARIABLES LIKE 'tmpdir' C:\WINDOWS\TEMP\ mysqld-nt.exe is running as SYSTEM and has full access to the TEMP directory. For what it's worth, this version of MySQL came packaged with WampServer 2.0c, although the same error was happening before I upgraded to the latest version and was running WAMP (project's previous name) 1.6.4b. I ran this setup for several months with no issue. I think there MAY be some issue of compatibility with MS SQL Server (Express, at least; included with Visual Studio). Although I do not have it running -- the primary service and VSS Writer are set to Manual (and have not started), the AD Helper and Browser are Disabled -- I first noticed the issue when I was working on a separate project utilizing ASP and MS SQL. It *may* be a coincidence however, but I'd like to know if the others experiencing this problem have a similar software setup.
[23 Jun 2008 18:24]
John Nutting
>I think there MAY be some issue of compatibility with MS SQL Server (Express, at least; >included with Visual Studio). Although I do not have it running -- the primary service and >VSS Writer are set to Manual (and have not started), the AD Helper and Browser are >Disabled -- I first noticed the issue when I was working on a separate project utilizing >ASP and MS SQL. It *may* be a coincidence however, but I'd like to know if the others >experiencing this problem have a similar software setup. I do not have MS SQL installed on the machine where I was experiencing this problem.
[23 Jun 2008 18:50]
MySQL Verification Team
Hello Mr. Burke, Could you please provide the dump file to populate the 2 tables: unit_history and shipments uploading it as private?. Your my.ini would be nice to send too. Thanks in advance.
[23 Jun 2008 20:03]
Aaron Burke
I've attached them as private files. I chose a smaller data set for the sample (otherwise it would've been a 19M upload as opposed to 4K), so use this query: SELECT DISTINCT unit_history.id , DATE_FORMAT( unit_history.log_time, '%m.%d.%Y %r' ) AS `cal_time` , unit_history.Comment_Source , unit_history.action , unit_history.Comment , unit_history.show_level , unit_history.cal_id, unit_history.log_time AS log FROM `shipments` RIGHT JOIN unit_history ON shipments.serial_num = unit_history.Serial_Num AND shipments.db_product_id = unit_history.db_product_id WHERE unit_history.cal_id IS NOT NULL AND shipments.rma_num = '3001' UNION SELECT `id` , DATE_FORMAT( `log_time` , '%m.%d.%Y %r' ) AS `cal_time` , `Comment_Source` , `action` , `Comment` , `show_level` , `cal_id`, log_time AS log FROM `unit_history` WHERE `rma_num` = '3001' ORDER BY `log` desc; If the data set is not large enough or if the error does not reproduce, let me know and I'll come up with a larger one and re-post. I'm subscribed to email updates on this Bug and will be monitoring it closely.
[23 Jun 2008 20:51]
MySQL Verification Team
Thank you for feedback. I guess I need more data to populate the tables, so could you please provide the whole dump file uploading it into a zip file at: ftp://ftp.mysql.com/pub/mysql/upload Could you please comment here the file's name when done. Thanks in advance.
[23 Jun 2008 21:35]
Aaron Burke
File has finished uploading to the server. Filename: bug-data-33114.zip
[3 Jul 2008 15:55]
Jonathon Freeman
I just experienced this bug on my production server (Windows Server 2003 Enterprise) with MySQL 5.0.51a. The query that produced the problem is: SELECT DISTINCT(name) FROM product.load WHERE id NOT IN (SELECT loadid FROM product.loadindex WHERE modelid=72) AND id IN (3, 4) Other output: SHOW VARIABLES LIKE 'tmpdir'; C:\WINDOWS\TEMP I have not been able to reproduce the problem.
[3 Jul 2008 16:04]
Aaron Burke
@Jonathan: The next time it happens, try removing the DISTINCT clause to see if it succeeds or results in the same error.
[20 Jul 2008 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[4 Sep 2008 13:58]
Lindsay Vine
I believe I have found the solution. This was the original question: > Hi, > >> show columns from users like 'torrent_pass'; >> Can't create/write to file 'C:\WINDOWS\TEMP\#sql_aa0_0.MYD' (Errcode: 13) > > This happens sometimes, not always. The disk isn't full. > What does error 13 mean? The solution: Shut off the anti-virus software on the server.
[4 Sep 2008 14:16]
Jonathon Freeman
@Lindsay: I'm not sure that's the cause--in my case at least--because we don't have anti-virus software running.
[4 Sep 2008 20:31]
Olaf van der Spek
I don't have any AV. Besides, why would that be a solution?
[23 Sep 2008 14:59]
Harald Groven
I have had this problem several times on win development boxes. I got rid of it by shutting down the Anti-virus program. I tried to exclude *.myd and *.myi files from being accessed by the virus software (in my case McAfee) but that didn't work. This issue should be reported to the anti-virus companies so that they may make sure that both Mysql and AV can be running at the same time.
[10 Oct 2008 9:44]
Peter Arien
We had the same problem with following config/soft: Xampp 1.6.8 (MySQL 5.0.67) with qcodo (http://www.qcodo.com/) and McAfee antivirus running on Windows VISTA. Consistent 'Errcode 13' problems when qcodo did it's codegeneration. With every run it stopped on a simple 'DESCRIBE table' (always different tables). The problem disappeared after switching off McAfee antivirus. Thanks for the suggestion!
[10 Oct 2008 14:43]
John Nutting
I also had McAfee virus scanner running when the problem was happening on my machine.
[10 Oct 2008 14:44]
Olaf van der Spek
I don't have AV running. Did anyone report this issue to McAfee>
[19 Oct 2008 13:23]
Tobias Asplund
I don't have a windows machine to test, but couldn't this be circumvented by setting tmpdir internally to something else? ie, same as datadir?
[28 Nov 2008 16:32]
Sven Sandberg
cf BUG#39750, especially the commit comments from 20 Nov 9:37.
[22 Jan 2009 4:17]
James Masters
I had the same issue with the McAfee On-Access Scanner. To get around it I first redirected where the MySql temporary files go (as per http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html), put the following in your configuration file: [mysqld] tmpdir=C:/MySqlTmp Restart the mysqld service (Start->Administrative Tools->Services; then right-click MySql and restart). Then in McAfee virus scan console (I have Enterprise, so this may be different for other versions), I did the following: 1. Right click on McAfee icon 2. Choose VirusScan Enterprise 3. Choose VirusScan Console 4. A window should pop up (the console) 5. Double-click "OnAccess Scanner" item 6. Click on "Default Processes" icon on left side 7. Click on "Detection" tab 8. Press "Exclusions" button 9. Click "Add" and add your new C:\MySqlTmp as an exclusion Good luck... it worked for me.
[22 Jan 2009 13:07]
Peter Laursen
MySQL should get in dialogue with McAffee about this. I hope they did already! We have had a few situations where AV software blocked our installers. After reporting to the AV vendor it was fixed every time in max. 2 days with a new detections update.
[20 Aug 2009 12:02]
MySQL Verification Team
I continue not able to repeat on Vista Home Premium 64-bit and McAfee. Please test again with latest server and McAfee updates. Thanks in advance.
[23 Aug 2009 18:29]
Olaf van der Spek
Note that in my case no AV software was installed. I did drop this Windows system, so I can't try anything anymore.
[25 Aug 2009 16:32]
Aaron Burke
I did not have any anti-virus or anti-malware / other "shields" installed either. My work has migrated away from MySQL/web work for the time being though, and I have not experienced this error in some time.
[29 Aug 2011 22:40]
ann gallic
I was receiving this same error. I have Microsoft Security Essentials AV program running on my machine. I added *.myd and *.myi as excluded file types in Microsoft Security Essentials and the error stopped!
