Solution to the "Can't create/write to file xxx (Errcode: 13)" error in MySQL

This article analyzes and introduces the cause and solution of the "Can't create/write to file xxx (Errcode: 13)" error prompt in MySQL.

Recently, I encountered a problem where MySQL could not create temporary files. I learned some knowledge about the solution. Here, I would like to share with you some help for those who encountered similar problems ~

About MySQL temporary files:

1. MySQL uses the value of the environmental variable TMPDIR as the path name of the directory for saving the temporary file. If TMPDIR is not set, MySQL uses the default value of the system, usually/tmp,/var/tmp, or/usr/tmp.
2. MySQL creates all temporary files implicitly. This ensures that all temporary files are deleted when mysqld is aborted.
3. When sorting (order by or group by), MySQL usually uses one or more temporary files.
4. For some SELECT queries, MySQL will also create temporary SQL tables. They are not implicit tables and have SQL _ * Format names.

Analyze the problem:

If some queries encounter a "Can't create/write to file xxx (Errcode: 13)" type error, it means that MySQL cannot create a temporary file for the result set in the temporary directory.
Google queries may cause the following three problems:

①. % TMPDIR % the folder has insufficient permissions.
② % TMPDIR % the folder disk is full, the file cannot be written, and the TEMP folder is cleared;
③ Third-party restrictions (such as anti-virus software restrictions)

Solution:

Method 1: search for "[mysqld]" in the my. inip configuration file and add a line under it:
Tmpdir = % the temporary MySQL file directory you want to specify (for example, D: temp) %
Method 2: If the computer is installed with anti-virus software, try to exclude the scanning of temporary files.
360 anti-virus (tested ):
Set-> whitelist settings-> Add directory-> % TMPDIR %-> OK.
Coffee (online information, not tested in person ):
Open the McAfee VirusScan console-> scan by Access Program-> properties-> all processes-> check items-> exclude items-> Add % TMPDIR %, and select the read and write time as the exclusion time.

Resource:

Can't create/write to file... Solution

A.4.4. where does MySQL store temporary files? -dev.mysql.com/doc/refman/5.1/zh/problems.html?temporary-files

A.2.12. file creation/writing failure -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

Modification Method

The following describes two ways to restore a MySQL table.

1. myisamchk

To use myisamchk, you must temporarily stop the MySQL server. For example, we want to repair the blog database. Perform the following operations:

# Service mysql stop (stop MySQL)
# Myisamchk-r/datawhere/blog/* MYI
# Service mysql start myisamchk automatically checks and fixes index errors in the data table.

2. mysqlcheck

You can use mysqlcheck to perform hotfix without stopping MySQL. The procedure is as follows: (repair the blog Library)

# Mysqlcheck-r-uroot-p blog

Note: Do not use-f to forcibly fix myisamchk or mysqlcheck. The-f parameter will be deleted when the fix fails.
Some error data to try to fix. Therefore, do not use-f unless necessary.

If you are afraid of a problem with the command, you can use phpmyadmin to fix it directly.