Install MySQL connector for hive metastore

[#1]

I started a fresh Ambari installation on Ubuntu 16 following https://docs.hortonworks.com/HDPDocuments/Ambari-2.6.2.0/bk_ambari-installation/content/ch_Getting_R.... All options are using default values and it completed successfully. In the process, it did not ask for mysql connector installation.

At time of starting hive-metastore, it complains:

SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/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://msl-dpe-perf86.dhcp.msl.lab/hive?createDatabaseIfNotExist=true
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: hive
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: com.mysql.cj.jdbc.exceptions.CommunicationsException : Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
SQL Error code: 0
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
at org.apache.hive.beeline.HiveSchemaHelper.getConnectionToMetastore(HiveSchemaHelper.java:80)
at org.apache.hive.beeline.HiveSchemaTool.getConnectionToMetastore(HiveSchemaTool.java:133)
at org.apache.hive.beeline.HiveSchemaTool.testConnectionToMetastore(HiveSchemaTool.java:187)
at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:291)
at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:277)
at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:526)
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:233)
at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:832)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.hive.beeline.HiveSchemaHelper.getConnectionToMetastore(HiveSchemaHelper.java:76)
... 11 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the 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.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)
at com.mysql.cj.NativeSession.connect(NativeSession.java:152)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:952)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:822)
... 17 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:173)
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
... 20 more
*** schemaTool failed ***

Based on my previous questions, it is pointing to missing mysql connector. I followed https://docs.hortonworks.com/HDPDocuments/Ambari-2.6.2.0/bk_ambari-administration/content/using_amba..., downloaded and installed connector. But at step #2, I ran into following error. What should I do to make mysql running?

Note, this machine has fresh Ubuntu 16.04 installation with only Ambari installed

root@msl-dpe-perf87:/usr/share/java# ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar
Using python  /usr/bin/python
Setup ambari-server
Copying /usr/share/java/mysql-connector-java.jar to /var/lib/ambari-server/resources
If you are updating existing jdbc driver jar for mysql with mysql-connector-java.jar. Please remove the old driver jar, from all hosts. Restarting services that need the driver, will automatically copy the new jar to the hosts.
JDBC driver was successfully initialized.
Ambari Server 'setup' completed successfully.
root@msl-dpe-perf87:/usr/share/java# ls /usr/share/java/mysql-connector-java.jar
/usr/share/java/mysql-connector-java.jar
root@msl-dpe-perf87:/usr/share/java# mysql -u root -p
The program 'mysql' can be found in the following packages:
 * mysql-client-core-5.7
 * mariadb-client-core-10.0
Try: apt install <selected package>
root@msl-dpe-perf87:/usr/share/java# 

[#2 · Accepted solution]

@Harry Li

The mentioned error and the stack trace of the error indicates that you might be using a higher version of mysql-connector-java JDBC Driver.

As the new API "com.mysql.cj" can be seen in the StackTrace.

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-api-changes.html

This section describes the changes to the Connector/J API going from version 5.1 to 6.0 / 8.0. You might need to adjust your API calls accordingly:

• The name of the class that implementsjava.sql.Driverin MySQL Connector/J has changed fromcom.mysql.jdbc.Drivertocom.mysql.cj.jdbc.Driver. The old class name has been deprecated.
• The names of these commonly-used interfaces have also been changed:
  • ExceptionInterceptor: fromcom.mysql.jdbc.ExceptionInterceptortocom.mysql.cj.exceptions.ExceptionInterceptor
  • StatementInterceptor: fromcom.mysql.jdbc.StatementInterceptorV2tocom.mysql.cj.interceptors.QueryInterceptor
  • ConnectionLifecycleInterceptor: fromcom.mysql.jdbc.ConnectionLifecycleInterceptortocom.mysql.cj.jdbc.interceptors.ConnectionLifecycleInterceptor
  • AuthenticationPlugin: fromcom.mysql.jdbc.AuthenticationPlugintocom.mysql.cj.protocol.AuthenticationPlugin
  • BalanceStrategy: fromcom.mysql.jdbc.BalanceStrategytocom.mysql.cj.jdbc.ha.BalanceStrategy.

.

Please make sure that you are using the correct MySQL JDBC driver. Like if you are using HDP 2.6.5 then MySQL 5.7 Database and respective JDBC driver version needs to be used.

.

[#3]

@Harry Li

Please try this to verify your JDBC driver version:

On Ambari Server Host:

# mkdir /tmp/JDBC
# cd /tmp/JDBC
# cp -f /var/lib/ambari-server/resources/mysql-connector-java.jar /tmp/JDBC/
# jar xvf mysql-connector-java.jar 

.

The grep the version:

# grep 'Implementation-Versio' META-INF/MANIFEST.MF 
Implementation-Version: 8.0.11
# cat META-INF/services/java.sql.Driver
com.mysql.cj.jdbc.Driver

.

So if your MySQL JDBC Driver version is correct (MySQL 5 JDBC Driver then you might be seeing something like following instead:

# grep 'Implementation-Versio' META-INF/MANIFEST.MF 
Implementation-Version: 5.1.25-SNAPSHOT
# cat META-INF/services/java.sql.Driver
com.mysql.jdbc.Driver

.

MySQL connector 5.1 download link: https://dev.mysql.com/downloads/connector/j/5.1.html