Running optimize on DB tables for WP shows errors about invalid default values
-
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. -
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_DATEmean 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_modefor 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 .
