WordPress.org

Ready to get started?Download WordPress

Forums

Category Numbers in the Thousands? Why? (23 posts)

  1. Lorelle
    Member
    Posted 9 years ago #

    I am cleaning up my site and decided to break things down into a few new categories to help people isolate the information they want. Imagine my surprise when I was checking the category panel in admin and found that the number sequence of categories goes 61, 62, 63, 4452, 4453, 4456, etc.

    HUH?

    Why would the numbers suddenly jump to the THOUSANDS in the category list?

  2. vkaryl
    Member
    Posted 9 years ago #

    I don't know. Did you do anything unusual with permalinks or some such?

  3. Lorelle
    Member
    Posted 9 years ago #

    Nope. I don't have permalinks turned on yet.

  4. vkaryl
    Member
    Posted 9 years ago #

    Well, gosh. That was my single not-so-bright thought.

  5. ifelse
    Member
    Posted 9 years ago #

    I can't see anything that would account for this behaviour. The category_id column is auto incremented by MySQL and thus should be sequential. Are you using any category/tag plugins?

  6. ifelse
    Member
    Posted 9 years ago #

    Alos note that it is possible to reset the AUTO_INCREMENT counter through MySQL. However, as this is not affecting anything else, I'd be tempted to just acknowledge this situation has occured and move on.

  7. Lorelle
    Member
    Posted 9 years ago #

    Like what category or tag plugins? I have plugins running, but nothing, I think, other than Adhesive, which might impact categories when entered in the Category admin panel...

    I just discovered it, so I don't know if it is affecting anything else. OMG, it might be a terrorist category mangler...kidding. I'll have to poke around and see if anything else is borked.

    I manually went in and changed them to something more manageable, but I thought it might be worth mentioning. If others pay attention, this could be caught as a bug. If it is just me...well, it won't be the first time. ;)

  8. vkaryl
    Member
    Posted 9 years ago #

    Well, I'm running adhesive too, the only cat-type plugin I have installed, and I have nowhere near 60+ cats, but mine are just fine - from 1 on up normally....

  9. Lorelle
    Member
    Posted 9 years ago #

    Oh, yeah, well, there's a consequence of changing the category ids to something more in keeping with the sequence - none of the children of the parent categories where I made the changes show up.

    None of the categories I changed had children, but they are now lost to their parents, so I have to go into the database and track them down....I think I'll try changing them in WP category panel first - so something else and then back and see if they show up then.

    Poop!

  10. Joshua Sigar
    Member
    Posted 9 years ago #

    Each of posts is tied to one or more category ids. If you change the category ids, you have to update its reference in wp_post2cat table.

  11. Lorelle
    Member
    Posted 9 years ago #

    Thanks....that will make it easier.

  12. Lorelle
    Member
    Posted 9 years ago #

    Well, I poured through the wp_post2cat table and fixed them all. Nice save, alphaoide! Thanks.

  13. vkaryl
    Member
    Posted 9 years ago #

    Hopefully it won't happen again, Lorelle....

  14. Lorelle
    Member
    Posted 9 years ago #

    F$%*&!#$!!!!!

    I just added two new categories and it did it again!

    I spent way too long fixing all this last night. Dang it!

    The auto-increment is on, but I want to try to reset it. What will that do? Totally realign all of my preciously done categories or just fix it so the next ones will be in line with the rest of the count?

    I hate this!

  15. vkaryl
    Member
    Posted 9 years ago #

    Oh jeez. Gremlins. I hate gremlins. There's got to be something wonky in the auto-increment then - really, if that's the only code you have addressing the relevant areas....

    Is this an add on or does it come with the package? (Autoincrement I mean....)

  16. ifelse
    Member
    Posted 9 years ago #

    Lorelle,

    To reset the auto_increment counter, you can run do the following.

    You'll need to remove all the records from wp_categories and wp_post2cat tables where the category id is greater > the sensible value i.e. 70.

    You'll then need to run the following in MySQL:
    ALTER TABLE wp_post2cat AUTO_INCREMENT = 70

    Now the category_ids will start incrementing from 70.

    However, as this appears to be only a cosmetic issue, my advice would still be to just live with it. However, if you do decide to do this, make sure that you back up your DB before doing so. I cannot stress this enough. Direct DB manipulation is inherently risky so make sure that you're in a situation to recover from any mishaps.

    I tested this on a dummy table that I generated which was successful but I cannot guarantee that this will be the same in your case as I neither have access to nor enough information on your system.

  17. ifelse
    Member
    Posted 9 years ago #

    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.

  18. vkaryl
    Member
    Posted 9 years ago #

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

  19. Lorelle
    Member
    Posted 9 years ago #

    >>>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!

  20. ifelse
    Member
    Posted 9 years ago #

    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.

  21. Lorelle
    Member
    Posted 9 years ago #

    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.

  22. vkaryl
    Member
    Posted 9 years ago #

    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?

  23. Lorelle
    Member
    Posted 9 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic