Error with inserting into mysql database

Question

I am using cfwheels (coldfusion orm framework).

I recently moved some data from my previous host to a new one. Now I am trying to insert into a table, but am getting an error message: "Error Executing Database Query. Duplicate entry '13651' for key 'PRIMARY'"

I looked into the database and it appears a record with id 13651 already exists. So I think the problem is with mysql generating the right auto increment value.

What happens if you manually write an insert statement with null as the primary key? — Matt Fellows, Jun 26 '12 at 07:59

score 4 · Accepted Answer · answered Jun 26 '12 at 07:51

4

It seems Auto_Increment value is damaged or not set to max value in that column. It's possible due to bulk insert.

So as per solution, set the maximum PK value + 1 as new AUTO_INCREMENT value. Now when you insert the records in this table, they will automatically pick the next incremented correctly.

ALTER.TABLE tablename AUTO_INCREMENT = value

answered Jun 26 '12 at 07:51

Romil Kumar Jain

20,239
9
63
92

thank you... it solved my problem...(y). my inserts work fine now – yomexzo Jun 26 '12 at 09:45
But you can accept the answer by clicking on Right Symbol in front of my answer. – Romil Kumar Jain Jun 26 '12 at 10:16

score 0 · Answer 2 · answered Jun 26 '12 at 07:22

0

Is the rest of the data for that record, and the one you are trying to insert, the same? If you you might just need to tell the ORM to replace that value?

answered Jun 26 '12 at 07:22

Matt Fellows

6,512
4
35
57

they are different... entirely – yomexzo Jun 26 '12 at 07:32

score 0 · Answer 3 · answered Jun 26 '12 at 07:27

0

If primary key has auto increment attribute turned on, do not insert it manually. remove that primary key part from your insert query (whatever the syntax according to the taste of your ORM framework).

answered Jun 26 '12 at 07:27

manurajhada

5,284
3
24
43

it doesn't insert the id... it used to work normally.. it started this error after migrating the data – yomexzo Jun 26 '12 at 07:33
ohh ok, so issue is data migration, I suggest you if both data are same with the primary id then you need to truncate table and import again, Or just import the data without their respective primary id if it is new data for the table. – manurajhada Jun 26 '12 at 07:49

Error with inserting into mysql database

3 Answers3