Blog Documentation Try Vanilla Cloud
Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

Problem Adding Categories

2

Answers

  • Options
    x00x00 MVP

    I've got

     Keyname                    Type    Unique  Packed  Column          Cardinality Collation
 PRIMARY                    BTREE   Yes     No      CategoryID      7           A
 K_Category_InsertUserID    BTREE   No      No      InsertUserID    2           A

    BTREE index.

    I would say it is a problem with the index being unsigned. try

    ALTER TABLE `GDN_Category` CHANGE `CategoryID` `CategoryID` INT( 11 ) SIGNED NOT NULL AUTO_INCREMENT

    then

    ALTER TABLE GDN_Category AUTO_INCREMENT = 5;

    grep is your friend.

  • Options
    x00x00 MVP
    edited January 2012

    You could create your own mysl procedure to increment, but it does seem a little silly to come to that.

    grep is your friend.

  • Options
    fxgeekfxgeek

    I did that but the next index is still the same

    Is there a way to reset it completely?

  • Options
    x00x00 MVP

    this bug existed for 4.0.15
    http://bugs.mysql.com/bug.php?id=1366

    and more recently

    http://bugs.mysql.com/bug.php?id=36411

    grep is your friend.

  • Options
    fxgeekfxgeek

    Ok, I finally managed to fix it - I think

    I set the category ID of root to 1 which reset the auto index

    I then st it back to -1 and now it seems to be working again

    Thanks everyone for your help !

  • Options
    x00x00 MVP

    That is almost certainly a bug

    grep is your friend.

  • Options
    wpbizbundlewpbizbundle

    I am getting this same error. Tried doing same as fxgeek above and still not luck.

    "I set the category ID of root to 1 which reset the auto index

    I then st it back to -1 and now it seems to be working again"

    Not working for me.

  • Options
    fxgeekfxgeek

    You might need to also run the commands that x00 suggested above to reset the sequence.

    From what I can make out from the documentation of the SQL bug it won't allow the auto increment values to be read if the root is set to -1 so try setting it to 1, run the commands above, and then set it back to -1

    I'm not 100% sure of how I fixed it in the end because I was fiddling around with it for days, but it's something they're going to have to address if more people are having it

  • Options
    wpbizbundlewpbizbundle

    Thanks, I will try it. I was a bit confused about the order of things. Will report on it in a few.

  • Options
    wpbizbundlewpbizbundle

    That worked!!! You just have to followed the steps in order of what you said. Thanks so much!

  • Options
    solvesolve New

    Hi,

    I still have the exact same problem.

    Application Version: 2.0.18.4
    MYSQL: 5.1.26-rc-5.1.26rc-log

    "Duplicate entry '2147483647' for key 'PRIMARY'"

    I've trued several combinations of what has been suggested without any luck. What else can be done?

    Thanks

  • Options
    solvesolve New

    Doesn't anybody have this problem or is there a generic solution? It seems to me that this has been a known MYSQL bug for many months. Since the previously mentioned solution doesn't work for me, I've tried using a different MYSQL version. However the problem continues.

  • Options
    UnderDogUnderDog MVP

    Were you 'fxgeek' last january and now you are 'solve'?


    This is one of the solutions I read from those bug reports:

    The problem is that the integer you want to insert into your field is 7051100001, and that is bigger than the maximum value for your integer field. This results in mysql adding the biggest possible value into the field, that would be 2147483647.

    Each time you try to insert another too big value, you will get the error messge.


    Try changing the column to bigint instead of int

    The real problem is how many categories you have and especially the CategoryID's there number is 'too long'

    There was an error rendering this rich post.

  • Options
    x00x00 MVP

    Your indexes are way too high, there is not way you have 2147483647 categories.

    The auto index value need to be reset.

    grep is your friend.

  • Options
    solvesolve New
    edited October 2012

    Hi UnderDog. No, I've just stumbled upon Vanillaforums and thought I give it a shot.

    • It's a fresh installation and I'm trying to add the 1st category (aside from the General category that has already been added by default)
    • How can I change the columns to bigint?

    Dear x00

    • I've tried to follow the previous steps to reset the index (command / changing value to 1) but I'm still getting the same error.

    What else can I do? Are there any specific steps I should follow?

    Thanks in advance.

  • Options
    x00x00 MVP

    have you set the category root to 1 then reset the index? then set the category root back to -1?

    This is a mysql bug, it isn't really the fault of vanilla.

    grep is your friend.

  • Options
    solvesolve New
    edited October 2012

    Hi x00,

    Yes, I'm aware it's a MYSQL bug but it should affect almost anybody who installs the community edition. I mean, I'm on a pretty recent release and it appears to date back quite a few revisions.

    Anyway, I've tried it again:
    1) Set CategoryID root to 1
    2) Run SQL command ALTER TABLE GDN_Category AUTO_INCREMENT = 5;
    3) Set CategoryID root to -1

    I'm still getting the same error.

  • Options
    x00x00 MVP
    edited October 2012

    run

    describe GDN_Category;

    and

    select CategoryID , ParentCategoryID , TreeLeft , TreeRight , Depth, Name from GDN_Category;

    and post the results

    grep is your friend.

  • Options
    x00x00 MVP

    sorry the category root should be set to 0 not 1

    grep is your friend.

  • Options
    solvesolve New

    I appreciate your help. I've attached screenshots for the results.

Sign In or Register to comment.