Problem Adding Categories

x00

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;

x00

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

fxgeek

I did that but the next index is still the same

Is there a way to reset it completely?

x00

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

fxgeek

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 !

x00

That is almost certainly a bug

wpbizbundle

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.

fxgeek

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

wpbizbundle

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

wpbizbundle

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

solve

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

solve

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.

UnderDog

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'

x00

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

The auto index value need to be reset.

solve

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.

x00

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.

solve

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.

x00

run

describe GDN_Category;

and

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

and post the results

x00

sorry the category root should be set to 0 not 1

solve

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