Upgrading MetaStore schema from 0.12.0 to 0.13.0

[#1]

While upgrading HDP 2.0 to HDP 2.1 and Metastore Schema from 0.12 to 0.13 I got the Error: Duplicate column name ‘OWNER_NAME’ (state=42S21,code=1060). The Metastore version is 0.12 in the VERSION Table however the ‘OWNER_NAME’ column in the ‘DBS’ table already exists.

Here is the detailed error:

+———————————————+
| < HIVE-6386: Add owner filed to database > |
+———————————————+
1 row selected (0.001 seconds)
0: jdbc:mysql://hadoop.domain> ALTER TABLE <code>DBS

ADD OWNER_NAME varchar(128) Error: Duplicate column name ‘OWNER_NAME’ (state=42S21,code=1060) Closing: 0: jdbc:mysql://hadoop.domain/hive?createDatabaseIfNotExist=true org.apache.hadoop.hive.metastore.HiveMetaException: Upgrade FAILED! Metastore state would be inconsistent !! org.apache.hadoop.hive.metastore.HiveMetaException: Upgrade FAILED! Metastore state would be inconsistent !! at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:242) at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:211) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:489) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.hadoop.util.RunJar.main(RunJar.java:212) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:377) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:350) at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:237) … 7 more *** schemaTool failed ***

Has anyone run into the same issue? Any idea what is the source of this problem?

[#2]

One possibility this can happen if you previously were on Hive 0.12 and you upgraded the binaries to Hive 0.13 and started metastore, likely datanucleus.autoCreateSchema was set to true. This may have already upgraded some of the tables including the table DBS. Now when you run the schemaTool it fails.

[#3]

@Neeraj This issue is reproducible in customer environments, I have faced this a couple of times in customer environments during upgrade in past one month. This happens upgrading clusters with Hive0.12 or earlier which were created using datanucleus.autoCreateSchema.

[#4 · Accepted solution]

This happens if you previously were on Hive0.12 and metastore database was created by autoCreateSchema as @Deepesh. So, to start with, first of all set datanucleus.autoCreateSchema to false. Contact Hortonworks Support (support.hortonworks.com) while doing in production and ensure you have backed up Hive Metastore Database before doing this.

I have faced this issue many times in past while upgrade. And then, I resolve this by performing below steps.

###### Modify/correct table schemas and indexes

######Note: These are empty tables with wrong schema in Hive0.12.0 metastore schema created by autocreation. (Note: This is example of HDP2.2.8 and assumes MySQL as Metastore Datbase)

mysql -u hive -p
Enter Password:

mysql> use hive;
Database changed
DROP INDEX PCS_STATS_IDX ON PART_COL_STATS;
DROP TABLE TAB_COL_STATS;
DROP TABLE PART_COL_STATS;

#######Recreate these tables and index
-- Table structure for table `TAB_COL_STATS`
--
CREATE TABLE IF NOT EXISTS `TAB_COL_STATS` (
 `CS_ID` bigint(20) NOT NULL,
 `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `TBL_ID` bigint(20) NOT NULL,
 `LONG_LOW_VALUE` bigint(20),
 `LONG_HIGH_VALUE` bigint(20),
 `DOUBLE_HIGH_VALUE` double(53,4),
 `DOUBLE_LOW_VALUE` double(53,4),
 `BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
 `BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
 `NUM_NULLS` bigint(20) NOT NULL,
 `NUM_DISTINCTS` bigint(20),
 `AVG_COL_LEN` double(53,4),
 `MAX_COL_LEN` bigint(20),
 `NUM_TRUES` bigint(20),
 `NUM_FALSES` bigint(20),
 `LAST_ANALYZED` bigint(20) NOT NULL,
  PRIMARY KEY (`CS_ID`),
  CONSTRAINT `TAB_COL_STATS_FK` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `PART_COL_STATS`
--
CREATE TABLE IF NOT EXISTS `PART_COL_STATS` (
 `CS_ID` bigint(20) NOT NULL,
 `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `PARTITION_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `PART_ID` bigint(20) NOT NULL,
 `LONG_LOW_VALUE` bigint(20),
 `LONG_HIGH_VALUE` bigint(20),
 `DOUBLE_HIGH_VALUE` double(53,4),
 `DOUBLE_LOW_VALUE` double(53,4),
 `BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
 `BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
 `NUM_NULLS` bigint(20) NOT NULL,
 `NUM_DISTINCTS` bigint(20),
 `AVG_COL_LEN` double(53,4),
 `MAX_COL_LEN` bigint(20),
 `NUM_TRUES` bigint(20),
 `NUM_FALSES` bigint(20),
 `LAST_ANALYZED` bigint(20) NOT NULL,
  PRIMARY KEY (`CS_ID`),
  CONSTRAINT `PART_COL_STATS_FK` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE;

######Now, Edit this file '/usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/upgrade-0.12.0-to-0.13.0.mysql.sql' to correct path from relative path for following files otherwise metastore upgrade would fail as file paths will not resolve:
###replace '016-HIVE-6386.mysql.sql' with /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/016-HIVE-6386.mysql.sql
####replace '017-HIVE-6458.mysql.sql' with
/usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/017-HIVE-6458.mysql.sql
replace '018-HIVE-6757.mysql.sql' with /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/018-HIVE-6757.mysql.sql
####replace
'hive-txn-schema-0.13.0.mysql.sql' with
/usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/hive-txn-schema-0.13.0.mysql.sql

###On Hive Metastore Node:
cd /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/
vi /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/upgrade-0.12.0-to-0.13.0.mysql.sql

###Upgrade Hive Metastore Database
######
mysql -u hive -p
Enter Password:

mysql> use hive;
Database changed
mysql> source /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/upgrade-0.12.0-to-0.13.0.mysql.sql
..............
..............
..............
mysql>exit;
#######

/usr/hdp/2.2.8.0-3150/hive/bin/schematool -upgradeSchema -dbType mysql -userName hive -passWord gacluster -verbose
Metastore connection URL:        jdbc:mysql://gpgbyhppn02.srv.gapac.com/hive
Metastore Connection Driver :    com.mysql.jdbc.Driver
Metastore connection User:       hive
Starting upgrade metastore schema from version 0.13.0 to 0.14.0
Upgrade script upgrade-0.13.0-to-0.14.0.mysql.sql
Looking for pre-0-upgrade-0.13.0-to-0.14.0.mysql.sql in /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql
Connecting to jdbc:mysql://hive.srv.test.com/hive
Connected to: MySQL (version 5.1.66)
Driver: MySQL-AB JDBC Driver (version mysql-connector-java-5.1.17-SNAPSHOT ( Revision: ${bzr.revision-id} ))
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc:mysql://hive.srv.test.com/hiv> !autocommit on
Autocommit status: true
0: jdbc:mysql://hive.srv.test.com/hiv> SELECT 'Upgrading MetaStore schema from 0.13.0 to 0.14.0' AS ' '
+---------------------------------------------------+--+
|                                                   |
+---------------------------------------------------+--+
| Upgrading MetaStore schema from 0.13.0 to 0.14.0  |
+---------------------------------------------------+--+
1 row selected (0.015 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> CREATE TABLE IF NOT EXISTS `PART_COL_STATS` ( `CS_ID` bigint(20) NOT NULL, `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARTITION_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PART_ID` bigint(20) NOT NULL, `LONG_LOW_VALUE` bigint(20), `LONG_HIGH_VALUE` bigint(20), `DOUBLE_HIGH_VALUE` double(53,4), `DOUBLE_LOW_VALUE` double(53,4), `BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `NUM_NULLS` bigint(20) NOT NULL, `NUM_DISTINCTS` bigint(20), `AVG_COL_LEN` double(53,4), `MAX_COL_LEN` bigint(20), `NUM_TRUES` bigint(20), `NUM_FALSES` bigint(20), `LAST_ANALYZED` bigint(20) NOT NULL, PRIMARY KEY (`CS_ID`), CONSTRAINT `PART_COL_STATS_FK` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.002 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE
No rows affected (0.298 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> UPDATE VERSION SET SCHEMA_VERSION='0.14.0', VERSION_COMMENT='Hive release version 0.14.0' where VER_ID=1
1 row affected (0.101 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> SELECT 'Finished upgrading MetaStore schema from 0.13.0 to 0.14.0' AS ' '
+------------------------------------------------------------+--+
|                                                            |
+------------------------------------------------------------+--+
| Finished upgrading MetaStore schema from 0.13.0 to 0.14.0  |
+------------------------------------------------------------+--+
1 row selected (0.002 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> !closeall
Closing: 0: jdbc:mysql://hive.srv.test.com/hive
beeline>
Completed upgrade-0.13.0-to-0.14.0.mysql.sql
schemaTool completed

######Now, Start Hive Service from Ambari UI##