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.