I am developing a website for ecommerce and I'm using foreign keys for my tables relationship in my database. So I decided to use InnoDB engine for all of my tables in MySQL. But my hosting server used MyIsam for default storage engine databases and I cannot change it to InnoDB. One day, one table in my database with InnoDB engine is being corrupted and error. It says " table does not exists in engine ".
I have asked hosting customer service but they can't give me solutions. Does my table cannot be accessed because of different engine between table and database? Because all of my tables are using InnoDB as engine but my database default storage engine is using MyIsam.
Please give me some explanation about how and maybe the solution to my problem. Thank you.
The default storage engine is relevant only if you create a table, but you don't declare the engine explicitly.
// uses the default storage engine:
CREATE TABLE mytable ( ... );
// uses innodb storage engine, regardless of default:
CREATE TABLE mytable ( ... ) ENGINE=InnoDB;
If you declare your table with ENGINE=InnoDB, it stays that way. Changing the default storage engine to MyISAM should not have any effect on existing tables. It only affects tables created subsequently, and only if they don't specify the engine.
The error "table does not exist in engine" happens when you try to query a table but the tablespace has been discarded with ALTER TABLE mytable DISCARD TABLESPACE, or if the .ibd file has been deleted from the filesystem.
This means someone destroyed your data. Unless you have a backup, there is no way to recover it now.
InnoDB has been the default storage engine since 2010. It's time to treat it as the primary way to take care of data in MySQL.
I hope you will find a new hosting provider.