Mysql错误1005 (HY000):无法创建表'tmp' (errno: 13)

I'm Running Mysql on ubuntu 9.10, the process of Mysql is running as root, I'm using root account when logging to Mysql, which I gave all privileges, I'm using my own db(not mysql), I can create a table, but when i try to create Temporary table i get this error:

我在ubuntu 9.10上运行Mysql, Mysql进程以root身份运行，我登录到Mysql时使用root账号，我给了所有的特权，我使用自己的db(不是Mysql)，我可以创建一个表，但是当我尝试创建临时表时，我得到了这个错误:

ERROR 1005 (HY000): Can't create table 'tmp' (errno: 13)

错误1005 (HY000):无法创建表'tmp' (errno: 13)

For this query:

对于这个查询:

CREATE TEMPORARY TABLE tmp (id int);

创建临时表tmp (id int);

I've plenty of space in my hard drive, all permissions are granted(also var/lib/mysql have mysql permissions).

我的硬盘上有足够的空间，所有的权限都被授予(同时var/lib/mysql也有mysql权限)。

Any idea? Thanks, Koby

任何想法?谢谢,·库柏

7 个解决方案

#1

I had the same issue a couple of weeks ago. The database folder on the filesystem was owned by the wrong user. A simple chown -R mysql:mysql /var/lib/mysql/database_name did the trick!

几周前我也遇到过同样的问题。文件系统上的数据库文件夹属于错误的用户。一个简单的chown -R mysql:mysql /var/lib/mysql/database_name成功了!

Everything's explained here: http://www.dinosources.eu/2010/10/mysql-cant-create-table (it's italian, but it's pretty clear)

这里解释了一切:http://www.dinosources./2010/10/myl -cant-create-table(这是意大利语，但很清楚)

Cheers

干杯

#2

Well... in /etc/mysql/my.cnf there's the "tmp" folder for use which is /tmp (from root) as default.. and do not have mysql privileges. chmod 0777 /tmp will do the trick

嗯…在/etc/mysql/my.cnf中，有一个“tmp”文件夹供使用，该文件夹默认为/tmp(来自root)。并且没有mysql特权。chmod 0777 /tmp就可以了

#3

I had the error above with correct permissions on /tmp, correct context and sufficient disk space on Fedora 16.

上面的错误是关于/tmp的正确权限、正确上下文和Fedora 16的足够磁盘空间。

After a day of ripping my hair out, I tracked the problem down to a setting in systemd configuration for the MySQL service.

经过一天的努力，我终于找到了MySQL服务的systemd配置。

In /etc/systemd/system/multi-user.target.wants/mysqld.service check for if there is a setting PrivateTmp=true. This change forces MySQL to use a /tmp/systemd-namespace-XXXXX subdirectory instead of putting files directly into /tmp. Apparently MySQL does not like that and fail with a permission denied error (13) for any query that required the creation of a temp file.

在/etc/systemd/system/multi-user.target.wants / mysqld。服务检查是否有设置PrivateTmp=true。这一改变迫使MySQL使用/tmp/system -namespace- xxxxx子目录，而不是直接将文件放入/tmp中。显然，MySQL不喜欢这样，对于任何需要创建临时文件的查询，都会出现拒绝错误(13)。

You can override this setting as follows:

您可以按以下方式重写此设置:

cat >> /etc/systemd/system/mysqld.service << END_CONFIG
.include /lib/systemd/system/mysqld.service
[Service]
PrivateTmp=false
END_CONFIG

Then reload configuration by running: systemctl daemon-reload and restart MySQL.

然后通过运行:systemctl daemon-reload重新加载并重新启动MySQL来重新加载配置。

#4

do you set the attribute MaxNoOfOrderedIndexes in your config.ini? It’s default value is 128,so if you have lots of tables to create,last of them cannot be created. see: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-maxnooforderedindexes

是否在config.ini中设置了MaxNoOfOrderedIndexes属性?它的默认值是128，因此如果您有很多表要创建，那么它们的最后一个无法创建。见:http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html ndbparam-ndbd-maxnooforderedindexes

#5

I had the same issue today on my Amazon Red Hat instance. I was able to perform neither mysql decribe (from mysql shell) nor execute mysqldump. To solve this I tried the most obvious solution:

今天在我的Amazon Red Hat实例中遇到了同样的问题。我既不能执行mysql decribe(来自mysql shell)，也不能执行mysqldump。为了解决这个问题，我尝试了最明显的方法:

# chown root:root /tmp -v
# chmod 1777 /tmp -v
# /etc/init.d/mysqld restart

But this didn't help. In the /var/log/mysqld.log I still saw:

但这并没有帮助。在/var/log/mysqld.日志我还看到:

141022 10:23:35  InnoDB: Error: unable to create temporary file; errno: 13
141022 10:23:35 [ERROR] Plugin 'InnoDB' init function returned error.
141022 10:23:35 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

It came out that it was SELinux which didn't allow MySQL daemon to write to /tmp. Therefore, what I did was to:

结果是SELinux不允许MySQL守护进程写入/tmp。因此，我所做的就是:

# getenforce 
Enforcing

To verify if SELinux is running in enforcing mode (you can read more about this here). The fast and quick solution for this was to switch to SELinux permissive mode:

为了验证SELinux是否在强制模式下运行(您可以在这里阅读更多相关内容)。快速而快速的解决方法是切换到SELinux许可模式:

# setenforce 0
# getenforce 
Permissive
# /etc/init.d/mysqld restart

The above solved my problem.

以上解决了我的问题。

Please note that, if you are working on hardened production, you should be very careful when you are switching from enforcing to permissive. please also note that this specific setting will be reset after the reboot.

请注意，如果您正在进行强化生产，那么当您从强制转换为许可时，您应该非常小心。请注意，此特定设置将在重新启动后重置。

#6

I was having these (errno: 13) errors and only figured them out after looking into /var/log/syslog, so my advice is this:

我有这些错误(errno: 13)，只有在研究了/var/log/syslog之后才发现它们，所以我的建议是:

tail -f /var/log/syslog

See if that has anything to do with database files after you try to access the database, in my case it was

在尝试访问数据库之后，看看这是否与数据库文件有关，以我为例

apparmor=[DENIED]

Which means you need to deal with apparmor, but in your case it might be something else.

这就意味着你需要和幻影移形器打交道，但在你的情况下，它可能是别的什么东西。

#7

with my case:

我的情况:

    # semanage fcontext -a -t mysqld_db_t "/datadir(/.*)?"
    # restorecon -Rv /datadir
    #chcon -R -t mysqld_db_t /datadir

solved my problem.

解决了我的问题。

#1