Unexpected change of MySQL MySQL server?

Question

I added tables to MySQL databases by copying and pasting them from a file into the mysql command-line application. My procedure is to copy the CREATE TABLE statement from the file and paste it into my development database, and then paste it into the test database on another machine as well. I am the only one using a test database. My CREATE TABLE statements set Engine = InnoDB. InnoDB is included in the test database.

Recently, another developer discovered that the tables in the test database are all MyISAM tables. I checked my file, it still says “Engine = InnoDB” for everything. I checked my development database, tables there Engine = InnoDB.

To prove that the test database has no problems with InnoDB, I just ALTER TABLEd to configure the InnoDB engine to all tables.

Hope I just made a copy and paste or some other stupid mistake. But it’s strange that all the test tables are MyISAM ... of course, I can not agree with my mistakes. All backups and restores were performed using mysqldump.

Is there a way to inadvertently change engines?

Answer 1

I think this is another example of the well-known MySQL trap; I hit this issue several times.

The main problem is that if the InnoDB engine does not start for any reason (usually a configuration problem), then MySQL will quietly return to the MyISAM engine. Even if your statement says:

CREATE TABLE (...) ENGINE=InnoDB; 

then if InnoDB is inactive, MySQL will happily create the MyISAM table without even warning you. Even if you later find and fix the InnoDB problem, all tables created during this problem remain MyISAM. Decent data integrity! :)

You can run SHOW ENGINES to see which engines are active. See this MySQL bug report for more details.