db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sean Xiong (Issue Comment Edited) (JIRA)" <j...@apache.org>
Subject [jira] [Issue Comment Edited] (DDLUTILS-271) ERROR: current transaction is aborted, commands ignored until end of transaction block
Date Thu, 27 Oct 2011 00:21:32 GMT

    [ https://issues.apache.org/jira/browse/DDLUTILS-271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13136629#comment-13136629
] 

Sean Xiong edited comment on DDLUTILS-271 at 10/27/11 12:20 AM:
----------------------------------------------------------------


My Solution:
           1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch().
           2) Throw sql exception if the execute in step 1 is rejected by DB serve.
           3) Catch the sql exception and call connection.rollback to roll the status of connection
back to the savepoint we created in step 1.


Affected files:
                   PlatformImplBase.java of DDLUtils
         modifications:
            1) Method public void insert(Connection connection, Database model, DynaBean dynaBean)
throws DatabaseOperationException;
            2) Method public void update(Connection connection, Database model, DynaBean dynaBean)
throws DatabaseOperationException;
            3) Method private void executeBatch(PreparedStatement statement, int numRows,
Table table) throws DatabaseOperationException;
         All modified block is putted between //<Modification done By Sean Xiong> //</Modification
done By Sean Xiong>

Method (1)
 public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
    {
        SqlDynaClass      dynaClass       = model.getDynaClassFor(dynaBean);
        SqlDynaProperty[] properties      = getPropertiesForInsertion(model, dynaClass, dynaBean);
        Column[]          autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);

        if ((properties.length == 0) && (autoIncrColumns.length == 0))
        {
            _log.warn("Cannot insert instances of type " + dynaClass + " because it has no
usable properties");
            return;
        }

        String insertSql        = createInsertSql(model, dynaClass, properties, null);
        String queryIdentitySql = null;

        if (_log.isDebugEnabled())
        {
            _log.debug("About to execute SQL: " + insertSql);
        }

        if (autoIncrColumns.length > 0)
        {
            if (!getPlatformInfo().isLastIdentityValueReadable())
            {
                _log.warn("The database does not support querying for auto-generated column
values");
            }
            else
            {
                queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
            }
        }

        boolean           autoCommitMode = false;
        PreparedStatement statement      = null;

        try
        {
            if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
            {
                autoCommitMode = connection.getAutoCommit();
                connection.setAutoCommit(false);
            }

            beforeInsert(connection, dynaClass.getTable());
            
            statement = connection.prepareStatement(insertSql);


            for (int idx = 0; idx < properties.length; idx++ )
            {
                setObject(statement, idx + 1, dynaBean, properties[idx]);
            }
		
          //<Modification done By Sean Xiong>

            int count = -1;
            try {
            	 count = statement.executeUpdate();
                 connection.commit();
            } catch (SQLException sqle) {
            	connection.rollback();
            	// reset the autoCommit of connection to true
            	connection.setAutoCommit(autoCommitMode);
            	throw sqle;
            } 
           //</Modification done By Sean Xiong>

            afterInsert(connection, dynaClass.getTable());

            if (count != 1)
            {
                _log.warn("Attempted to insert a single row " + dynaBean +
                          " in table " + dynaClass.getTableName() +
                          " but changed " + count + " row(s)");
            }
        }
        catch (SQLException ex)
        {
            throw new DatabaseOperationException("Error while inserting into the database:
" + ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(statement);
        }
        if (queryIdentitySql != null)
        {
            Statement queryStmt       = null;
            ResultSet lastInsertedIds = null;

            try
            {
                if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
                {
                    // we'll commit the statement(s) if no auto-commit is enabled because
                    // otherwise it is possible that the auto increment hasn't happened yet
                    // (the db didn't actually perform the insert yet so no triggering of
                    // sequences did occur)
                    if (!connection.getAutoCommit())
                    {
                        connection.commit();
                    }
                }

                queryStmt       = connection.createStatement();
                lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);

                lastInsertedIds.next();

                for (int idx = 0; idx < autoIncrColumns.length; idx++)
                {
                    // we're using the index rather than the name because we cannot know how
                    // the SQL statement looks like; rather we assume that we get the values
                    // back in the same order as the auto increment columns
                    Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx],
idx + 1);

                    PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
                }
            }
            catch (NoSuchMethodException ex)
            {
                // Can't happen because we're using dyna beans
            }
            catch (IllegalAccessException ex)
            {
                // Can't happen because we're using dyna beans
            }
            catch (InvocationTargetException ex)
            {
                // Can't happen because we're using dyna beans
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException("Error while retrieving the identity
column value(s) from the database", ex);
            }
            finally
            {
                if (lastInsertedIds != null)
                {
                    try
                    {
                        lastInsertedIds.close();
                    }
                    catch (SQLException ex)
                    {
                        // we ignore this one
                    }
                }
                closeStatement(statement);
            }
        }
        if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
        {
            try
            {
                // we need to do a manual commit now
                connection.commit();
                connection.setAutoCommit(autoCommitMode);
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException(ex);
            }
        }
    }


