[
https://issues.apache.org/jira/browse/TRAFODION-952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah updated TRAFODION-952:
-------------------------------------
Fix Version/s: (was: 2.2.0)
> LP Bug: 1415156 - DELETE concurrent with index creation causes corruption
> -------------------------------------------------------------------------
>
> Key: TRAFODION-952
> URL: https://issues.apache.org/jira/browse/TRAFODION-952
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-exe
> Reporter: Apache Trafodion
> Assignee: Prashanth Vasudev
> Priority: Major
>
> If queries delete rows from a table during CREATE INDEX there is a risk that
> the index will have more rows than the base table. See the example sqlci
> session quoted below. Note that the delete happens in the background with no
> output shown. The test script is attached.
> >>
> >>obey index_corrupter_traf(cr_table);
> >>create table t113b (uniq int not null,
> +> c100k int, c10K int , c1K int, c100 int,
> +> c10 int, c1 int, primary key (uniq) );
> --- SQL operation complete.
> >>
> >>prepare s1 from upsert using load into t113b select
> +>0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) +
> +> (100 * x100) + (10 * x10) +( 1 * x1),
> +>0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) +
> +> (10 * x10) +( 1 * x1),
> +>0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
> +>0 + (100 * x100) + (10 * x10) + (1 * x1),
> +>0 + (10 * x10) + (1 * x1),
> +>0 + (1 * x1),
> +>0
> +>from (values(0)) t
> +>transpose 0,1,2,3,4,5,6,7,8,9 as x100000
> +>transpose 0,1,2,3,4,5,6,7,8,9 as x10000
> +>transpose 0,1,2,3,4,5,6,7,8,9 as x1000
> +>transpose 0,1,2,3,4,5,6,7,8,9 as x100
> +>transpose 0,1,2,3,4,5,6,7,8,9 as x10
> +>transpose 0,1,2,3,4,5,6,7,8,9 as x1;
> --- SQL command prepared.
> >>
> >>explain options 'f' s1;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 9 . 10 root 1.00E+007
> 7 8 9 tuple_flow 1.00E+007
> . . 8 trafodion_load T113B 1.00E+000
> 6 . 7 transpose 1.00E+006
> 5 . 6 transpose 1.00E+005
> 4 . 5 transpose 1.00E+004
> 3 . 4 transpose 1.00E+003
> 2 . 3 transpose 1.00E+002
> 1 . 2 transpose 1.00E+001
> . . 1 values 1.00E+000
> --- SQL operation complete.
> >>
> >>display qid for s1;
> QID is MXID11000015197212289139259874701000000000206U3333300_478_S1
> QID details:
> ============
> Segment Num: 0
> Segment Name:
> Cpu: 0
> Pin: 15197
> ExeStartTime: 212289139259874701= 2015/01/27 17:20:59.874701 LCT
> SessionNum: 2
> UserName: U33333
> SessionName: NULL
> QueryNum: 478
> StmtName: S1
> SessionId: MXID11000015197212289139259874701000000000206U3333300
> >>
> >>execute s1;
> --- 1000000 row(s) inserted.
> >>
> >>get statistics for qid current;
> Qid
> MXID11000015197212289139259874701000000000206U3333300_478_S1
> Compile Start Time 2015/01/27 17:21:18.824433
> Compile End Time 2015/01/27 17:21:20.080504
> Compile Elapsed Time 0:00:01.256071
> Execute Start Time 2015/01/27 17:21:20.124949
> Execute End Time 2015/01/27 17:22:24.244243
> Execute Elapsed Time 0:01:04.119294
> State CLOSE
> Rows Affected 1,000,000
> SQL Error Code 0
> Stats Error Code 0
> Query Type SQL_INSERT_NON_UNIQUE
> Sub Query Type SQL_STMT_NA
> Estimated Accessed Rows 0
> Estimated Used Rows 0
> Parent Qid NONE
> Parent Query System NONE
> Child Qid NONE
> Number of SQL Processes 1
> Number of Cpus 1
> Transaction Id -1
> Source String upsert using load into t113b select 0 + (100000 *
> x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10)
> +( 1 * x1), 0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 *
> x10) +( 1 * x1), 0 + (1000 * x1000) + (100 * x100) +
> SQL Source Length 613
> Rows Returned 0
> First Row Returned Time -1
> Last Error before AQR 0
> Number of AQR retries 0
> Delay before AQR 0
> No. of times reclaimed 0
> Cancel Time -1
> Last Suspend Time -1
> Stats Collection Type OPERATOR_STATS
> SQL Process Busy Time 22,685,983
> UDR Process Busy Time 0
> SQL Space Allocated 618 KB
> SQL Space Used 604 KB
> SQL Heap Allocated 142 KB
> SQL Heap Used 142 KB
> EID Space Allocated 0 KB
> EID Space Used 0 KB
> EID Heap Allocated 0 KB
> EID Heap Used 0 KB
> Processes Created 0
> Process Create Time 0
> Request Message Count 0
> Request Message Bytes 0
> Reply Message Count 0
> Reply Message Bytes 0
> Scr. Overflow Mode UNKNOWN
> Scr File Count 0
> Scr. Buffer Blk Size 0
> Scr. Buffer Blks Read 0
> Scr. Buffer Blks Written 0
> Scr. Read Count 0
> Scr. Write Count 0
> Table Name
> Records Accessed Records Used Hbase Hbase
> Hbase IO Hbase IO
> Estimated/Actual Estimated/Actual IOs IO MBytes
> Sum Time Max Time
> TRAFODION.SCH.T113B
> 0 1
> 0 1,000,000 16,669 104
> 1,875,790 1,875,790
> --- SQL operation complete.
> >>
> >>
> >>obey index_corrupter_traf(cr_index);
> >>
> >>prepare s1 from
> +>create index idx2 on t113b(c1k);
> --- SQL command prepared.
> >>
> >>display qid for s1;
> QID is MXID11000015197212289139259874701000000000206U3333300_524_S1
> QID details:
> ============
> Segment Num: 0
> Segment Name:
> Cpu: 0
> Pin: 15197
> ExeStartTime: 212289139259874701= 2015/01/27 17:20:59.874701 LCT
> SessionNum: 2
> UserName: U33333
> SessionName: NULL
> QueryNum: 524
> StmtName: S1
> SessionId: MXID11000015197212289139259874701000000000206U3333300
> >>
> >>sh sqlci -i"index_corrupter_traf(uniq_deletes)" >/dev/null &;
> >>
> >>execute s1;
> --- SQL operation complete.
> >>
> >>
> >>
> >>set parserflags 1;
> --- SQL operation complete.
> >>
> >>select count(*) from table(index_table idx2);
> (EXPR)
> --------------------
> 999960
> --- 1 row(s) selected.
> >>
> >>select count(*) from t113b;
> (EXPR)
> --------------------
> 999574
> --- 1 row(s) selected.
> >>exit;
> End of MXCI Session
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
