Reindexing tables with bad dates

Sometimes, upon adding indexes to MariaDb or MySQL tables with columns with zero-default dates you’ll get an error message like this:

SQL Error (1067): Invalid default value for 'date_column'

This happens because your server’s default sql_mode includes the NO_ZERO_DATE value. It is prevalent when working on WordPress indexes using phpmyadmin or some other MySQL client program. To prevent it, change the sql_mode before doing your indexing operation. Here’s an example.

SET @@sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');
ALTER TABLE wp_posts ADD KEY post_date_gmt (post_date_gmt DESC);

From code inside WordPress, there’s no need to change the sql_mode. WordPress does it for you when it establishes each database connection.

Leave a Comment