PHPmyAdmin

[creole]

OK...here is a strange problem. I am getting this error when I try this INSERT statement.

INSERT INTO po_gallery_images (gid, id, thumb_image, large_image, text) VALUES ('10', '', 'misc_01_thumb.jpg', 'misc_01.jpg', '')
MySQL said: Duplicate entry '127' for key 1

I first got it with PHPMyAdmin, then I tried it command line and got the same error. I don't know where it is coming from. Here is a description of my table.

FIELD        TYPE        NULL    EXTRA
gid          tinyint(4)  No  0 
id           tinyint(6)  No    auto_increment
thumb_image  tinytext    No   
large_image  tinytext    No   
text         tinytext    Yes

I would guess that key 1 would be the GID? If so, then the error must be wrong because there are only 10 entries (1-10) in that table so far. I just looekd at the number of entries in the table so far. There are 127 entries. It seems as if the table is corrupted or is only allowing me a certain number of entries and I know that has to be wrong.

I even tried to enter 128 in the ID field manually and it gave me the same error.

Anyone have any ideas?

[freddydoesphp]

Did you try the old

INSERT INTO po_gallery_images set gid = 10, thumb_image = 'misc_01_thumb.jpg', large_image = 'misc_01.jpg';

[Kevin Yank]

Your ID column is maxed out. TINYINT is too small a number for use as a primary key in anything but the smallest tables, because it only allows values between -128 and 127.

Try:

ALTER TABLE po_gallery_images CHANGE COLUMN ID ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY

This will change your ID column into an UNSIGNED INT, which should last you considerably longer.
<Edited by kyank on 01-23-2001 at 11:15 PM>

[freddydoesphp]

Kevin just out of curiosity's sake what would be the max for tinyint(4) I thought it would be 9999 but apparently I am mistaken, or is 7 * 4 or something?

[Kevin Yank]

See updated message above. TINYINT allows numbers from -128 (-2^7) to 127 (2^7-1). UNSIGNED TINYINT allows from 0 to 255 (2^8-1).
<Edited by kyank on 01-23-2001 at 11:18 PM>

[freddydoesphp]

Yeah I just ran a test and got the same error at 127 as Andy, I was just curious I always use int(11) UNSIGNEd for my auto-increment PRIMARY KEY fields.

[Kevin Yank]

Yup, UNSIGNED INT allows numbers from 0 to 4294967295 (2^32-1). Much more breathing room.

[creole]

man...I knew it had to be something like that...you guys are a lifesaver...I usually do unsigned anyway because I never work with negative numbers. I guess I just must have forgotten.