Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've been beating my head against this one for a few days now. We have a two weeks' worth of daily backup snapshots of our production mysql data dir (i.e. the actual binary files, not sql dumps) and I need to restore a table from one of the backups so we can compare it to what we have now.

So I created a dummy schema directory and extracted the relevant table files (.MYI, .MYD and .frm) and restarted mysql. It shows up, I can "show tables", but if I try to interact with it in any way ("desc tablename", "select...", etc) I get:

Can't find file: './schema_name/table_name.frm' (errno: 13)

[ed: real names sanitized]

Errno 13 is permissions so I double-checked everything. The directory and files have the same owner and group (mysql:mysql) as all the other schemas. They also have the same perms (700 on the dir, 660 on the files). Looking at "ls -n" the uids also match up exactly.

Most recently I have tried doing a full extract of a different backup and then linking it into the mysql data dir (there's not enough room on that volume to extract the full thing) and I get the same error. I've also tried pointing mysql's data dir in my.cnf to the dir holding the backup and restarting. It built out the mysql tables that needed to be there but I still got the same error.

The only thing I can find through googling is people who had this problem because of actual ownership or permission errors, which does not seem to be the case for me. I've also found some remark in passing about a UMASK env variable that can result in this error but I think that has to do with fresh installs, which this is not.

share|improve this question
    
Note: I've tried chmodding everything to 777 –  c0bra Jun 5 '09 at 16:55
    
Also "myisamchk -r" didn't fix anything and "repair table" in mysql results in the same error. –  c0bra Jun 5 '09 at 16:57

5 Answers 5

up vote 3 down vote accepted

Are you running something like selinux or some other similar package? I'd suggest disabling that (or modifying the security policies) to see if something there is preventing MySQL from being able to access the files.

[edit] If so, check syslog to see if selinux is blocking mysql from doing anything. If it is SELinux, I'm told this may disable it so that you might test this theory.

/usr/sbin/setenforce Permissive
share|improve this answer
    
Yup, it was selinux. I found it just before I read your message. –  c0bra Jun 5 '09 at 19:12
    
"restorecon -R -v /var/lib/mysql/tablename" looks to have fixed it –  c0bra Jun 5 '09 at 19:16

Check what is the user name configured in /etc/my.cnf

[mysqld]
bind-address=<IP Address>
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=<username>

Could be root or which you have configured. I modified to root and it started working for me.

Then start mysql

service mysqld stop

service mysqld start
share|improve this answer

I recently had this issue, and I solved this by changing the database directory permission of everyone of the database directory located into /var/lib/mysql like this:

PWD: /var/lib/mysql
chmod u+x schema_db1
chmod u+x schema_db2
chmod u+x schema_db3
chmod u+x schema_db4

Restart the MySQL engine and is working again! I hope this could work this for you.

share|improve this answer

When I've run into this problem before, it was because the files weren't owned by the mysql user, regardless of their permissions. chmod 777 is not really something you want to be running on a data directory edited by a server anyway.

share|improve this answer
    
Sure, it was just a temporary check since nothing else was working. Also, the files are owned by the mysql user. –  c0bra Jun 5 '09 at 18:59

strace is your friend in this instance. Find the process id of your mysql then run:

strace -efile -f -o /tmp/mysql.log -p $pid

In another window do something that causes the error. You can then press ctrl-c to kill strace. If you look in /tmp/mysql.log you should see what caused the problem.

As an aside, I really recommend you don't rely on copying the binary datafiles. SQL dumps are a lot more reliable and flexible. Also, it would appear you're using MyISAM. I'd really recommend against using those unless you don't care about the data in them. InnoDB has so many advantages over MyISAM.

share|improve this answer
    
31285 open("./schemaname/tablename.frm", O_RDONLY|O_LARGEFILE) = -1 EACCES (Permission denied) Other than a Fcntl flag I haven't seen before, that doesn't tell me much. Also this is a "legacy" app that is supposed to be replaced "any second now", so my options for modifications/upgrades are non-existant. –  c0bra Jun 5 '09 at 17:03
    
And what was that process's current working directory? Probably not what you expected. On Linux, for example, /proc/pid/cwd will tell you. –  derobert Jun 5 '09 at 17:18
    
The cwd is /var/lib/mysql, which would be correct. Besides, wouldn't that affect any other db operation? I can use the other schemas just fine, it's only this one that's the problem. –  c0bra Jun 5 '09 at 19:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.