CODEWITHSUNDEEP
javajdbcsqlexception
Mr rain
Mr rain

Reputation: 1003

How to detect the SQL error state of SQLException

I am trying to make a JDBC connection pool component, but I have met a problem. That is: How to detect the error state of java.sql.SQLException. It's said that the state should follow SQL2003 conventions and XOPEN SQL conventions in JDK documentations. But I can't find any documents about these two conventions.Could someone provide it for me?

I want to know what every state stands for, so I can decide when to close the connection fully or reconnect.

I have refered to source code of BoneCP. Here is a part which will be actived when a SQLException occurs :

    ImmutableSet<String> sqlStateDBFailureCodes = ImmutableSet.of("08001", "08007", "08S01", "57P01", "HY000"); 
    String state = e.getSQLState();
        ConnectionState connectionState = this.getConnectionHook() != null ? this.getConnectionHook().onMarkPossiblyBroken(this, state, e) : ConnectionState.NOP; 
        if (state == null){ // safety;
            state = "08999"; 
        }

        if (((sqlStateDBFailureCodes.contains(state) || connectionState.equals(ConnectionState.TERMINATE_ALL_CONNECTIONS)) && this.pool != null) && this.pool.getDbIsDown().compareAndSet(false, true) ){
            logger.error("Database access problem. Killing off all remaining connections in the connection pool. SQL State = " + state);
            this.pool.connectionStrategy.terminateAllConnections();
            this.pool.poisonAndRepopulatePartitions();
        }
char firstChar = state.charAt(0);
        if (connectionState.equals(ConnectionState.CONNECTION_POSSIBLY_BROKEN) || state.equals("40001") || 
                state.startsWith("08") ||  (firstChar >= '5' && firstChar <='9') /*|| (firstChar >='I' && firstChar <= 'Z')*/){
            this.possiblyBroken = true;
        }

        // Notify anyone who's interested
        if (this.possiblyBroken  && (this.getConnectionHook() != null)){
            this.possiblyBroken = this.getConnectionHook().onConnectionException(this, state, e);
        }

According to these code, boneCP considers it as a database server crash when the state of SQLException equals one of "08001", "08007", "08S01", "57P01", "HY000".

But why, what do these states stand for ?

Upvotes: 0

Views: 7090

Answers (3)

wwadge
wwadge

Reputation: 3544

Links to status code explanations are literally in the same block of code you referred to: https://github.com/wwadge/bonecp/blob/master/bonecp/src/main/java/com/jolbox/bonecp/ConnectionHandle.java#L163

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109257

The SQL standard (ISO/IEC-9075), specifically book 2 Foundation defines SQL States. They are a 2-character class value followed by a 3-character subclass value.

Class values that begin with one of the digits '0', '1', '2', '3', or '4' or one of the simple Latin upper case letters 'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other International Standard. [...] Subclass values associated with such classes that also begin with one of those 13 characters are returned only for conditions defined in ISO/IEC 9075 or some other International Standard. [...] Subclass values associated with such classes that begin with one of the digits '5', '6', '7', '8', or '9' or one of the simple Latin upper case letters 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined conditions and are called implementation-defined subclasses.

Class values that begin with one of the digits '5', '6', '7', '8', or '9' or one of the simple Latin upper case letters 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined exception conditions and are called implementation-defined classes. All subclass values except '000', which means no subclass, associated with such classes are reserved for implementation-defined conditions and are called implementation-defined subclasses.

(from SQL:2011 book 2)

These standard classes (and standard defined subclasses) are listed in book 2 (foundation), 3 (CLI), 4 (PSM), 9 (MED), 10 (OLB), 13 (JRT) and 14 (XML). It also lists whether something is an exception, warning, successful completion condition or no-data completion condition (not all SQL states are errors!)

For example the sqlstates listed in your question are:

  • 08001: class: connection exception, subclass: SQL-client unable to establish SQL-connection
  • 08007: class: connection exception, subclass: transaction resolution unknown
  • 08S01: class: connection exception, subclass: begins with S, so is an implementation-defined subclass (it is defined in ODBC as Communication link failure)
  • 57P01: class: begins with 5, so is an implementation defined-class, it seems to be the PostgreSQL sqlstate for ADMIN SHUTDOWN
  • HY000: class: CLI-specific condition (defined in book 3, CLI), subclass: (no subclass)

HY000 is a very generic error as some databases or drivers will return it for any error that has no specific sqlstate, so it is usually treated as fatal. I do that BoneCP's behavior to treat it as a reason to kill off the entire connection pool a bit overkill as HY000 could also be thrown for relatively benign errors. The other sqlstates are usually an indication of a problem connecting to the database (08001, 08S01) or unavailability of the database (57P01), or a problem with the database itself (08007).

Upvotes: 2

BalusC
BalusC

Reputation: 1109635

The first 2 characters are specified in "Table_23-SQLSTATE_class_and_subclass_values" of the SQL92 specification.

Here's an extract of relevance:

00  success completion
01  warning
02  no data
07  dynamic SQL error
08  connection exception
0A  feature not supported
21  cardinality violation
22  data exception
23  integrity constraint violation
24  invalid cursor state
25  invalid transaction state
26  invalid SQL statement name
27  triggered data change violation
28  invalid authorization specification
2A  direct SQL syntax error or access rule violation
2B  dependent privilege descriptors still exist
2C  invalid character set name
2D  invalid transaction termination
2E  invalid connection name
33  invalid SQL descriptor name
34  invalid cursor name
35  invalid condition number
37  dynamic SQL syntax error or access rule violation
3C  ambiguous cursor name
3D  invalid catalog name
3F  invalid schema name
40  transaction rollback
42  syntax error or access rule violation
44  with check option violation
HZ  remote database access

The remaining characters are DB vendor dependent. So it's generally recommended to just perform a startsWith() check.

Upvotes: 5

Related Questions