5

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.

2
  • Note: I've tried chmodding everything to 777
    – c0bra
    Jun 5, 2009 at 16:55
  • Also "myisamchk -r" didn't fix anything and "repair table" in mysql results in the same error.
    – c0bra
    Jun 5, 2009 at 16:57

6 Answers 6

5

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
2
  • Yup, it was selinux. I found it just before I read your message.
    – c0bra
    Jun 5, 2009 at 19:12
  • "restorecon -R -v /var/lib/mysql/tablename" looks to have fixed it
    – c0bra
    Jun 5, 2009 at 19:16
2

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.

3
  • 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, 2009 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, 2009 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, 2009 at 19:02
1

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.

0

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.

1
  • Sure, it was just a temporary check since nothing else was working. Also, the files are owned by the mysql user.
    – c0bra
    Jun 5, 2009 at 18:59
0

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
0

I've had this error, SELinux was denying access to the files (they provided from another server which crashed - so they were 'unconfined_u:object_r:home_root_t') so I did this to restore correct context :

semanage fcontext -a -t mysqld_db_t -s system_u "/var/lib/mysql/<my dbname>(/.*)?"
restorecon -vFr /var/lib/mysql/<my dbname>

This fixed the problem :)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .