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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.