Recently I noticed one of my older WP site still had some MyISAM tables. These days most folks are running InnoDB due to its advantages over MyISAM. If your WP database is using MyISAM you should probably update to InnoDB as well (ummm, make sure there isn’t some reason you need to stay on MyISAM first!).
Changing the engine for a table should be fairly simple:
ALTER TABLE dbname.prefix_users ENGINE=InnoDB;
But when I tried this I received an unfriendly error about an “Invalid Default Value” in a datetime column.
Ends up MySQL is now more strict in what it allows in a datetime field and it doesn’t like values of all zeroes. An easy way to fix this is to temporarily disable this strictness when running our query. This is controlled in our sql_mode. To get the current values of your MySQL sql_mode:
You want to remove from this list
NO_ZERO_DATE. To do this we will use
SET sql_mode = '<listofvalues>';
<listofvalues> with the results of your
SELECT @@sql_mode above making sure to remove
NO_ZERO_DATE from the list.
You should end up with something like:
SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Put this before your
ALTER TABLE statement and this time when you execute the command it should succeed. In sum, you should be running a query that looks something like this:
SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; ALTER TABLE dbname.prefix_users ENGINE=InnoDB;
Hope this helps someone else who finds themselves in the same situation!
- Converting WordPress Database Tables from MyISAM to InnoDB with WP-CLI. WP Bullet Guides, 2018.
- MySQL Storage Engine – How to Convert MyISAM to InnoDB. kinsta, 2019.