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:
SELECT @@sql_mode
You want to remove from this list NO_ZERO_IN_DATE
and NO_ZERO_DATE
. To do this we will use
SET sql_mode = '<listofvalues>';
Replace <listofvalues>
with the results of your SELECT @@sql_mode
above making sure to remove NO_ZERO_IN_DATE
and 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!
Selected Bibliography
- StackOverflow
- 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.