Category: Database

When You Get an Invalid Default Value Error While Changing Your MySQL Engine on a Table from MyISAM to InnoDB

Instructions on overcoming a default value error when attempting to convert a MyISAM table to InnoDB.

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