[
https://issues.apache.org/jira/browse/DBCP-338?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mark Thomas resolved DBCP-338.
------------------------------
Resolution: Cannot Reproduce
Just tested this with the latest Oracle database and JDBC driver (12.1.0.1) and
it looks like this Oracle bug has been fixed.
> 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.1.5#6160)
