mysql – Mostly Harmless

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

WordPress blogs are based on MySQL and all the dynamic content is stored in a database. After trying out plugins, odd themes, and generally screwing around, my database has items in it that I can’t even recall what they were or if I need them.

One way to clean out the database is to export the blog to an XML file. This will produce the posts, pages, and all the comments and not export the ton of junk in the database. This way I create a clean empty blog, add the users, set up the theme and look I want and import the data.

This produces a very clean installation. It also removes me as the author for the comments I’ve left on my own blog. The table wp_comments table has a user_id field that the XML file does not have.

What I wanted to do is run a MySQL command that will find all instances in wp_comments which match my e-mail address, and update that record with my user_id on the blog. I could do this one at a time using phpMyAdmin but that’s pretty inefficient, not geeky, and time consuming.

After some research I found out to run these commands:

$ mysql -p
USE blogdatabase;
Database changed
SELECT * FROM wp_users WHERE user_email = 'notreally@myemail.com';

That gets me the ID number for my user. Since it’s the first user created the ID is 2.

UPDATE wp_comments SET user_id = 2
WHERE comment_author_email = 'notreally@myemail.com';
Query OK, 113 rows affected (0.00 sec)
Rows matched: 113  Changed: 113  Warnings: 0

This updates the wp_comments table so comments left by me using my e-mail address.

Tag: mysql (page 1 of 1)

Fixing broken post_author IDs

WP-CLI is your friend

Backups are good but do you know how to use them?

Cleaning up the WP database

Subscribe to Blog via Email

Search

Jan Dembowski