Duplicate entry INSERT problem

swissmissflash

"Duplicate entry '9954850' for key 1"

column type INT(10)

I'm lost, any ideas? 🙂

leatherback

You cannot insert the same value twice ina primary key value. Possibly you have run out f options (What is the value of int(10)? and need to increase the colum to int(11)

swissmissflash

it's auto increment so I dont give it a value... i need to change to int(11) then?

leatherback

well.. the dtaabase tries to give it a value, and the maximum value is automaticaly reduced to the maximum to fit the table.

Try increasing to int(11). However.. You SHOULD make a backup of your database before doing any alterations, as these thing COLD go wrong.

On a different note.. What options do you have to reduce the number of values in the database? I do not know what you are storing in the database, or how you organize it. but it sounds like you have a lt of records..

swissmissflash

The overhead was high on the table... I ran the optimize query and the issue was resolved. Any clue what might cause that? I'm not sure what you mean about 'options to reduce', do you mean delete options?

This table has 7,819,713 rows. It's a good size. 🙂

Sxooter

I think you guys need to look up int(n) on mysql. It doen't do what you think it does.

leatherback

Sxooter,

Willing to elaborate? I assumed the I(n) defined the number of bytes used for storage. Cannot really find anything useful on http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html about it though

Sxooter

leatherback wrote:
Sxooter,

Willing to elaborate? I assumed the I(n) defined the number of bytes used for storage.

The answer you seek is on this page:

http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html

To quote:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

swissmissflash

Guys, can anyone offer some insight on this issue i'm having? int(10) is my column type and I keep getting that error duplicate entry 'xxx' for key 1. This only happens on inserts but it's auto increm. Also, when i check the table in phpMyAdmin, the overhead is really high. Seems to fix the problem when i run the 'optimize query' but i need to know what might be causing this....I read your link but I guess im not grasping the concept.

I really appricate your time thus far. I'll keep reading in the mean time. 🙂

Sxooter

Show us the insert statement.

Oh, and the schema of the table, code that made the statement, etc... The more info you give us the better. But don't give us page after page of code, just the part that's breaking.

leatherback

Thx Sxooter. Learned something new again!

Sxooter

Yeah, I generally consider the int(n) implementation to be a misfeature because it doesn't follow the rule of least surprise. I.e. the obvious thing would be for it to limit the number of digits an int can have, but it affects how they're padded with spaces on retrieval. Seriously, ints shouldn't have any spaces in them, they're ints, not char types.

Of course, MySQL isn't the only database that does things like that. Orcale, for instance, mixes scalar and floating point types in their number implementation, so that there's never a guarantee that you're using an exact data type. It just seems like MySQL has more than its fair share of nasty surprises, i.e. gotchas in it. I use it for text storage / full text indexing and that's pretty much it. I don't trust it for anything where math might be involved. 🙂

Sxooter

So, when you insert to this table, are you trying to insert to the autoincrement column? suppose you have a table like:

create table a (x int autoinc, y varchar(10), z timestamp);

you should insert to it in either of these two ways:

insert into a (y,z) values ('abc','2007-03-01 12:00:00');
insert into a (x,y,z) values (DEFAULT,'abc','2007-03-01 12:00:00');

Note that older version of MySQL use NULL to insert the right value in the autoinc or default. this is a bad habit to get into because A: No other database in the universe does that, cause It's just plain wrong by the SQL standard and B: Later versions of MySQL will start breaking on such things and you'll have to change your code.

Note that if you have to use an earlier version of MySQL that doesn't support the DEFAULT keyword, then use the insert method where you simply don't define the column in the column-list.

swissmissflash

Sxooter,

Thank you for all your help. I REALLY appriciate it.

Yes, my INSERT does use NULL I also found one where I just INSERT with '' for the auto increm value.

This seems to happen once a week. I am going to try and use the method without declaring the primary key at all you mentioned. I have also attached a SS so you can see what is happening and the table structure...

I only have a basic knowlege of MySQL (as you can see 😛) so if you can point out any other improvements I could make to help avoid this issue I would greatly appriciate it.

There is always a high overhead when this happens too... any idea why?

swissmissflash

Duplicate entry '11151256' for key 1

That's the latest one... this happens multiple times in a week.

Anyone know how I can prevent this? 🙁

Not sure if this helps but my table is used by many members who INSERT, DELETE, UPDATE records a lot but for any INSERT queries I DO NOT enter a value for the auto increment value.

Roger_Ramjet

Never in any database whatsoever specify any value whatsoever for an autoincrement column even if you are only specifying DEFAULT. Autoinc columns define their own values and all you can do is to mess this up. Having defined a column as autoinc, you have passed all control of it over to the database, so leave it alone

Now I know, and I am sure that Sxooter can tell you, where this is not true. But the cases where this is not true are so rare that you should just forget about them.