schematool error

[#1]

Please help to resolve the schematool error

$HIVE_HOME/bin/schematool -initSchema -dbType mysql SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/lib/hive/apache-hive-2.3.4-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory] Metastore connection URL: jdbc:mysql://localhost/metastore_db?createDatabaseIfNotExist=true Metastore Connection Driver : com.mysql.jdbc.Driver Metastore connection User: hiveuser Mon Nov 12 11:14:46 UTC 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. Starting metastore schema initialization to 2.3.0 Initialization script hive-schema-2.3.0.mysql.sql Mon Nov 12 11:14:47 UTC 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 Use --verbose for detailed stacktrace. *** schemaTool failed ***

[#2]

Are you trying to setup hive metastore for the first time? Based on below it either looks like you are run init from hive client of a different version or you have run it multiple times that entry already committed into mysql db due to first run.

I faced below problem when I did hive upgrade from 1.2.x to 2.0 as part of migration effort to Ambari from the Apache hive.

Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 Use --verbose for detailed stacktrace. *** schemaTool failed ***

One thing I figured out is rather than running upgrade scripts individually better to do it using below approach.

 export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server

 [root@myhost ~]# /usr/hdp/current/hive-server2-hive2/bin/schematool -upgradeSchema -dbType mysql -userName hive_user -passWord 'XXXXXXXXXX'
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.5.5.0-157/hive2/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.5.5.0-157/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL: jdbc:mysql://XXXXX/hive_db Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: hive_user
Starting upgrade metastore schema from version 1.2.0 to 2.1.0
Upgrade script upgrade-1.2.0-to-1.2.1000.mysql.sql
Completed upgrade-1.2.0-to-1.2.1000.mysql.sql
Upgrade script upgrade-1.2.1000-to-2.0.0.mysql.sql
Completed upgrade-1.2.1000-to-2.0.0.mysql.sql
Upgrade script upgrade-2.0.0-to-2.1.0.mysql.sql
Completed upgrade-2.0.0-to-2.1.0.mysql.sql
schemaTool completed

note: If your hive metadata is important, before you do any upgrade , I strongly suggest to take backup of it using mysqldump utility.