php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #74912 PDO errorInfo 1062 Duplicate entry
Submitted: 2017-07-12 16:02 UTC Modified: 2020-04-07 12:37 UTC
Votes:11
Avg. Score:3.8 ± 1.1
Reproduced:9 of 9 (100.0%)
Same Version:4 (44.4%)
Same OS:4 (44.4%)
From: marc dot w dot hagen at gmail dot com Assigned:
Status: Verified Package: PDO related
PHP Version: 5.6.31 OS: Debian 3.2.86-1 x86_64
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: marc dot w dot hagen at gmail dot com
New email:
PHP Version: OS:

 

 [2017-07-12 16:02 UTC] marc dot w dot hagen at gmail dot com 
Description:
------------
PDOStatement->errorInfo() will have driver errors even if there is a new query that has been executed.
If, for example, there is a duplicated query on the first statement it will return   false and populates the errorInfo.
But the next query that succeeds still has the errorInfo, only array key 1 and 2, from the previous query.
This should be cleared.

Test script:
---------------
<?php
$db = new PDO("mysql:host=".$dboptions['host'].";port=".$dboptions['port'].";dbname=".$dboptions['dbname'], $dboptions['username'], $dboptions['password']);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$data = [ [ 'php', 'rulez' ], [ 'php', 'rulez' ], [ 'php', 'rule' ] ];
$stmt = $db->prepare('INSERT INTO testTable VALUES (?, ?)');
$stmt->bindParam(1, $lang, PDO::PARAM_STR);
$stmt->bindParam(2, $action, PDO::PARAM_STR);
foreach($data as $set) {
    $lang = $set[0];
    $action = $set[1];
    $stmt->execute();
    var_dump($stmt->errorInfo());
}
$db = null;

Expected result:
----------------
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
array(3) {
  [0]=>
  string(5) "23000"
  [1]=>
  int(1062)
  [2]=>
  string(45) "Duplicate entry 'php-rulez' for key 'PRIMARY'"
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}

Actual result:
--------------
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
array(3) {
  [0]=>
  string(5) "23000"
  [1]=>
  int(1062)
  [2]=>
  string(45) "Duplicate entry 'php-rulez' for key 'PRIMARY'"
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  int(1062)
  [2]=>
  string(45) "Duplicate entry 'php-rulez' for key 'PRIMARY'"
}

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2017-07-12 16:11 UTC] martins dot teresko at gmail dot com 
It's not a bug. You need to learn SQL.
 [2017-07-12 16:22 UTC] marc dot w dot hagen at gmail dot com 
Re: martins dot teresko at gmail dot com
Well more information is needed than that. As i see it this has nothing to do with SQL... This was a MySQL database.
 [2017-07-12 16:30 UTC] martins dot teresko at gmail dot com 
From the fine manual: 


"If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out."

https://dev.mysql.com/doc/refman/5.7/en/insert.html
 [2017-07-12 16:33 UTC] martins dot teresko at gmail dot com 
Ah, damn. Nevermind, I missed the error code :(
 [2017-07-12 16:36 UTC] marc dot w dot hagen at gmail dot com 
ah, can happen!
 [2017-09-21 14:17 UTC] serpens dot mors at gmail dot com 
"Duplicate entry 'php-rulez' for key 'PRIMARY'"

Look up this Link: https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html

If you are beginner in using SQL, take a look at this side: https://www.w3schools.com/sql/sql_primarykey.asp
 [2017-09-21 14:35 UTC] marc dot w dot hagen at gmail dot com 
Guys it's not a SQL BUG, it's the error code that not being reset.
Please READ the description and look at the results.
Please look at array(3) of both the Expected result and Actual result
 [2020-04-07 12:19 UTC] cmb@php.net
-Status: Open +Status: Verified
 [2020-04-07 12:19 UTC] cmb@php.net 
I can confirm the broken behavior.  Interestingly, pdo_sqlite3
also repeats the driver specific error code and message, but has
the SQLSTATE error reset to zero.
 [2020-04-07 12:23 UTC] cmb@php.net 
> Interestingly, pdo_sqlite3 also repeats the driver specific
> error code and message, but has the SQLSTATE error reset to zero.

Oops, this is exactly the same as with pdo_mysql.
 [2020-04-07 12:37 UTC] marc dot w dot hagen at gmail dot com 
Running this code on PHP (7.3.16 / 7.4.2 / 7.4.4) still results in same behaviour. 
I do not know nor test it on sqlite.
 [2020-11-05 12:11 UTC] vlobovikov at gmail dot com 
I have same problem with "INSERT ... ON DUPLICATE KEY UPDATE ...".
My PHP have v7.4.11
 [2020-11-05 12:14 UTC] vlobovikov at gmail dot com 
$stmt->execute() === false

AND

erroInfo:

Array
(
[0] => 00000
[1] =>
[2] =>
)
 [2020-11-05 12:18 UTC] vlobovikov at gmail dot com 
INSERT INTO someTable(...) VALUES(...),(...),..

ON DUPLICATE KEY UPDATE
                    field1 = VALUES(field1),
                    field2 = VALUES(field2)


CREATE TABLE someTable (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  subId VARCHAR(255) NOT NULL,
...
  PRIMARY KEY (id),
  UNIQUE KEY k_transaction(subId),
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Apr 25 22:01:29 2024 UTC