hive 3.1 metastore error on startup connecting to mysql rds

Question

Trying to startup Hive Metastore 3.1 version connecting to a new MySQL RDS and getting this error. When using Hive 2.3.2 it worked fine (on a separate MySQL RDS instance). Does anyone know what is causing this? Have already initiliased the mysql before starting HiveMetastore process with:

• echo "CREATE DATABASE IF NOT EXISTS metastore;" > hive_metastore.sql echo "USE metastore;" >> hive_metastore.sql echo "SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-3.1.0.mysql.sql;"

  hive_metastore.sql echo "CREATE USER IF NOT EXISTS 'hive'@'%' IDENTIFIED BY '$hive_pasw';" >> hive_metastore.sql echo "REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'%';" >> hive_metastore.sql echo "GRANT ALL PRIVILEGES ON metastore.* TO 'hive'@'%';" >> hive_metastore.sql echo "CREATE USER IF NOT EXISTS 'ro_hive'@'%' IDENTIFIED BY '$ro_hive_pasw';" >> hive_metastore.sql echo "REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'ro_hive'@'%';" >> hive_metastore.sql echo "GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON . TO 'ro_hive'@'%';" >> hive_metastore.sql echo "FLUSH PRIVILEGES;" >> hive_metastore.sql echo "quit" >> hive_metastore.sql

  18/08/24 00:17:48 INFO metastore.ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order" 18/08/24 00:17:48 INFO metastore.MetaStoreDirectSql: Using direct SQL, underlying DB is MYSQL 18/08/24 00:17:48 INFO metastore.ObjectStore: Initialized ObjectStore 18/08/24 00:17:49 WARN DataNucleus.MetaData: Metadata has jdbc-type of null yet this is not valid. Ignored 18/08/24 00:17:53 WARN metastore.ObjectStore: Failed to get database hive.default, returning NoSuchObjectException 18/08/24 00:17:53 WARN metastore.HiveMetaStore: Retrying creating default database after error: Exception thrown flushing changes to datastore javax.jdo.JDODataStoreException: Exception thrown flushing changes to datastore at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543) at org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:171) at org.apache.hadoop.hive.metastore.ObjectStore.commitTransaction(ObjectStore.java:765) at org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:953) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97) at com.sun.proxy.$Proxy22.createDatabase(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB_core(HiveMetaStore.java:751) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:769) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:538) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:108) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.(RetryingHMSHandler.java:80) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:93) at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:8661) at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:8656) at org.apache.hadoop.hive.metastore.HiveMetaStore.startMetaStore(HiveMetaStore.java:8926) at org.apache.hadoop.hive.metastore.HiveMetaStore.main(HiveMetaStore.java:8843) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) NestedThrowablesStackTrace: java.sql.BatchUpdateException: Unknown column 'CATALOG_NAME' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778) at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262) at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970) at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:125) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:366) at org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:676) at org.datanucleus.store.rdbms.SQLController.processStatementsForConnection(SQLController.java:644) at org.datanucleus.store.rdbms.SQLController$1.transactionFlushed(SQLController.java:731) at org.datanucleus.store.connection.AbstractManagedConnection.transactionFlushed(AbstractManagedConnection.java:89) at org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionFlushed(ConnectionManagerImpl.java:450) at org.datanucleus.TransactionImpl.flush(TransactionImpl.java:210) at org.datanucleus.TransactionImpl.commit(TransactionImpl.java:274) at org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:107) at org.apache.hadoop.hive.metastore.ObjectStore.commitTransaction(ObjectStore.java:765) at org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:953) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97) at com.sun.proxy.$Proxy22.createDatabase(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB_core(HiveMetaStore.java:751) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:769) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:538) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:108) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.(RetryingHMSHandler.java:80) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:93) at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:8661) at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:8656) at org.apache.hadoop.hive.metastore.HiveMetaStore.startMetaStore(HiveMetaStore.java:8926) at org.apache.hadoop.hive.metastore.HiveMetaStore.main(HiveMetaStore.java:8843) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

Answer 1

https://issues.apache.org/jira/browse/HIVE-19740 After setting hive.metastore.event.db.notification.api.auth to false on hive-site.xml. Hive can start and function normally.