hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zhenxiao Luo (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HIVE-3535) derby metastore upgrade script throw errors when updating from 0.7 to 0.8
Date Thu, 04 Oct 2012 22:09:47 GMT
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

Mime
View raw message