Bug #10999 Exception in JDBC transaction using getGeneratedKeys when triggers are enabled

• Submitted: 31 May 2005 19:04
• Modified: 31 May 2005 20:15
• Reporter: Martin Dubuc
• Status: Can't repeat
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 3.1.8
• OS: Linux (Linux 2.6.11)
• CPU Architecture: Any

[31 May 2005 19:04] Martin Dubuc

Description:
Using MySQL Server 5.04 beta.

I am having seeing SQL exceptions when I use getGeneratedKeys inside JDBC transactions and triggers are present on the tables used in the JDBC transaction. If I remove the triggers, then I don't see any SQL exceptions.

How to repeat:
After creating a trigger on tables that are used in JDBC transactions, I get  SQLException inside the transaction when invoking getGeneratedKeys to retrieve the auto-incremented value of inserted rows. The SQL exception is logged as:

ERROR: ----- SQLException -----
SEVERE: Message:   ResultSet is from UPDATE. No Data.
SEVERE: SQLState:  S1000
SEVERE: ErrorCode: 0

If I drop the triggers, then code works as expected. If I add the triggers, after a call to getGeneratedKeys, rs.next() returns false. If I run a SELECT statement after doing getGeneratedKeys, I can get the auto-incremented value, but I get the exception. If I remove the getGeneratedKeys call all together, but create the updateStatement with Statement.RETURN_GENERATED_KEYS option, the exception only occurs sporadically.

Here is an excerpt from my code:

        Connection con = null;
        java.sql.Statement stmt = null;
        ResultSet rs = null;
        String updateSQL;
        String querySQL;
        int rowCount;
        int id;
        ...
        try {
            // Get connection to database
            con = ConnectionHelper.open("mydb");

            // Configure connection to wrap following SQL statements as an
            // atomic transaction.
            con.setAutoCommit(false);

            stmt = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                       java.sql.ResultSet.CONCUR_UPDATABLE);

            String updateSQL;

            updateSQL = "INSERT INTO tab1 ...";

            rowCount = stmt.executeUpdate(updateSQL,
                                                          Statement.RETURN_GENERATED_KEYS);

            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                   id = rs.getInt(1);
                    if (log.isDebugEnabled())
                        log.debug("Retrieved ID = " + id);
            }
            else {
                log.error("Can't retrieve ID with getGeneratedKeys.");

                // Retrieve ID using a SELECT statement instead.
                querySQL = "SELECT id from tab1 WHERE ...";

                if (log.isDebugEnabled())
                    log.debug(querySQL);

                rs = stmt.executeQuery(querySQL);

                if (rs.next()) {
                    id = rs.getInt("id");

                    if (log.isDebugEnabled())
                        log.debug("Allocated ID " + id);
                }
                else {
                    // Can't get id. Abort transaction.
                    log.error("Can't retrieve ID");

                    con.rollback();

                    // Display an error page
                    serverSoftwareError();
                }
            }

            updateSQL = "INSERT INTO tab2 ..."; // Using id in this statement

            if (log.isDebugEnabled())
                log.debug(updateSQL);

            rowCount = stmt.executeUpdate(updateSQL);
            ...
            // Commit transaction.
            con.commit();
        }
        catch(SQLException ex) {
            if (log.isErrorEnabled()) {
                log.error("\nERROR: ----- SQLException -----\n");
                while (ex != null) {
                    log.error("Message:   " + ex.getMessage());
                    log.error("SQLState:  " + ex.getSQLState());
                    log.error("ErrorCode: " + ex.getErrorCode());
                    ex = ex.getNextException();
                }
            }

            try {
                if (!con.getAutoCommit())
                    con.rollback();
            }
            catch (SQLException sqx) {
                if (log.isErrorEnabled()) {
                    log.error("\nERROR: ----- SQLException -----\n");
                    while (sqx != null) {
                        log.error("Message:   " + sqx.getMessage());
                        log.error("SQLState:  " + sqx.getSQLState());
                        log.error("ErrorCode: " + sqx.getErrorCode());
                        sqx = sqx.getNextException();
                    }
                }
            }

            // Display an error page
            serverError(ex);
        }
        finally {
            ConnectionHelper.close(con, stmt, rs);
        }

Triggers look like this:

CREATE TRIGGER tab1Update
AFTER UPDATE ON tab1
FOR EACH ROW
BEGIN
    // Invoke appropriate UDF
END;//

CREATE TRIGGER tab1Insert
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
    // Invoke appropriate UDF
END;//

I have triggers on tab1 and tab2.

[31 May 2005 20:15] Mark Matthews

I'm not able to repeat this here locally.

This sounds an awful lot like an out-of-memory condition that is getting swallowed up by finally{} blocks, as it's suspicious that Statement.getGeneratedKeys() is failing to return a value.

I'd also suggest rethinking your "fallthrough" method, which isn't safe to use. If you still want to have a fallthrough if getGeneratedKeys() doesn't work, you should use "SELECT last_insert_id()" instead.

What does increasing the maximum heap available to the application do to this bug? (i.e. -Xmx256M).