Fixing the Post Id > 2³¹-1 Issue

4-minute read
WordPress • CMS

Regarding my WordPress issues, which I actually did not want to tackle anymore, I accidentally stumbled upon an interesting post over on StackExchange, when I was looking for another issue. But as it turns out, this does fix the former issues as well. Nice.

The new issue did appear to me when I clicked through the Jetpack stats pages. Top Pages & Posts did only show one entry which looked like this:

I had no clue what that meant, so I checked out another WordPress installation I’m maintaining. That blog showed the post titles (yes, more than one) without the enormous number just fine.

Every post’s id > 2³¹-1 (> 2,147,483,647) was being max’d to that number, it seems. The above mentioned post explains, that these huge numbers come from Tumblr imports. Tumblr uses such high numbers to identify it’s posts. I actually did the import from Tumblr when I set up this new blog. Mystery solved!

But how do we fix this? Luckily, the guy who answered the post over at StackExchange had an idea that seemed to work: Just go through all your database entries and shrink the numbers manually. Yeah, well. It’s not that many posts on my site but lazy as I am even about 350 are too many to tackle them all manually. So I wrote a little script that did that for me. It can be found here. I know, it’s spaghetti code and not beautiful, but for me it did the trick. And as I will probably only use it once that’s ok.

So, let’s just go through the whole process step by step:

  1. Export the needed tables. For me it was wp_comments, wp_postmeta, wp_posts and wp_term_relationships. If you have a more complex setup than me you might need to export more tables (and adjust the script appropriately). In order for the script to work you will need .sql files (one for every table) with the permission to drop the old table and re-create it when it’s being imported.
  2. Run the script on all the exported files (see my warning below!). You should now have a second file for every single table you need to alter. If you looked at the script you might be wondering why I started $post_count at 20 instead of 0. For me, my wp_term_relationships had some entries that had normal looking ids (up to 19) and as I wanted to avoid conflicts I thought starting one higher might be a good idea. These few entries had to be adapted manually because my script does not cover them. So I opened up both the original and the just created file and fixed these few lines.
  3. If you did a backup of your website (of course you did, right?) and if the new files are prepared you can go ahead and import them into your database (Again, see my warning below!). If you did it like described, this will drop your original tables, re-create them, and import the data afterwards.
  4. Check your website. That should be it.

My site is still working fine and I hope so is yours. Though I’m not sure if Jetpack will show the titles (I think this might take until tomorrow or until more posts have been clicked) Update: It worked, my stats are being displayed nicely. And I’m happy the WordPress iOS apps aren’t confused anymore.

So, thanks again to that guy on StackExchange for figuring this out. For a detailed explanation I suggest checking out his answer.

I hope this helps someone.

Warning

Do only use the script and instructions at your own risk and if you know what you’re doing! I cannot guarantee that it’ll work with your configuration. You might blow up your machine or even the whole data center! Maybe. Also, you should adjust it to your needs. You might also consider to set up [a staging environment] for your blog to play around with.