LOB: Drop table/schema returns 8616 error but the drop statement can't be retried

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.

1. 1. 1. 1. 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;

1. 1. 1. 1. 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.