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

Question

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:

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

For this query:

CREATE TEMPORARY TABLE tmp (id int);

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

Any idea? Thanks, Koby

Nakada

Running the command perror 13 from the command line would tell you what that error number means. Error Code 13 is "Permission Denied" on Linux. – Starshaped

Answer 1

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

Nakada

Answer 2

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!

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

Cheers

Spitfire

This worked for me. I copied the directories from another drive and they had the wrong owner. – Dario

If you are on mac, try sudo chown -R mysql:mysql /usr/local/mysql/data/my_database – Farber

Answer 3

I had the error above with correct permissions on /tmp, correct context and sufficient disk space on 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.

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.

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.

Facelifting

This solution is as true today as it was last year! Nicely done. mysqld still does not like privatetmp. – Passim

Answer 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

Resident

Answer 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:

# 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:

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:

# 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:

# 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.

Bangka

Answer 6

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

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.

Cayuse

Answer 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.

Silicious

Answer 8

If installed PhpMyAdmin with XAMPP on Linux, user can be set in this path:

sudo chown -R mysql:mysql /opt/lampp/var/mysql/my_database

Rase