Unique key error as duplicate key in mysql perl script

Question

I have one sql table, which has Primary key which is combination of two columns of same table as shown below:

PRIMARY KEY (`CONN_ID`,`PWORD`)

I am migrating the data from one database to another database using perl script.

$select = $dbh1->prepare("SELECT * FROM db2");
$insert = $dbh2->prepare("INSERT INTO db1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");

$dbh2->do( 'SET FOREIGN_KEY_CHECKS=0;' );
$select->execute;
while (  my($PWORD,$CASE,$SPECIAL_XP,$ENA_TRANS,$TRANS,$DESC,$COMP,$SER,$CONN_ID,$S_ID,$EXT) = $select->fetchrow_array )
{
  $insert->execute($PWORD,$CASE,$SPECIAL_XP,$ENA_TRANS,$TRANS,$DESC,$COMP,$SER,$CONN_ID,$S_ID,$EXT,'NULL');
}
$dbh2->do( 'SET FOREIGN_KEY_CHECKS=1;' );

While running the script i am getting the following error, how to overcome that error and copy the data without any discrepancies.

DBD::mysql::st execute failed: Duplicate entry '10560-???' for key 'PRIMARY' at ./upgrade.sh.

Table Schema:

CREATE TABLE `MDB_KEYWORD` (
`PWORD` varchar(255) NOT NULL,
`CASE` char(1) DEFAULT NULL,
`SPECIAL_XP` char(1) DEFAULT NULL,
`ENA_TRANS` char(1) DEFAULT NULL,
`TRANS` varchar(300) DEFAULT NULL,
 `DESC` varchar(500) DEFAULT NULL,
`COMP` blob,
`SER` bigint(20) NOT NULL,
`CONN_ID` bigint(20) NOT NULL,
`S_ID` bigint(20) DEFAULT NULL,
`EXT` bigint(20) DEFAULT NULL,
`U_LIST` char(1) DEFAULT NULL,
PRIMARY KEY (`CONN_ID`,`KEYWORD`),
KEY `FK_KEYWORD_SERVICECONN` (`SER`),
KEY `FK_KEYWORD_SERVICE` (`S_ID`),
KEY `FK_KEYWORD_EXTID` (`EXT`),
CONSTRAINT `FK_KEYWORD_DRIVERCONN` FOREIGN KEY (`CONN_ID`) REFERENCES     `PDB_PROFILE` (`PR_ID`),
CONSTRAINT `FK_KEYWORD_EXTID` FOREIGN KEY (`EXT_ID`) REFERENCES `PDB_PROFILE` (`PR_ID`),
CONSTRAINT `FK_KEYWORD_SERVICE` FOREIGN KEY (`S_ID`) REFERENCES `PDB_PROFILE` (`PR_ID`),
CONSTRAINT `FK_KEYWORD_SERVICECONN` FOREIGN KEY (`SER`) REFERENCES `PDB_PROFILE` (`PR_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The only difference between the tow tables is :

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; DB1

ENGINE=InnoDB DEFAULT CHARSET=latin1; DB2

Try [on duplicate key ignore](http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) or [check this](http://stackoverflow.com/a/4596409/5260590) — Pionas, Feb 10 '16 at 08:13
@Pionas If i ignore the duplicate key, then it will not insert. i want all data to be migrated from db1 to db2 ignoring that duplicate key error. — ashu, Feb 10 '16 at 08:25
You try `INSERT IGNORE`? Maybe remove the primary key and add it to the end? — Pionas, Feb 10 '16 at 08:28

score 0 · Answer 1 · edited Feb 10 '16 at 11:42

0

You can't have two rows with the same primary key. It appears that you have two rows with CONN_ID = 10560 and PWORD = ??? in your data set.

Remove the primary key or clean up your data (get rid of the rows with identical primary keys).

Another possible cause of the problem is the SELECT * and the INSERT INTO db1 VALUES .... You should always name the columns you want to retrieve and insert to avoid problems with column order etc.

I saw that the table schema has

PRIMARY KEY (`CONN_ID`,`KEYWORD`)

which is different from

PRIMARY KEY (`CONN_ID`,`PWORD`)

PS: This question isn't really perl-related as-is.

edited Feb 10 '16 at 11:42

mscha

6,509
3
24
40

answered Feb 10 '16 at 10:02

neuhaus

3,886
1
10
27

sorry, its typo mistake PRIMARY KEY (`CONN_ID`,`PWORD`) is right for both db – ashu Feb 10 '16 at 10:39
no, if do insert ignore, it just doesnt throw error, no data is inserted – ashu Feb 10 '16 at 10:43
Just print every row before you insert it then you will see which row(s) is causing the problems. – neuhaus Feb 10 '16 at 10:47
See the PWORD and CONN_ID in db1 : `PWORD ` varchar(255) COLLATE utf8_bin NOT NULL..... in db2 its schema is: `PWORD ` varchar(255) NOT NULL, does this cause issue? – ashu Feb 10 '16 at 10:48
insert a line in the while loop before `$insert->execute`: `print "'$PWORD'\t'$CONN_ID'\n";` – neuhaus Feb 10 '16 at 10:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103072/discussion-between-neuhaus-and-ashu). – neuhaus Feb 10 '16 at 10:52

Unique key error as duplicate key in mysql perl script

1 Answers1