Catch duplicate entry Exception

Question

How can i catch this Exception :

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 
                                      Duplicate entry '22-85' for key 'ID_CONTACT'

Marolda

It would be better to solve the issue which is causing this exception – Hyetal

it's not a issue but with a web application a user can change http request parameters and i want to be sure that every thing work so i want to add this layer security. – Marolda

But as you can see from the stacktrace that during insert you are violating a constraint and it's better to first validate the data before sending it to database. You can obviously catch exception if you really want to but I would prefer a cleaner solution – Hyetal

The cleanest solution would be redirect the user to some generic web error page which doesn't show the exception trace (obviously) but alerts the system owner. Much better than trying to handle these special attack cases... – Ratchet

But how do you know its a duplicate entry exception. The exception says constraint violation. What if its another constraint? – Maggiemaggio

Answer 1

I use spring so we resolve it by org.springframework.dao.DataIntegrityViolationException

try {
    ao_history_repository.save(new AoHistory(..));
} catch (DataIntegrityViolationException e) {
    System.out.println("history already exist");
}

But as @KevinGuancheDarias mention it:

Please note that while this works. I suggest to solve the problem by issuing a findBy before the save, as this is messy, and I think it's not warranted that it will work in future versions, may even break without notification.

Marolda

This didn't work for me in my project I don't know why, but my spring throws other exception from the repository, look at my answer for further information – Bipinnate

For anyone coming across this answer, be cautious of the suggestion at the bottom of the answer in envs with multiple instances . If I'm not mistaken, which I very well could be, if your consuming service runs multiple instances (E.g. AWS EC2s with auto-scaling groups) then you could run into race-conditions and still end up causing the database constraint violation to be thrown. I.e., A findBy response comes back "not found", but another instance has persisted that object before the post-findBy logic can persist it. – Evangelia

The race condition in DBs can always happen. Of course you can check if the entry already exists prior to inserting the data into your DB but right between the findBy call and the save call someone else might have inserted exactly that entry! That has nothing to do with how many instances of your service or your DBs are currently running -as long as more than one user can insert data simultaneously (see also unrepeatable reads). – Fear

Answer 2

catch SQLIntegrityConstraintViolationException, if you are using Java 1.6+

e.g.

try {
    ps.executeUpdate("INSERT INTO ...");
} catch (SQLIntegrityConstraintViolationException e) {
    // Duplicate entry
} catch (SQLException e) {
    // Other SQL Exception
}

or

try {
    ps.executeUpdate("INSERT INTO ...");
} catch (SQLException e) {
    if (e instanceof SQLIntegrityConstraintViolationException) {
        // Duplicate entry
    } else {
        // Other SQL Exception
    }
}

Polemics

Unreachable catch block for SQLIntegrityConstraintViolationException. This exception is never thrown from the try statement body – Marolda

@Youssef, what is the version of your mysql-connector-java ? And what is the SQL you try to update ? – Polemics

Catching SQLException is required. Then you can detect if it is a SQLIntegrityConstraintViolationException. Note that MySQLIntegrityConstraintViolationException is a type of SQLIntegrityConstraintViolationException. – Polemics

Answer 3

I use Spring. So catch org.springframework.dao.DuplicateKeyException

try{
    ...
} catch (DuplicateKeyException dke) {
    ...
} 

Theodoretheodoric

Answer 4

vendorCode 2601 is for unique index constraint violate so you can check SQLException cewndorCode by e.getErrorCode() == 2601. sample code:

try {
    ao_history_repository.save(new AoHistory(..));
} catch (SQLException e) {
    if (e.getErrorCode() == 2601) {
        System.out.println("handle duplicate index error here!");
    } else {
        System.out.println("handle other error code here!");
    }
}

Quoits

Can you provide relevant documentation links here so that users can look at other error codes? – Ghana

@Ghana You can find those MySQL database errors codes online. I find the MariaDB error code documentation easier to read, and they share a lot of the same error codes. – Battista

Answer 5

A - Log the exception in detail

Here is what I use to log SQL Exceptions so that I can be sure of what to catch;

private static void handleSQLError(SQLException e) throws SQLException {
    log.info("SQL Error Type : " + e.getClass().getName());
    log.info("Error Message  : " + e.getMessage());
    log.info("Error Code     : " + e.getErrorCode());
    log.info("SQL State      : " + e.getSQLState());

    throw e;
}

