You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Grandl Robert <rg...@yahoo.com> on 2014/08/29 00:31:29 UTC
hive unable to use metastore database
Hi guys,
I was trying to configure the metastore database, following the steps:
1. sudo apt-get install mysql-server
2. sudo service mysql start
3. sudo apt-get install libmysql-java
-> copied /user/share/java/libmysql-*.jar to $HIVE_HOME/lib
4.
sudo /usr/bin/mysql_secure_installation
[...]
Enter current password for root (enter for none):
OK, successfully used password, moving on...
[...]
Set root password? [Y/n] y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
[...]
Disallow root login remotely? [Y/n] N
[...]
Remove test database and access to it [Y/n] Y
[...]
Reload privilege tables now? [Y/n] Y
All done!
5.
mysql -u root -p
Enter password:
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.10.0.mysql.sql;
6.
mysql> CREATE USER 'hive'@'nectar-11' IDENTIFIED BY 'hive';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'nectar-11';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON metastore.* TO 'hive'@'nectar-11';
mysql> FLUSH PRIVILEGES;
mysql> quit;
Then I set the following properties in hive-site.xml
property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://nectar-11/metastore</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
</property>
But when I start hive, it throws a bunch of exception with the main cause being:
Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://nectar-11/metastore, username = hive. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
com.mysql.jdbc.exceptions.jdbc4.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.
Do you have any ideas, what I am doing wrong here ? Thanks.
Re: hive unable to use metastore database
Posted by Grandl Robert <rg...@yahoo.com>.
I figured out what was happening.
As default, in ubuntu, mysql has the bind-address set to localhost.
So you need to go into /etc/mysql/my.cnf and comment the line: bind-address = 127.0.0.1
Robert
On Thursday, August 28, 2014 3:32 PM, Grandl Robert <rg...@yahoo.com> wrote:
Hi guys,
I was trying to configure the metastore database, following the steps:
1. sudo apt-get install mysql-server
2. sudo service mysql start
3. sudo apt-get install libmysql-java
-> copied /user/share/java/libmysql-*.jar to $HIVE_HOME/lib
4.
sudo /usr/bin/mysql_secure_installation
[...]
Enter current password for root (enter for none):
OK, successfully used password, moving on...
[...]
Set root password? [Y/n] y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
[...]
Disallow root login remotely? [Y/n] N
[...]
Remove test database and access to it [Y/n] Y
[...]
Reload privilege tables now? [Y/n] Y
All done!
5.
mysql -u root -p
Enter password:
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.10.0.mysql.sql;
6.
mysql> CREATE USER 'hive'@'nectar-11' IDENTIFIED BY 'hive';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'nectar-11';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON metastore.* TO 'hive'@'nectar-11';
mysql> FLUSH PRIVILEGES;
mysql> quit;
Then I set the following properties in hive-site.xml
property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://nectar-11/metastore</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
</property>
But when I start hive, it throws a bunch of exception with the main cause being:
Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://nectar-11/metastore, username = hive. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
com.mysql.jdbc.exceptions.jdbc4.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.
Do you have any ideas, what I am doing wrong here ? Thanks.