Unable to modify a table

I'm having trouble with mysql. I can read and write, but now I want to add some fields to a table. I run this command: ALTER TABLE Pubs ADD COLUMN issue tinyint AFTER volume;

but get this error message: ERROR 7 (HY000): Error on rename of './user_acct/Pubs.MYI' to './user_acct/#sql2-cb0-76f2.MYI' (Errcode: 13)

I created thi开发者_运维知识库s table a few months ago, modified it a little bit, so it worked then. I can still update and insert items, but I just can't modify the table anymore.

Any help would be appreciated.

--Dave

You can use perror to undesrtand mysql error:

$ perror 13
OS error code  13:  Permission denied

Do the MySQL user on the system have write permission to the ./user_acct/ folder?

Only the access permission is the issue

Error:

mysql>  rename table BL_Backup.TMP_BL_transaction_02  to BL_Backup.BL_transaction_02;
ERROR 7 (HY000): Error on rename of './BL_Backup/TMP_BL_transaction_02.MYI' to './BL_Backup/BL_transaction_02.MYI' (Errcode: 13)

Solution

[root@s4 Db_Backup]# ll
total 52
drwxr-xr-x  2 root  root   4096 Sep 28 18:19 BL_Backup
drwx------  2 mysql mysql 49152 May 19 15:59 mauj_2010_2011

[root@s4 Db_Backup]# chown mysql:mysql BL_Backup

[root@s4 Db_Backup]# ll
total 52
drwxr-xr-x  2 mysql mysql  4096 Sep 28 18:19 BL_Backup
drwx------  2 mysql mysql 49152 May 19 15:59 mauj_2010_2011

mysql>  rename table TMP_BL_transaction_02  to BL_transaction_02;   
Query OK, 0 rows affected (0.00 sec)

I just ran into this. I was getting errcode 13 when I tried to do anything which would change the .frm of my older MyISAM tables, but no newer ones.

It was because the ubuntu update process (done a few weeks before) had somehow gotten mysql to check permissions on a file deep in /tmp which it didn't have persmissions to.

root root /tmp/upgrade-XX-X/var/.wh..wh.afs

I discovered this by using 'dmesg' and seeing the permission denied error.

[111111111.222222] type=1503 audit(1862.6:7):  operation="link" 
    pid=<> parent=1 profile="/usr/sbin/mysqld" requested_mask="::l" 
    denied_mask="::l" fsuid=<> ouid=<> 
    name="/tmp/upgrade-XX-X/var/lib/mysql/<dbname>" 
    name2="/tmp/upgrade-XX-X/var/.wh..wh.aufs"

And wondering why in the world mysql was trying to get information about this file?

chmod 777 on this .wh file didn't help, but chown mysql:mysql did.