Sunday, 14 August 2011

Problem: error Can't create/write to file 'C:\WINDOWS\TEMP\#sql_668_0.MYD' (Errcode: 17) in MySQL database server.

The problem is related to temporary folder MySQL uses.  

1)      Check the temporary folder MySQL is using the command

mysql> show variables like 'tmpdir';

This will show the temporary folder MySQL is using.
2)      Check whether MYSQL has appropriate permission to the temporary folder or the partition has free space.
3)      Check is Antivirus software is blocking any access to that folder.
4)      First time when this error came I changed the temporary folder used by MySQL and the problem got solved at that time.
Steps to change the temporary folder:
i)  Create a folder suppose C:\temp and give appropriate permission to that folder to MySQL.
ii) Open MySQL configuration file. It is inside the MySQL installation folder. The name of config  file is my.ini in Windows. In Server Section i.e. in [mysqld] section enter the following line:
tmpdir = “C:/temp”
iii) Restart MySQL.

But after some days the same problem came again.
This time the permission was not the issue, so I thought that the problem is related to Antivirus software McAfee we are using. One solution may be is to stop the Antivirus but this would be serious security threat. So I excluded MySQL’s temporary folder from McAfee antivirus scan. I followed the following steps to do that:

1)  Right click on the McAfee icon on the system tray and select “On-Access Scan Properties”

       2) Select All Processes on the right hand side.

       3) Go to the Detection tab and click on the Exclusions button

     4) We are going to add MySQL’s temporary folder to the Exclusions list of McAfee. Click on Add button in the Set Exclusion window

5) Select By name/location (can include wildcards or * or ?): and to locate MySQL’s temporary directory click on Browse button


       6) Browse and select the MySQL’s temporary folder, in our case it is c:\Temp

        7) Now click OK and set the Exclusion and click on Apply to save the settings.

জয় আই অসম,
প্রণব শর্মা