Detect mysql update/insertion failure due to violated unique constraint

Question

This is kind of similar to this question:

PHP MySQL INSERT fails due to unique constraint

but I have a different twist. Let's say I have a table with only one column. The column's name is "title" and it has a unique constraint placed on it.

First I insert a row where title = "something". The next time I try to insert "something" it will fail due to a unique key constraint (which is good). What I'd like to do is allow it to fail, and check the error code provided by mysql to ensure it failed due to a unique key constraint. (i.e. let the database handle the uniqueness, and I just handle the error code and tell the user that title already exists when the result comes back).

Is there a way to do this?

Answer 1

Now that it's the year 2015, there are very few reasons not to be using PHP's PDO implementation.

The proper, modern, "OO" method for detecting and handling an insertion failure due to a key constraint violation is as follows:

try {
    //PDO query execution goes here.
}
catch (\PDOException $e) {
    if ($e->errorInfo[1] == 1062) {
        //The INSERT query failed due to a key constraint violation.
    }
}

The PDOException object has a lot more to say about the specific nature of the error, too (more detail than one could possibly ever want or need, seemingly).

Answer 2

http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

http://php.net/manual/en/function.mysql-errno.php

I've had to do this in the past, and it's not fun:

if( mysql_errno() == 1062) {
    // Duplicate key
} else {
    // ZOMGFAILURE
}

A note on programming style (Credits to jensgram from this answer)
You should always seek to avoid the use of magic numbers. Instead, you could assign the known error code (1062) to a constant (e.g. MYSQL_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

Answer 3

Topic is of interest for fellow PHP/Mysql users so let me outline a solution. Please note

1. • There is no magical portable way to do it
2. • situation is not unique to PHP, if you want to detect DB2 unique key constraint violation with openJPA - you have to restore to similar kind of handling

Suppose you have a form - where you have a field "Name"

1) In the DB table

Add a unique constraint like -

alter table wb_org add constraint uniq_name unique(name);

2 ) The form handler script

The form handler script should pass the data to DB layer and if there are any errors, the DB layer would signal it as an DBException (An exception defined by us). we wrap the code sending data to DB layer in a try-catch block (only relevant code is shown)

try{

        .... 
        $organizationDao = new \com\indigloo\wb\dao\Organization();
        $orgId = $organizationDao->create($loginId,$fvalues["name"]) ;
        ....

    } catch(UIException $ex) {

       ....
       // do UI exception handling

    } catch(DBException $ex) {

        $errors = array();
        $code = $ex->getCode();
        $message = $ex->getMessage();

        // look for code 23000, our constraint name and keyword duplicate 
        // in error message thrown by the DB layer
        // Util::icontains is just case-insensitive stripos wrapper

        if( ($code == 23000)
            && Util::icontains($message,"duplicate")
            && Util::icontains($message,"uniq_name")) {
                $errors = array("This name already exists!");
        } else {
            // Not sure? show generic error
            $errors = array(" Error: doing database operation!") ;
        }

        // log errors
        Logger::getInstance()->error($ex->getMessage());
        Logger::getInstance()->backtrace($ex->getTrace());

        // store data in session to be shown on form page
        $gWeb->store(Constants::STICKY_MAP, $fvalues);
        $gWeb->store(Constants::FORM_ERRORS,$errors);

        // go back to form 
        $fwd = base64_decode($fUrl);
        header("Location: " . $fwd);
        exit(1);


    }catch(\Exception $ex) {

       // do generic error handling
    }

Please note that you have to find the ex->getCode() for your situation. Like in above, the PDO layer is actually throwing back the SQLSTATE 23000 as ex->code ( where the actual mysql error code is 1062). The code can vary from DB to DB also. Same way ex->message can also vary. It would be better to wrap this check in one place and fiddle using a configuration file.

