AnsweredAssumed Answered

Unable to establish mysql connection from hive

Question asked by pkp on May 29, 2018
Latest reply on May 31, 2018 by maprcommunity

I have recently installed MapR 6.0 on Single node cluster and using mysql as Meta store. Hive MetaStore isn't working as it is not establishing connection to hive db in mysql server. I checked the user priviligies on the system, tested telnet connection on the port 3306. they look good but connection is refused by mysql. I am not sure how to set up privileges to make hive  services run properly. here are some details.

 

[root@denvmschwhadoop /]# /opt/mapr/hive/hive-2.1/bin/schematool -initSchema -dbType mysql
Metastore connection URL: jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: hive
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException : Could not create connection to database server.

 

java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://denvmschwhadoop:3306/hive?createDatabaseIfNotExist=true, username = hive. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

 

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>****</value>
<description>password to use against metastore database</description>
</property>

 

mysql> show grants;
+--------------------------------------------------------+
| Grants for hive@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `hive`@`localhost` |
| GRANT ALL PRIVILEGES ON `hive`.* TO `hive`@`localhost` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants;
+------------------------------------------------+
| Grants for hive@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `hive`@`%` |
| GRANT ALL PRIVILEGES ON `hive`.* TO `hive`@`%` |
+------------------------------------------------+
2 rows in set (0.00 sec)

 

Really appreciate your inputs on this problem resolution.

Outcomes