In the long history of my of my blog (over 900 posts), I’ve done many things that were… ill-advised. I’m still recovering from messing up my media library somehow. All the images load but a couple of galleries disappeared.
While playing with the Wapuuvatar plugin I noticed that my recent 60 or so posts were showing my Gravatar photo but the older posts where showing a Wapuu. I thought I ran into some weird bug in the plugin. Ha! The plugin is fine. My old posts were set to post_author ID=0.
There is no such author ID but fixing it was straight forward. Here’s what I did.
WP-CLI is your friend
To get an idea of how bad the problem was I ssh’ed to my server, cd’ed to my installation and ran this command.
wp post list --fields=ID,post_title,post_name,post_date,post_author --url=blog.dembowski.net
This command outputs a neat table and the fields on the CLI include the post_author ID.
I wanted to see those fields and since I’m running on multisite I had to specify the URL. Yep, the recent posts were set correctly but almost all of the old ones were set to post_author=0. I probably could use wp-cli to fix it but I ended up using mysql commands.
wp db export ~/author-munging-save-me.sql
Backup your database, backup your database, backup your database. Don’t rely on your existing backup, just make a new one.
From my wp-config.php file I copied the mysql database name, user ID, password and table prefix. I then ran this command.
$ mysql -D mydbsitedbname -u mydbuser -p
Which put me on the mysql command line. The idea was to fix each post where the post_author=0. Easy!
And I promptly ran an update on the wrong table in my database.
A quick check showed me that nothing changed for what I was trying to fix. I’m not sure what I broke but I am sure I would be sorry if I didn’t fix it pronto.
Backups are good but do you know how to use them?
I wasn’t worried because I had that backup and ran this command.
$ mysql -D mydbsitedbname -u mydbuser -p < ~/author-munging-save-me.sql
You see, I like to poke at my site and while I don’t always break it I have done horrible things in the past. Everyday I make a full automated backup and once a week backup all of my files and I know how to restore the full database. I could rely on last night’s file but why bother? I just made a new one before working on the database.
I forgot that I was running multisite and that this blog is the second one in my site. The wrong post_author ID was 0, the correct ID is 1. I re-ran mysql on the command line and used this:
UPDATE myprefix_2_posts SET post_author=1 WHERE post_author=0;
A quick check and I’m good. The only thing that was updated was the post_author where I wanted it to be. I’m sure I would have caught this eventually but using the Wapuuvatar plugin pointed it out to me sooner.
Update: That broke things. The Gravatars on the front page went away. I rolled back the database and will look at being a little more selective on how I update the post_author.
Update for the update: When Twenty Sixteen only sees published posts from one author then the Gravatar isn’t displayed on the main list of posts. Nothing broke and I assigned Lily’s 3 posts to her account. 😉
Featured image photo by masatsu 
