Get specific MySQL/MariaDB error info

[NotionCommotion]

I wish to respond specifically to PDO errors.  $e->getMessage() shows something like the following:

• SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails...
• SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ...

I suppose I can just look for 1452 and 1062, but thought using http://php.net/manual/en/pdo.errorcode.php would be better, but it is empty.  I am using Server version: 10.2.18-MariaDB MariaDB Server.  Any suggestions?

Thanks

try{
    $this->pdo->prepare($sql)->execute([$value, $id, $this->accountsId]);
}
catch(\PDOException $e) {
    syslog(LOG_ERR, json_encode($this->pdo->errorCode()));
    syslog(LOG_ERR, json_encode($this->pdo->errorInfo()));
}

"00000"
["00000",null,null]

 

[benanamen]

The error message could not be any more clear. You are attempting to insert/update/delete a column with a duplicate constraint with data that is already in the DB and incorrectly messing with data in a parent table that has child data.

Edited January 17, 2019 by benanamen

[NotionCommotion]

4 minutes ago, benanamen said:

The error message could not be any more clear. You are attempting to insert/update/delete a column with a duplicate constraint with data that is already in the DB

I have no issues with the clarity of the error message.  Why does PDO::errorCode() and PDO::errorInfo() provide no content?

[benanamen]

I don't understand what you are looking for. What "content" do you want? Are you asking how to handle the error? If so, you can try/catch the error and respond the way you want.

From the manual

Quote

PDO::errorCode() only retrieves error codes for operations performed directly on the database handle. If you create a PDOStatement object through PDO::prepare() or PDO::query() and invoke an error on the statement handle, PDO::errorCode() will not reflect that error.

Source: http://php.net/manual/en/pdo.errorcode.php

Edited January 17, 2019 by benanamen

[Barand]

Perhaps there is something here that might help

[NotionCommotion]

Thanks benanamen, I missed that part.  Looks like I need to look into http://php.net/manual/en/pdostatement.errorcode.php

[NotionCommotion]

5 minutes ago, Barand said:

Perhaps there is something here that might help

I don't think this will help determine whether a duplicate or fk issue.

[Barand]

Oh yes it will

try {
    $db->exec("INSERT INTO units(id, unit) VALUES (1, 'Test')");      // deliberate duplicate 
}
catch (PDOException $e) {
    echo '<pre>', print_r($e->errorInfo, 1), '</pre>';
}

Outputs

Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '1' for key 'PRIMARY'
)

 

[NotionCommotion]

Thanks Barand,  I stand corrected.