1

For some reason, one of my top posts as reported by WordPress Stats is actually non-existent...

What I see is:

#2147483647 (loading title)

and it links to "...?p=2147483647" which returns a 404.

Now a bit of background info:

  • I'm actually using the following permalink settings: /%category%/%postname%/
  • Stats for other posts seem to be fine
  • I migrated from tumblr to WordPress

The odd thing is looking into the database where the posts are stored I can not find any reference to that particular ID.

Also, I appear to have several 'duplicates' of posts in the database - the difference being in a couple of fields (I'm guessing versioning or something?)

I've googled a bit, and the closest I've seem to come is: http://wordpress.org/support/topic/solution-for-id-loading-title-coming-back-from-stats_get_csv-function

However, that seems to assume that the ID actually exists, but WordPress/WordPressStats is just for some reason unable to get the title.

So does anyone know why WordPress Stats is listing this non-existent page? (It comes #2 in all time top posts!)

Thanks!

Improve this question
8
  • I guess the problem came with the migration which failed in some point (How many posts have you got? - 2.147.483.647 is quite a high number). The "dublicates" you're talking about maybe post revisions.
    – kaiser
    Commented Jun 19, 2011 at 13:51
  • Not many posts at all, in fact 9 all up. First post has ID of: 2142165720, and most recent post has ID of: 2165214978. I think these IDs are taken from tumblr IDs. I've checked tumblr too - no posts that correspond to that ID!
    – pyko
    Commented Jun 19, 2011 at 14:21
  • What version of Wordpress are you using? and What version of Wordpress Stats are you using?
    – Hameedullah Khan
    Commented Jul 1, 2011 at 4:57
  • I'm using WordPress 3.1.3 and WordPress Stats 1.8.1. I have another blog using the same combination and has no problems.
    – pyko
    Commented Jul 1, 2011 at 5:20
  • 2
    2147483647 = 2^31 - 1. I suspect you hit a counting bug somewhere.
    – Otto
    Commented Jul 2, 2011 at 15:37

2 Answers 2

Reset to default
2
+50

Turns out WordPress doesn't deal well with post IDs > 2^31.

http://core.trac.wordpress.org/ticket/16445

Tumblr in particular has post IDs greater than this, so it's not recommended to keep old post IDs from Tumblr on an import.

Improve this answer
2

After Otto's awesome observation, some SQL trickery I believe I have now fixed the problem :)

The Problem

I was using tumblr and transferred my posts to WordPress. However, since approx mid-Dec tumblr postIDs were greater than 32-bit MAX_INT (2147483647). Of course, I had several posts created after mid-Dec, thus they all had postIDs greater than MAX_INT - http://wordpress.org/support/topic/plugin-wordpress-importer-importer-fails-for-post_ids-over-php_int_max-of-2147483647

For whatever reason, my posts imported fine, however the WordPress Stats did not like these insanely large numbered postIDs, thus I experienced the problem outlined in my question.

The Fix

WARNING!!!

Fiddling around with postIDs/database can potentially screw up your blog - linking etc. So perform at your own risk!

I was very lucky that this was a fairly new blog - 0 comments and only 18 posts... so overall it was still fairly simple.

Remember to back up, incase something horrible goes wrong!

To solve this problem, I backed up WordPress, cleaned up my WordPress database, exported all tables that had references to postIDs, reduced the postID values, reset the auto-increment value, re-inserted the data.

Backing up: I did an XML export and also performed a full backup of the blog via cPanel.

Cleanup database: There was a useless rows (for post revisions) in the database, so to simplify the later steps I first cleaned up my database using the following plugin: http://wordpress.org/extend/plugins/wp-cleanup/

Export tables: Going into phpAdmin through cPanel, I went through each table and any that had references to postIDs I exported into a .sql file, remember to include the drop tables if exists option. The tables I ended up needing to export were: wp_posts, wp_postmeta, wp_term_relationships. !!! NOTE: I was lucky that this is a relatively new blog. So there were 0 comments, thus did not need to worry about comments!!!

Reducing postIDs: Using numbers (ie. excel) I first made a mapping of the old/new postIDs. (eg: 2165214909->3, 2165214895->4 etc). With the old/new postIDs 'set', I went through each of the exported .sql files and did find/replace all. Note: it appears that attachments in posts also have their own postID so it can be a tad confusing

Reset auto-increment number: In the wp_post.sql file, I reset the auto-increment value to something like 30. That way the next blog will have a postID starting at 30. If you don't reset this, any new posts created will still have high postIDs and you'll be faced with the same problem.

Re-inserting data: Back in phpAdmin, using the .sql files containing the new postIDs I imported the data back in. Since in the export I chose to drop table if it exists, it cleared the existing tables and inserted with the "new" data.

After doing all of this, I did a quick sanity check to make sure everything was fine. Public blog - clicked around, made sure images were there, searching works, tags worked etc. Admin section - check that when you hover over 'Edit' it shows the correct new postID, creating a new post uses the new auto-increment id, and of course (after a few days) that the WordPress stats was back to normal.

All checks passed, so I think the fix has worked :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.