Mashq and Machine

A curious error bumped into me this week while using Doctrine2 at work, when users try to re-register with a previously used username or email (its always their own username or email). A PDOException is always raised in these cases when we save to the database, as it should. The problem emerges when we have to recover from the error.

You see, when working with MySQL, I have to catch a PDO Exception like so:

try {
    $app['doctrine.orm.en']->flush();
}
catch (PDOException $e) {

}

The default message provided by $e->getMessage() was developer friendly but not user friendly, and so we must produce a better one for the re-registrant. I do this by rethrowing a custom exception:

try {
    $app['doctrine.orm.en']->flush();
}
catch (PDOException $e) {
    $email = ...;
    $username = ...;
    throw new DuplicateException("
        Are you sure you did not already register? 
        Your username '{$username}' and/or email address '{$email}' 
        are already in our system.
        If this is you, please just login>.
    ");
}

But, what if its not really a duplicate field erorr? Ok, I have an answer for that, too! So, when I checked $e->getCode(), its always ‘23000’, which is just a generic SQL state code. That didn’t help me at all; instead, I needed the driver-specific error codes. These error codes are gotten through PDO::errorInfo and PDOStatement::errorInfo. I just did this:

$pdo_error = $app['doctrine.orm.em']->getConnection()->errorInfo();
if ( in_array($pdo_error[1], array('1022','1062','1052','1169')) ) {
    throw DuplicateException();
}

The 2nd element of the returned array (index [1] since we count from zero in PHP) was the exact driver-specific error code we need. See the bottom of this post for a full list of relevant mysql error codes.

But, here’s the real kick in the face: getConnection()->errorInfo() was only populated when the Account entity’s username is a duplicate:

Array
(
    [0] => 23000
    [1] => 1062
    [2] => duplicate column 'username': ....
)

But it was empty for the Email entity, even though $e->getMessage() was fully there and informative:

Array
(
    [0] => 00000
    [1] => null
    [2] => null
)

The PHP docs say that values after key [0] will be null if there is no SQL state code, so this is kind of expected, but a real downer.

However, when I dumped PDOStatement::errorInfo by extracting it from the PDOException, the email’s errorInfo array is fully present and accounted for. Sigh. This means more complicated logic to ensure we’re dealing with a bona fide duplicate error. It would be a shame to throw an inaccurate exception, wouldn’t it? Thus:

try {
    $app['doctrine.orm.en']->flush();
}
catch (PDOException $e) {
    /* only contains error for duplicate usernames */
    $pdo_error = $app['doctrine.orm.em']->getConnection()->errorInfo();
    if ( empty($pdo_error[1]) ) {
        /* only contains error for duplicate emails */
        $pdo_error = $e->errorInfo;
    }

    if ( in_array($pdo_error[1], array('1022','1062','1052','1169')) ) {                    
        $email = ...;
        $username = ...;
        throw new DuplicateException("
            Are you sure you did not already register? 
            Your username '{$username}' and/or email address '{$email}' 
            are already in our system.
            If this is you, please just login>.
        ");
    }
}

So, there we have it.

A Selection of My SQL Error Codes Relevant to this Blog

• 1022 SQLSTATE: 23000 (ER_DUP_KEY) “Can’t write; duplicate key in table”
• 1048 SQLSTATE: 23000 (ER_BAD_NULL_ERROR)
• 1052 SQLSTATE: 23000 (ER_NON_UNIQ_ERROR)
• 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) [for key]
• 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE) [uniqie constraint]
• 1216 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW) “Cannot add or update a child row: a foreign key constraint fails”
• 1217 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED) “Cannot delete or update a parent row: a foreign key constraint fails”
• 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2) “Cannot delete or update a parent row: a foreign key constraint fails”
• 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2) “Cannot add or update a child row: a foreign key constraint fails”