InnoDB:Error:unable to create temporary file; Errno:13

1. Version

1) Operating system

Cat/etc/issue
Cat/etc/issue
CentOS Release 6.6 (Final)
Kernel \ r \m

Cat/proc/version
Cat/proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) ( GCC) #1 SMP Wed Oct 04:27:16 UTC 2014

2 MySQL database version

MySQL--version

MySQL Ver 14.14 distrib 5.1.73, for Unknown-linux-gnu (x86_64) using ReadLine 5.1

2. Description of the problem

Today, a friend said he modified the MySQL database tmpdir parameters, restart the MySQL instance, the InnoDB table for the Optimize action report similar to the following error (before the modification can be performed optimize operation):

Optimize table test_1;
+-------------+----------+----------+-------------------------------+
| Table       | Op       | Msg_type | Msg_text                      |
+-------------+----------+----------+-------------------------------+
| test.test_1 | optimize | Error    | Unknown table engine ' InnoDB ' |
| test.test_1 | Optimize | Error    | Corrupt                       |
+-------------+----------+----------+-------------------------------+
2 rows in Set (0.00 sec)

# #此时对innodb表的操作如select, DML and other operations will be reported unknown table engine ' InnoDB ' wrong

3. Problem analysis

3.1 View the storage engine currently supported by the database

Mysql> show engines; +------------+---------+----------------------------------------------------------------+--------------+------+ ------------+
| Engine | Support | Comment | Transactions | XA |
savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+ ------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV Storage Engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables |         NO  | NO | NO | | Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive Storage Engine | NO | NO |
NO | +------------+---------+----------------------------------------------------------------+--------------+------+ ------------+ 7 rows in Set (0.00 sec)

# #我们看到此时数据库支持的存储引擎中已经没有了innodb (But the friend confirmed that the library was really supporting the InnoDB storage engine, and most of the libraries were InnoDB tables)

3.2 View Database Errorlog

Since the database is the InnoDB storage engine is normal, after the modification of the Tmpdir parameter restart instance found an exception, it must be related to this change. (Most of the directory permissions issues), we looked at the database errorlog, in the errorlog found the following error:

^g/usr/sbin/mysqld:can ' t create/write to file '/home/tmp_test/ibjokvit ' (errcode:13)
160511 16:46:58  : Error:unable to create temporary file; Errno:13
160511 16:46:58 [ERROR] Plugin ' InnoDB ' init function returned ERROR.
160511 16:46:58 [ERROR] Plugin ' InnoDB ' registration as a STORAGE ENGINE.

# #从errorlog中我们可以清楚的看到因为mysqld进程没有写新指定的tmpdir目录 (/home/tmp_test) permissions, InnoDB storage engine initialization failed. So you log in to the database. InnoDB table related Operations times unknown table engine ' InnoDB ' wrong

Note: If the same situation happens on MySQL 5.6, you can not start the MySQL instance, errorlog will report the following error:

^g/usr/local/mysql/bin/mysqld:can ' t create/write to file '/home/tmp_test/ibow1z7q ' (Errcode:13-permission denied) 
  2016-05-11 15:44:04 7f306b223720  InnoDB:Error:unable to create temporary file; errno:13
2016-05-11 15:44:04 2 432 [ERROR] Plugin ' InnoDB ' init function returned ERROR.
2016-05-11 15:44:04 2432 [ERROR] Plugin ' InnoDB ' registration as a STORAGE ENGINE.
2016-05-11 15:44:04 2432 [ERROR] unknown/unsupported storage engine:innodb 2016-05-11 15:44:04
2432 [ERROR] Aborting

4. The solution

Modify the Tmpdir directory permissions to 777, restart the MySQL instance, problem solving.