Hive connectivity to MySQL: Access denied for user 'hive'@'localhost' hive

Question

I am setting up Hive on CentOS, and I have installed hadoop, hive and MySQL on the same server. I have also setup metastore DB on mySQL and user ID - hive has access to the database.

I have the below issue - Error Message is given below. Can some one help to fix this issue?

show databases; FAILED: Error in metadata: MetaException(message:Got exception: org.apache.hadoop.hive.metastore.api.MetaException javax.jdo.JDODataStoreException: Access denied for user 'hive'@'localhost' (using password: YES) NestedThrowables: java.sql.SQLException: Access denied for user 'hive'@'localhost' (using password: YES)) FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

Hive-site.xml is given below.

   <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://127.0.0.1/metastore?createDatabaseIfNotExist=true</value>
    </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>
     </property>

     <property>
      <name>javax.jdo.option.ConnectionPassword</name>
     <value>password</value>
     </property>

     <property>
      <name>hive.hwi.war.file</name>
      <value>/usr/lib/hive/lib/hive-hwi-0.10.0-cdh4.2.0.jar</value>
      <description>This is the WAR file with the jsp content for Hive Web Interface     </description>
     </property>

     <property>
      <name>datanucleus.fixedDatastore</name>
      <value>true</value>
      </property>

      <property>
       <name>datanucleus.autoCreateTables</name>
       <value>true</value>
      </property>

      <property>
      <name>hive.metastore.uris</name>
        <value>thrift://127.0.0.1:9083</value>
        <description>IP address </description>
      </property>

       <property>
       <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive2/warehouse</value>
         <description> warehouse </description>
        </property>

I am able to login to MySQL using hive and password and able to create tables in Metastore Database. Not sure where is the issue? Any suggestions?

Did you keep the mysql-connector jar in $HIVE_HOME/lib directory ? — Mukesh S, Apr 13 '14 at 08:22
Thanks.. this got resolved with the answer from Sachin. I had granted the privileges, but I did not include "IDENTIFIED BY 'password' " in the query. I included the same per Sachin's recommendation and it worked. — user3528338, Apr 18 '14 at 15:28

score 9 · Answer 1 · answered Apr 13 '14 at 15:41

The above error is because of insufficient privilege for 'hive'@'localhost' in mysql server. Start mysql shell as root, then execute the following grant option there

$ mysql -u root -p -hlocalhost
Enter password: 

mysql> GRANT ALL PRIVILEGES ON *.* TO  'hive'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

score 4 · Answer 2 · answered Jan 07 '15 at 10:33

   The error is while granting the privileges in mySQL configuration. You should give as follows:

grant all on . to 'hive'@127.0.0.1 identified by 'YourPassword'

Since you have given the hostname as 127.0.0.1 in hive-site.xml as below: javax.jdo.option.ConnectionURL : jdbc:mysql://127.0.0.1/metastore?createDatabaseIfNotExist=true

score 1 · Answer 3 · answered Jul 24 '19 at 06:41

Just ran into this issue, share my story to anyone who might meet same scenario.

If you follow most tutorials, at first, you will set javax.jdo.option.ConnectionURL's host as localhost or 127.0.0.1.

And you also guarantee the privilege to MySQL like grant all on *.* to 'user'@localhost indentified by 'password';

As time pass by, you may need to change javax.jdo.option.ConnectionURL to remote MySQL server IP. Be sure that the user may not have the privilege to access it unless you guarantee the privilege for that specific host, namely

grant all on *.* to 'user'@remote_ip_address indentified by 'password'

Hive connectivity to MySQL: Access denied for user 'hive'@'localhost' hive

3 Answers3