Monday WordPress Woes

Monday WordPress Woes

I picked the wrong day to give up sniffing glue!

Been building a new WordPress site and I was trying to migrate it to a new domain when something went very wrong. I had a backup before you ask, but when I tried to restore it, things went tits up

Here are the results of the MySQL CLI alchemy: (edited to just show errors)

It’s not as bad as it looks; there are two errors basically

• Attempted insert of string with 4-byte value fails
• Invalid default value. This turns out to be a date value of '0000-00-00 00:00:00'

I came very close to laying down a fresh install of WP and starting again; it was an option and probably the quicker one, yet my concern was I wouldn’t learn the root cause of the issue and it would come back to bite me at a later date, anyway.

Invalid string – It’s an encoding issue, stupid!

Looks like good old utf8 is rearing its head again. Remember how I told y’all you need to be across encoding issues?

A quick look under the bonnet shows the script is trying to create utf8mb4 tables and we likely are using a uft8 client connection to stuff those values in the rows. Bad

I found this post useful. Indeed inserting a SET NAMES statement before my inserts caused the string insert error to disappear so that one is likely a client connection issue.

0000-00-00 00:00:00 = bad

As can be seen in the above, some key wp tables are missing, one of them being wp_users. Not good. Let’s fall back to the cli and try and create the table from the code in the backup sql file.

Nope. Here’s our error: ERROR 1067 (42000): Invalid default value for ‘user_registered’

Let’s stop and think about the problem for a minute. Why are we trying to set a date string full of zeros for a start? Pretty stupid, why are people so afraid of NULLs – they exist for a reason!

Hey somebody give TomH a cigar will you. He calls it perfectly.

That doesn’t solve my problem, however. I’m not about to play with DDL to solve things. A little Googling gives me the answer I need:

The Holy Grail

Here’s what you need, which solved all my woes SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

The MySQL docs say:

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

Just type it into the MySQL CLI and it will persist for the session; allowing you to process the import properly using the source command.

Thinking more about this after being AFK – I’m on a different host with (likely) a newer version of MySQL. I’m sure that’s playing a part in what happened.

Leave a Reply

2 Comments on "Monday WordPress Woes"

Notify of
Sort by:   newest | oldest | most voted
Excellent post. (except the “tits up” reference) RE: > Why are we trying to set a date string full of zeros for a start? Pretty stupid, why are people so afraid of NULLs – they exist for a reason! Agreed that ‘0000-00-00 00:00:00’ is an undesirable default date value. To answer your question: 1. ‘0000-00-00 00:00:00’ did not cause errors until mySQL 5.7, where the default config is now set to NO_ZERO_DATE. This can be changed in your my.cnf file. ( 2. The reason WordPress *still* uses zeroes as the default, even in new installs, is for legacy reasons. Not… Read more »