[
https://issues.apache.org/jira/browse/DERBY-6670?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14065292#comment-14065292
]
Rick Hillegas commented on DERBY-6670:
--------------------------------------
Since calling database procedures causes Derby to create an internal savepoint,
this problem can also be caused by dropping the table inside a dbproc and then
raising an exception. To see this, first compile the following class…
{noformat}
import java.sql.*;
public class zz
{
public static void dropTable( String tableName )
throws SQLException
{
Connection conn = DriverManager.getConnection(
"jdbc:default:connection" );
conn.prepareStatement( "drop table " + tableName ).execute();
throw new SQLException( "Oh sorry, an error occurred." );
}
}
{noformat}
…then run the following script…
{noformat}
connect 'jdbc:derby:memory:db;create=true';
create procedure dropTable( tableName varchar( 128 ) )
language java parameter style java modifies sql data
external name 'zz.dropTable';
create table t( a int primary key initially deferred );
autocommit off;
insert into t( a ) values ( 1 ), ( 1 );
call dropTable( 'T' );
commit;
-- table has duplicate primary key values
select * from t;
{noformat}
> Rollback to savepoint allows violation of deferrable constraints
> ----------------------------------------------------------------
>
> Key: DERBY-6670
> URL: https://issues.apache.org/jira/browse/DERBY-6670
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Knut Anders Hatlen
>
> The bug is illustrated by the following code snippet:
> {code}
> Connection c =
> DriverManager.getConnection("jdbc:derby:memory:db;create=true");
> c.setAutoCommit(false);
> Statement s = c.createStatement();
> s.execute("create table t1(x int primary key initially deferred)");
> s.execute("insert into t1 values 1,1,1,1");
> Savepoint sp = c.setSavepoint();
> s.execute("drop table t1");
> c.rollback(sp);
> // Since there are four identical rows in T1, this call should have
> // failed because the primary key was violated.
> c.commit();
> // Instead, it succeeds, and all four rows are committed, as can
> // be seen here:
> ResultSet rs = s.executeQuery("select * from t1");
> while (rs.next()) {
> System.out.println(rs.getInt(1));
> }
> // Insert yet another row, so that we have five identical rows ...
> s.execute("insert into t1 values 1");
> // ... and now commit complains ...
> c.commit();
> {code}
> With auto-commit off, add duplicates into a deferred primary key. Then set a
> savepoint, drop the table, and roll back to the savepoint.
> Apparently, when you drop the table, information about any constraint
> violations seen on that table is lost, and that information is not restored
> when the drop table operation is undone by the rollback to savepoint.
> So when you commit the transaction after having rolled back the drop
> operation, no deferred checking of constraints happens, and the duplicates
> you have inserted are committed.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
