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)
Vérace
31k9 gold badges73 silver badges86 bronze badges
asked Sep 28, 2017 at 7:21
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! Commented 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!). Commented Sep 28, 2017 at 15:21
  • What parameters did you have on mysqldump (or whatever dump tool you used)? Commented 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"

answered Sep 28, 2017 at 8:07
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.

answered Nov 8, 2017 at 8:42

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.