Dev {Tricks}

Purpose:

To solve the error “General error: 1005 Can’t create table ` `.`review_round_files` (errno: 150 “Foreign key constraint is incorrectly formed”)”

We will convert MySQL tables from MyISAM into InnoDB in two different ways.

1. PhpMyAdmin (suitable for shared hosting)
2. SSH Methed (suitable for vps, vds, dedicated server owner)

PhpMyAdmin

• Login to PhpMyAdmin
• run the query below (Replace DBNAME to your database name)

  SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
  FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

• You will get result like below

  ALTER TABLE DBNAME.access_keys ENGINE=InnoDB;
  ALTER TABLE DBNAME.announcement_settings ENGINE=InnoDB;
  ALTER TABLE DBNAME.announcement_type_settings ENGINE=InnoDB;
  ALTER TABLE DBNAME.announcement_types ENGINE=InnoDB;
  ALTER TABLE DBNAME.announcements ENGINE=InnoDB;
  ALTER TABLE DBNAME.article_event_log ENGINE=InnoDB;
  ALTER TABLE DBNAME.auth_sources ENGINE=InnoDB;

• If you don’t see full line text like above, you have to activate the “Full Text” option just above the result.
• Now copy the result and paste it in PhpMyAdmin query again and wait for the conversion.
• You will get success message.
• That’s all.

SSH Method

• Login to MySQL/MariaDB

mysql -u root -p

• Then run below command to get conversion commands.

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'yourdb';

Replace yourdb to your database name. And the conversion commands will be used later.

• Now switch to the database

use yourdb

• Then copy each line of conversion commands and paste it. For example

ALTER TABLE pln_deposit_objects ENGINE=InnoDB;
ALTER TABLE issues ENGINE=InnoDB;
ALTER TABLE custom_section_orders ENGINE=InnoDB;
ALTER TABLE access_keys ENGINE=InnoDB;
ALTER TABLE journals ENGINE=InnoDB;
ALTER TABLE notification_subscription_settings ENGINE=InnoDB;
ALTER TABLE notifications ENGINE=InnoDB;
ALTER TABLE review_object_types ENGINE=InnoDB;
ALTER TABLE filter_settings ENGINE=InnoDB;
ALTER TABLE users ENGINE=InnoDB;

That’s all.

 

Related scripts you may need.

Convert all DBNAME’s InnoDB Tables to MyISAM

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

Convert all DBNAME’s MyISAM Tables to InnoDB

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

Convert all InnoDB Tables to MyISAM (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

Convert all MyISAM Tables to InnoDB (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';