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

neuhaus neuhaus · Accepted Answer · 2016-02-10T10:02:04

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.

Unique key error as duplicate key in mysql perl script

1 Answers