Hive metastore started with error "Table 'xxx' already exists"

Document created by Hao Zhu Employee on Feb 18, 2016
Version 1Show Document
  • View in full screen mode

Author: Hao Zhu

Original Publication Date: April 10, 2015

 

Env:

Hive 0.13

Mysql as backen Hive metastore database.

Symtom:

Hive metastore starts with below error in log file:

 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'DATABASE_PARAMS' already exists

  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

  at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

  at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

  at com.mysql.jdbc.Util.getInstance(Util.java:386)

  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)

  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)

  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)

  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)

  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)

  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)

In other similar cases, the above table name may change, but the stacktrace is the same.This article is taking "DATABASE_PARAMS" for example.

Root Cause:

Hive Metastore runs below SQL to check if the table exists or not.

SHOW FULL TABLES FROM `<dbname>` LIKE '<tablename>';

If the table does not exist or above SQL returns unexpected output due to some reason, Hive Metastore will try to recreate the table by running SQL like:

CREATE TABLE IF NOT EXISTS `<tablename>` (...);

However if the table actually exists in MySQL and Hive Metastore wrongly believes it does not exist based on the SQL results, below error shows in log:

Table '<tablename>' already exists

Solution:

1. Make sure hive-site.xml contains correct configurations to backend MySQL database.

javax.jdo.option.ConnectionURL 
javax.jdo.option.ConnectionDriverName
javax.jdo.option.ConnectionUserName
javax.jdo.option.ConnectionPassword

Try manually connect to MySQL using OS command:

mysql -h <hostname> -u <username> -p <database name>

Then type password to make sure password is correct.2. Confirm table "DATABASE_PARAMS" exists in MySQL.After log on MySQL and use the correct database, run:

show tables;

The table "DATABASE_PARAMS" should in the output.If the table does not exist:

3. Make sure the table is readable and working fine if the table does exist.Hive metastore uses below SQL to detect the table in MySQL.This can be found by enabling MySQL general log follow this article: How to enable mysql general log.

SHOW FULL TABLES FROM `<dbname>` LIKE 'DATABASE_PARAMS';

Please run above SQL manually to make sure it returns correct output.And also query on this table to make sure it is readable.

select * from DATABASE_PARAMS;

One possible issue is that below SQL returns "ERROR" for Table_Type:

 

mysql> SHOW FULL TABLES FROM `hive` LIKE 'DATABASE_PARAMS'

  -> ;

+----------------------------------+------------+

| Tables_in_hive (DATABASE_PARAMS) | Table_type |

+----------------------------------+------------+

| DATABASE_PARAMS | ERROR  |

+----------------------------------+------------+

1 row in set (0.00 sec)

Then action items are:

  • Please check the permission and ownership of mysql data directory(default: /var/lib/mysql) are correct.
  • If permission and ownerships are all good, engage MySQL DBA to check if it is possible to recover the table.

Attachments

    Outcomes