Mysql error

Tanax · 03-10-2009, 02:14 PM

Hi!

What's causing this error? :S

Quote:

INSERT INTO `images` ( `image_id` , `image_productid` , `image_src` , `image_front` )
VALUES (
NULL , '', 'includes/images/palmyra10sl.jpg', ''
)

#1062 - Duplicate entry '127' for key 1

I have 127 entries in that table. I'm adding another entry, manually via phpmyadmin. The image_id is set to primary key. When I add the entry, I've tried leaving image_id blank(meaning it SHOULD add it to the next available key), and also tried putting it to 128, but it doesn't work either :S:S

etoolbox · 03-10-2009, 09:59 PM

What field type and size is your primary key?

codeguy · 03-10-2009, 10:45 PM

Since you seem to expect image_id to increment from submitting a null value, I assume auto_increment was specified for image_id.

my guess is the same as where i think etoolbox was going. Use a larger int (guessing image_id is tinyint) small int/medium int/int (i'd prolly recommend Int).

tony · 03-10-2009, 10:45 PM

Without knowing the type like etoolbox said it's kind of hard.
If it is a primary key, it is not safe to leave it a null, so the record is not created. That could be the reason.
If the image_id is set to auto_increment, you don't need to make an explicit insert with that field, something like this would work:

Code:

INSERT INTO images(image_productid, image_src, image_front)
VALUES ('', 'includes/images/palmyra10sl.jpg', '')

But I might be wrong, I just finished a project working with Access SQL so that might not be right. If that is not right try to save the default value like this:

Code:

INSERT INTO images(image_id, image_productid, image_src, image_front)
VALUES (DEFAULT, '', 'includes/images/palmyra10sl.jpg', '')

I am no expert in mysql though, this is just speculations, but maybe it would help.

etoolbox · 03-10-2009, 10:58 PM

My guess is it's a tinyint with a max integer value of 127. You cannot insert a value > 127 into this field. What MySQL "helpfully" does is to try to make it the highest value if it's not specified and because that key already exists you get a "duplicate entry" error when in fact that's not the real error, it's that you have an overflow on the PK.

Salathe · 03-10-2009, 11:03 PM

Quote:

Originally Posted by codeguy

Use a larger int (guessing image_id is tinyint) small int/medium int/int (i'd prolly recommend Int).

I'd also go with the unsigned tinyiny column theory. The tinyint (when unsigned) has a max value of 127. Trying to insert a new auto_incremented value higher than that will indeed raise the "Duplicate entry '127' for key 1".

Tanax · 03-11-2009, 12:09 AM

Wow, alot of replies

Quote:

Originally Posted by etoolbox

What field type and size is your primary key?

image_id tinyint(3)

Quote:

Originally Posted by codeguy

Since you seem to expect image_id to increment from submitting a null value, I assume auto_increment was specified for image_id.

my guess is the same as where i think etoolbox was going. Use a larger int (guessing image_id is tinyint) small int/medium int/int (i'd prolly recommend Int).

Indeed you are correct to assume that. I have it auto_increment.
You're good at guessing

Thanks! I'll try changing it

Quote:

Originally Posted by tony

Without knowing the type like etoolbox said it's kind of hard.
If it is a primary key, it is not safe to leave it a null, so the record is not created. That could be the reason.
If the image_id is set to auto_increment, you don't need to make an explicit insert with that field, something like this would work:

Code:

INSERT INTO images(image_productid, image_src, image_front)
VALUES ('', 'includes/images/palmyra10sl.jpg', '')

But I might be wrong, I just finished a project working with Access SQL so that might not be right. If that is not right try to save the default value like this:

Code:

INSERT INTO images(image_id, image_productid, image_src, image_front)
VALUES (DEFAULT, '', 'includes/images/palmyra10sl.jpg', '')

I am no expert in mysql though, this is just speculations, but maybe it would help.

Yea, I think I already know now what the problem is based on what the others said

Quote:

Originally Posted by etoolbox

My guess is it's a tinyint with a max integer value of 127. You cannot insert a value > 127 into this field. What MySQL "helpfully" does is to try to make it the highest value if it's not specified and because that key already exists you get a "duplicate entry" error when in fact that's not the real error, it's that you have an overflow on the PK.

Thank you for the information, I'll be sure to remember it next time

Quote:

Originally Posted by Salathe

I'd also go with the unsigned tinyiny column theory. The tinyint (when unsigned) has a max value of 127. Trying to insert a new auto_incremented value higher than that will indeed raise the "Duplicate entry '127' for key 1".

Thanks alot! I'll edit this error asap

tony · 03-11-2009, 05:05 AM

Great, Let us know how you fix it then ;)

Tanax · 03-11-2009, 05:35 PM

Quote:

Originally Posted by tony

Great, Let us know how you fix it then ;)

I just changed the column to int(11) unsigned, and it's working great now

tony · 03-11-2009, 09:03 PM

Good! I tend to forget about the sizes of the numeric fields too.

Tanax · 03-11-2009, 10:54 PM

I certainly did that now xDD But luckily talkphp exists