CDH upgrade from 4.7 to CDH 5.2 hive metastore issue

[#1]

Hello All, 

I upgraded the cluster from CDH-4.7 to CDH 5.2. HDFS and Mapreduce looks good but having issue with hive metastore. Following is error I see on metastore log file. I use mysql database. Please let me know how to fix this issue. I have backup of database if needed.

DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.

org.apache.hadoop.hive.metastore.MetaStoreDirectSql: Database initialization failed; direct SQL is disabled

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'A0.BIG_DECIMAL_HIGH_VALUE' in 'field list'

2014-10-21 17:02:49,588 INFO org.apache.hadoop.hive.metastore.HiveMetaStore: Starting hive metastore on port 9083
2014-10-21 17:02:49,776 INFO org.apache.hadoop.hive.metastore.HiveMetaStore: 0: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
2014-10-21 17:02:49,816 INFO org.apache.hadoop.hive.metastore.ObjectStore: ObjectStore, initialize called
2014-10-21 17:02:50,083 INFO DataNucleus.Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
2014-10-21 17:02:50,083 INFO DataNucleus.Persistence: Property datanucleus.cache.level2 unknown - will be ignored
2014-10-21 17:02:50,895 INFO org.apache.hadoop.hive.metastore.ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order"
2014-10-21 17:02:52,175 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
2014-10-21 17:02:52,176 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
2014-10-21 17:02:52,290 ERROR org.apache.hadoop.hive.metastore.MetaStoreDirectSql: Database initialization failed; direct SQL is disabled
javax.jdo.JDOException: Exception thrown when executing query
at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:596)
at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:230)
at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.<init>(MetaStoreDirectSql.java:120)
at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:253)
at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:224)
at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73)
at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
at org.apache.hadoop.hive.metastore.RawStoreProxy.<init>(RawStoreProxy.java:58)
at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:67)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:506)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:484)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:532)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:406)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:365)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:55)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:60)
at org.apache.hadoop.hive.metastore.HiveMetaStore.newHMSHandler(HiveMetaStore.java:4953)
at org.apache.hadoop.hive.metastore.HiveMetaStore.startMetaStore(HiveMetaStore.java:5173)
at org.apache.hadoop.hive.metastore.HiveMetaStore.main(HiveMetaStore.java:5093)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.util.RunJar.main(RunJar.java:212)
NestedThrowablesStackTrace:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'A0.BIG_DECIMAL_HIGH_VALUE' in 'field list'
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:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:174)
at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:381)
at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:504)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:651)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1786)
"hadoop-cmf-hive1-HIVEMETASTORE-chadvt3endc02.ops.tiaa-cref.org.log.out" 1514L, 139878C

[#2 · Accepted solution]

Hello All,

I went to the mysql command line and used schema create/update  -  able to get the metastore working & also able to get the tables form hive as well as impala.

database :mysql 

datastore database: hive 

mysql> source hive-schema-0.13.0.mysql.sql;

mysql> use hive
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source upgrade-0.10.0-to-0.11.0.mysql.sql
+--------------------------------------------------+
| |
+--------------------------------------------------+
| Upgrading MetaStore schema from 0.10.0 to 0.11.0 |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+-----------------------------------------------------------+
| |
+-----------------------------------------------------------+
| Finished upgrading MetaStore schema from 0.10.0 to 0.11.0 |
+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> source upgrade-0.11.0-to-0.12.0.mysql.sql
+--------------------------------------------------+
| |
+--------------------------------------------------+
| Upgrading MetaStore schema from 0.11.0 to 0.12.0 |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------------------------------------------------+
| |
+---------------------------------------------------+
| < HIVE-3255 Master Key and Delegation Token DDL > |
+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

+-----------------------------------------------------------+
| |
+-----------------------------------------------------------+
| < HIVE-3764 Support metastore version consistency check > |
+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1062 (23000): Duplicate entry '1' for key 1
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

+-----------------------------------------------------------+
| |
+-----------------------------------------------------------+
| Finished upgrading MetaStore schema from 0.11.0 to 0.12.0 |
+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> source upgrade-0.12.0-to-0.13.0.mysql.sql
+--------------------------------------------------+
| |
+--------------------------------------------------+
| Upgrading MetaStore schema from 0.12.0 to 0.13.0 |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+-----------------------------------------------------------------------+
| |
+-----------------------------------------------------------------------+
| < HIVE-5700 enforce single date format for partition column storage > |
+-----------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

+--------------------------------------------+
| |
+--------------------------------------------+
| < HIVE-6386: Add owner filed to database > |
+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)

ERROR 1060 (42S21): Duplicate column name 'OWNER_NAME'
ERROR 1060 (42S21): Duplicate column name 'OWNER_TYPE'
+---------------------------------------------------------------------------------------------+
| |
+---------------------------------------------------------------------------------------------+
| <HIVE-6458 Add schema upgrade scripts for metastore changes related to permanent functions> |
+---------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

+----------------------------------------------------------------------------------+
| |
+----------------------------------------------------------------------------------+
| <HIVE-6757 Remove deprecated parquet classes from outside of org.apache package> |
+----------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1050 (42S01): Table 'TXNS' already exists
ERROR 1050 (42S01): Table 'TXN_COMPONENTS' already exists
ERROR 1050 (42S01): Table 'COMPLETED_TXN_COMPONENTS' already exists
ERROR 1050 (42S01): Table 'NEXT_TXN_ID' already exists
Query OK, 1 row affected (0.00 sec)

ERROR 1050 (42S01): Table 'HIVE_LOCKS' already exists
ERROR 1061 (42000): Duplicate key name 'HL_TXNID_IDX'
ERROR 1050 (42S01): Table 'NEXT_LOCK_ID' already exists
Query OK, 1 row affected (0.00 sec)

ERROR 1050 (42S01): Table 'COMPACTION_QUEUE' already exists
ERROR 1050 (42S01): Table 'NEXT_COMPACTION_QUEUE_ID' already exists
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

+-----------------------------------------------------------+
| |
+-----------------------------------------------------------+
| Finished upgrading MetaStore schema from 0.12.0 to 0.13.0 |
+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>