Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive metastore installation fails with constraints issues

Solved Go to solution
Highlighted

Hive metastore installation fails with constraints issues

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Hive metastore installation fails with constraints issues

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.

4 REPLIES 4

Re: Hive metastore installation fails with constraints issues

Super Guru

@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

@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

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

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

Don't have an account?
Coming from Hortonworks? Activate your account here