Here is the sample Output;

2018 Nis 05 11:20:32,248 INFO MySQLUtil: SQL Error Type : com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
2018 Nis 05 11:20:32,248 INFO MySQLUtil: Error Message  : Duplicate entry 'test2 CAMT052' for key 'customer_file_customer_file_name_idxu'
2018 Nis 05 11:20:32,249 INFO MySQLUtil: Error Code     : 1062
2018 Nis 05 11:20:32,249 INFO MySQLUtil: SQL State      : 23000
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'test' for key 'customer_file_customer_file_name_idxu'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

B - Catch the Exception and check the parameters

To catch duplicate key Exception, we need to catch the specific class which is MySQLIntegrityConstraintViolationException. Also the following parameter must match;

SQL State      : 23000

So I use the following block to catch duplicate entries;

try {
    dbUtil.persistEntry(entry);
} catch (SQLException e) {
    if(e instanceof MySQLIntegrityConstraintViolationException) {
        if(e.getSQLState().equals("23000")) {
            if(e.getMessage().contains("Duplicate")) {
                isDuplicateEntryError = true;
            }
        }
    }
}

Guacharo

If someone comes here (as me): The SQL State is NO direct indicator for duplicated entry errors. If you look into the official error documentation of MariaDB (they have shared errors; mariadb.com/kb/en/library/mariadb-error-codes) you will see that there are multiple errors which result in that state. The more important information here is the error code as this is directly mapped to an error. (In some cases you must check for multiple codes, e.g. i found deadlocks can return 1213 and 1205). TLDR: better check error code instead of sql state – Canning

Answer 6

Loook at Spring framework source code Look at spring JDBC error resolve code.

org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator#doTranslate

else if (Arrays.binarySearch(this.sqlErrorCodes.getDuplicateKeyCodes(), errorCode) >= 0)

{ logTranslation(task, sql, sqlEx, false); return new DuplicateKeyException(buildMessage(task, sql, sqlEx), sqlEx); }

There are multiple ways how you can hit different Exception translators:

• Spring load metadata/error codes from your db - one translator
• Spring fails to connect to db - another one
• Hibernate JPA may have different translator

So Dublicate behavior may change from DuplicateKeyException to DataIntegrityViolationException.

Charla

Answer 7

In my Spring project, the exception thrown was org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement.

So after debugging I had a parent JpaSystemException with a cause PersistenceException and that had a cause ConstraintViolationException, this last one had the Database specific exception, but I ended up catching ConstraintViolationException with

public static boolean isSqlDuplicatedKey(Exception e) {
        return e.getCause() != null && e.getCause().getCause() != null
                && ConstraintViolationException.class.isInstance(e.getCause().getCause());
}
// ....

try {
   someRepository.save(some);
} catch (JpaSystemException e) {
    if (ExceptionUtilService.isSqlDuplicatedKey(e)) {
        // Do something to handle it
    } else {
        throw e;
    }
}

Please note that while this works. I suggest to solve the problem by issuing a findBy before the save, as this is messy, and I think it's not warranted that it will work in future versions, may even break without notification.

Bipinnate

Answer 8

The following code works for me:

try {
    requete.executeUpdate();
} catch (final ConstraintViolationException e) {

}

Animism

Answer 9

I agree, but we've something like this in my Spring Application,:- RequestValidationException/MessageConstants are custom one's:

import org.springframework.dao.DuplicateKeyException;
|
|
|
catch (Exception exception) {
if(exception instanceof DuplicateKeyException) {
 logger.error("exception as duplicate Name Found: " + exception.getMessage());
 throw new RequestValidationException(MessageConstants.DUPLICATE_NAME_FOUND_ERROR_CD, MessageConstants.DUPLICATE_NAME_FOUND_ERROR_MSG); 
 }else{
        logger.error("exception on update: " + exception.getMessage());
        throw exception;
    }
 }

Placidia

In my case, exception instanceof DuplicateKeyException never goes inside of if loop. why ? – Kellogg

@Kellogg : Can you share stack trace ? I mean what kind of exception are you getting and what exactly are you using for db transactions. – Placidia