Mysql ERROR 1005 (HY000): Can't create table &

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

Answer 1

with my case:

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

solved my problem.

Answer 2

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.

Answer 3

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

Answer 4

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.

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.

Answer 6

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