3) inside DB layer (using PDO)

 static function create($loginId, $name) {
            $dbh = NULL ;

            try {

                $dbh =  PDOWrapper::getHandle();
                //Tx start
                $dbh->beginTransaction();
               ...
               // do DB operations
               //Tx end
                $dbh->commit();
                $dbh = null;

            } catch(\Exception $ex) {
                $dbh->rollBack();
                $dbh = null;
                throw new DBException($ex->getMessage(),$ex->getCode());
            }

4) Back on the form (after hitting form Handler => DB Layer => Form Handler error handler => Form)

Extract error messages set in session and display them on the form.

5) DBException class

<?php

namespace com\indigloo\exception {

    class DBException extends \Exception  {

        public function __construct($message,$code=0, \Exception $previous = null) {
            // PDO exception etc. can return strange string codes
            // Exception expects an integer error code.
            settype($code,"integer");
            parent::__construct($message,$code,$previous);
        }

    }
}

?>

6) icontains utility method

 static function icontains($haystack, $needle) {
        return stripos($haystack, $needle) !== false;
    }

Can we do this without exceptions and PDO?

7) without PDO and using only mysqli

Get error code and error message from mysqli and throw DBException from DB layer Handler the DBException same way.

8) Can we do this w/o using exceptions?

I am writing this without any experience of actually doing it in live code. So please let me know if you do not agree. Also, please share if you have a better scheme. if you just want a catch-it-all generic sort of handler then yes.

• inside the DB layer: raise errors using trigger_error instead of throwing exceptions. inside trigger_error method - use some MAGIC_STRING + DB_CODE
• define a custom error handler for form handler page
• inside your custom error_handler for form handler : look for MAGIC_STRING + code
• if you get MAGIC_STRING + code then
  • set appropriate message in session
  • forward to form page
  • display a custom message set in session

The problem I find with trigger_error and error_handlers is that

• you cannot trap them in the flow of execution like you can do with exceptions. However this is not a problem in our case because our error_handler for page just needs to redirect to form page.
• I do not know a way to raise specific error codes (what code I want) with trigger_error method. If only it were possible to raise an error with code X and our message Y. So far as I know you cannot do that. That is why we are restoring to parsing every error_message that our error_handler receives.

I do not have much experience working with error codes (I have been raised on exceptions) - so maybe someone else can enlighten us.

The code samples are from my public github repo https://github.com/rjha/website - code that I am writing for create a website builder to launch thousands of sites from same DB. The code above is used to check unique name for a website.

Answer 4

I believe the error code for duplicate keys is 1586. If you were to attempt to execute a query and then, on failure, check the error code using mysql_errno()/mysqli::errno() and compare it to 1586, that should do it. If it's not 1586, check what it actually is by echoing the error code after your query.

Answer 5

Why not just do a select first to see if the entry already exists. Or suppress an error altogether by using INSERT ON DUPLCATE KEY UPDATE, or even use the mysql IGNORE keyword. Why purposely cause an error?

Answer 6

From PHP Documentation on the function mysql_errno:

Returns the error number from the last MySQL function, 
or 0 (zero) if no error occurred. 

Also, from MySQL Documentation on Constraint Violation Errors, error code 893 corresponds to:

Constraint violation e.g. duplicate value in unique index

So, we can then write something like this to do the work:

if (!$result) {
    $error_code = mysql_errno();
    if ($error_code == 893) {
        // Duplicate Key
    } else {
        // Some other error.
    }
}

Answer 7

If you know some SQL, try this solution (tested)

$username = "John";
$stmt = $pdo->prepare("
    INSERT INTO users (
        username
    ) SELECT * FROM (
        SELECT :username
    ) AS compare
    WHERE NOT EXISTS (
        SELECT username 
        FROM users 
        WHERE username = :username
    ) LIMIT 1;
");
$stmt->bindParam(":username", $username);
if ($stmt->execute()) {
    if ($stmt->rowCount() == 0) {
        echo "Dublicate Username, ".$username." already exists.";
    } else {
        echo $username." not in use yet.";
    }
}