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:18:11 UTC
[jira] [Created] (TRAFODION-792) LP Bug: 1396311 - Failed alter
table causes schema to become un-droppable
Alice Chen created TRAFODION-792:
------------------------------------
Summary: LP Bug: 1396311 - Failed alter table causes schema to become un-droppable
Key: TRAFODION-792
URL: https://issues.apache.org/jira/browse/TRAFODION-792
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Weishiun Tsai
Assignee: Anoop Sharma
Priority: Blocker
When the following alter table statement fails, the schema becomes un-droppable. After the failure, there is no object related to that schema in hbase, but constraints and keys remain in the Trafodion “_MD_” tables. At that point, ‘drop table cascade’ and ‘drop schema cascade’ can no longer be used to clean up the related entries in the “_MD_” tables and ‘get schemas’ always shows the schema even after earlier attempts of ‘drop schema cascade’.
This is seen on the v1125_0830 build installed on a workstation. Since this is yet another one of the many causes that lead to inconsistent Trafodion metadata, the bug report is marked as critical.
----------------------------------------------------------------------------------------------------------
Here is the entire script to reproduce this problem and the queries to query the “_MD_” tables for the schema afterwards.
get schemas;
set schema trafodion.MYSCHEMA1;
create table a6t2 (
char200 char(200) not null not droppable,
char50 char(50) not null not droppable,
char5 char(5) not null not droppable,
unique (char200, char50, char5)) no partition;
insert into a6t2 values
('aaaaaaa','bbbbb','ccccc'),
('aaaaaaa','bbbb','ccccc');
alter table a6t2 add primary key (char5);
drop table a6t2 cascade;
drop schema trafodion.MYSCHEMA1 cascade;
get schemas;
set param ?mysch 'MYSCHEMA1';
set schema my_cleanup_schema;
create table UID (object_uid largeint no default not null not droppable primary key);
insert into UID (select OBJECT_UID from "_MD_".OBJECTS where SCHEMA_NAME=?mysch);
select * from "_MD_".VIEWS_USAGE where USING_VIEW_UID in (select * from UID) or USED_OBJECT_UID in (select * from UID);
select * from "_MD_".VIEWS where VIEW_UID in (select * from UID);
select * from "_MD_".UNIQUE_REF_CONSTR_USAGE where UNIQUE_CONSTRAINT_UID in (select * from UID) or FOREIGN_CONSTRAINT_UID in (select * from UID);
select * from "_MD_".TEXT where TEXT_UID in (select * from UID);
select * from "_MD_".TABLE_CONSTRAINTS where TABLE_UID in (select * from UID) or CONSTRAINT_UID in (select * from UID) or INDEX_UID in (select * from UID);
select * from "_MD_".TABLES where TABLE_UID in (select * from UID);
select * from "_MD_".SEQ_GEN where SEQ_UID in (select * from UID);
select * from "_MD_".ROUTINES where UDR_UID in (select * from UID) or LIBRARY_UID in (select * from UID);
select * from "_MD_".REF_CONSTRAINTS where REF_CONSTRAINT_UID in (select * from UID) or UNIQUE_CONSTRAINT_UID in (select * from UID);
select * from "_MD_".LIBRARIES where LIBRARY_UID in (select * from UID);
select * from "_MD_".LIBRARIES_USAGE where USING_LIBRARY_UID in (select * from UID) or USED_UDR_UID in (select * from UID);
select * from "_MD_".KEYS where OBJECT_UID in (select * from UID);
select * from "_MD_".INDEXES where BASE_TABLE_UID in (select * from UID) or INDEX_UID in (select * from UID);
select * from "_MD_".COLUMNS where OBJECT_UID in (select * from UID);
select * from "_MD_".OBJECTS where OBJECT_UID in (select * from UID);
drop table UID cascade;
----------------------------------------------------------------------------------------------------------
Here is the execution output showing that the schema becomes un-droppable after the alter table failure and the queries showing that keys and constraints remain in the “_MD_” tables. At that moment, there is nothing in hbase related to that schema anymore.
>>get schemas;
Schemas in Catalog TRAFODION
============================
_MD_
--- SQL operation complete.
>>set schema trafodion.MYSCHEMA1;
--- SQL operation complete.
>>
>>create table a6t2 (
+>char200 char(200) not null not droppable,
+>char50 char(50) not null not droppable,
+>char5 char(5) not null not droppable,
+>unique (char200, char50, char5)) no partition;
--- SQL operation complete.
>>
>>insert into a6t2 values
+>('aaaaaaa','bbbbb','ccccc'),
+>('aaaaaaa','bbbb','ccccc');
--- 2 row(s) inserted.
>>
>>alter table a6t2 add primary key (char5);
*** ERROR[8110] Duplicate rows detected.
*** ERROR[1029] Object TRAFODION.MYSCHEMA1.A6T2_335452946_5568 could not be created.
--- SQL operation failed with errors.
>>
>>drop table a6t2 cascade;
--- SQL operation complete.
>>
>>drop schema trafodion.MYSCHEMA1 cascade;
--- SQL operation complete.
>>
>>get schemas;
Schemas in Catalog TRAFODION
============================
MYSCHEMA1
_MD_
--- SQL operation complete.
>>
>>set param ?mysch 'MYSCHEMA1';
>>set schema my_cleanup_schema;
--- SQL operation complete.
>>create table UID (object_uid largeint no default not null not droppable primary key);
--- SQL operation complete.
>>insert into UID (select OBJECT_UID from "_MD_".OBJECTS where SCHEMA_NAME=?mysch);
--- 1 row(s) inserted.
>>select * from "_MD_".VIEWS_USAGE where USING_VIEW_UID in (select * from UID) or USED_OBJECT_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".VIEWS where VIEW_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".UNIQUE_REF_CONSTR_USAGE where UNIQUE_CONSTRAINT_UID in (select * from UID) or FOREIGN_CONSTRAINT_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".TEXT where TEXT_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".TABLE_CONSTRAINTS where TABLE_UID in (select * from UID) or CONSTRAINT_UID in (select * from UID) or INDEX_UID in (select * from UID);
TABLE_UID CONSTRAINT_UID CONSTRAINT_TYPE DISABLED DROPPABLE IS_DEFERRABLE ENFORCED VALIDATED LAST_VALIDATED COL_COUNT INDEX_UID FLAGS
-------------------- -------------------- --------------- -------- --------- ------------- -------- --------- -------------------- ----------- -------------------- --------------------
28199067276446619 28199067276448434 U N N N Y Y 212283702074405874 1 0 0
--- 1 row(s) selected.
>>select * from "_MD_".TABLES where TABLE_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".SEQ_GEN where SEQ_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".ROUTINES where UDR_UID in (select * from UID) or LIBRARY_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".REF_CONSTRAINTS where REF_CONSTRAINT_UID in (select * from UID) or UNIQUE_CONSTRAINT_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".LIBRARIES where LIBRARY_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".LIBRARIES_USAGE where USING_LIBRARY_UID in (select * from UID) or USED_UDR_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".KEYS where OBJECT_UID in (select * from UID);
OBJECT_UID COLUMN_NAME KEYSEQ_NUMBER COLUMN_NUMBER ORDERING NONKEYCOL FLAGS
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ----------- ----------- --------------------
28199067276448434 CHAR5 1 3 0 0 0
--- 1 row(s) selected.
>>select * from "_MD_".INDEXES where BASE_TABLE_UID in (select * from UID) or INDEX_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".COLUMNS where OBJECT_UID in (select * from UID);
--- 0 row(s) selected.
>>select * from "_MD_".OBJECTS where OBJECT_UID in (select * from UID);
CATALOG_NAME SCHEMA_NAME OBJECT_NAME OBJECT_TYPE OBJECT_UID CREATE_TIME REDEF_TIME VALID_DEF DROPPABLE OBJECT_OWNER SCHEMA_OWNER FLAGS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- -------------------- -------------------- --------- --------- ------------ ------------ --------------------
TRAFODION MYSCHEMA1 A6T2_335452946_5568 UC 28199067276448434 212283702074405874 212283702074405874 N 33333 33333 0
--- 1 row(s) selected.
>>drop table UID cascade;
-bash-4.1$ ../local_hadoop/hbase/bin/hbase shell
2014-11-25 19:03:29,726 INFO [main] Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available
HBase Shell; enter 'help<RETURN>' for list of supported commands.
Type "exit<RETURN>" to leave the HBase Shell
Version 0.98.1-cdh5.1.0, rUnknown, Sat Jul 12 08:20:07 PDT 2014
hbase(main):001:0> list 'TRAFODION.MYSCHEMA1.*'
TABLE
0 row(s) in 1.1140 seconds
=> []
hbase(main):002:0>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)