commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Steitz (JIRA)" <j...@apache.org>
Subject [jira] [Closed] (DBCP-338) ORA-01453 on connections with previous errors
Date Tue, 24 Feb 2015 03:23:30 GMT

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

Phil Steitz closed DBCP-338.
----------------------------

> 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 was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message