Method (2)
 public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
    {
        SqlDynaClass      dynaClass   = model.getDynaClassFor(dynaBean);
        SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();

        if (primaryKeys.length == 0)
        {
            _log.info("Cannot update instances of type " + dynaClass + " because it has no
primary keys");
            return;
        }

        SqlDynaProperty[] properties = dynaClass.getNonPrimaryKeyProperties();
        String            sql        = createUpdateSql(model, dynaClass, primaryKeys, properties,
null);

        PreparedStatement statement  = null;

        if (_log.isDebugEnabled())
        {
            _log.debug("About to execute SQL: " + sql);
        }
        try
        {
            beforeUpdate(connection, dynaClass.getTable());

            statement = connection.prepareStatement(sql);

            int sqlIndex = 1;

            for (int idx = 0; idx < properties.length; idx++)
            {
                setObject(statement, sqlIndex++, dynaBean, properties[idx]);
            }
            for (int idx = 0; idx < primaryKeys.length; idx++)
            {
                setObject(statement, sqlIndex++, dynaBean, primaryKeys[idx]);
            }

            //<Modification done By Sean Xiong> 
            boolean autoCommitMode = connection.getAutoCommit();
            connection.setAutoCommit(false);

            int count = -1;
            try {
            	count = statement.executeUpdate();
		connection.commit();
            } catch (SQLException sqle) {
		connection.rollback();
            	throw sqle;
            } finally {
            	// reset the autoCommit of connection to its original value
            	connection.setAutoCommit(autoCommitMode);
            }

            //</Modification done By Sean Xiong> 

            afterUpdate(connection, dynaClass.getTable());

            if (count != 1)
            {
                _log.warn("Attempted to insert a single row " + dynaBean +
                         " into table " + dynaClass.getTableName() +
                         " but changed " + count + " row(s)");
            }
        }
        catch (SQLException ex)
        {
            throw new DatabaseOperationException("Error while updating in the database", ex);
        }
        finally
        {
            closeStatement(statement);
        }
    }

    /**
     * {@inheritDoc}
     */
    public void update(Database model, DynaBean dynaBean) throws DatabaseOperationException
    {
        Connection connection = borrowConnection();

        try
        {
            update(connection, model, dynaBean);
        }
        finally
        {
            returnConnection(connection);
        }
    }


Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
    {
        if (statement != null)
        {
            try
            {
                Connection connection = statement.getConnection();

                beforeInsert(connection, table);

               //<Modification done By Sean Xiong> 

                boolean autoCommitMode = connection.getAutoCommit();
                connection.setAutoCommit(false);
                
            	int[] results = null;
                try {
                	results = statement.executeBatch();
			connection.commit();
                } catch (SQLException sqle) {
			connection.rollback();
                	throw sqle;
                } finally {
                	// reset the autoCommit of connection to its original value
                	connection.setAutoCommit(autoCommitMode);
                }

              //</Modification done By Sean Xiong>

                closeStatement(statement);
                afterInsert(connection, table);

                boolean hasSum = true;
                int     sum    = 0;

                for (int idx = 0; (results != null) && (idx < results.length);
idx++)
                {
                    if (results[idx] < 0)
                    {
                        hasSum = false;
                        if (Jdbc3Utils.supportsJava14BatchResultCodes())
                        {
                            String msg = Jdbc3Utils.getBatchResultMessage(table.getName(),
idx, results[idx]);

                            if (msg != null)
                            {
                                _log.warn(msg);
                            }
                        }
                    }
                    else
                    {
                        sum += results[idx];
                    }
                }
                if (hasSum && (sum != numRows))
                {
                    _log.warn("Attempted to insert " + numRows + " rows into table " + table.getName()
+ " but changed " + sum + " rows");
                }
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException("Error while inserting into the database",
ex);
            }
        }
    }

                
      was (Author: seanxiong):
    
My Solution:
           1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch().
           2) Throw sql exception if the execute in step 1 is rejected by DB serve.
           3) Catch the sql exception and call connection.rollback to roll the status of connection
