0

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:

  1. Because of this all referential integrity were lost where the tables engine was previously InnoDB and now MyISAM.
  2. "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> 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)
3
  • In future it would beneficial if you could make use of the editing tools above the box where you ask your question - it makes your text easier to read and increases the likelihood of good responses and a solution to your problem. p.s. welcome to the forum!
    – Vérace
    Sep 28, 2017 at 15:16
  • Locking is to be expected because MyISAM is locking - it's the architecture. InnoDB is MVCC - designed so that locks don't occur or are minimal when they have to! Look here for an explanation of why that is occurring. If you can backup now, do so. Dump your database and use sed to change your script putting in 'ENGINE=InnoDB' (or similar - can't recall exact syntax!).
    – Vérace
    Sep 28, 2017 at 15:21
  • What parameters did you have on mysqldump (or whatever dump tool you used)?
    – Rick James
    Sep 28, 2017 at 15:35

2 Answers 2

0

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"

0

Actually I found the issue, when I was restoring the databases from .sql file somehow the InnoDB storage engine get disabled because of which all tables of InnoDB type are imported as MyISAM tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.