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)
  • 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 '17 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 '17 at 15:21
  • What parameters did you have on mysqldump (or whatever dump tool you used)? – Rick James Sep 28 '17 at 15:35
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, privacy policy and cookie policy

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