back to the savepoint we created in step 1.


Affected files:
                   PlatformImplBase.java of DDLUtils
         modifications:
            1) Method public void insert(Connection connection, Database model, DynaBean dynaBean)
throws DatabaseOperationException;
            2) Method public void update(Connection connection, Database model, DynaBean dynaBean)
throws DatabaseOperationException;
            3) Method private void executeBatch(PreparedStatement statement, int numRows,
Table table) throws DatabaseOperationException;
         All modified block is putted between //<Modification done By Sean Xiong> //</Modification
done By Sean Xiong>

Method (1)
 public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
    {
        SqlDynaClass      dynaClass       = model.getDynaClassFor(dynaBean);
        SqlDynaProperty[] properties      = getPropertiesForInsertion(model, dynaClass, dynaBean);
        Column[]          autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);

        if ((properties.length == 0) && (autoIncrColumns.length == 0))
        {
            _log.warn("Cannot insert instances of type " + dynaClass + " because it has no
usable properties");
            return;
        }

        String insertSql        = createInsertSql(model, dynaClass, properties, null);
        String queryIdentitySql = null;

        if (_log.isDebugEnabled())
        {
            _log.debug("About to execute SQL: " + insertSql);
        }

        if (autoIncrColumns.length > 0)
        {
            if (!getPlatformInfo().isLastIdentityValueReadable())
            {
                _log.warn("The database does not support querying for auto-generated column
values");
            }
            else
            {
                queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
            }
        }

        boolean           autoCommitMode = false;
        PreparedStatement statement      = null;

        try
        {
            if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
            {
                autoCommitMode = connection.getAutoCommit();
                connection.setAutoCommit(false);
            }

            beforeInsert(connection, dynaClass.getTable());
            
            statement = connection.prepareStatement(insertSql);

			//System.out.println("insetSql " + insertSql);

            for (int idx = 0; idx < properties.length; idx++ )
            {
                setObject(statement, idx + 1, dynaBean, properties[idx]);
            }
			//System.out.println("statement" + statement.toString());

          //<Modification done By Sean Xiong>

        	int count = -1;
            try {
            	 count = statement.executeUpdate();
                 connection.commit();
            } catch (SQLException sqle) {
            	connection.rollback();
            	// reset the autoCommit of connection to true
            	connection.setAutoCommit(autoCommitMode);
            	throw sqle;
            } 
           //</Modification done By Sean Xiong>

            afterInsert(connection, dynaClass.getTable());

            if (count != 1)
            {
                _log.warn("Attempted to insert a single row " + dynaBean +
                          " in table " + dynaClass.getTableName() +
                          " but changed " + count + " row(s)");
            }
        }
        catch (SQLException ex)
        {
            throw new DatabaseOperationException("Error while inserting into the database:
" + ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(statement);
        }
        if (queryIdentitySql != null)
        {
            Statement queryStmt       = null;
            ResultSet lastInsertedIds = null;

            try
            {
                if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
                {
                    // we'll commit the statement(s) if no auto-commit is enabled because
                    // otherwise it is possible that the auto increment hasn't happened yet
                    // (the db didn't actually perform the insert yet so no triggering of
                    // sequences did occur)
                    if (!connection.getAutoCommit())
                    {
                        connection.commit();
                    }
                }

                queryStmt       = connection.createStatement();
                lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);

                lastInsertedIds.next();

                for (int idx = 0; idx < autoIncrColumns.length; idx++)
                {
                    // we're using the index rather than the name because we cannot know how
                    // the SQL statement looks like; rather we assume that we get the values
                    // back in the same order as the auto increment columns
                    Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx],
idx + 1);

                    PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
                }
            }
            catch (NoSuchMethodException ex)
            {
                // Can't happen because we're using dyna beans
            }
            catch (IllegalAccessException ex)
            {
                // Can't happen because we're using dyna beans
            }
            catch (InvocationTargetException ex)
            {
                // Can't happen because we're using dyna beans
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException("Error while retrieving the identity
column value(s) from the database", ex);
            }
            finally
            {
                if (lastInsertedIds != null)
                {
                    try
                    {
                        lastInsertedIds.close();
                    }
                    catch (SQLException ex)
                    {
                        // we ignore this one
                    }
                }
                closeStatement(statement);
            }
        }
        if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
        {
            try
            {
                // we need to do a manual commit now
                connection.commit();
                connection.setAutoCommit(autoCommitMode);
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException(ex);
            }
        }
    }


