Problem Adding Categories

I've just installed Vanilla forums and I was trying to add some categories but I keep getting an error. I enabled detailed errors and it tells me:

Duplicate entry '2147483647' for key 'PRIMARY'

Does anyone have any idea how to solve this problem?

please help!!!

Thanks in advance Thomas

fxgeek

After a bit more investigation, what I think is going wrong is that every tim it tries to add a category it uses the same key as the existing one if that helps? Anyone any ideas?

Tim

2147483647 is actually MAX SIGNED INT. Looks like something is up with your database. PHP version, MySQL version, Vanilla version?

fxgeek

Hi,

PHP is version 5.2.17

MySQL is version 5.0.32

Vanilla is the latest version

fxgeek

I did a complete clean install and it's doing the exact same thing. Every other aspect seems to work fine.

422

Perhaps post copy of your php.ini for the devs to check , i only ever had problems With pdo and bwlimited .. I really dont get under the hood.

fxgeek

I'm not sure my hosting provider will let me access the php.ini file. I will check.

422

Create a file called phpinfo.php and put this in it.

<?php phpinfo(); ?>

Upload that file anywhere on your website and call it up in your browser.

fxgeek

Ok - here's the result:

community.theapertureblog.com/phpinfo.php

422

Looks ok, im no expert. Far from it, suhosin has give us grief before. Hopefully one of the guys here can help

fxgeek

Thanks for your help anyway - I was able to manually create categories by editing the database in the sql editor - but I still can't create them directly.

422

You did install correctly? And 777 appropriate folders. Have u deleted all .ini files in cache folder.

Sometimes u have go to via cpanel filemanager to set permissions... Clutching at straws for you.

fxgeek

yep, did all that!

x00

http://forums.mysql.com/read.php?10,53986,53986

x00

hm categories you say. Sound like an increment problem

I believe root is -1, is the column signed or unsigned? Perhaps the default is to make it unsigned, and that is causing the problem.

fxgeek

I'm not brilliant with SQL - how do I tell?

x00

In phpMyAdmin go to GDN_Category

First root CategoryID should be -1 the first row.

then look at the structure of the table. There is an auto increment on CategoryID

at the bottom it says Next Autoindex, what does it say.

fxgeek

Ok, Category ID is -1

Next Autoindex says: 4,294,967,305

Here's a screenshot of the structure:

dl.dropbox.com/u/9380800/php-structure.png

422

Wow

Tim

You can manually fix the auto index with:

ALTER TABLE GDN_Category AUTO_INCREMENT = 5;

fxgeek

Ok, I did that and now it says next auto index = 18,446,744,073,709,552,000

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!