mySQL Duplicate Entry Error

[Navigator]

I keep getting an error with two separate databases where I get an error like:

"#1062 - Duplicate entry '127' for key 1 "

The key being:
`id` tinyint(255) NOT NULL AUTO_INCREMENT PRIMARY KEY

(^ Sorry if I typed that in the wrong syntax I normally use the phpMyAdmin to make tables ^)

But anyway, I have gotten this error in two databases and in multiple tables. I've gotten it both on the internet page it's self and in phpMyAdmin. I have already contacted the host they claim there is no error, but obviously there is. My dad say's it's with my code which I don't see how.

I have also done this:
- Deleted and remade the id field
- Copied all information to another table with the same fields (still got the same error)


Another thing is I go into the Operations tab in phpMyAdmin and under "Table options" in the "auto_increment" field it recognizes that the auto_increment is at 128 but it puts 127. On the Structure page, in the list of indexes it displays the "Cardinality" of the id field as 126. I am not sure but that sounds off.

If I delete the rows with 126 and 127 I can add a row after that but I get the error again. It also seems to forget which number it's on in the increment. Shouldn't it, if you delete row 127 and 126, then start at 128?

[Fou-Lu]

Tinyint is too small.
Since its signed, it only has a maximum value of 127, after that it has only one place to go: -128. Since it can't go there (auto_increment) its only choice is to stay where it is.
Deleting the rows itself doesn't reset you're primary key pointer (the cardinality actually lists the value for the next). Repair it first and you'll find that two more insertions will hit the same problem.

Upgrade the column type to an unsigned integer value. This will allow you, lesse... 4294967295 keys I believe it is (2^32) - 1. Thats a lot better than the tinyint (2^8) - 1 or 255. The difference between this and what you have is signed versus unsigned, the field you have allows negative numbers so the two's compliments notation steals the leftmost bit as a negative indicator (so you can go from -128 - 127 instead of 0 - 255).

Simply change you're field type to int (10) unsigned not null auto_increment primary key to fix.