@Rahul P This is known issue. To resolve this: Please take backup of your metastore DB before doing any changes. On the Hive Metastore installed node: 1) As the error mentions "Initialization script hive-schema-2.1.1000.mysql.sql", check /usr/hdp/current/hive-server2-hive2/scripts/metastore/upgrade/mysql/hive-*schema-2.1.0.mysql.sql For example: cd /usr/hdp/current/hive-server2-hive2/scripts/metastore/upgrade/mysql/
grep 'CREATE INDEX' hive-*schema-2.1.0.mysql.sql
hive-schema-2.1.1000.mysql.sql:CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE;
hive-schema-2.1.1000.mysql.sql:CREATE INDEX `CONSTRAINTS_PARENT_TABLE_ID_INDEX` ON KEY_CONSTRAINTS (`PARENT_TBL_ID`) USING BTREE;
hive-txn-schema-2.1.0.mysql.sql:CREATE INDEX HL_TXNID_IDX ON HIVE_LOCKS (HL_TXNID); 2) Delete above indexes from MySQL: Make sure you are using the database that is specified in log above. Connecting to jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true use <hivedB>
drop index CONSTRAINTS_PARENT_TABLE_ID_INDEX on KEY_CONSTRAINTS;
drop index PCS_STATS_IDX on PART_COL_STATS;
drop index HL_TXNID_IDX on HIVE_LOCKS; 3) Add "IF NOT EXISTS" in the following tables by editing hive-txn-schema-2.1.0.mysql.sql file: grep 'CREATE TABLE' hive-*schema-2.1.0.mysql.sql | grep -v 'IF NOT EXISTS'
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE TXNS (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE TXN_COMPONENTS (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE COMPLETED_TXN_COMPONENTS (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE NEXT_TXN_ID (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE HIVE_LOCKS (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE NEXT_LOCK_ID (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE COMPACTION_QUEUE (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE COMPLETED_COMPACTIONS (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE NEXT_COMPACTION_QUEUE_ID (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE AUX_TABLE (
hive-txn-schema-2.1.0.mysql.sql:CREATE TABLE WRITE_SET ( For example: sed -i.bak 's/^CREATE TABLE \([^I][^F]\)/CREATE TABLE IF NOT EXISTS \1/g' hive-txn-schema-2.1.0.mysql.sql 4) Starting Hive Metastore from Ambari 5) After starting the metastore, if the following error happens, remove the conflicting row and start again. 0: jdbc:mysql://sandbox.hortonworks.com/hive> INSERT INTO VERSION (VER_ID, SCHEMA
_VERSION, VERSION_COMMENT) VALUES (1, '2.1.0', 'Hive release version 2.1.0')
Error: Duplicate entry '1' for key 'PRIMARY' (state=23000,code=1062)
... View more
