[ https://issues.apache.org/jira/browse/HIVE-3794?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13535612#comment-13535612 ]
Hudson commented on HIVE-3794: ------------------------------ Integrated in Hive-0.10.0-SNAPSHOT-h0.20.1 #7 (See [https://builds.apache.org/job/Hive-0.10.0-SNAPSHOT-h0.20.1/7/]) HIVE-3794 : Oracle upgrade script for Hive is broken (Deepesh Khandelwal via Ashutosh Chauhan) (Revision 1423482) Result = ABORTED hashutosh : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1423482 Files : * /hive/branches/branch-0.10/metastore/scripts/upgrade/derby/010-HIVE-3072.derby.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/derby/hive-schema-0.10.0.derby.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/mysql/010-HIVE-3072.mysql.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/mysql/hive-schema-0.10.0.mysql.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/oracle/010-HIVE-3072.oracle.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/oracle/hive-schema-0.10.0.oracle.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/postgres/010-HIVE-3072.postgres.sql * /hive/branches/branch-0.10/metastore/scripts/upgrade/postgres/hive-schema-0.10.0.postgres.sql * /hive/branches/branch-0.10/metastore/src/model/package.jdo > Oracle upgrade script for Hive is broken > ---------------------------------------- > > Key: HIVE-3794 > URL: https://issues.apache.org/jira/browse/HIVE-3794 > Project: Hive > Issue Type: Bug > Components: SQL > Affects Versions: 0.10.0 > Environment: Oracle 11g r2 > Reporter: Deepesh Khandelwal > Assignee: Deepesh Khandelwal > Priority: Critical > Fix For: 0.10.0 > > Attachments: HIVE-3794.patch > > > As part of Hive configuration for Oracle I ran the schema creation script for > Oracle. Here is what I observed when ran the script: > % sqlplus hive/hive@xe > SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 10 18:47:11 2012 > Copyright (c) 1982, 2011, Oracle. All rights reserved. > Connected to: > Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production > SQL> @scripts/metastore/upgrade/oracle/hive-schema-0.10.0.oracle.sql; > ..... > ALTER TABLE SKEWED_STRING_LIST_VALUES ADD CONSTRAINT > SKEWED_STRING_LIST_VALUES_FK1 FOREIGN KEY (STRING_LIST_ID) REFERENCES > SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED > > * > ERROR at line 1: > {color:red}ORA-00904: "STRING_LIST_ID": invalid identifier{color} > ..... > ALTER TABLE SKEWED_STRING_LIST_VALUES ADD CONSTRAINT > SKEWED_STRING_LIST_VALUES_FK1 FOREIGN KEY (STRING_LIST_ID) REFERENCES > SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED > > * > ERROR at line 1: > {color:red}ORA-00904: "STRING_LIST_ID": invalid identifier{color} > Table created. > Table altered. > Table altered. > CREATE TABLE SKEWED_COL_VALUE_LOCATION_MAPPING > * > ERROR at line 1: > {color:red}ORA-00972: identifier is too long{color} > Table created. > Table created. > ALTER TABLE SKEWED_COL_VALUE_LOCATION_MAPPING ADD CONSTRAINT > SKEWED_COL_VALUE_LOCATION_MAPPING_PK PRIMARY KEY (SD_ID,STRING_LIST_ID_KID) > * > ERROR at line 1: > {color:red}ORA-00972: identifier is too long{color} > ALTER TABLE SKEWED_COL_VALUE_LOCATION_MAPPING ADD CONSTRAINT > SKEWED_COL_VALUE_LOCATION_MAPPING_FK1 FOREIGN KEY (STRING_LIST_ID_KID) > REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED > * > ERROR at line 1: > {color:red}ORA-00972: identifier is too long{color} > ALTER TABLE SKEWED_COL_VALUE_LOCATION_MAPPING ADD CONSTRAINT > SKEWED_COL_VALUE_LOCATION_MAPPING_FK2 FOREIGN KEY (SD_ID) REFERENCES SDS > (SD_ID) INITIALLY DEFERRED > * > ERROR at line 1: > {color:red}ORA-00972: identifier is too long{color} > Table created. > Table altered. > ALTER TABLE SKEWED_VALUES ADD CONSTRAINT SKEWED_VALUES_FK1 FOREIGN KEY > (STRING_LIST_ID_EID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY > DEFERRED > > * > ERROR at line 1: > {color:red}ORA-00904: "STRING_LIST_ID": invalid identifier{color} > Basically there are two issues here with the Oracle sql script: > (1) Table "SKEWED_STRING_LIST" is created with the column "SD_ID". Later the > script tries to reference "STRING_LIST_ID" column in "SKEWED_STRING_LIST" > which is obviously not there. Comparing the sql with that for other flavors > it seems it should be "STRING_LIST_ID". > (2) Table name "SKEWED_COL_VALUE_LOCATION_MAPPING" is too long for Oracle > which limits identifier names to 30 characters. Also impacted are identifiers > "SKEWED_COL_VALUE_LOCATION_MAPPING_PK" and > "SKEWED_COL_VALUE_LOCATION_MAPPING_FK1". -- 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