Integrity constraint violation: 1062 Duplicate entry - but no duplicate lines
I am converting an application from native mysqli calls to PDO. An error occurred while trying to insert a row into a table with a foreign key constraint.
Note: This is a simplified test version and should not be copied / pasted into a production environment.
Info PHP 5.3, MySQL 5.4
First, here are the tables:
CREATE TABLE `z_one` (
`customer_id` int(10) unsigned NOT NULL DEFAULT '0',
`name_last` varchar(255) DEFAULT NULL,
`name_first` varchar(255) DEFAULT NULL,
`dateadded` datetime DEFAULT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `z_one` VALUES (1,'Khan','Ghengis','2014-12-17 10:43:01');
CREATE TABLE `z_many` (
`order_id` varchar(15) NOT NULL DEFAULT '',
`customer_id` int(10) unsigned DEFAULT NULL,
`dateadded` datetime DEFAULT NULL,
PRIMARY KEY (`order_id`),
KEY `order_index` (`customer_id`,`order_id`),
CONSTRAINT `z_many_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `z_one` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Or, if you like,
mysql> describe z_one;
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| customer_id | int(10) unsigned | NO | PRI | 0 | |
| name_last | varchar(255) | YES | | NULL | |
| name_first | varchar(255) | YES | | NULL | |
| dateadded | datetime | YES | | NULL | |
+-------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe z_many;
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| order_id | varchar(15) | NO | PRI | | |
| customer_id | int(10) unsigned | YES | MUL | NULL | |
| dateadded | datetime | YES | | NULL | |
+-------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Next, here's the request:
$order_id = '22BD24';
$customer_id = 1;
try
{
$q = "
INSERT INTO
z_many
(
order_id,
customer_id,
dateadded
)
VALUES
(
:order_id,
:customer_id,
NOW()
)
";
$stmt = $dbx_pdo->prepare($q);
$stmt->bindValue(':order_id', $order_id, PDO::PARAM_STR);
$stmt->bindValue(':customer_id', $customer_id, PDO::PARAM_INT);
$stmt->execute();
} catch(PDOException $err) {
// test case only. do not echo sql errors to end users.
echo $err->getMessage();
}
This results in the following PDO error:
SQLSTATE [23000]: Integrity constraint violation: 1062 Duplicate record '22BD24' for key 'PRIMARY'
The same request works great when processed mysqli
. Why does PDO reject INSERT with 'duplicate entry' message if no duplicates are found?
source to share
Since not all code is available (from the php side), just in case your request is in some form, the fastest (and possibly partial) solution for this is:
$order_id = '22BD24';
$customer_id = 1;
try {
$q = "INSERT INTO `z_many` (`order_id`,`customer_id`,`dateadded`)
VALUES (:order_id,:customer_id,NOW())
ON DUPLICATE KEY UPDATE `dateadded`=NOW()";
$stmt = $dbx_pdo->prepare($q);
$stmt->bindValue(':order_id', $order_id, PDO::PARAM_STR);
$stmt->bindValue(':customer_id', $customer_id, PDO::PARAM_INT);
$stmt->execute();
} catch(PDOException $err) {
// test case only. do not echo sql errors to end users.
echo $err->getMessage();
}
source to share
I copied the SQL schema given in my mysql database and added the script code, but with PDO initialization at the beginning:
$dbx_pdo = new PDO('mysql:host=127.0.0.1;dbname=test12;charset=utf8','root','');
.. and it worked fine , but my setup is php 5.5.9 and mysql 5.6.16
So, I think your code is being executed twice and possibly inside a transaction, which is why you get rolled back. Need to know more context
source to share
DELETE default for primary key column. Also use the construction
INSERT INTO (`field1`, `field2`, `...`) values (val1, val2, val3);
If you specify what is the default for inserts - some versions of mysql may see errors when inserting. This is why you should use auto-increment or not use the default at all.
source to share
Just a shot in the dark. I am using PDO with ORACLE PL / SQL with only bindParam (). And look at the fourth parameter ,15
if it's a value PARAM_STR
. So try this, hope it helps.
$stmt->bindParam(':order_id', $order_id, PDO::PARAM_STR, 15);
$stmt->bindParam(':customer_id', $customer_id, PDO::PARAM_INT);
This 15 describes the length (bufffer-) of the order_id in your table.
source to share