CODEWITHSUNDEEP
mysqlhadoophiveclouderametastore
user3528338
user3528338

Reputation: 81

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

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?

Upvotes: 4

Views: 19497

Answers (3)

Eugene
Eugene

Reputation: 11055

Just ran into this issue; sharing my story to anyone who might encounter a similar 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 identified 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 identified by 'password'

Upvotes: 1

Swathi
Swathi

Reputation: 41

   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

Upvotes: 4

SachinJose
SachinJose

Reputation: 8522

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;

Upvotes: 9

Related Questions