Running optimize on DB tables for WP shows errors about invalid default values

[d19dotca]

I stumbled across something a bit strange... some tables by default in a brand new WP app install show errors around invalid default values for some table columns. I had seen this on a few of my own sites but realized some of mine also seem to have a mix of storage types and such, so spun up a fresh one to compare and try to bring my affected ones in line, but noticed these errors occur on a fresh one too. Is this expected? I kind of wonder if the DB defaults aren't set correctly somehow for default values.

Thoughts?

19558a605fe34e5e.wp_commentmeta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_comments
note     : Table does not support optimize, doing recreate + analyze instead
error    : Invalid default value for 'comment_date'
status   : Operation failed
19558a605fe34e5e.wp_links
note     : Table does not support optimize, doing recreate + analyze instead
error    : Invalid default value for 'link_updated'
status   : Operation failed
19558a605fe34e5e.wp_options
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_postmeta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_posts
note     : Table does not support optimize, doing recreate + analyze instead
error    : Invalid default value for 'post_date'
status   : Operation failed
19558a605fe34e5e.wp_term_relationships
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_term_taxonomy
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_termmeta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_terms
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_usermeta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
19558a605fe34e5e.wp_users
note     : Table does not support optimize, doing recreate + analyze instead
error    : Invalid default value for 'user_registered'
status   : Operation failed
Success: Database optimized.

[girish]

I don't see an error in a fresh instance but I can guess the source of the error.

I think this is related to values in the "date" fields. This is controlled by sql_mode .

mysql>    SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The NO_ZERO_IN_DATE,NO_ZERO_DATE mean that 0 value is not a valid date. This is the default in recent SQL versions (but wasn't in MySQL 5.x iirc).

You can set the sql_mode for the session like so:

mysql> set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Maybe try running optimize afterwards? Also, I think sql_mode does not affect existing data, so you might have to fix them manually to make it work by default with the latest sql_mode .

[d19dotca]

@girish strange that you don’t see that error on a fresh instance, that’s how I reproduced the problem before to compare a fresh DB with my used ones and noticed it on the fresh one too. I’ll look into that further. Thanks