Trying to reinstall Hive but have metastore errors

[#1]

Hi, I am trying to reinstall Hive because i have issue with the metastore. I don't have an ibdata file anymore. I have run the schema tool but i still get the error below. I use mysql on HDP 2.4.3.

bin/schematool -initSchema -dbType mysql
WARNING: Use "yarn jar" to launch YARN applications.
Metastore connection URL:        jdbc:mysql://host/hivedb?createDatabaseIfNotExist=true
Metastore Connection Driver :    com.mysql.jdbc.Driver
Metastore connection User:       hive_dev
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
*** schemaTool failed ***

[#2]

@Joshua Adeleke

It may be due to already existing the database. Share the output of schematool -initSchema -dbType mysql -dryRun and output of following query from Hive metastore db:

select * from "VERSION";

[#3]

@Sindhu

Thanks. I recreated the hive DB when i reinstalled. see the output of the commands below;

bin/schematool -initSchema -dbType mysql -dryRun
WARNING: Use "yarn jar" to launch YARN applications.
Metastore connection URL:        jdbc:mysql://host/hivedb?createDatabaseIfNotExist=true
Metastore Connection Driver :    com.mysql.jdbc.Driver
Metastore connection User:       hive_dev
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
*** schemaTool failed ***
==================================================================================
output of select * from "VERSION"; on Hive metastore

mysql> select * from VERSION;
ERROR 1146 (42S02): Table 'hivedb.VERSION' doesn't exist

[#4]

The error message from ambari while installing below...

resource_management.core.exceptions.Fail: Execution of 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-metastore/bin/schematool -initSchema -dbType mysql -userName hive_dev -passWord [PROTECTED]' returned 1. WARNING: Use "yarn jar" to launch YARN applications.
Metastore connection URL:	 jdbc:mysql://host/hivedb?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 hive_dev
Starting metastore schema initialization to 1.2.1000
Initialization script hive-schema-1.2.1000.mysql.sql
Error: Specified key was too long; max key length is 1000 bytes (state=42000,code=1071)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
*** schemaTool failed ***

[#5]

@Joshua Adeleke

Seems like some tables are present on hive_dev which is causing the issue. Try using clean database for metastore and try.

[#6]

@Sindhu I used a clean database for the hive metastore and i still get the same error message (below). hive_dev is the user. @Benjamin Leonhardi @Sunile Manjee @Kuldeep Kulkarni

resource_management.core.exceptions.Fail: Execution of 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-metastore/bin/schematool -initSchema -dbType mysql -userName hive_dev -passWord [PROTECTED]' returned 1. WARNING: Use "yarn jar" to launch YARN applications.
Metastore connection URL:	 jdbc:mysql://host/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 hive_dev

Starting metastore schema initialization to 1.2.1000
Initialization script hive-schema-1.2.1000.mysql.sql
Error: Specified key was too long; max key length is 1000 bytes (state=42000,code=1071)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
*** schemaTool failed ***

[#7]

@Joshua Adeleke

Issue is related to MySQL limitation. Refer to below links to resolve the same:

https://confluence.atlassian.com/fishkb/mysql-database-migration-fails-with-specified-key-was-too-lo...

http://dba.stackexchange.com/questions/76567/how-to-resolve-specified-key-was-too-long-max-key-lengt...

[#8]

i need some help with this error??!!!!

hive> (base) hadoop@sajanraj:~$ schematool -dbType mysql -initSchema --verbose

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/usr/local/apache-hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.25.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://192.168.5.249:3306/hive?createDatabaseIfNotExist=true

Metastore Connection Driver : com.mysql.jdbc.Driver

Metastore connection User: hiveuser

Wed Jun 12 14:34:03 IST 2019 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 3.1.0

Initialization script hive-schema-3.1.0.mysql.sql

Connecting to jdbc:mysql://192.168.5.249:3306/hive?createDatabaseIfNotExist=true

Wed Jun 12 14:34:03 IST 2019 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.

Connected to: MySQL (version 5.7.26-0ubuntu0.18.04.1)

Driver: MySQL Connector Java (version mysql-connector-java-5.1.45 ( Revision: ${revinfo.commit} ))

Transaction isolation: TRANSACTION_READ_COMMITTED

0: jdbc:mysql://192.168.5.249:3306/hive> !autocommit on

Autocommit status: true

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */

No rows affected (0.002 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET NAMES utf8 */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40103 SET TIME_ZONE='+00:00' */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */

No rows affected (0.005 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */

No rows affected (0.002 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @saved_cs_client = @@character_set_client */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = utf8 */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( `SD_ID` bigint(20) NOT NULL, `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), KEY `BUCKETING_COLS_N49` (`SD_ID`), CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

No rows affected (0.007 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = @saved_cs_client */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @saved_cs_client = @@character_set_client */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = utf8 */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> CREATE TABLE IF NOT EXISTS `CDS` ( `CD_ID` bigint(20) NOT NULL, PRIMARY KEY (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = @saved_cs_client */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @saved_cs_client = @@character_set_client */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = utf8 */

No rows affected (0 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> CREATE TABLE IF NOT EXISTS `COLUMNS_V2` ( `CD_ID` bigint(20) NOT NULL, `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TYPE_NAME` MEDIUMTEXT DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`CD_ID`,`COLUMN_NAME`), KEY `COLUMNS_V2_N49` (`CD_ID`), CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

No rows affected (0.002 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = @saved_cs_client */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET @saved_cs_client = @@character_set_client */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = utf8 */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( `DB_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`DB_ID`,`PARAM_KEY`), KEY `DATABASE_PARAMS_N49` (`DB_ID`), CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> /*!40101 SET character_set_client = @saved_cs_client */

No rows affected (0.001 seconds)

0: jdbc:mysql://192.168.5.249:3306/hive> CREATE TABLE `CTLGS` ( `CTLG_ID` BIGINT PRIMARY KEY, `NAME` VARCHAR(256), `DESC` VARCHAR(4000), `LOCATION_URI` VARCHAR(4000) NOT NULL, UNIQUE KEY `UNIQUE_CATALOG` (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Error: Table 'CTLGS' already exists (state=42S01,code=1050)

Closing: 0: jdbc:mysql://192.168.5.249:3306/hive?createDatabaseIfNotExist=true

org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!

Underlying cause: java.io.IOException : Schema script failed, errorcode 2

org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!

at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:594)

at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:567)

at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1517)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.hadoop.util.RunJar.run(RunJar.java:323)

at org.apache.hadoop.util.RunJar.main(RunJar.java:236)

Caused by: java.io.IOException: Schema script failed, errorcode 2

at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1226)

at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1204)

at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:590)

... 8 more

*** schemaTool failed ***