Workaround for "Can ' t create/write to file xxx (errcode:13)" under MySQL

Recently encountered a MySQL can not create temporary file errors, the resolution process to learn some knowledge, here to share, I hope to encounter similar problems with the students help ~

About MySQL temp file:

1, MySQL uses the environment variable Tmpdir value as the path name of the directory where the temporary files are saved. If Tmpdir,mysql is not set, the default value for the system is used, usually/tmp,/VAR/TMP, or/usr/tmp.
2. mysql will create all temporary files in an implied manner. This ensures that all temporary files are deleted when the mysqld is aborted.
3. When sorting (order by or group by), MySQL usually uses 1 or more temporary files.
4. For some select queries, MySQL also creates temporary SQL tables. They are not hidden tables and have names in sql_* form.

Analyze the problem:

If some queries encounter a "Can ' t create/write to file xxx (errcode:13)" Type of error, it means that MySQL cannot create temporary files for the result set in the temporary directory.
Google query knows that the following three situations may cause this problem:

①,%tmpdir% folder permissions are not sufficient.
②,%tmpdir% folder full of disk, file write not go in, empty TEMP folder;
③, third party restrictions (such as the limitations of antivirus software)

Workaround:

Method One, look for "[mysqld]" in the My.inip configuration file, and add a row below it:
tmpdir=% you want to specify the MySQL temp file directory (e.g. D:temp)%
Method Two, if the machine installed anti-virus software, you can try to exclude the temporary file scan.
360 Antivirus (has been tested):
Set->>> Whitelist settings->>> add directory->>>%tmpdir%–>>> OK.
Mccafés (online information, not personally tested):
Open the McAfee VirusScan console-> Press the Access Scanner-> property-> All Processes-> detections-> exclusions-> add%tmpdir%, excluding the time to select write on read.

Resources:

Can ' t create/write to file ... Workaround--hi.baidu.com/%d3%b2%ba%ba%d6%dd%b3%a4/blog/item/42166e9bccbb97bfc9eaf4ca.html

a.4.4. mysql where to store the temporary files-dev.mysql.com/doc/refman/5.1/zh/problems.html#temporary-files

a.2.12. Unable to create file/write file-dev.mysql.com/doc/refman/5.1/zh/problems.html#cannot-create

MySQL More questions-dev.mysql.com/doc/refman/5.1/zh/problems.html

Modify method

Here are two ways to fix tables for MySQL.

1. Myisamchk

The MySQL server must be temporarily stopped using MYISAMCHK. For example, we need to overhaul the blog database. Perform the following actions:

# service MySQL stop (stop MySQL)
# Myisamchk-r/datawhere/blog/*myi
# service MySQL start myisamchk automatically checks for and repairs index errors in the datasheet.

2. Mysqlcheck

With Mysqlcheck you do not need to stop MySQL for hot fixes. Operation Steps as follows: (Overhaul blog library)

# mysqlcheck-r-uroot-p Blog

Note that, whether it is Myisamchk or mysqlcheck, do not normally use the-F force fix, and the-f parameter will be deleted when a general fix cannot succeed
Partial error data to try to fix. Therefore, you should not use-f unless you have to.

If you are afraid to use the command problem, you can use phpMyAdmin Direct repair OH