You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2015/10/08 01:14:27 UTC
[jira] [Updated] (TRAFODION-1182) LP Bug: 1444684 - Drop schema
fails and objects become un-droppable with invalid state
[ https://issues.apache.org/jira/browse/TRAFODION-1182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Suresh Subbiah updated TRAFODION-1182:
--------------------------------------
Assignee: Prashanth Vasudev
> 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)