Seems I found the reason.
I'm try to upgrade my hive-0.5.0 to hive-0.7 today, and executed the sql in
upgrade directory, migrate conf to new hive. Then found that can't drop
table again.
When drop a table, I found there is a connection from hive try to do
something on "IDXS" table in postgress, and hung at this sql.
16385 | hive_metadb | 8259 | 16386 | hive | ALTER TABLE "IDXS" ADD
CONSTRAINT "IDXS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "SDS" (
"SD_ID") INITIALLY DEFERRED | t | 2011-04-28 13:54:16.384769+08 |
2011-04-28 13:54:16.404204+08 | 2011-04-28 13:54:01.784839+08 | 12
7.0.0.1 | 37438
But there is no table named "IDXS" exists. So I think this problem is cased
by these missing tables. Then I migrate a upgrade sql file from mysql, and
upgrade my postgress, the problem solved.
The attachment is the upgrade SQLs to run.
2011/1/6 wd <w...@wdicc.com>
> hi,
>
> I've setup a single node hadoop and hive. And can create table in hive, but
> can't drop table, hive cli will hang there, nothing more infos.
>
> hive-0.6.0-bin
> hadoop-0.20.2
> jre1.6.0_23
> postgresql-9.0-801.jdbc4.jar (have tried postgresql-8.4-701.jdbc4.jar)
> pgsql 9.0.2
>
> How to find what's wrong happed? thx.
>
CREATE TABLE "IDXS" (
"INDEX_ID" bigint NOT NULL,
"CREATE_TIME" int NOT NULL,
"DEFERRED_REBUILD" bit(1) NOT NULL,
"INDEX_HANDLER_CLASS" varchar(256) DEFAULT NULL,
"INDEX_NAME" varchar(128) DEFAULT NULL,
"INDEX_TBL_ID" bigint DEFAULT NULL,
"LAST_ACCESS_TIME" int NOT NULL,
"ORIG_TBL_ID" bigint DEFAULT NULL,
"SD_ID" bigint DEFAULT NULL,
PRIMARY KEY ("INDEX_ID"),
CONSTRAINT "UNIQUEINDEX" UNIQUE ("INDEX_NAME","ORIG_TBL_ID"),
CONSTRAINT "IDXS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID"),
CONSTRAINT "IDXS_FK2" FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID"),
CONSTRAINT "IDXS_FK3" FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
) ;
create index "IDXS_FK1" on "IDXS" using btree ("SD_ID");
create index "IDXS_FK2" on "IDXS" using btree ("INDEX_TBL_ID");
create index "IDXS_FK3" on "IDXS" using btree ("ORIG_TBL_ID");
CREATE TABLE "INDEX_PARAMS" (
"INDEX_ID" bigint NOT NULL,
"PARAM_KEY" varchar(256) NOT NULL,
"PARAM_VALUE" varchar(767) DEFAULT NULL,
PRIMARY KEY ("INDEX_ID","PARAM_KEY"),
CONSTRAINT "INDEX_PARAMS_FK1" FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID")
);
CREATE TABLE "ROLES" (
"ROLE_ID" bigint NOT NULL,
"CREATE_TIME" int NOT NULL,
"OWNER_NAME" varchar(128) DEFAULT NULL,
"ROLE_NAME" varchar(128) DEFAULT NULL,
PRIMARY KEY ("ROLE_ID"),
CONSTRAINT "ROLEENTITYINDEX" UNIQUE ("ROLE_NAME")
) ;
CREATE TABLE "ROLE_MAP" (
"ROLE_GRANT_ID" bigint NOT NULL,
"ADD_TIME" int NOT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"ROLE_ID" bigint DEFAULT NULL,
PRIMARY KEY ("ROLE_GRANT_ID"),
CONSTRAINT "USERROLEMAPINDEX" UNIQUE ("PRINCIPAL_NAME","ROLE_ID","GRANTOR","GRANTOR_TYPE"),
CONSTRAINT "ROLE_MAP_FK1" FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID")
) ;
CREATE TABLE "GLOBAL_PRIVS" (
"USER_GRANT_ID" bigint NOT NULL,
"CREATE_TIME" int NOT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"USER_PRIV" varchar(128) DEFAULT NULL,
PRIMARY KEY ("USER_GRANT_ID"),
CONSTRAINT "GLOBALPRIVILEGEINDEX" UNIQUE ("PRINCIPAL_NAME","PRINCIPAL_TYPE","USER_PRIV","GRANTOR","GRANTOR_TYPE")
) ;
CREATE TABLE "DB_PRIVS" (
"DB_GRANT_ID" bigint NOT NULL,
"CREATE_TIME" int NOT NULL,
"DB_ID" bigint DEFAULT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"DB_PRIV" varchar(128) DEFAULT NULL,
PRIMARY KEY ("DB_GRANT_ID"),
CONSTRAINT "DBPRIVILEGEINDEX" UNIQUE ("DB_ID","PRINCIPAL_NAME","PRINCIPAL_TYPE","DB_PRIV","GRANTOR","GRANTOR_TYPE"),
CONSTRAINT "DB_PRIVS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
) ;
CREATE TABLE "TBL_PRIVS" (
"TBL_GRANT_ID" bigint NOT NULL,
"CREATE_TIME" int NOT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"TBL_PRIV" varchar(128) DEFAULT NULL,
"TBL_ID" bigint DEFAULT NULL,
PRIMARY KEY ("TBL_GRANT_ID"),
CONSTRAINT "TBL_PRIVS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
) ;
create index "TABLEPRIVILEGEINDEX" on "TBL_PRIVS" using btree ("TBL_ID","PRINCIPAL_NAME","PRINCIPAL_TYPE","TBL_PRIV","GRANTOR","GRANTOR_TYPE");
CREATE TABLE "TBL_COL_PRIVS" (
"TBL_COLUMN_GRANT_ID" bigint NOT NULL,
"COLUMN_NAME" varchar(128) DEFAULT NULL,
"CREATE_TIME" int NOT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"TBL_COL_PRIV" varchar(128) DEFAULT NULL,
"TBL_ID" bigint DEFAULT NULL,
PRIMARY KEY ("TBL_COLUMN_GRANT_ID"),
CONSTRAINT "TBL_COL_PRIVS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
) ;
create index "TABLECOLUMNPRIVILEGEINDEX" on "TBL_COL_PRIVS" using btree ("TBL_ID","COLUMN_NAME","PRINCIPAL_NAME","PRINCIPAL_TYPE","TBL_COL_PRIV","GRANTOR","GRANTOR_TYPE");
CREATE TABLE "PART_PRIVS" (
"PART_GRANT_ID" bigint NOT NULL,
"CREATE_TIME" int NOT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PART_ID" bigint DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"PART_PRIV" varchar(128) DEFAULT NULL,
PRIMARY KEY ("PART_GRANT_ID"),
CONSTRAINT "PART_PRIVS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
) ;
create index "PARTPRIVILEGEINDEX" on "PART_PRIVS" using btree ("PART_ID","PRINCIPAL_NAME","PRINCIPAL_TYPE","PART_PRIV","GRANTOR","GRANTOR_TYPE");
CREATE TABLE "PART_COL_PRIVS" (
"PART_COLUMN_GRANT_ID" bigint NOT NULL,
"COLUMN_NAME" varchar(128) DEFAULT NULL,
"CREATE_TIME" int NOT NULL,
"GRANT_OPTION" smallint NOT NULL,
"GRANTOR" varchar(128) DEFAULT NULL,
"GRANTOR_TYPE" varchar(128) DEFAULT NULL,
"PART_ID" bigint DEFAULT NULL,
"PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
"PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
"PART_COL_PRIV" varchar(128) DEFAULT NULL,
PRIMARY KEY ("PART_COLUMN_GRANT_ID"),
CONSTRAINT "PART_COL_PRIVS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
);
create index "PARTITIONCOLUMNPRIVILEGEINDEX" on "PART_COL_PRIVS" using btree ("PART_ID","COLUMN_NAME","PRINCIPAL_NAME","PRINCIPAL_TYPE","PART_COL_PRIV","GRANTOR","GRANTOR_TYPE");
