[jira] Created: (DBCP-338) ORA-01453 on connections with previous errors

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[jira] Created: (DBCP-338) ORA-01453 on connections with previous errors

JIRA jira@apache.org
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.

Reply | Threaded
Open this post in threaded view
|

[jira] Updated: (DBCP-338) ORA-01453 on connections with previous errors

JIRA jira@apache.org

     [ https://issues.apache.org/jira/browse/DBCP-338?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Phil Steitz updated DBCP-338:
-----------------------------

    Environment: Oracle driver 11.1.0.7.0.
    Description:
Adapted from a post to commons-user by Tim Dudgeon:

When an Oracle 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.



  was:
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.




> 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.0, 1.1, 1.2, 1.2.1, 1.2.2, 1.3, 1.4
>         Environment: Oracle driver 11.1.0.7.0.
>            Reporter: Phil Steitz
>             Fix For: 1.3.1, 1.4.1
>
>
> Adapted from a post to commons-user by Tim Dudgeon:
> When an Oracle 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.