MySQL Issue After Restore Attempt

Submitted by PaliGap on Fri, 02/10/2012 - 09:30 Pro Licensee

Hi

I have tried to restore a backup from my CentOS 5 server. However it errored and now I cannot access the MySQL servers module. I get:

SQL select table_schema,table_name from information_schema.views where table_schema = ? failed : Can't create/write to file '/tmp/#sql_2256_0.MYI' (Errcode: 13)

In case this was an issue with my /tmp drive, I changed the tmp folder in my.cnf, but no better:

SQL select table_schema,table_name from information_schema.views where table_schema = ? failed : Can't create/write to file '/root/test/#sql_2256_0.MYI' (Errcode: 13)

Status: 
Closed (fixed)

Comments

Submitted by andreychek on Fri, 02/10/2012 - 09:38

Howdy -- MySQL's Error Code 13 suggests that there is a problem in writing to the temp directory.

What output do you get if you run these two commands:

mount
df -h

Submitted by PaliGap on Fri, 02/10/2012 - 10:15 Pro Licensee

Thanks - I've done some tests - it IS something wrong with my 'tmp' directory (must be 'too secure'!).

What fooled me was when it didn't work with changing MySQL tmp in my.cnf. But I guess that wasn't enough.

Submitted by JamieCameron on Fri, 02/10/2012 - 12:12

Changing to a directory under /root wouldn't work, as MySQL writes as the mysql user, who wouldn't have permissions to that directory.

Submitted by Locutus on Fri, 02/10/2012 - 13:34

Maybe wrong permissions on the /tmp directory? Usually it is owned by root:root and world-writable with sticky bit (1777).

Also note that when you mount a filesystem, the directory where it is mounted gets a different set of permissions than the directory without active mount. I.e. when setting permissions, you need to do so AFTER mounting a filesystem. I fell for that a few times when putting /tmp on a separate FS. :)

Submitted by PaliGap on Sat, 02/11/2012 - 05:14 Pro Licensee

Yes! That's it - needed chmod 1777 /tmp

Thanks Locutus.

Submitted by Issues on Sat, 02/25/2012 - 11:17

Automatically closed -- issue fixed for 2 weeks with no activity.