You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <de...@db.apache.org> on 2005/03/04 20:12:46 UTC
[jira] Created: (DERBY-160) Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
-------------------------------------------------------------------------------------------------
Key: DERBY-160
URL: http://issues.apache.org/jira/browse/DERBY-160
Project: Derby
Type: Bug
Components: Network Server
Versions: 10.0.2.1
Reporter: Mamta A. Satoor
When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
create table t1 (c11 int, c12 int);
insert into t1 values (1,1), (2,2), (3,3);
insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
get cursor c1 as 'select * from t1';
next c1;
-- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
-- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
-- The statement has been rolled back."
delete from tableWithPrimaryKey;
-- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
-- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
-- next c1 spits out contents of next row.
next c1;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-160) Foreign key constraint failure closes
the cursors in embedded mode but not in Network Server mode
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-160?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12730882#action_12730882 ]
Myrna van Lunteren commented on DERBY-160:
------------------------------------------
Although there's a difference between embedded and network server it seems to me either way could be seen as acceptable...
Or is there a standard/spec reference that clearly states the cursors should be closed?
I can also imagine an application could work-around this by doing a check on a delete failing and just not do the next, making sure it always works the same way - whether with embedded or NetworkServer.
Because of these two reasons, I marked the urgency 'normal'.
Eduardo - in case you're still interested...
This is classified as 'Major' (the default priority), not 'Critical'. It's not a crash, and not incorrect data returned, and I think it has a possible workaround, so I think the priority is correct and the urgency is normal...
I checked the archives and noticed you also sent a message to derby-user that went unanswered. Normally our community tries to be more responsive than that...
If you are still interested and find this a critical issue, you could 'vote' for it which would increase the impact the community gives to it. Or you could attempt to address it yourself. If you'd like that, you can start by reading: http://wiki.apache.org/db-derby/ForNewDevelopers and link to: http://wiki.apache.org/db-derby/DerbyCommitProcess.
> Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-160
> URL: https://issues.apache.org/jira/browse/DERBY-160
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.0.2.1
> Reporter: Mamta A. Satoor
>
> When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
> create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
> create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
> create table t1 (c11 int, c12 int);
> insert into t1 values (1,1), (2,2), (3,3);
> insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
> insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
> get cursor c1 as 'select * from t1';
> next c1;
> -- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
> -- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
> -- The statement has been rolled back."
> delete from tableWithPrimaryKey;
> -- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
> -- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
> -- next c1 spits out contents of next row.
> next c1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-160) Foreign key constraint failure closes
the cursors in embedded mode but not in Network Server mode
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-160?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12899844#action_12899844 ]
Knut Anders Hatlen commented on DERBY-160:
------------------------------------------
JDBC 4 added a new meta-data call to tell how the drivers behave on error: DatabaseMetaData.autoCommitFailureClosesAllResultSets(). The embedded driver returns true and the client driver returns false. See DERBY-3422.
> Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-160
> URL: https://issues.apache.org/jira/browse/DERBY-160
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.0.2.1
> Reporter: Mamta A. Satoor
>
> When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
> create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
> create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
> create table t1 (c11 int, c12 int);
> insert into t1 values (1,1), (2,2), (3,3);
> insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
> insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
> get cursor c1 as 'select * from t1';
> next c1;
> -- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
> -- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
> -- The statement has been rolled back."
> delete from tableWithPrimaryKey;
> -- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
> -- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
> -- next c1 spits out contents of next row.
> next c1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-160) Foreign key constraint failure closes
the cursors in embedded mode but not in Network Server mode
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-160:
-------------------------------------
Derby Categories: [Embedded/Client difference]
> Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-160
> URL: https://issues.apache.org/jira/browse/DERBY-160
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.0.2.1
> Reporter: Mamta A. Satoor
>
> When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
> create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
> create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
> create table t1 (c11 int, c12 int);
> insert into t1 values (1,1), (2,2), (3,3);
> insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
> insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
> get cursor c1 as 'select * from t1';
> next c1;
> -- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
> -- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
> -- The statement has been rolled back."
> delete from tableWithPrimaryKey;
> -- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
> -- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
> -- next c1 spits out contents of next row.
> next c1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-160) Foreign key constraint failure closes
the cursors in embedded mode but not in Network Server mode
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Myrna van Lunteren updated DERBY-160:
-------------------------------------
Urgency: Normal
> Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-160
> URL: https://issues.apache.org/jira/browse/DERBY-160
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.0.2.1
> Reporter: Mamta A. Satoor
>
> When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
> create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
> create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
> create table t1 (c11 int, c12 int);
> insert into t1 values (1,1), (2,2), (3,3);
> insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
> insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
> get cursor c1 as 'select * from t1';
> next c1;
> -- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
> -- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
> -- The statement has been rolled back."
> delete from tableWithPrimaryKey;
> -- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
> -- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
> -- next c1 spits out contents of next row.
> next c1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-160) Foreign key constraint failure closes
the cursors in embedded mode but not in Network Server mode
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Myrna van Lunteren updated DERBY-160:
-------------------------------------
Issue & fix info: [Repro attached]
the repro is actually included in the description.
> Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-160
> URL: https://issues.apache.org/jira/browse/DERBY-160
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.0.2.1
> Reporter: Mamta A. Satoor
>
> When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
> create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
> create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
> create table t1 (c11 int, c12 int);
> insert into t1 values (1,1), (2,2), (3,3);
> insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
> insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
> get cursor c1 as 'select * from t1';
> next c1;
> -- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
> -- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
> -- The statement has been rolled back."
> delete from tableWithPrimaryKey;
> -- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
> -- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
> -- next c1 spits out contents of next row.
> next c1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-160) Foreign key constraint failure closes
the cursors in embedded mode but not in Network Server mode
Posted by "Eduardo Pérez Ureta (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-160?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12654058#action_12654058 ]
Eduardo Pérez Ureta commented on DERBY-160:
-------------------------------------------
I started developing an application using Apache Derby Embedded and hit this issue that I need working.
I have seen this critical issue has been open for more than 3 years.
I would like to know if you think this issue should be fixed or maybe it is not that important.
I would like to know if closing all the cursors is something you expect or maybe it is a bug because I do not know why closing all the cursors gives any value.
> Foreign key constraint failure closes the cursors in embedded mode but not in Network Server mode
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-160
> URL: https://issues.apache.org/jira/browse/DERBY-160
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.0.2.1
> Reporter: Mamta A. Satoor
>
> When running embedded Derby in autocommit true mode(default), a foreign key violation closes the open cursors. But the cursors remain open after the foreign key violation when Derby is running in Network Server. Following code can be tried in ij for embedded and network server to see the behavior difference.
> create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2));
> create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey);
> create table t1 (c11 int, c12 int);
> insert into t1 values (1,1), (2,2), (3,3);
> insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4);
> insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4);
> get cursor c1 as 'select * from t1';
> next c1;
> -- In both embedded and Network server modes, delete below will throw exception "ERROR 23503: DELETE on
> -- table 'TABLEWITHPRIMARYKEY' caused a violation of foreign key constraint 'FK' for key (1,1).
> -- The statement has been rolled back."
> delete from tableWithPrimaryKey;
> -- In embedded mode, next on cursor below throws exception "ERROR XCL16: ResultSet not open, operation 'next' not
> -- permitted. Verify that autocommit is OFF." But in Network Server mode, the cursor is still open and
> -- next c1 spits out contents of next row.
> next c1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.