[
https://issues.apache.org/jira/browse/TRAFODION-2735?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16161693#comment-16161693
]
Sandhya Sundaresan commented on TRAFODION-2735:
-----------------------------------------------
This problem can probably be reproduced by giving another stream of DDL running
in the background.
(1) sqlci -i mytest2.sql
(2) Wait until ‘create table mytable’ finish
(3) Use another terminal and run sqlci -i mytest1.sql to start another stream
of ddl operations.
(4) If mytest2.sql sees 8616 error at drop table, kill the sqlci of mytest1.sql
(5) Start another sqlci
Issue ‘drop table mytable cascade’ again.
<repeat (1)-(5) until you hit the problem>
$ cat mytest1.sql
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int);
drop table t;
$ cat mytest2.sql
cqd TRAF_BLOB_AS_VARCHAR 'OFF';
cqd TRAF_CLOB_AS_VARCHAR 'OFF';
drop table if exists mytable cascade;
create table mytable (row_id int, c1 blob, c2 clob, c3 blob, c4 clob, c5 blob,
c6 clob);
insert into table mytable values (1, stringtolob('column 1'),
stringtolob('column 2'), stringtolob('column 1'), stringtolob('column 2'),
stringtolob('column 1'), stringtolob('column 2'));
insert into table mytable values (2, stringtolob('column 1'),
stringtolob('column 2'), stringtolob('column 1'), stringtolob('column 2'),
stringtolob('column 1'), stringtolob('column 2'));
insert into table mytable values (3, stringtolob('column 1'),
stringtolob('column 2'), stringtolob('column 1'), stringtolob('column 2'),
stringtolob('column 1'), stringtolob('column 2'));
drop table mytable cascade;
$ sqlci -i mytest2.sql
>>cqd TRAF_BLOB_AS_VARCHAR 'OFF';
--- SQL operation complete.
>>cqd TRAF_CLOB_AS_VARCHAR 'OFF';
--- SQL operation complete.
>>
>>drop table if exists mytable cascade;
--- SQL operation complete.
>>create table mytable (row_id int, c1 blob, c2 clob, c3 blob, c4 clob, c5
>>blob, c6 clob);
--- SQL operation complete.
#### Use another terminal to run sqlci –i mytest1.sql now ####
>>insert into table mytable values (1, stringtolob('column 1'),
>>stringtolob('column 2'), stringtolob('column 1'), stringtolob('column 2'),
>>stringtolob('column 1'), stringtolob('column 2'));
--- 1 row(s) inserted.
>>insert into table mytable values (2, stringtolob('column 1'),
>>stringtolob('column 2'), stringtolob('column 1'), stringtolob('column 2'),
>>stringtolob('column 1'), stringtolob('column 2'));
--- 1 row(s) inserted.
>>insert into table mytable values (3, stringtolob('column 1'),
>>stringtolob('column 2'), stringtolob('column 1'), stringtolob('column 2'),
>>stringtolob('column 1'), stringtolob('column 2'));
--- 1 row(s) inserted.
>>drop table mytable cascade;
*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 38 regionTX: false
status: PENDING neverReadOnly: false scan Size: 29 write Size: 21 startSQ: 68
prepareEditSize: 0 endEditSize: 0 editSize: 42]] has a scan,
scanRange[startRow: null, endRow:null] that conflicts with a committed
Transaction [[transactionId: 44 regionTX: false status: COMMITED neverReadOnly:
false scan Size: 1 write Size: 1 startSQ: 73 prepareEditSize: 0 endEditSize: 1
editSize: 0 commitedSQ:73]] which , deleted a row with
key[\xC9r|\x00\x10\xF59g], in the region
[TRAF_RSRVD_1:TRAFODION._MD_.OBJECTS_UNIQ_IDX,,1501694820330.13235ed804dcdce8a5c16c8a9bfcdb7b.]
org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:1088)
org.trafodion.dtm.HBaseTxClient.prepareCommitCommon(HBaseTxClient.java:1025)!
--- SQL operation failed with errors.
>>exit;
#### kill the sqlci for mytest1.sql, start another sqlci ####
>>drop table mytable cascade;
*** ERROR[1031] Object TRAFODION.SEABASE.MYTABLE could not be dropped.
--- SQL operation failed with errors.
> LOB: Drop table/schema returns 8616 error but the drop statement can't be
> retried
> ---------------------------------------------------------------------------------
>
> Key: TRAFODION-2735
> URL: https://issues.apache.org/jira/browse/TRAFODION-2735
> Project: Apache Trafodion
> Issue Type: Bug
> Components: dtm, sql-exe
> Affects Versions: 2.2-incubating
> Environment: May need to run 2 streams to see the effect of this. But
> see how we handle the 8616 error with LOB objects may be a good place to
> start.
> Reporter: Sandhya Sundaresan
>
> With the DDL in TRX feature when a DDL returns the 8616 error, the statement
> is generally rolled back and it can be retried until it succeeds. This,
> however, is not the case for a table/schema with LOB columns. Drop a table or
> schema with internal LOB tables would often returns the 8616 error, and on
> the next retry, returns the 1031 or 1073 error with no further explanation.
> This is likely caused by the 8616 error not handling the internal LOB tables
> correctly. At that moment, using cleanup to clean up the tables and the
> schema becomes the only option.
> This is often seen when SQL regression is run with multiple streams. It is
> causing a huge problem for running LOB tests since once a table/schema is not
> dropped cleanly, all tests afterwards get messed up.
> Here are some of such failures seen on the AdvEnt2.2 1010 build:
> SQL>drop table mytable cascade;
> *** ERROR[8616] A conflict was detected during commit processing. Transaction
> has been aborted. [2016-10-12 12:53:54]
> SQL>drop table mytable cascade;
> *** ERROR[1031] Object TRAFODION.FEATURES_LOB.MYTABLE could not be dropped.
> [2016-10-12 12:54:01]
> SQL>drop table mytable1 cascade;
> *** ERROR[8616] A conflict was detected during commit processing. Transaction
> has been aborted. [2016-10-12 13:20:20]
> SQL>drop table mytable1 cascade;
> *** ERROR[1031] Object TRAFODION.FEATURES_LOB.MYTABLE1 could not be dropped.
> [2016-10-12 13:20:37]
> *** MISMATCH *** expecting: '--- SQL operation complete.'
> SQL>drop schema trafodion.features_lob_TEST007 cascade;
> *** ERROR[8616] A conflict was detected during commit processing. Transaction
> has been aborted. [2016-10-12 12:59:48]
> SQL>drop schema trafodion.features_lob_TEST007 cascade;
> *** ERROR[1031] Object TRAFODION.FEATURES_LOB_TEST007.MYTABLE could not be
> dropped. [2016-10-12 13:00:01]
> *** ERROR[1073] Schema TRAFODION.FEATURES_LOB_TEST007 was partially dropped,
> call CLEANUP SCHEMA to remove remaining entries. [2016-10-12 13:00:01]
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
