Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Date: Thu, 18 Oct 2012 01:16:03 +0000 (UTC)
From: "Carl Steinbach (JIRA)" <jira@apache.org>
To: hive-dev@hadoop.apache.org
Message-ID: <2092453756.61485.1350522963826.JavaMail.jiratomcat@arcas>
In-Reply-To: <683635849.2593.1349388587330.JavaMail.jiratomcat@arcas>
Subject: [jira] [Commented] (HIVE-3535) derby metastore upgrade script throw
 errors when updating from 0.7 to 0.8
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/HIVE-3535?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13478559#comment-13478559 ] 

Carl Steinbach commented on HIVE-3535:
--------------------------------------

+1. Will commit if tests pass. Thanks.
                
> 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
>
>         Attachments: HIVE-3535.1.patch.txt
>
>
> $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