Fixing Haywire Post Table Auto-Increment Value
 

For some odd reason, my auto-increment value for the post table is in the billions, instead of the millions where it belongs. While this isn't a problem right now, when it gets higher I can see it posing some real issues.

I'm trying to think of ways to get all the (16k) posts that are at or above that auto-increment value back down to where they belong, and hunting through the db for all the places postid might live sounds like a bad idea.

Is it possible to use impex to export the posts, so that I can reset the auto-increment value, and then import the posts again at their proper place?

ImpEx will renumber id's, so yes you could use this.

But what is the reason that this happened? Can you give some real example values.

Awesome, thank you.

I'm actually ignorant as to why it happened, but the post auto-increment value was at over 1.2 million and is now far higher. I couldn't say if it has to do with vBulletin or not, but we have had some odd server issues recently that have been thrashing mysql that are likely to blame.

If there are only 3 posts with such a high value, then i would consider deleting these 3 and reset the auto increment value to highest value +1.

Notice the 'limit 4' :)

As I said in the original post, there are no probably around 20k at that high value.

Missed that limit.

I would just let it go then. The maximum value for this column is 4294967295, so this will still leave you a lot of values left.

For those that care, I didn't go with the leave it alone option because if it happens 3 more times, all of a sudden I am at that limit.

What I did was
take a content-less dump (i.e. a db schema) of vbulletin
grep for postid (which included lastpostid and other similar fields)
determined the offset
ran update table set postfield=postfield-offset where postfield > jumpvalue
alter table post auto_increment = newvalue

It seems to have done the trick.

All steps were fast, except the alter table which needed 10 minutes of re-indexing.

I hope you are aware that the postid is also referenced on other places.