MySQL Storage Engine Changed from InnoDB to MyISAM on restoring backup in lower version I have two production instance, old production instance have MySQL version "5.1.73-community-log" and the new production instance have MySQL version "5.1.72-community"

When I take backup from the old production instance to new production instance some of the InnoDB tables changed to MyISAM tables.

I have almost same setting in both the instance the difference is I have changed "innodb_log_buffer_size" and "innodb_log_file_size" on new production instance to speedup the restore process.

Change effect:


Because of this all referential integrity were lost where the tables engine was previously InnoDB and now MyISAM.
"LOCKING" occurred frequently in "SELECT, INSERT, UPDATE" queries.


I am not able to find correct cause for this because when I check the SHOW ENGINES it shows below result which means InnoDB is enabled:

mysql&gt; SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
If the backup was restored from a .sql dump which didn't include the ENGINE on the CREATE TABLE statements, MySQL would fall back to the "default_storage_engine" (5.7 manual version), which before 5.5.5 was "MyISAM"