Mailing-List: contact issues-help@commons.apache.org; run by ezmlm
Precedence: bulk
Reply-To: issues@commons.apache.org
Message-ID: <10261779.69971276452493705.JavaMail.jira@thor>
Date: Sun, 13 Jun 2010 14:08:13 -0400 (EDT)
From: "Phil Steitz (JIRA)" <jira@apache.org>
To: issues@commons.apache.org
Subject: [jira] Created: (DBCP-338) ORA-01453 on connections with previous
 errors
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

ORA-01453 on connections with previous errors
---------------------------------------------

                 Key: DBCP-338
                 URL: https://issues.apache.org/jira/browse/DBCP-338
             Project: Commons Dbcp
          Issue Type: Bug
    Affects Versions: 1.4, 1.3, 1.2.2, 1.2.1, 1.2, 1.1, 1.0
            Reporter: Phil Steitz
             Fix For: 1.3.1, 1.4.1


Adapted from a post to commons-user by Tim Dudgeon:

When a connection with autocommit=true encounters an error executing a DDL statement,  the connection is left in a transactional state that can lead to ORA-01453 when the connection is subsequently retrieved from the pool.  The following code illustrates the problem:

{code}

package foo;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class Ora01453Example {

    private static final String URL = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";
    private static final String USERNAME = "ijc";
    private static final String PASSWORD = "ijc";
    private PoolingDataSource dataSource;

    public static void main(String[] args) throws SQLException {
        Ora01453Example instance = new Ora01453Example();
        instance.run();
    }

    Ora01453Example() {
        GenericObjectPool connectionPool = new GenericObjectPool(null);
        connectionPool.setMaxActive(5);
        connectionPool.setMaxIdle(2);
        connectionPool.setMaxWait(10000);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(URL, USERNAME, PASSWORD);
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
                connectionFactory, connectionPool, null, null, false, true);
        dataSource = new PoolingDataSource(connectionPool);
        dataSource.setAccessToUnderlyingConnectionAllowed(true);
    }

    void run() throws SQLException {
        System.out.println("Running...");
        // get the connection
        Connection con = getConnection();
        try {
            // this will fail, either first time or second
            executeSql(con, "create table qwerty (id varchar2(100))");
            executeSql(con, "create table qwerty (id varchar2(100))");
        } catch (SQLException e) {
            System.out.println("Failed as expected");
        } finally {
            // close connection so it goes back to pool
            con.close();
        }

        // get a connection from pool again.
        con = getConnection();
        System.out.println("Setting transaction level");
        // try to set isolation level - will fail (assuming same connection is retrieved)
        try {
            con.setAutoCommit(false);
            con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        } finally {
            con.close();
        }
    }

    Connection getConnection() throws SQLException {
        Connection con = dataSource.getConnection();
        System.out.println(
                "Got Connection: " + con.hashCode()
                + " autoCommit=" + con.getAutoCommit()
                + " isolation=" + con.getTransactionIsolation());
        return con;
    }

    void executeSql(Connection con, String sql) throws SQLException {
        Statement stmt = con.createStatement();
        try {
            stmt.execute(sql);
        } finally {
            stmt.close();
        }
    }
}

{code}

Assuming the problem is that the driver creates, but does not commit a transaction on the failed DDL statement (despite autocommit=true), the simplest DBCP workaround would be to remove the autocommit test that guards the rollback in PooloableConnectionFactory#passivate.  That would have performance impacts, so it may be better to take an approach similar to DBCP-116, where we add a configuration parameter to either force rollback on each passivate or extend the rollbackAfterValidation config parameter defined there to PoolableConnectionFactory.


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.