Topic: MYSQL auto_increment not incrementing/something's wrong..

[Dragen]

Hi,
I'm having a strange problem when I try to add something to a table in my database.
MYSQL server: 5.1.32 & 5.0.77 (I've tried both)

I have a table called store_items, which as an id Primary Key, which auto increments.
I have been using it and adding/removing rows from it with no problem, but it now seems that the auto_increment value is 'stuck'.
I've just added a 127th row and when I try and add another it throws up this error:

Code: [Select]

SQL Query:

INSERT INTO `daisymoon`.`store_items` (
`id` ,
`name` ,
`price` ,
`qty` ,
`cat_id` ,
`keywords` ,
`description` ,
`weight` ,
`i_date` ,
`live`
)
VALUES (
NULL , 'test', '1.00', '1', '1', '', '', '', '2010-03-16 13:54:20', '1'
)


MySQL Said:

#1062 - Duplicate entry '127' for key 'PRIMARY'
But as you can see, I'm not entering an id :/
I've also tried setting the id to 128 in the SQL statement, which still brought up the same error.
I then tried changing the auto_increment value on the 'Operations' page in MyAdmin.. same problem.

Does anyone have any advice?
Surely there's no limit to the auto_increment value?? I can't think what would cause this to happen.

Kind regards.

[PFMaBiSmAd]

Doesn't that suggest to you that the column definition being used can only hold that many values, such as if it were a TINYINT?

[Dragen]

Thank you so much!
I feel rather silly now, as I'd never actually looked into how large a number a tinyint could hold and it didn't even cross my mind..

I've changed it to an int now and it works fine.

Thanks again!