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?

Answer 1

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;

Answer 2

   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