MySQL 5.7 default configuration breaks things

Attn: Adam Czajczyk

I spoke to Adam on Live Chat and we spent a while going through several things to have a look at this issue. For the benefit of Adam's curiosity and anyone else who may be experiencing issues with WordPress and MySQL 5.7, the fix was to turn off the STRICT_TRANS_TABLES SQL Mode. The details of how to do that for your hosting will vary, but that fixed all my issues.

What issues? Well, (on our multisite) for starters:

Uploading images to any sub-site gave the error "Could not insert post into the database" (meaning the file uploaded to the server, but a database entry couldn't be created).

Creating new posts/pages also wouldn't work.

Media library grid view on either the main network site, or all sub-sites wouldn't work. The list view worked, but not the grid view.

Unable to create new sub-sites either.

Ultimately, we were able to narrow this down to the database not setting primary keys or AUTO_INCREMENT for tables. While a time consuming and temporary workaround was the manually set those in the database, it was ultimately fruitless because the next operation that needed to create a table would cause an issue.

Basically, updating existing database entries would work. But creating anything new would not.

So that was the issue. STRICT_TRANS_TABLES. All our errors melted away after that mode was turned off.

I hope this helps anybody struggling with MySQL 5.7, and I hope this was interesting for you Adam, if nothing else! ?