Hive shell hangs

[#1]

SYMPTOMS: Sometimes the Hive CLI hangs and the gives no response. At the same time, the /var/log/hive/hivemetastore.log reports timeouts

ERROR [<hostname>-47]: txn.TxnHandler (TxnHandler.java:getDbConn(984)) - There is a problem with aconnection from the pool, retrying(rc=9): Timed out waiting for a free available connection.
(SQLState=08001,ErrorCode=0) 
java.sql.SQLException: Timed out waiting for a free available connection.

ROOT CAUSE: The Hive Metastore service is waiting for the database connections to get free. At the time of error, not enough concurrent connections were available.

RESOLUTION: (Note: This is for mysql DB only).

To fix this problem, the number of max database connection should be increased.

1. Open the /etc/my.cnf in the text editor.

vi /etc/my.cnf

2. Under [mysqld] section, add:

max_connections = 250

3. Save the file and restart the mysqld service

service mysqld restart