Hive Metastore Connection Issues with MySQL
Category: Troubleshooting → Hive Metastore
Applies To: Apache Hive 2.x, 3.x with MySQL Metastore
Issue summary:
Users or Hive services (HiveServer2, Hive CLI, Spark with Hive integration) are unable to connect to the Hive Metastore when MySQL is configured as the backend database. This often manifests as "Unable to connect to Metastore" or "Communication link failure" errors in application logs.
Possible cause(s):
Incorrect hive-site.xml Configuration: Misspelled JDBC URL, wrong database name, incorrect username, or invalid password for the MySQL Metastore.
Missing or Incorrect MySQL JDBC Driver: The mysql-connector-java.jar file is either not present in the Hive classpath (e.g., Hive lib/ directory or HADOOP_CLASSPATH) or is an incompatible version.
Network Connectivity Issues: Firewall blocking the MySQL port (default 3306), incorrect IP address for the MySQL host, or general network latency/unreachability.
MySQL Server Status: The MySQL server process is not running, or the database instance is down/unresponsive.
MySQL User Permissions: The MySQL user configured for the Hive Metastore lacks the necessary GRANT privileges (e.g., SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX) on the Metastore database.
Hostname Resolution Issues: The hostname for the MySQL server configured in hive-site.xml cannot be resolved by the Hive/Metastore server.
Version Incompatibility: Incompatible versions between the MySQL JDBC driver, the MySQL server, or the Hive version itself.
Step-by-Step Resolution:
Verify hive-site.xml Configuration:
Navigate to your Hive configuration directory (usually $HIVE_HOME/conf).
Open hive-site.xml and meticulously check the following properties:
nano $HIVE_HOME/conf/hive-site.xml
javax.jdo.option.ConnectionURL: Ensure the JDBC URL is correct (e.g., jdbc:mysql://<mysql_hostname_or_ip>:3306/<metastore_db_name>?createDatabaseIfNotExist=true). Pay attention to hostname, port, and database name.
javax.jdo.option.ConnectionUserName: Verify the exact MySQL username.
javax.jdo.option.ConnectionPassword: Confirm the password is correct.
Ensure there are no typos or leading/trailing spaces.
Check MySQL JDBC Driver:
Locate the mysql-connector-java.jar file. It should be in Hive's $HIVE_HOME/lib directory.
If not present, download the appropriate version from the MySQL official website (ensure compatibility with your MySQL server and Hive version).
Copy the JAR file to $HIVE_HOME/lib on all Hive Metastore and HiveServer2 nodes.
If running Hive/Spark on YARN, ensure the JAR is also available on the classpath of the YARN containers (e.g., via --jars in Spark submit or by placing it in a shared HDFS location that gets distributed).
Test Network Connectivity:
From the server(s) running Hive Metastore and HiveServer2, use ping <mysql_hostname_or_ip> to check basic network reachability.
Use telnet <mysql_hostname_or_ip> 3306 (or the configured MySQL port) to check if the port is open and the MySQL server is listening. A successful connection will show a blank screen or a MySQL version string. If it hangs or refuses connection, a firewall or server issue is likely.
Verify MySQL Server Status:
Log in to the MySQL server host.
Check if the MySQL service is running: sudo systemctl status mysql or sudo service mysql status.
If it's not running, start it: sudo systemctl start mysql or sudo service mysql start.
Check MySQL User Permissions:
Connect to your MySQL server as a root or administrative user.
Run the following SQL query to verify privileges for the Hive Metastore user (replace hiveuser and metastore_db):
SHOW GRANTS FOR 'hiveuser'@'%';
Or for a specific host:
SHOW GRANTS FOR 'hiveuser'@'hive_metastore_host_ip';
Check privileges on the metastore database specifically:
SELECT * FROM mysql.db WHERE User='hiveuser' AND Db='metastore_db';
The user needs at least SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX on the Metastore database.
If permissions are missing, grant them:
GRANT ALL PRIVILEGES ON metastore_db.* TO 'hiveuser'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Ensure the IDENTIFIED BY clause correctly uses the password you set.
Test Hostname Resolution:
On the Hive Metastore and HiveServer2 nodes, try to resolve the MySQL hostname using dig <mysql_hostname> or nslookup <mysql_hostname>.
Ensure the IP address returned matches the actual MySQL server's IP. If not, check /etc/hosts or DNS configuration.
Check Logs:
Examine the Hive Metastore logs (hive-metastore.log), HiveServer2 logs (hiveserver2.log), and client application logs (e.g., Spark driver logs) for specific error messages and stack traces. These logs often contain detailed underlying JDBC errors.
Also, check the MySQL server's error logs (error.log, location varies by OS/install) for any connection attempts or authentication failures.
Version Compatibility:
Consult the Apache Hive documentation for the specific Hive version you are using to check its compatibility matrix with MySQL server versions and JDBC driver versions.
For example, newer MySQL servers (8.x) might require a newer JDBC driver (8.x series).