[
https://issues.apache.org/jira/browse/TRAFODION-1182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15282272#comment-15282272
]
Prashanth Vasudev commented on TRAFODION-1182:
----------------------------------------------
Many fixes related to this scenario has been delivered to 2.0. See
TRAOFDION-1923 and TRAFODION-1948.
Please verify this problem exists anymore with the above fixes already in.
Thanks,
Prashanth
> 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
> Assignee: Prashanth Vasudev
> 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)
