Unable to open a test connection to the given database 1


Error Scenario 1:

In this post, we will discuss about a few varieties of error message Unable to open a test connection to the given database and their resolutions.

Unable to open a test connection to the given database / Communications link failure

Below is the error message for this communications link failure:

Root Cause:

We will get the above error message, if we specify incorrect hostname: port values in property javax.jdo.option.ConnectionURL in hive-site.xml file.

In the above error message, we have given wrong hostname:port number combination (localhost:3308) in hive-site.xml.

Resolution:

We have to specify the correct hostname:port value in javax.jdo.option.ConnectionURL property value.

In local mode, we can specify this property with just localhost as hostname and port number as 3306 in distributed mode we can specify the actual hostname inplace of localhost. For local mode, it is shown below.

Note:  Just by specifying the hostname without any port number will also resolves this issue. But if we are specifying the port number then we have to make sure that, we are giving correct port number.

Error Scenario 2:

Unable to open a test connection to the given database / Access denied for user ‘mysql_usr’@’localhost’ (using password: YES)

Below is the error message from console for this scenario:

Root Cause:

Incorrect username and password combination will result in the above error message 

OR

User doesn’t have privileges to MySQL to access/create databases.

Note:  Here in the above error message, (using password: YES) doesn’t mean that it is taking the password as ‘YES’. Even though it takes the correct password given in hive-site.xml file, while throwing error message it will display the default password: YES to maintain the confidentiality of our password.

Resolution:

Here in our scenario, even with correct username (mysql_usr on ubuntu) with its correct password we have received this error message. I.e. user mysql_usr doesn’t have access/write privileges on MySQL databases.

So, now we have to provide root user credentials in hive-site.xml file as shown below

replace ‘xxxxxx’ with actual password for root account on Ubuntu Machine.

Error Scenario 3:

Unable to open a test connection to the given database / Unknown database

If there is any syntax error in specify value for javax.jdo.option.ConnectionURL property then we will receive error message similar to below.

Here value is specified incorrectly as 

jdbc:mysql://localhost:3306/hive_metastore_db;createDatabaseIfNotExist=true

instead of correct syntax

jdbc:mysql://localhost:3306/hive_metastore_db?createDatabaseIfNotExist=true


Profile photo of Siva

About Siva

Senior Hadoop developer with 4 years of experience in designing and architecture solutions for the Big Data domain and has been involved with several complex engagements. Technical strengths include Hadoop, YARN, Mapreduce, Hive, Sqoop, Flume, Pig, HBase, Phoenix, Oozie, Falcon, Kafka, Storm, Spark, MySQL and Java.


Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “Unable to open a test connection to the given database

  • praveen

    [hduser@localhost bin]$ hive

    Logging initialized using configuration in jar:file:/usr/local/hadoop-1.0.4/apache-hive-0.13.1-bin/lib/hive-common-0.13.1.jar!/hive-log4j.properties
    Exception in thread “main” java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:346)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
    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:601)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
    Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
    at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1412)
    at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:62)
    at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:72)
    at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2453)
    at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2465)
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:340)
    … 7 more
    Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1410)
    … 12 more
    Caused by: MetaException(message:Could not connect to meta store using any of the URIs provided. Most recent failure: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: connect timed out
    at org.apache.thrift.transport.TSocket.open(TSocket.java:185)
    at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:336)
    at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:214)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1410)
    at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:62)
    at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:72)
    at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2453)
    at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2465)
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:340)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
    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:601)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
    Caused by: java.net.SocketTimeoutException: connect timed out
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:391)
    at java.net.Socket.connect(Socket.java:579)
    at org.apache.thrift.transport.TSocket.open(TSocket.java:180)
    … 19 more
    )
    at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:382)
    at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:214)
    … 17 more

    how to solve this i have placed mysql jar connector and my hive-site.xml

    <configuration>

    <property>
    <name>hive.metastore.uris</name>
    <value>thrift://hostname:9083</value>
    <description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/metastore_db?createDatabaseIfNotExit=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>mypassword</value>
    <description>password to use against metastore database</description>
    </property>

    <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
    </property>

    <property>
    <name>hive.metastore.schema.verification</name>
    <value>false</value>
    </property>

    </configuration>

    pls help me out