Method (2)

private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
    {
        if (statement != null)
        {
            try
            {
                Connection connection = statement.getConnection();

                beforeInsert(connection, table);

               //<Modification done By Sean Xiong> 

                boolean autoCommitMode = connection.getAutoCommit();
                connection.setAutoCommit(false);
                
            	int[] results = null;
                try {
                	results = statement.executeBatch();
			connection.commit();
                } catch (SQLException sqle) {
			connection.rollback();
                	throw sqle;
                } finally {
                	// reset the autoCommit of connection to its original value
                	connection.setAutoCommit(autoCommitMode);
                }

              //</Modification done By Sean Xiong>

                closeStatement(statement);
                afterInsert(connection, table);

                boolean hasSum = true;
                int     sum    = 0;

                for (int idx = 0; (results != null) && (idx < results.length);
idx++)
                {
                    if (results[idx] < 0)
                    {
                        hasSum = false;
                        if (Jdbc3Utils.supportsJava14BatchResultCodes())
                        {
                            String msg = Jdbc3Utils.getBatchResultMessage(table.getName(),
idx, results[idx]);

                            if (msg != null)
                            {
                                _log.warn(msg);
                            }
                        }
                    }
                    else
                    {
                        sum += results[idx];
                    }
                }
                if (hasSum && (sum != numRows))
                {
                    _log.warn("Attempted to insert " + numRows + " rows into table " + table.getName()
+ " but changed " + sum + " rows");
                }
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException("Error while inserting into the database",
ex);
            }
        }
    }


Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
    {
        if (statement != null)
        {
            try
            {
                Connection connection = statement.getConnection();

                beforeInsert(connection, table);

              //<Modification done By Sean Xiong>

                boolean autoCommitMode = connection.getAutoCommit();
                connection.setAutoCommit(false);
                
            	int[] results = null;
                try {
                	results = statement.executeBatch();
					connection.commit();
                } catch (SQLException sqle) {

		        connection.rollback();
                	throw sqle;
                } finally {
                	// reset the autoCommit of connection to its original value
                	connection.setAutoCommit(autoCommitMode);
                }

              //</Modification done By Sean Xiong>

                closeStatement(statement);
                afterInsert(connection, table);

                boolean hasSum = true;
                int     sum    = 0;

                for (int idx = 0; (results != null) && (idx < results.length);
idx++)
                {
                    if (results[idx] < 0)
                    {
                        hasSum = false;
                        if (Jdbc3Utils.supportsJava14BatchResultCodes())
                        {
                            String msg = Jdbc3Utils.getBatchResultMessage(table.getName(),
idx, results[idx]);

                            if (msg != null)
                            {
                                _log.warn(msg);
                            }
                        }
                    }
                    else
                    {
                        sum += results[idx];
                    }
                }
                if (hasSum && (sum != numRows))
                {
                    _log.warn("Attempted to insert " + numRows + " rows into table " + table.getName()
+ " but changed " + sum + " rows");
                }
            }
            catch (SQLException ex)
            {
                throw new DatabaseOperationException("Error while inserting into the database",
ex);
            }
        }
    }

                  
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> --------------------------------------------------------------------------------------
>
>                 Key: DDLUTILS-271
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-271
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core (No specific database)
>    Affects Versions: 1.0
>            Reporter: Sean Xiong
>            Assignee: Thomas Dudziak
>              Labels: violation,unique,index
>
> After a violation of unique index inside the database an Exception is thrown and DDLUTILS
keeps throwing "ERROR: current transaction is aborted, commands ignored until end of transaction
block" stopping the application to do any database activity through it.
> User case: trying to insert two data rows with the same primary key to table. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message