question Re: Hive metastore installation fails with constraints issues in Archives of Support Questions (Read Only)

Hive metastore installation fails with constraints issues

gunnar_tapper — Fri, 16 Sep 2022 10:21:22 GMT

I'm trying to install HDP 2.3 on an AWS cluster. HDP 2.3.4.7-4.

Starting the Hive Metastore yields: 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 -passWord [PROTECTED]' returned 1. WARNING: Use "yarn jar" to launch YARN applications.

Metastore connection URL:	 jdbc:mysql://ip-172-31-53-134.ec2.internal/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 hive
Starting metastore schema initialization to 1.2.0
Initialization script hive-schema-1.2.0.mysql.sql
Error: Can't create table 'hive.BUCKETING_COLS' (errno: 121) (state=HY000,code=1005)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
*** schemaTool failed ***

Version information:


Hive 1.2.1.2.3.4.7-4
Subversion git://c66-slave-20176e25-3/grid/0/jenkins/workspace/HDP-patch-centos6/bigtop/build/hive/rpm/BUILD/hive-1.2.1.2.3.4.7 -r eb78776f8bfd65b27ded6c1736ecb42d5c06c137
Compiled by jenkins on Thu Feb 11 08:27:22 UTC 2016
From source with checksum 79234aeebb6f0580c584c61257828e61

So, I decided to try to source in the schema DDL from the mysql shell. A LOT of error 121; that is, constraint issues.

My first attempt was to source in /usr/hdp/2.3.4.7-4/hive/scripts/metastore/upgrade/mysql/hive-schema-1.2.0.mysql.sql, which yielded many errors. So, it must be the DDL itself. Partial output:

mysql> source hive-schema-1.2.0.mysql.sql;
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.BUCKETING_COLS' (errno: 121)
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.CDS' (errno: 121)
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.COLUMNS_V2' (errno: 121)
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.DATABASE_PARAMS' (errno: 121)
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.DBS' (errno: 121)
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.DB_PRIVS' (errno: 121)
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


ERROR 1005 (HY000): Can't create table 'hive.GLOBAL_PRIVS' (errno: 121)
Query OK, 0 rows affected (0.00 sec)

So, I tried with a single CREATE TABLE example:



mysql> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40101 SET NAMES utf8 */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40103 SET TIME_ZONE='+00:00' */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Query OK, 0 rows affected (0.00 sec)


mysql> 
mysql> --
mysql> -- Table structure for table `BUCKETING_COLS`
mysql> --
mysql> 
mysql> /*!40101 SET @saved_cs_client     = @@character_set_client */;
Query OK, 0 rows affected (0.00 sec)


mysql> /*!40101 SET character_set_client = utf8 */;
Query OK, 0 rows affected (0.00 sec)


mysql> 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;
ERROR 1005 (HY000): Can't create table 'hive.BUCKETING_COLS' (errno: 121)
mysql> 


I've also added a new metastore on a different node and deleted the old one. No difference.

Re: Hive metastore installation fails with constraints issues

KuldeepK — Tue, 24 May 2016 13:10:35 GMT

@Gunnar Tapper - Please run below command and see what error says:

/usr/hdp/current/hive-metastore/bin/schematool -initSchema -dbType mysql -userName hive -passWord '$password' -verbose

Re: Hive metastore installation fails with constraints issues

gunnar_tapper — Tue, 24 May 2016 13:13:41 GMT

@Kuldeep Kulkarni

WARNING: Use "yarn jar" to launch YARN applications. Metastore connection URL: jdbc:mysql://ip-172-31-53-134.ec2.internal/hive?createDatabaseIfNotExist=true Metastore Connection Driver : com.mysql.jdbc.Driver Metastore connection User: hive Starting metastore schema initialization to 1.2.0 Initialization script hive-schema-1.2.0.mysql.sql Connecting to jdbc:mysql://ip-172-31-53-134.ec2.internal/hive?createDatabaseIfNotExist=true Connected to: MySQL (version 5.1.73) 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://ip-172-31-53-134.ec2.internal> !autocommit on Autocommit status: true 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ No rows affected (0.005 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ No rows affected (0.001 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET NAMES utf8 */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ No rows affected (0.001 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40103 SET TIME_ZONE='+00:00' */ No rows affected (0.001 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.001 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> /*!40101 SET character_set_client = utf8 */ No rows affected (0 seconds) 0: jdbc:mysql://ip-172-31-53-134.ec2.internal> 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 Error: Can't create table 'hive.BUCKETING_COLS' (errno: 121) (state=HY000,code=1005) Closing: 0: jdbc:mysql://ip-172-31-53-134.ec2.internal/hive?createDatabaseIfNotExist=true org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:278) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:251) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:490) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:373) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:341) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:274) ... 8 more *** schemaTool failed ***

Re: Hive metastore installation fails with constraints issues

gunnar_tapper — Tue, 24 May 2016 13:51:56 GMT

I figured it out. I had terrible problems installing on AWS with files missing etc. In the end, I cleaned out software and directories to bare bones, installed basic software, fixed errors with missing files, and ensured that they were running. Then, I could install HBase. Somewhere along the way, the hive database was created in MySQL. I dropped the database and reran the /usr/hdp/current/hive-metastore/bin/schematool -initSchema -dbType mysql -userName hive -passWord '$password' -verbose command. Now, it works.

Re: Hive metastore installation fails with constraints issues

gunnar_tapper — Tue, 24 May 2016 14:06:44 GMT

Files missing = Ambari did not install the files on a node even though the service was configured to run there.