InnoDB: Error opening table after MyISAM convert

Bestrafung

Active Member
Apr 2, 2012
42
0
56
cPanel Access Level
Root Administrator
After converting one of our databases to MyISAM from InnoDB everything seems to be fine except for the below error message spamming the log for each table in that database. We had a file system issue a little while back and I've recovered from it using backups. I'm not sure if it's relevant but it might be. I've also noticed that I can no longer complete a 'mysqlcheck --all-databases -r' or 'mysqlcheck --all-databases -o' anymore and receive the error:
Code:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'REPAIR TABLE ... '
When I view the error log to determine what caused the repair/optimize to fail all I see is the original error:
Code:
140310 14:14:50  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
140310 14:14:50  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './database/table.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
spammed some more along with the following:
Code:
InnoDB: Page directory corruption: infimum not pointed to
140310 14:19:34  InnoDB: Page dump in ascii and hex (16384 bytes):
followed by a large dump.

I've been trying to research the best way to resolve this but I'm not very knowledgeable with this yet and everything is working so I don't want to break anything else trying to fix it. Any ideas would be much appreciated as it would be nice to be able to complete a repair and optimize again.
 

Bestrafung

Active Member
Apr 2, 2012
42
0
56
cPanel Access Level
Root Administrator
Hello :)

I recommend reviewing the following documentation first:

MySQL :: MySQL 5.5 Reference Manual :: 14.3.19.3 Troubleshooting InnoDB Data Dictionary Operations

Try the solutions offered here and let us know the outcome.

Thank you.
Thank you for the reply. I don't see anything useful in that documentation. I already dropped the tables and recreated them without issue. They are currently working as MYISAM with no issues but MySQL keeps complaining that it can't find the filespaces/.ibd files. The only thing I can think to try is to drop the tables again and copy all of the .frm and .ibd files from an older backup from before the MYISAM conversion to see if that makes it happy. Surely there's another way to do this than that. All I need it to do is recognize that there is no INNODB anymore and stop looking for the .ibd files. I'd appreciate any ideas.