HOWTO move WordPress from MyISAM to InnoDB

When importing an old WordPress database, it may have tables in MyISAM. You can convert them to InndoDB in MySQL like so:

ALTER TABLE wp_commentmeta ENGINE=InnoDB;
ALTER TABLE wp_comments ENGINE=InnoDB;
ALTER TABLE wp_links ENGINE=InnoDB;
ALTER TABLE wp_options ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;
ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_term_relationships ENGINE=InnoDB;
ALTER TABLE wp_term_taxonomy ENGINE=InnoDB;
ALTER TABLE wp_termmeta ENGINE=InnoDB;
ALTER TABLE wp_terms ENGINE=InnoDB;
ALTER TABLE wp_usermeta ENGINE=InnoDB;
ALTER TABLE wp_users ENGINE=InnoDB;

If you run into errors like this:

ERROR 1067 (42000): Invalid default value for 'user_registered'

It’s because of the SQL mode. You need to remove NO_ZERO_DATE from the mode. First, see what SQL mode you have set:

select @@sql_mode \G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then, set it to everything except NO_ZERO_DATE. In my case, that is:

set SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

This setting is temporary, and will go away as soon as you exit the MySQL client.

Now, re-run the ALTER TABLE statements above.