Zhenxiao Luo created HIVE-3535:
----------------------------------
Summary: derby metastore upgrade script throw errors when updating
from 0.7 to 0.8
Key: HIVE-3535
URL: https://issues.apache.org/jira/browse/HIVE-3535
Project: Hive
Issue Type: Bug
Components: Metastore
Affects Versions: 0.9.0
Reporter: Zhenxiao Luo
Assignee: Zhenxiao Luo
Fix For: 0.10.0
$DERBY_HOME/bin/ij upgrade.sql
ij version 10.4
ij> CONNECT 'jdbc:derby:/var/lib/hive/metastore/metastore_db';
ij> RUN
'/usr/lib/hive/scripts/metastore/upgrade/derby/upgrade-0.7.0-to-0.8.0.derby.sql';
ij> -- Upgrade MetaStore schema from 0.7.0 to 0.8.0
RUN '008-HIVE-2246.derby.sql';
ij> /*
* Creates the following tables:
* - CDS
* - COLUMNS_V2
* The new columns table is called COLUMNS_V2
* because many columns are removed, and the schema is changed.
* It'd take too long to migrate and keep the same table.
*/
CREATE TABLE "CDS" (
"CD_ID" bigint NOT NULL,
PRIMARY KEY ("CD_ID")
);
0 rows inserted/updated/deleted
ij> CREATE TABLE "COLUMNS_V2" (
"CD_ID" bigint NOT NULL,
"COMMENT" varchar(4000),
"COLUMN_NAME" varchar(128) NOT NULL,
"TYPE_NAME" varchar(4000),
"INTEGER_IDX" INTEGER NOT NULL,
PRIMARY KEY ("CD_ID", "COLUMN_NAME")
);
0 rows inserted/updated/deleted
ij> ALTER TABLE "COLUMNS_V2"
ADD CONSTRAINT "COLUMNS_V2_FK1"
FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")
ON DELETE NO ACTION ON UPDATE NO ACTION
;
0 rows inserted/updated/deleted
ij> /* Alter the SDS table to:
* - add the column CD_ID
* - add a foreign key on CD_ID
* - create an index on CD_ID
*/
ALTER TABLE SDS
ADD COLUMN "CD_ID" bigint
;
0 rows inserted/updated/deleted
ij> ALTER TABLE SDS
ADD CONSTRAINT "SDS_FK2"
FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")
;
0 rows inserted/updated/deleted
ij> /*
* Migrate the TBLS table
* Add entries into CDS.
* Populate the CD_ID field in SDS for tables
* Add entires to COLUMNS_V2 based on this table's sd's columns
*/
/* In the migration, there is a 1:1 mapping between CD_ID and SD_ID
* for tables. For speed, just let CD_ID = SD_ID for tables
*/
INSERT INTO CDS (CD_ID)
SELECT t.SD_ID FROM TBLS t WHERE t.SD_ID IS NOT NULL ORDER BY t.SD_ID;
ERROR 42X01: Syntax error: Encountered "ORDER" at line 13, column 54.
ij> UPDATE SDS
SET CD_ID = SD_ID
WHERE SD_ID in
(SELECT t.SD_ID FROM TBLS t WHERE t.SD_ID IS NOT NULL ORDER BY t.SD_ID);
ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
ij> INSERT INTO COLUMNS_V2
(CD_ID, COMMENT, COLUMN_NAME, TYPE_NAME, INTEGER_IDX)
SELECT
c.SD_ID, c.COMMENT, c.COLUMN_NAME, c.TYPE_NAME, c.INTEGER_IDX
FROM
COLUMNS c
JOIN
TBLS t
ON
t.SD_ID = c.SD_ID
;
ERROR 23503: INSERT on table 'COLUMNS_V2' caused a violation of foreign key
constraint 'COLUMNS_V2_FK1' for key (1). The statement has been rolled back.
ij> /*
* Migrate the partitions.
* Update the partitions' SDS to use the parent tables' CD_ID BEGIN
* Derby does not allow joins in update statements,
* so we have to make a temporary tableh
*/
DECLARE GLOBAL TEMPORARY TABLE "TMP_TBL" (
"SD_ID" bigint not null,
"CD_ID" bigint not null
) ON COMMIT PRESERVE ROWS NOT LOGGED;
0 rows inserted/updated/deleted
ij> INSERT INTO "SESSION"."TMP_TBL" SELECT
p.SD_ID, sds.CD_ID
FROM PARTITIONS p
JOIN TBLS t ON t.TBL_ID = p.TBL_ID
JOIN SDS sds on t.SD_ID = sds.SD_ID
WHERE p.SD_ID IS NOT NULL;
ERROR 23502: Column 'CD_ID' cannot accept a NULL value.
ij> UPDATE SDS sd
SET sd.CD_ID =
(SELECT tt.CD_ID FROM SESSION.TMP_TBL tt WHERE tt.SD_ID = sd.SD_ID)
WHERE sd.SD_ID IN (SELECT SD_ID FROM SESSION.TMP_TBL);
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.
ij> /*
* Migrate IDXS
*/
INSERT INTO CDS (CD_ID)
SELECT i.SD_ID FROM IDXS i WHERE i.SD_ID IS NOT NULL ORDER BY i.SD_ID;
ERROR 42X01: Syntax error: Encountered "ORDER" at line 5, column 54.
ij> UPDATE SDS
SET CD_ID = SD_ID
WHERE SD_ID in
(SELECT i.SD_ID FROM IDXS i WHERE i.SD_ID IS NOT NULL ORDER BY i.SD_ID);
ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
ij> INSERT INTO COLUMNS_V2
(CD_ID, COMMENT, COLUMN_NAME, TYPE_NAME, INTEGER_IDX)
SELECT
c.SD_ID, c.COMMENT, c.COLUMN_NAME, c.TYPE_NAME, c.INTEGER_IDX
FROM
COLUMNS c
JOIN
IDXS i
ON
i.SD_ID = c.SD_ID
;
ERROR 42X05: Table/View 'IDXS' does not exist.
ij> /*
* rename the old COLUMNS table
*/
RENAME TABLE COLUMNS TO COLUMNS_OLD;
0 rows inserted/updated/deleted
ij> RUN '009-HIVE-2215.derby.sql';
ij> -- Table PARTITION_EVENTS for classes
[org.apache.hadoop.hive.metastore.model.MPartitionEvent]
CREATE TABLE PARTITION_EVENTS
(
PART_NAME_ID BIGINT NOT NULL,
DB_NAME VARCHAR(128),
EVENT_TIME BIGINT NOT NULL,
EVENT_TYPE INTEGER NOT NULL,
PARTITION_NAME VARCHAR(767),
TBL_NAME VARCHAR(128)
);
0 rows inserted/updated/deleted
ij> ALTER TABLE PARTITION_EVENTS ADD CONSTRAINT PARTITION_EVENTS_PK PRIMARY KEY
(PART_NAME_ID);
0 rows inserted/updated/deleted
7 Errors in the middle:
ERROR 42X01: Syntax error: Encountered "ORDER" at line 13, column 54.
ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
ERROR 23503: INSERT on table 'COLUMNS_V2' caused a violation of foreign key
constraint 'COLUMNS_V2_FK1' for key (1). The statement has been rolled back.
ERROR 23502: Column 'CD_ID' cannot accept a NULL value.
ERROR 42X01: Syntax error: Encountered "ORDER" at line 5, column 54.
ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
ERROR 42X05: Table/View 'IDXS' does not exist.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
