Category Numbers in the Thousands? Why?

vkaryl,

Auto_increment is a MySQL feature i.e. a database feature. It’s used for sequential id’s (i.e. similar to sequences and identity fields in other databases). This is used to typically generate unique primary keys i.e. post_id and category_ids. An auto_increment field will always retrieve the next highest number in a given table.

Ah. Thanks, ifelse. I have less than zero knowledge in this area…. only enough to manipulate MySql dbs through phpmyadmin….

>>>An auto_increment field will always retrieve the next highest number in a given table.<<<

Well, it ain’t in this case. I don’t see a next highest number from from 62 to 4423. That’s a serious jump.

I just finished five months of imports of my old site – barely been “open for business” for a week and so the last thing I want to do is screw up the database. Invitation to more trauma, so for now, I will manually change the ID numbers of any new categories before adding posts. I’m not anticipating more than a few more categories since I’m pretty set, and maybe something will resolve this on its own.

But at least, like vkaryl says, we learned something here. I’ll bookmark this thread in case I decide to be brave.

Thanks!

An auto_increment field will always retrieve the next highest number in a given table.
Assume that we have a table test defined as thus:
id int auto_increment
message varchar
and we insert a set of values as thus:
insert into test(message) (‘a’);
insert into test(message) (‘b’);
insert into test(message) (‘c’);

The table will now contain the following:
id | message
---|----------
1 | a
2 | b
3 | c

Now we go and execute the following:
insert into test(id, message) (100,’d’); //note the explicit id value

The table will now contain the following:
id | message
---|----------
1 | a
2 | b
3 | c
100| d

If we now go and execute “insert into test(message) (‘e’)”, we’ll find that the next value retrieved by MySQL for the id column is 101 i.e. that is the next sequential value.

This may provide some clues as to how a scenario arose. At some point, a category with an id of 4423 was inserted into the db which naturally caused all subsequent id’s to start from that offset.

Excellent explanation, but how would such a category number get put in there? Doesn’t make sense since I don’t “create” the category numbers in the process of adding categories. They are done by WP. I’ll go through my notes and see if I messed with categories at any time. It’s been five months of messing…

If I run this:

insert into test(id, message) (100,'d');

Would it then set the database (obviously I have to target the categories table) to start incrementing at, say, 70 and not mess the whole table up? I’d backup of course, but without removing all the data?

Thanks for poking into this. It’s very odd.

Er. Scary thought, but I actually understood that, ifelse. So what’s the breakpoint here? How does one keep this from occurring again? Surely what Lorelle has had happen is not the norm, shouldn’t there be some failsafe against this?

Or alternatively, does it even matter? If the category list is “1, 2, 3, 4453,4454….” etc ad infinitum ad nauseam, is it going to cause any problems down the road if it’s left that way?

The only problem I have with this is that for right now, I’m not using permalinks because to get the effect I want with the permalinks, my host has to upgrade their server, and so far, they aren’t very willing to jump to my request. So I wait for a while, hoping for them to jump or a better fix for rewrites to come along.

This means that instead of having simple index.php?cat=5 or even index.php?cat=61, I would have to deal with index.php?cat=45632985 if things really got out of control…but I’m joking…okay, only slightly, but you get my point. So I manually fix them before I get too vested, but vakaryl, you bring up a seriously good question.

I do hope that I’m the only one dealing with this. And maybe I did, by some accident, get into the categories table and while switching or fixing something with category 44 mistyped a 4444 and then discovered my error immediately and then fixing it, and never giving it another thought. Not saying I did, but all things are possible. Unknowingly I would have reset the counter and the beat would have continued on from there.

Knowing this information may help me track it down, which is a help. And for anyone else who may stumble across this thread, they might have an answer for this.

So thanks to everyone for hanging in there to figure it out. Good problem solving detectives we is.