#1062 - Duplicate entry '32767' for key 1

but I'm using auto_increment

[turbohost]

Hi Guys,

Got an annoying problem. I can't add extra rows in my table because Mysql always generates this error message : "#1062 - Duplicate entry '32767' for key 1". I'm using auto_increment for this key though. I already tried to repair the table and delete/add the primary key again but this has no effect. Somebody knows a solution for this? Another strange thing : when I remove the primary key and add a new row key 1 is always set at 32767 although I enter 32768 and 32769. Very strange

Turbo

[bcolflesh]

There is a relevant bug report here:

[bugs.mysql.com...]

and they can't reproduce it - anytime this has occurred to me, it was my own programming error because I was accidentally trying to update the value of the auto_increment field.

[LifeinAsia]

Just a random thought- what's the dataype for the primaty key?

[turbohost]

ok guys, solved it myself. Changed the data type from smallint to int and now I can add new rows again. Thx for all the replies.

[LifeinAsia]

Changed the data type from smallint to int and now I can add new rows again.

Yup- that's what I suspected, given the number it was bombing on.

[txbakers]

I have a funny (now) story about datatypes.

When I first converted to mySQL from Access I didn't know much. The Access field was a number. The number would never get larger than 400, so I figured a tinyint datatype would be good at 3 digits.

I transferred all my data and people started complaining - everything was reverting back to the item which had the code of 255. I was baffled and embarassed why I couldn't get the database to accept anything about 255.

Then it hit me like a ton of bricks! 3 BYTES not 3 DIGITS! 255 was the max. So, a quick change of datatype, and a very quick restore from the Access file.

Wasn't funny then, very scary then. Funny now.