Tracking down strange “Can’t find file” (ER_FILE_NOT_FOUND) Errors

Ever see an error like this in your console or error log, respectively, and wonder where it originated from?

Console (5.5.30):

ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

Error Log (5.5.30):

130307 23:22:04 [ERROR] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: 
Can't find file: '.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

(Fwiw, they are harmless, unless it is indicative of some sql injection – but you may want to sanitize your data better, unless it’s just a one-time user error. Also, if you’re seeing it from the command line, then you’ll likely realize what you did immediately, and thus no need to ‘figure out’ what happened. However, when you *only* see this entry in the error log, and you want to know how that happened after the fact, then this is more relevant.)

At any rate, after searching the code, one can see that “can’t fild file” can originate from several locations, two such being in ./sql/ and ./sql/ (the code for SHOW CREATE TABLE and RENAME TABLE, respectively).

So, working backwards, I tried both a SHOW CREATE TABLE and RENAME TABLE (with bogus table names), and was able to reproduce the error (because I was wondering how this ended up in an error log I was analyzing):

mysql> SHOW CREATE TABLE `select * from t1`;
ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

mysql> RENAME TABLE `select * from t1` to `t2`;
ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 2)

Note the first *does* get logged to the error log, whereas the second one does not. I’ve filed a bug report about this behavior:

The only difference between the two errors are the errno values are different for each (22 vs. 2). For reference, here are the meanings for codes 22 and 2 respectively:

C:\Windows\system32>perror 2
OS error code   2:  No such file or directory
Win32 error code 2: The system cannot find the file specified.

C:\Windows\system32>perror 22
OS error code  22:  Invalid argument
Win32 error code 22: The device does not recognize the command.

I will say that in 5.6, the error message is a bit more descriptive by mentioning the text of the “errno”:

Console (5.6.10):

mysql> show create table `select * from t1`;
ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22 - Invalid argument)
mysql> rename table `select * from t1` to `t2`;
ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 2 - No such file or directory)

Error Log (5.6.10):

2013-03-09 10:33:38 6340 [ERROR]
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld:
Can't find file: '.\test\select@0020@002a@0020from@0020t1.frm'
(errno: 22 - Invalid argument)

Hope this helps. 🙂