WordPress Comments Out of Order: A Solution for Old Imported Posts
When I started looking back over many of my older posts in my WordPress site that I pulled in from the original sites, I noticed that the comments were often out of order. They weren’t reverse order, newest to oldest. They were simple in no order whatsoever.
Many people have had problems with the ordering due to a fault with the Reply To This Comment (or “threading”) feature. You had to go into the database and clear the comment_parent field contents.
That’s not the issue here, either.
The problem was that some but not all of the posts came over with comment_date_gmt set to 0000-00-00 00:00:00. The comment_date was populated sensically. What got mangled was the notation for the GMT time that the comment got posted, rather than the local time on the server. The host server has been in at least two countries and several states over the years, what with all my changes.
And I’ve been with WordPress for several years. So it’s been a problem for a long time and I’ve never gone back over the old comments closely enough to have noticed it.
The solution was easy enough. You simply run an UPDATE on the [prefix]_comments table:
UPDATE [prefix]_comments SET `comment_date_gmt`=`comment_date`WHERE `comment_date_gmt`="0000-00-00 00:00:00"
where [prefix] is whatever the prefix is for your WordPress installation.
Of course, you must back up your database before running these commands, and run them as transactions if you can.
I think that this is due to the fact that I migrated multiple times over different platforms. I know I went from Blogger to my own MovableType to something else to WordPress. And migrated from WordPress to WordPress at least once, too, afterwards.
That’s a lot of migrations with a lot of potential for screwing things up. The comment_date_gmt field was probably not in the Blogger version I used, then not in the MoveableType, which would have simply put in NULL into it. When I migrated to WordPress at some point that got pulled in because WordPress believed that whatever it was that I was migrating from had a correctly populated comment_date_gmt.
Let this be a lesson to those of us who work on large, old systems. Any time you do a migration, you aren’t just migrating from CURRENT to NEW. You are also migrating from every previous version and platform of the data that they used for getting to CURRENT. Never assume that a migration goes well. Always, always test.
Because I certainly would not have thought of this.
Image Credit: GPN-2000-000354 Analog Computing Machine in Fuel Systems Building Lewis Flight Propulsion Lab (NASA)