You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Phil Steitz (JIRA)" <ji...@apache.org> on 2010/06/13 20:08:13 UTC
[jira] Created: (DBCP-338) ORA-01453 on connections with previous
errors
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.
[jira] Updated: (DBCP-338) ORA-01453 on connections with previous
errors
Posted by "Phil Steitz (JIRA)" <ji...@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.