You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:20:19 UTC
[jira] [Created] (TRAFODION-1182) LP Bug: 1444684 - Drop schema
fails and objects become un-droppable with invalid state
Alice Chen created TRAFODION-1182:
-------------------------------------
Summary: LP Bug: 1444684 - Drop schema fails and objects become un-droppable with invalid state
Key: TRAFODION-1182
URL: https://issues.apache.org/jira/browse/TRAFODION-1182
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Weishiun Tsai
Priority: Critical
Fix For: 2.0-incubating
We still see random situations that an SQL object would get into an invalid state and become un-droppable. When it happens, using the cleanup utility to clean them up becomes the only option. This is a rare occasion that we were able to capture a short script to reproduce it. As shown here. The sequence of statements causes drop schema at the end to return TMF 97 error. After that, any attempt to drop an object in the schema would return the 4254 ‘invalid state’ error. The user then has to rely on the cleanup utility to clean them up manually.
SQL>drop schema mytest cascade;
*** ERROR[8606] Transaction subsystem TMF returned error 97 on a commit transaction. [2015-04-15 20:10:00]
SQL>set schema mytest;
--- SQL operation complete.
SQL>get tables;
Tables in Schema TRAFODION.MYTEST
=================================
NIZ000
NIZ001
SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
--- SQL operation complete.
SQL>drop table niz000;
*** ERROR[4254] Object TRAFODION.MYTEST.NIZ000 has invalid state and cannot be accessed. Use cleanup command to drop it. [2015-04-15 20:10:35]
SQL>drop table niz001;
*** ERROR[4254] Object TRAFODION.MYTEST.NIZ001 has invalid state and cannot be accessed. Use cleanup command to drop it. [2015-04-15 20:10:40]
SQL>drop table sb_histograms;
*** ERROR[4254] Object TRAFODION.MYTEST.SB_HISTOGRAMS has invalid state and cannot be accessed. Use cleanup command to drop it. [2015-04-15 20:10:43]
SQL>drop table sb_histogram_intervals;
*** ERROR[4254] Object TRAFODION.MYTEST.SB_HISTOGRAM_INTERVALS has invalid state and cannot be accessed. Use cleanup command to drop it. [2015-04-15 20:10:51]
This is seen on the v0415 build installed on a workstation. It’s fairly reproducible with this build. Unfortunately, this script can only reproduce this problem from trafci. Running it from sqlci cannot reproduce it. It’s not clear why, but it’s possible that mxosrvr handles things differently.
---------------------------------------------------------------------------------
Here are the steps to start trafci from your workstation instance:
Copy and paste the script bellow to an obey file, say mytest.sql
cd $MY_SQROOT/trafci/bin
trafci.sh -h localhost:<your port number> -u dontcare -p dontcare
SQL> obey mytest.sql;
---------------------------------------------------------------------------------
Here is the entire script to reproduce it:
log mytest.log clear;
create schema mytest;
set schema mytest;
create table niz000
(
seqno integer not null not droppable,
smin1 smallint signed default null,
smin2 smallint unsigned default null,
inte1 integer signed default null,
inte2 integer unsigned default null,
lint1 largeint default null,
lint2 largeint default null,
nume1 numeric(7) unsigned default null,
nume2 numeric(9,3) unsigned default null,
nume3 numeric(18) signed default null,
nume4 numeric(18,15) signed default null,
deci1 decimal(3) unsigned default null,
deci2 decimal(18,0) signed default null,
deci3 decimal(18,9) signed default null,
pict1 pic s9(18) comp default null,
pict2 pic sv9(2) comp default null,
pict3 pic s9(13)v9(5) default null,
pict4 pic 9(3)v9(6) default null,
flot1 float (12) default null,
flot2 float (52) default null,
real1 real default null,
real2 real default null,
dblp1 double precision default null,
dblp2 double precision default null,
primary key (seqno)
);
create index smin_idx on niz000(smin1);
create index inte_idx on niz000(inte1);
create index lint_idx on niz000(lint1);
create index nume_idx on niz000(nume1);
create index deci_idx on niz000(deci1);
create index pict_idx on niz000(pict1);
create index flot_idx on niz000(flot1);
create index real_idx on niz000(real1);
create index dblp_idx on niz000(dblp1);
insert into niz000 values
(1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, .1, 1, 1, 1, 1, 1, 1, 1, 1);
insert into niz000 (seqno) values (3);
insert into niz000 values
(4, -32768, 0, -2147483648, 1, -9223372036854775808, 2, 3, 4, -999999999999999999, -999.999999999999999, 5, -999999999999999999, -999999999.999999999, -999999999999999999, -0.99, -9999999999999.99999, 6.000001, 7.000000000001, -2.2250738585072014e-308, -1.17549435e-38, 8, -2.2250738585072014e-308, 9),
(5, 32767, 0, 2147483647, 1, 9223372036854775807, -2, 3, 4, 999999999999999999, 999.999999999999999, 5, 999999999999999999, 999999999.999999999, 999999999999999999, 0.99, 9999999999999.99999, 6.000001, 7.000000000001, 1.7976931348623157e+308, 3.40282347e+38, 8, 1.7976931348623157e+308, 9),
(6, -459, 7892, -992348, 293847923, 701291928791, 9809876586, 5622901, 23990.78, -129980, -46.012979798, 804, -62352342342, -8768889.1200454, -1283498234923749, 0.65, -7870234.00126, 786.124598, 11897.9998877656, 898889.00000997, 5.89100, 555.23, 10045.157e+8, -6120.00945e-21),
(7, 2834, 443, -90, 450, 4545990, 11, 102245, 77.923, -670, 831.44553, 21, 60778, -486.929, -8798799000, 0.13, 34007.00387, 506.55209,
-99082.007, 6712009.22, -3.1417, 10.39e-1, 30887.333e+10, -445e-25);
select count(*) from niz000;
update statistics for table niz000 on every column;
create table niz001 like niz000;
create index smin_mc_idx on niz001(smin1, smin2);
create index inte_mc_idx on niz001(inte1, inte2);
create index lint_mc_idx on niz001(lint1, lint2);
create index nume_mc_idx on niz001(nume1, nume2, nume3, nume4);
create index deci_mc_idx on niz001(deci1, deci2, deci3);
create index pict_mc_idx on niz001(pict1, pict2, pict3, pict4);
create index flot_mc_idx on niz001(flot1, flot2);
create index real_mc_idx on niz001(real1, real2);
create index dblp_mc_idx on niz001(dblp1, dblp2);
insert into niz001 values
(1, 34, 44, 123, 789, 99809, 99909, 1223311, 12.333, 9899898979, 11.098080980, 23, 123134534534, 98797.8768688, -4435, -0.39, -121213434.111, 345.8879, -1.2250E-28, 0.834673734574, 9.223e18, -1.79e-2, -0.00092746, -2.225E-38),
(2, 24923, 9992, -2147484, 9, 847201, 5098, 8365290, 12.333, 8998979, 444.238, 3,789729834, 669.02983487, 9928, 0.96, 12345678.1234, 797.799, -7.9098700456e-1, -0.7986750064688, 0.11, -0.7129, 2.222197264, 4.0876e-5),
(3, 108, 81, 925, 1351, 923749823, -615240, 81230, 119900.22, 7019239, 308.1274, 111, 6092340, 501293.5553, 8394230423, 0.01, 12111.0980, 405.0001, 0.128349234, 312.9999989e-5, 0.0008745245, -0.1007, 0.309e+3, 100 ),
(4, 25065, 10117, -2146436, 2149, 924696833, -510233, 9669831, 119924.886, 9915917197, 763.463480980000001, 137, 123930356708, 600760.462003670, 8394235916, 0.58, -108855644.88960, 158.687000, -6.62637770560000000E-001, 3.91287280941999936E-002, 9.22299985216877440E+018, -8.31499980762600960E-001, 3.11221269804000000E+002, 1.00000040876000000E+002),
(5, 12532, 5058, -1073218, 1074, 462348416, -255116, 4834915, 59962.443, 4957958598, 381.731740490000250, 68, 61965178354, 300380.231001835, 4197117958, 0.29, -54427822.44480, 426.843503, -3.31318885280000000E-001, 1.95643640470999968E-002, 4.61149992608438720E+018, -4.15749993640929472E-001, 1.55610634902000000E+002, 5.00000204380000128E+001);
select count(*) from niz001;
update statistics for table niz001 on every column;
showcontrol all;
drop schema mytest cascade;
---------------------------------------------------------------------------------
Here is the execution output:
SQL>create schema mytest;
--- SQL operation complete.
SQL>set schema mytest;
--- SQL operation complete.
SQL>create table niz000
(
seqno integer not null not droppable,
smin1 smallint signed default null,
smin2 smallint unsigned default null,
inte1 integer signed default null,
inte2 integer unsigned default null,
lint1 largeint default null,
lint2 largeint default null,
nume1 numeric(7) unsigned default null,
nume2 numeric(9,3) unsigned default null,
nume3 numeric(18) signed default null,
nume4 numeric(18,15) signed default null,
deci1 decimal(3) unsigned default null,
deci2 decimal(18,0) signed default null,
deci3 decimal(18,9) signed default null,
pict1 pic s9(18) comp default null,
pict2 pic sv9(2) comp default null,
pict3 pic s9(13)v9(5) default null,
pict4 pic 9(3)v9(6) default null,
flot1 float (12) default null,
flot2 float (52) default null,
real1 real default null,
real2 real default null,
dblp1 double precision default null,
dblp2 double precision default null,
primary key (seqno)
);
--- SQL operation complete.
SQL>create index smin_idx on niz000(smin1);
--- SQL operation complete.
SQL>create index inte_idx on niz000(inte1);
--- SQL operation complete.
SQL>create index lint_idx on niz000(lint1);
--- SQL operation complete.
SQL>create index nume_idx on niz000(nume1);
--- SQL operation complete.
SQL>create index deci_idx on niz000(deci1);
--- SQL operation complete.
SQL>create index pict_idx on niz000(pict1);
--- SQL operation complete.
SQL>create index flot_idx on niz000(flot1);
--- SQL operation complete.
SQL>create index real_idx on niz000(real1);
--- SQL operation complete.
SQL>create index dblp_idx on niz000(dblp1);
--- SQL operation complete.
SQL>insert into niz000 values
(1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, .1, 1, 1, 1, 1, 1, 1, 1, 1);
--- 2 row(s) inserted.
SQL>insert into niz000 (seqno) values (3);
--- 1 row(s) inserted.
SQL>insert into niz000 values
(4, -32768, 0, -2147483648, 1, -9223372036854775808, 2, 3, 4, -99999999999999999
9, -999.999999999999999, 5, -999999999999999999, -999999999.999999999, -99999999
9999999999, -0.99, -9999999999999.99999, 6.000001, 7.000000000001, -2.2250738585
072014e-308, -1.17549435e-38, 8, -2.2250738585072014e-308, 9),
(5, 32767, 0, 2147483647, 1, 9223372036854775807, -2, 3, 4, 999999999999999999,
999.999999999999999, 5, 999999999999999999, 999999999.999999999, 999999999999999
999, 0.99, 9999999999999.99999, 6.000001, 7.000000000001, 1.7976931348623157e+30
8, 3.40282347e+38, 8, 1.7976931348623157e+308, 9),
(6, -459, 7892, -992348, 293847923, 701291928791, 9809876586, 5622901, 23990.78,
-129980, -46.012979798, 804, -62352342342, -8768889.1200454, -1283498234923749,
0.65, -7870234.00126, 786.124598, 11897.9998877656, 898889.00000997, 5.89100, 5
55.23, 10045.157e+8, -6120.00945e-21),
(7, 2834, 443, -90, 450, 4545990, 11, 102245, 77.923, -670, 831.44553, 21, 60778
, -486.929, -8798799000, 0.13, 34007.00387, 506.55209,
-99082.007, 6712009.22, -3.1417, 10.39e-1, 30887.333e+10, -445e-25);
--- 4 row(s) inserted.
SQL>select count(*) from niz000;
(EXPR)
--------------------
7
--- 1 row(s) selected.
SQL>update statistics for table niz000 on every column;
--- SQL operation complete.
SQL>create table niz001 like niz000;
--- SQL operation complete.
SQL>create index smin_mc_idx on niz001(smin1, smin2);
--- SQL operation complete.
SQL>create index inte_mc_idx on niz001(inte1, inte2);
--- SQL operation complete.
SQL>create index lint_mc_idx on niz001(lint1, lint2);
--- SQL operation complete.
SQL>create index nume_mc_idx on niz001(nume1, nume2, nume3, nume4);
--- SQL operation complete.
SQL>create index deci_mc_idx on niz001(deci1, deci2, deci3);
--- SQL operation complete.
SQL>create index pict_mc_idx on niz001(pict1, pict2, pict3, pict4);
--- SQL operation complete.
SQL>create index flot_mc_idx on niz001(flot1, flot2);
--- SQL operation complete.
SQL>create index real_mc_idx on niz001(real1, real2);
--- SQL operation complete.
SQL>create index dblp_mc_idx on niz001(dblp1, dblp2);
--- SQL operation complete.
SQL>insert into niz001 values
(1, 34, 44, 123, 789, 99809, 99909, 1223311, 12.333, 9899898979, 11.098080980, 2
3, 123134534534, 98797.8768688, -4435, -0.39, -121213434.111, 345.8879, -1.2250E
-28, 0.834673734574, 9.223e18, -1.79e-2, -0.00092746, -2.225E-38),
(2, 24923, 9992, -2147484, 9, 847201, 5098, 8365290, 12.333, 8998979, 444.238, 3
,789729834, 669.02983487, 9928, 0.96, 12345678.1234, 797.799, -7.9098700456e-1,
-0.7986750064688, 0.11, -0.7129, 2.222197264, 4.0876e-5),
(3, 108, 81, 925, 1351, 923749823, -615240, 81230, 119900.22, 7019239, 308.1274,
111, 6092340, 501293.5553, 8394230423, 0.01, 12111.0980, 405.0001, 0.128349234,
312.9999989e-5, 0.0008745245, -0.1007, 0.309e+3, 100 ),
(4, 25065, 10117, -2146436, 2149, 924696833, -510233, 9669831, 119924.886, 99159
17197, 763.463480980000001, 137, 123930356708, 600760.462003670, 8394235916, 0.5
8, -108855644.88960, 158.687000, -6.62637770560000000E-001, 3.91287280941999936E
-002, 9.22299985216877440E+018, -8.31499980762600960E-001, 3.11221269804000000E+
002, 1.00000040876000000E+002),
(5, 12532, 5058, -1073218, 1074, 462348416, -255116, 4834915, 59962.443, 4957958
598, 381.731740490000250, 68, 61965178354, 300380.231001835, 4197117958, 0.29, -
54427822.44480, 426.843503, -3.31318885280000000E-001, 1.95643640470999968E-002,
4.61149992608438720E+018, -4.15749993640929472E-001, 1.55610634902000000E+002,
5.00000204380000128E+001);
--- 5 row(s) inserted.
SQL>select count(*) from niz001;
(EXPR)
--------------------
5
--- 1 row(s) selected.
SQL>update statistics for table niz001 on every column;
--- SQL operation complete.
SQL>showcontrol all;
No CONTROL QUERY SHAPE settings are in effect.
No CONTROL SESSION settings are in effect.
No CONTROL TABLE settings are in effect.
CONTROL QUERY DEFAULT
SCHEMA MYTEST
Current DEFAULTS
AUTO_QUERY_RETRY_WARNINGS OFF
BLOCK_TO_PREVENT_HALLOWEEN ON
CACHE_HISTOGRAMS_REFRESH_INTERVAL 3600
CATALOG TRAFODION
COMPRESSION_TYPE NONE
DEFAULT_DEGREE_OF_PARALLELISM 2
DEF_MAX_HISTORY_ROWS 1024
DISPLAY_DIVISION_BY_COLUMNS OFF
EXE_MEMORY_LIMIT_PER_CPU 0
EXPLAIN_DESCRIPTION_COLUMN_SIZE -1
HASH_JOINS ON
HIST_AUTO_GENERATION_OF_SAMPLE OFF
HIST_MISSING_STATS_WARNING_LEVEL 4
HIST_NO_STATS_REFRESH_INTERVAL 3600
HIST_PREFETCH ON
HIST_REMOVE_TRAILING_BLANKS ON
HIST_ROWCOUNT_REQUIRING_STATS 50000
HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION ON
INSERT_VSBB SYSTEM
ISOLATION_LEVEL READ_COMMITTED
ISOLATION_LEVEL_FOR_UPDATES NONE
JOIN_ORDER_BY_USER OFF
LAST0_MODE OFF
MC_SKEW_SENSITIVITY_THRESHOLD 0.1
MDAM_NO_STATS_POSITIONS_THRESHOLD 10
MDAM_SCAN_METHOD ON
MDAM_UNDER_NJ_PROBES_THRESHOLD 0
MERGE_JOINS ON
MULTI_COMMIT_SIZE 10000
MVGROUP_AUTOMATIC_CREATION ON
MVQR_REWRITE_ENABLED_OPTION OFF
MVQR_REWRITE_LEVEL 0
MVQR_REWRITE_SINGLE_TABLE_QUERIES ON
MV_AGE
MV_ALLOW_SELECT_SYSTEM_ADDED_COLUMNS OFF
NESTED_JOINS ON
NOT_ATOMIC_FAILURE_LIMIT 32000
OPI_ERROR73_RETRIES 10
OPTIMIZATION_LEVEL 3
PARALLEL_NUM_ESPS SYSTEM
POS_ABSOLUTE_MAX_TABLE_SIZE
PUBLISHING_ROLES
QUERY_CACHE 16384
QUERY_LIMIT_SQL_PROCESS_CPU 0
RISK_PREMIUM_NJ 1.0
RISK_PREMIUM_SERIAL 1.0
RISK_PREMIUM_SERIAL_SCALEBACK_MAXCARD_THRESHOLD 10000
ROBUST_QUERY_OPTIMIZATION SYSTEM
SAVE_DROPPED_TABLE_DDL OFF
SCHEMA MYTEST
SKEW_EXPLAIN ON
SKEW_ROWCOUNT_THRESHOLD 1000000
SKEW_SENSITIVITY_THRESHOLD 0.1
STREAM_TIMEOUT -1
SUBQUERY_UNNESTING ON
TABLELOCK SYSTEM
TIMEOUT 6000
UDR_DEBUG_FLAGS 0
UPD_ORDERED ON
USTAT_AUTO_CV_SAMPLE_SLOPE 0.5
USTAT_AUTO_PRIORITY 150
USTAT_MAX_READ_AGE_IN_MIN 5760
USTAT_MIN_ROWCOUNT_FOR_LOW_SAMPLE 1000000
USTAT_MIN_ROWCOUNT_FOR_SAMPLE 10000
USTAT_NECESSARY_SAMPLE_MAX 5000000
USTAT_OBSOLETE_PERCENT_ROWCOUNT 15
WMS_CHILD_QUERY_MONITORING OFF
WMS_QUERY_MONITORING OFF
--- SQL operation complete.
SQL>drop schema mytest cascade;
*** ERROR[8606] Transaction subsystem TMF returned error 97 on a commit transact
ion. [2015-04-15 20:10:00]
SQL>set schema mytest;
--- SQL operation complete.
SQL>get tables;
Tables in Schema TRAFODION.MYTEST
=================================
NIZ000
NIZ001
SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
--- SQL operation complete.
SQL>drop table niz000;
*** ERROR[4254] Object TRAFODION.MYTEST.NIZ000 has invalid state and cannot be a
ccessed. Use cleanup command to drop it. [2015-04-15 20:10:35]
SQL>drop table niz001;
*** ERROR[4254] Object TRAFODION.MYTEST.NIZ001 has invalid state and cannot be a
ccessed. Use cleanup command to drop it. [2015-04-15 20:10:40]
SQL>drop table sb_histograms;
*** ERROR[4254] Object TRAFODION.MYTEST.SB_HISTOGRAMS has invalid state and cann
ot be accessed. Use cleanup command to drop it. [2015-04-15 20:10:43]
SQL>drop table sb_histogram_intervals;
*** ERROR[4254] Object TRAFODION.MYTEST.SB_HISTOGRAM_INTERVALS has invalid state
and cannot be accessed. Use cleanup command to drop it. [2015-04-15 20:10:51]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)