Sandhya Sundaresan created TRAFODION-2708:
---------------------------------------------

             Summary: LOB: Drop table/schema returns 8616 error but the drop 
statement can't be retried
                 Key: TRAFODION-2708
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2708
             Project: Apache Trafodion
          Issue Type: Bug
          Components: dtm
    Affects Versions: 2.2-incubating
            Reporter: Sandhya Sundaresan
            Assignee: Prashanth Vasudev


With the DDL in TRX feature in EsgynDB R2.2, 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.
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]


The last drop table mytable cascade sees error 8616. But the next retry returns 
error 1031 and the table no longer can be dropped. The QA regression testing 
still has to run LOB testing in one single stream to avoid this problem:

SQL>create table mytable (row_id int, c1 blob, c2 clob, c3 blob, c4 clob, c5 
blob, c6 clob);
--- SQL operation complete.

SQL>insert into table mytable values (1, stringtolob('column 1'), 
stringtolob('column 2'), filetolob('/disk1/trafodion/QALibs/LOB/mydata.jpg'), 
filetolob('/disk1/trafodion/QALibs/LOB/mydata.txt'), 
filetolob('hdfs:///lobs/mydata.jpg'), filetolob('hdfs:///lobs/mydata.txt'));
--- 1 row(s) inserted.

SQL>insert into table mytable values (2, stringtolob('column 1'), 
stringtolob('column 2'), filetolob('/disk1/trafodion/QALibs/LOB/mydata.jpg'), 
filetolob('/disk1/trafodion/QALibs/LOB/mydata.txt'), 
filetolob('hdfs:///lobs/mydata.jpg'), filetolob('hdfs:///lobs/mydata.txt'));
--- 1 row(s) inserted.

SQL>insert into table mytable values (3, stringtolob('column 1'), 
stringtolob('column 2'), filetolob('/disk1/trafodion/QALibs/LOB/mydata.jpg'), 
filetolob('/disk1/trafodion/QALibs/LOB/mydata.txt'), 
filetolob('hdfs:///lobs/mydata.jpg'), filetolob('hdfs:///lobs/mydata.txt'));

SQL>select * from mytable order by 1;
ROW_ID C1 C2 C3 C4 C5 C6 
----------- 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
          1 
LOBH0000000200020364297430187285088919364297441253647184218212357396385655684044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200030364297430187285088919364297441254233036018212357396391480829044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200040364297430187285088919364297441254827003818212357396397435594044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200050364297430187285088919364297441255417994218212357396403356975044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200060364297430187285088919364297441256008036318212357396409295111044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200070364297430187285088919364297441256601055718212357396415174683044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
          2 
LOBH0000000200020364297430187285088919364297441257332028318212357396422514452044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200030364297430187285088919364297441257927990618212357396428491490044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200040364297430187285088919364297441258506992018212357396434296180044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200050364297430187285088919364297441259089030118212357396440117676044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200060364297430187285088919364297441259686001218212357396446022368044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200070364297430187285088919364297441260278018518212357396451935382044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
          3 
LOBH0000000200020364297430187285088919364297441260970151718212357396458804516044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200030364297430187285088919364297441261585081918212357396464985790044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200040364297430187285088919364297441262194032418212357396471082063044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200050364297430187285088919364297441262776035418212357396476986808044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200060364297430187285088919364297441263359996618212357396482791547044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200070364297430187285088919364297441263938971618212357396488604550044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"

--- 3 row(s) selected.

SQL>delete from mytable where row_id=1;
--- 1 row(s) deleted.

SQL>select * from mytable order by 1;
ROW_ID C1 C2 C3 C4 C5 C6 
----------- 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------

          2 
LOBH0000000200020364297430187285088919364297441257332028318212357396422514452044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200030364297430187285088919364297441257927990618212357396428491490044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200040364297430187285088919364297441258506992018212357396434296180044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200050364297430187285088919364297441259089030118212357396440117676044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200060364297430187285088919364297441259686001218212357396446022368044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200070364297430187285088919364297441260278018518212357396451935382044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
          3 
LOBH0000000200020364297430187285088919364297441260970151718212357396458804516044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200030364297430187285088919364297441261585081918212357396464985790044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200040364297430187285088919364297441262194032418212357396471082063044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200050364297430187285088919364297441262776035418212357396476986808044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200060364297430187285088919364297441263359996618212357396482791547044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"
 
LOBH0000000200070364297430187285088919364297441263938971618212357396488604550044"TRAFODION"."ZZZ_SINGLE_STREAM_FEATURES_LOB"

--- 2 row(s) selected.

SQL>delete from mytable;
--- 2 row(s) deleted.

SQL>select * from mytable order by 1;
--- 0 row(s) selected.

SQL>drop table mytable cascade;
*** ERROR[8616] A conflict was detected during commit processing. Transaction 
has been aborted. [2017-03-27 10:48:53]

INFO: Retrying the stmt for TMF error 73: 1 time(s).
SQL>drop table mytable cascade;
*** ERROR[1031] Object TRAFODION.ZZZ_SINGLE_STREAM_FEATURES_LOB.MYTABLE could 
not be dropped. [2017-03-27 10:49:53]
(0003529)
wtsai   (developer) 
2017-08-03 08:20

Edit Delete Make Private
This problem can probably be reproduced by giving another stream of DDL running 
in the background. With the AdvEnt2.3 20170802 daily build, this is what I did 
to reproduce it on nap001, with only one try:

(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
EsgynDB Advanced Conversational Interface 2.3.0
Copyright (c) 2015-2017 Esgyn Corporation
>>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.




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Reply via email to