| ij> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| -- |
| -- some negative test for error checking |
| -- |
| xa_datasource 'wombat'; |
| ij> xa_connect user 'negativeTest' password 'xxx'; |
| ij> -- start new transaction |
| xa_start xa_noflags 0; |
| ij> -- ERROR: cannot start without end |
| xa_start xa_noflags 1; |
| IJ ERROR: XAER_PROTO |
| ij> xa_getconnection; |
| ij(XA)> -- ERROR: cannot get connection again |
| xa_getconnection; |
| ERROR XJ059: Cannot close a connection while a global transaction is still active. |
| ij(XA)> -- ERROR: connot commit/rollback an xa connection |
| commit; |
| ERROR XJ057: Cannot commit a global transaction using the Connection, commit processing must go thru XAResource interface. |
| ij(XA)> -- ERROR: connot commit/rollback an xa connection |
| rollback; |
| ERROR XJ058: Cannot rollback a global transaction using the Connection, commit processing must go thru XAResource interface. |
| ij(XA)> drop table APP.negative; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'APP.NEGATIVE' because it does not exist. |
| ij(XA)> create table APP.negative (a char(10), b int); |
| 0 rows inserted/updated/deleted |
| ij(XA)> create unique index negativei on APP.negative(b); |
| 0 rows inserted/updated/deleted |
| ij(XA)> run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view'; |
| ij(XA)> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| create view global_xactTable as |
| select |
| cast(global_xid as char(2)) as gxid, |
| status, |
| case when first_instant is NULL then 'NULL' else 'false' end as readOnly, |
| cast (username as char(10)) as username, |
| type |
| from syscs_diag.transaction_table; |
| 0 rows inserted/updated/deleted |
| ij(XA)> insert into APP.negative values ('xyz', 1); |
| 1 row inserted/updated/deleted |
| ij(XA)> select * from APP.negative; |
| A |B |
| ---------------------- |
| xyz |1 |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback without end |
| xa_commit xa_1phase 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback without end |
| xa_rollback 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback without end |
| xa_prepare 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- OK suspend it |
| xa_end xa_suspend 0; |
| ij(XA)> -- ERROR: duplicate xid |
| xa_start xa_noflags 0; |
| IJ ERROR: XAER_DUPID |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended |
| xa_commit xa_1phase 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended |
| xa_rollback 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended |
| xa_prepare 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot commit/prepare/rollback with suspended |
| xa_commit xa_2phase 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> xa_end xa_success 0; |
| ij(XA)> xa_prepare 0; |
| ij(XA)> xa_commit xa_2phase 0; |
| ij(XA)> -- should be able to use this xid again |
| xa_start xa_noflags 0; |
| ij(XA)> -- ERROR: cannot start without end |
| xa_start xa_noflags 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: duplicate key exception, statement level rollback |
| insert into APP.negative values ('rollback', 1); |
| ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'NEGATIVEI' defined on 'NEGATIVE'. |
| ij(XA)> select * from APP.negative; |
| A |B |
| ---------------------- |
| xyz |1 |
| ij(XA)> insert into APP.negative values ('ok', 2); |
| 1 row inserted/updated/deleted |
| ij(XA)> select * from global_xactTable order by gxid, status, username, type; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (0 |ACTIVE |false|NEGATIVETE|UserTransaction |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| NULL|IDLE |NULL |APP |UserTransaction |
| ij(XA)> disconnect; |
| ij> xa_end xa_fail 0; |
| IJ ERROR: XA_RBROLLBACK |
| ij> xa_start xa_noflags 2; |
| ij> xa_getconnection; |
| ij(XA)> insert into APP.negative values ('ok', 3); |
| 1 row inserted/updated/deleted |
| ij(XA)> -- ERROR: cannot suspend some other xid |
| xa_end xa_suspend 3; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot end some other xid while I am still attached |
| xa_end xa_success 0; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> xa_end xa_suspend 2; |
| ij(XA)> -- ERROR: cannot join an xid I just suspended have to resume |
| xa_start xa_join 2; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> xa_start xa_resume 2; |
| ij(XA)> xa_end xa_suspend 2; |
| ij(XA)> xa_rollback 0; |
| ij(XA)> -- ERROR: should not find this xid any more |
| xa_prepare 0; |
| IJ ERROR: XAER_NOTA |
| ij(XA)> select * from global_xactTable order by gxid, status, username, type; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (2 |ACTIVE |false|NEGATIVETE|UserTransaction |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| NULL|IDLE |NULL |APP |UserTransaction |
| NULL|IDLE |NULL |NEGATIVETE|UserTransaction |
| ij(XA)> xa_end xa_success 2; |
| ij(XA)> disconnect; |
| ij> -- ERROR: can only join a successful branch, not resume |
| xa_start xa_resume 2; |
| IJ ERROR: XAER_PROTO |
| ij> -- this is OK |
| xa_start xa_join 2; |
| ij> xa_getconnection; |
| ij(XA)> -- ERROR: another dup |
| insert into APP.negative values ('rollback', 3); |
| ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'NEGATIVEI' defined on 'NEGATIVE'. |
| ij(XA)> xa_end xa_suspend 2; |
| ij(XA)> xa_end xa_success 2; |
| ij(XA)> -- ERROR: cannot call fail now |
| xa_end xa_fail 2; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- rollback is OK |
| xa_rollback 2; |
| ij(XA)> -- ERROR: cannot join something that is not there |
| xa_start xa_join 2; |
| IJ ERROR: XAER_NOTA |
| ij(XA)> -- ERROR: cannot join something that is not there |
| xa_start xa_resume 2; |
| IJ ERROR: XAER_NOTA |
| ij(XA)> -- start one |
| xa_start xa_noflags 1; |
| ij(XA)> -- ERROR: can only forget heuristically completed transaction |
| xa_forget 1; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> delete from APP.negative; |
| 1 row inserted/updated/deleted |
| ij(XA)> xa_end xa_success 1; |
| ij(XA)> -- ERROR: now try some bad flag |
| xa_start xa_suspend 1; |
| IJ ERROR: XAER_INVAL |
| ij(XA)> -- ERROR: now try some bad flag |
| xa_start xa_fail 1; |
| IJ ERROR: XAER_INVAL |
| ij(XA)> xa_prepare 1; |
| ij(XA)> -- can only forget heuristically completed transaction |
| xa_forget 1; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> xa_start xa_noflags 2; |
| ij(XA)> -- ERROR: deadlock, transaction trashed |
| select * from APP.negative; |
| A |B |
| ---------------------- |
| ERROR 40XL1: A lock could not be obtained within the time requested |
| ij(XA)> -- ERROR: should have no connection underneath |
| select * from APP.negative; |
| A |B |
| ---------------------- |
| ERROR 40XL1: A lock could not be obtained within the time requested |
| ij(XA)> -- ERROR: should have no connection underneath and xid 2 is gone |
| xa_end xa_suspend 2; |
| IJ ERROR: XA_RBTIMEOUT |
| ij(XA)> -- ERROR: should have no connection underneath and xid 2 is gone |
| xa_end xa_fail 2; |
| IJ ERROR: XA_RBTIMEOUT |
| ij(XA)> xa_rollback 2; |
| ij(XA)> disconnect; |
| ij> xa_start xa_noflags 3; |
| ij> xa_getconnection; |
| ij(XA)> select * from global_xactTable order by gxid, status, username, type; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (1 |PREPARED|false|NEGATIVETE|UserTransaction |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| (3 |IDLE |NULL |NEGATIVETE|UserTransaction |
| NULL|IDLE |NULL |APP |UserTransaction |
| ij(XA)> drop table foo; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist. |
| ij(XA)> create table foo (a int); |
| 0 rows inserted/updated/deleted |
| ij(XA)> xa_end xa_suspend 3; |
| ij(XA)> -- ERROR: cannot join a prepared transaction |
| xa_start xa_join 1; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: cannot resume a prepared transaction |
| xa_start xa_resume 1; |
| IJ ERROR: XAER_PROTO |
| ij(XA)> -- ERROR: bad flag |
| xa_start xa_fail 1; |
| IJ ERROR: XAER_INVAL |
| ij(XA)> -- ERROR: bad flag |
| xa_start xa_noflags 1; |
| IJ ERROR: XAER_DUPID |
| ij(XA)> -- rollback prepared transaction is OK |
| xa_rollback 1; |
| ij(XA)> -- ERROR: dup id |
| xa_start xa_noflags 3; |
| IJ ERROR: XAER_DUPID |
| ij(XA)> xa_start xa_resume 3; |
| ij(XA)> -- now that 1 is rolled back, this should succeed |
| select * from APP.negative; |
| A |B |
| ---------------------- |
| xyz |1 |
| ij(XA)> select * from global_xactTable order by gxid, status, username, type; |
| GXID|STATUS |READ&|USERNAME |TYPE |
| ------------------------------------------------------------- |
| (3 |ACTIVE |false|NEGATIVETE|UserTransaction |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| NULL|IDLE |NULL |APP |UserTransaction |
| ij(XA)> -- ERROR: bad flag |
| xa_end xa_noflags 3; |
| IJ ERROR: XAER_INVAL |
| ij(XA)> xa_end xa_fail 3; |
| IJ ERROR: XA_RBROLLBACK |
| ij(XA)> xa_rollback 3; |
| ij(XA)> -- ensure switching back and forward does not commit |
| -- the xact due to the commit in setAutoCommit(); |
| AUTOCOMMIT ON; |
| ij(XA)> create table t44g(a int); |
| 0 rows inserted/updated/deleted |
| ij(XA)> insert into t44g values 1,2; |
| 2 rows inserted/updated/deleted |
| ij(XA)> select * from t44g where a > 4000; |
| A |
| ----------- |
| ij(XA)> create table t44(i int); |
| 0 rows inserted/updated/deleted |
| ij(XA)> xa_start xa_noflags 44; |
| ij(XA)> insert into t44g values(4400); |
| 1 row inserted/updated/deleted |
| ij(XA)> insert into t44g values(4401); |
| 1 row inserted/updated/deleted |
| ij(XA)> xa_end xa_suspend 44; |
| ij(XA)> values (1,2,3); |
| 1 |2 |3 |
| ----------------------------------- |
| 1 |2 |3 |
| ij(XA)> commit; |
| ij(XA)> AUTOCOMMIT OFF; |
| ij(XA)> insert into t44 values(1); |
| 1 row inserted/updated/deleted |
| ij(XA)> insert into t44 values(2); |
| 1 row inserted/updated/deleted |
| ij(XA)> commit; |
| ij(XA)> insert into t44 values(3); |
| 1 row inserted/updated/deleted |
| ij(XA)> insert into t44 values(4); |
| 1 row inserted/updated/deleted |
| ij(XA)> rollback; |
| ij(XA)> AUTOCOMMIT ON; |
| ij(XA)> -- fail with lock issues |
| select * from t44g; |
| A |
| ----------- |
| ERROR 40XL1: A lock could not be obtained within the time requested |
| ij(XA)> xa_start xa_resume 44; |
| ij(XA)> insert into t44g values(4500); |
| 1 row inserted/updated/deleted |
| ij(XA)> insert into t44g values(4501); |
| 1 row inserted/updated/deleted |
| ij(XA)> xa_end xa_success 44; |
| ij(XA)> insert into t44 values(5); |
| 1 row inserted/updated/deleted |
| ij(XA)> insert into t44 values(6); |
| 1 row inserted/updated/deleted |
| ij(XA)> commit; |
| ij(XA)> AUTOCOMMIT OFF; |
| ij(XA)> insert into t44 values(7); |
| 1 row inserted/updated/deleted |
| ij(XA)> insert into t44 values(8); |
| 1 row inserted/updated/deleted |
| ij(XA)> commit; |
| ij(XA)> AUTOCOMMIT ON; |
| ij(XA)> xa_start xa_join 44; |
| ij(XA)> select * from t44g where a > 4000; |
| A |
| ----------- |
| 4400 |
| 4401 |
| 4500 |
| 4501 |
| ij(XA)> xa_end xa_success 44; |
| ij(XA)> -- fail with lock issues |
| select * from t44g; |
| A |
| ----------- |
| ERROR 40XL1: A lock could not be obtained within the time requested |
| ij(XA)> xa_rollback 44; |
| ij(XA)> -- should be empty if no commit occurred in the middle; |
| select * from t44g where a > 4000; |
| A |
| ----------- |
| ij(XA)> select * from t44; |
| I |
| ----------- |
| 1 |
| 2 |
| 5 |
| 6 |
| 7 |
| 8 |
| ij(XA)> |
