CODEWITHSUNDEEP
javamysqlexception
amp
amp

Reputation: 12352

Debugging a MysqlDataTruncation exception

I'm storing information on a database using routines like:

try{
connection = cpds.getConnection();
pstmt = connection.prepareStatement("select setSomeData(?,?)");
//set data to the statement
res = pstmt.executeQuery();
//process data retrieved
} catch (SQLException ex) {
        System.out.println("SQLException: " + ex);
        ex.printStackTrace();
        return null;
    } finally {
        try {
            pstmt.close();
            connection.close();
        } catch (SQLException ex) {
    }
}

But sometimes I'm getting this exception:

SQLException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: BIGINT UNSIGNED value is out of range in '(tstmp@5 - lastEvent@11)'
com.mysql.jdbc.MysqlDataTruncation: Data truncation: BIGINT UNSIGNED value is out of range in '(tstmp@5 - lastEvent@11)'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2066)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1922)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3414)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:481)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3120)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2288)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2709)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2677)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2134)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2300)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)

Is it possible to know more info about the exception like the column affected and the value that I'm trying to store?

For instance, for the exception: .MySQLTransactionRollbackException: Deadlock found when trying to get lock;

The query SHOW ENGINE INNODB STATUS is quite useful...

Upvotes: 0

Views: 2430

Answers (1)

spencer7593
spencer7593

Reputation: 108400

There is very little debugging information available through JDBC, other than the SQLSTATE, and corresponding MySQL error code and message.

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

For that first exception, the most likely explanation is that it's caused by the warning (or error) raised by MySQL when an attempt is made to store a negative value into an UNSIGNED integer datatype.

As an example:

CREATE TABLE foo (val BIGINT UNSIGNED);
INSERT INTO foo (val) VALUES (-4);

0 row(s) affected
Total Time     : 0.030 sec
--------------------------------------------------

1 row(s) affected, 1 warning(s)
Total Time     : 0 sec

Warning Code : 1264
Out of range value for column 'val' at row 1

For the other exception, this is the information about the MySQL error that is being raised:

Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)
Message: Deadlock found when trying to get lock; try restarting transaction

For errors encountered with InnoDB tables, yes, the output from a SHOW ENGINE INNODB STATUS statement can provide some insight. But that information is for the entire database, and not necessarily specific to a session that encountered an error.

Upvotes: 1

Related Questions