You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Sandhya Sundaresan (JIRA)" <ji...@apache.org> on 2016/05/13 16:07:12 UTC
[jira] [Updated] (TRAFODION-952) LP Bug: 1415156 - DELETE
concurrent with index creation causes corruption
[ https://issues.apache.org/jira/browse/TRAFODION-952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sandhya Sundaresan updated TRAFODION-952:
-----------------------------------------
Fix Version/s: 2.1-incubating
> 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: Sandhya Sundaresan
> Fix For: 2.0-incubating, 2.1-incubating
>
>
> 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
(v6.3.4#6332)