MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there

Question

I want to copy the database tables from my production server to a local test machine so I can perform test om (copies of) the real data.

I stopped mysql and deleted all the frm, MYD and MYI files. Starting mysql here and querying show tables gives an empty result set. I then shut down mysql and copied all the frm, MYD and MYI files from the server. When starting mysql "show tables" shows the tables as expected but trying to query them I get the error message

ERROR 1017 (HY000): Can't find file: './WhateverTableIQuery.frm' (errno: 13)

But the WhateverTableIQuery.frm file is on the disc and is identical to the one on the server.

Any ideas about what might be the problem?

Answer 1

I'd suggest giving two things a try:

1. Check Permissions

Make sure that your MySQL data directory and all the files in it are owned by mysql user and mysql group. This may not be the case if you copied the files onto your local test machine as root user:

chown -R mysql:mysql your-mysql-data-dir-here

2. Repair corrupted tables

Use mysqlcheck to check for corrupted tables and repair them if it finds any:

mysqlcheck -u root -p --auto-repair --all-databases

If you still can't use the tables after that then give mysqldump a go!

Answer 2

I encountered the same issue after restoring a MySQL database with frm and MYD files. After a number of hours spent I observed that I have configured the database directory with only read and write permission to mysql user but not execute permission. After adding execute permission to the database directory, the problem was solved.