Fixing Haywire Post Table Auto-Increment Value

[Eikinskjaldi]

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?

[Marco van Herwaarden]

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.

[Eikinskjaldi]

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.

mysql> select postid from post where postid > 1254039 limit 4;
+------------+
| postid     |
+------------+
|    1254040 | 
| 1059793148 | 
| 1059793149 | 
| 1059793150 | 
+------------+

[Marco van Herwaarden]

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.

[Eikinskjaldi]

Notice the 'limit 4'

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

[Marco van Herwaarden]

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.

[Eikinskjaldi]

Originally Posted by Marco van Herwaarden

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.

[Marco van Herwaarden]

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