`DROP DATABASE` generates a postgres issue after 1.0 TDP release

[mehdibn]

To test, from beeline :
CREATE DATABASE test LOCATION test

then

DROP DATABASE test

Table will be well dropped with an error:

ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Unable to clean up org.postgresql.util.PSQLException: ERREUR: la relation « txn_components » n'existe pas

[Pierrotws]

After checking if new 1.0 build could be in cause, I just see versioning commit on top of 0.1.0-SNAPSHOT, nothing that could justify such regression.

Have you confirmed that the bug disappears with 0.1.0-SNAPSHOT ?

[mehdibn]

After checking if new 1.0 build could be in cause, I just see versioning commit on top of 0.1.0-SNAPSHOT, nothing that could justify such regression.

Have you confirmed that the bug disappears with 0.1.0-SNAPSHOT ?

yes

[rpignolet]

The problem has been identified. Following the merge of TOSIT-IO/hive-old#5 we (DGFiP) forgot to build Hive, so we never tested this change before today.

[mehdibn]

thank you @rpignolet 👍

[rpignolet]

We (DGFiP) believe that it is TOSIT-IO/hive-old#5 that introduces this DROP DATABASE problem. We will have to discuss this at our next meeting.

It is not blocking for the TDP 1.0 release.

[hamdiazz]

Hello everyone,

I confirm that the bug disappears with 0.1.0-SNAPSHOT.

Thanks,

[kpgtek]

I confirm this error with apache-hive-3.1.3-1.0-bin (and also with apache-hive-3.1.3-TDP-0.1.0-SNAPSHOT-bin).

I think the PR TOSIT-IO/hive-old#5 added the fix https://issues.apache.org/jira/browse/HIVE-22546, which renames the following tables' names (including columns & index) in uppercase and is valid for HIVE v4.0.0:

aux_table 
compaction_queue 
completed_compactions 
completed_txn_components 
hive_locks 
materialization_rebuild_locks 
min_history_level datanucleus
next_compaction_queue_id 
next_lock_id 
next_txn_id 
next_write_id 
repl_txn_map 
runtime_stats 
txn_components 
txn_to_write_id 
txns 
write_set

With this error, it seems that the HIVE v3.1.0 JAVA code is still using these tables' names in lowercase.

We could remove the merge of the PR TOSIT-IO/hive-old#5 to avoid this error.

But if "DataNucleus Core" is used, the error on the table "RUNTIME_STATS" could occure according to the issue https://issues.apache.org/jira/browse/HIVE-22101. Because "DataNucleus" is part of HIVE package:

[root]# ls -l /opt/tdp/hive/lib/datanucleus*
-rwxr-xr-x. 1 root root  366748  9 nov.   2021 /opt/tdp/hive/lib/datanucleus-api-jdo-4.2.4.jar
-rwxr-xr-x. 1 root root 2016766  9 nov.   2021 /opt/tdp/hive/lib/datanucleus-core-4.1.17.jar
-rwxr-xr-x. 1 root root 1908681  9 nov.   2021 /opt/tdp/hive/lib/datanucleus-rdbms-4.1.19.jar

[kpgtek]

I confirm the following WARNING about the issue https://issues.apache.org/jira/browse/HIVE-22101 in HIVE metastore.err log file when the table runtime_stats is in lowercase:

[...] WARN DataNucleus.Query: Query for candidates of org.apache.hadoop.hive.metastore.model.MRuntimeStat and subclasses resulted in no possible candidates
Required table missing : ""RUNTIME_STATS"" in Catalog "" Schema "". DataNucleus requires this table to perform its persistence operations. Either your MetaData is incorrect, or you need to enable "datanucleus.schema.autoCreateTables"
org.datanucleus.store.rdbms.exceptions.MissingTableException: Required table missing : ""RUNTIME_STATS"" in Catalog "" Schema "". DataNucleus requires this table to perform its persistence operations. Either your MetaData is incorrect, or you need to enable "datanucleus.schema.autoCreateTables"
        at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:606)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3385)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2896)
        at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:119)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.manageClasses(RDBMSStoreManager.java:1627)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:672)
        at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getStatementForCandidates(RDBMSQueryUtils.java:425)
        at org.datanucleus.store.rdbms.query.JDOQLQuery.compileQueryFull(JDOQLQuery.java:865)
        at org.datanucleus.store.rdbms.query.JDOQLQuery.compileInternal(JDOQLQuery.java:347)
        at org.datanucleus.store.query.Query.performDeletePersistentAll(Query.java:2117)
        at org.datanucleus.store.query.AbstractJavaQuery.performDeletePersistentAll(AbstractJavaQuery.java:114)
        at org.datanucleus.store.query.Query.deletePersistentAll(Query.java:2096)
        at org.datanucleus.store.query.Query.deletePersistentAll(Query.java:2053)
        at org.datanucleus.api.jdo.JDOQuery.deletePersistentInternal(JDOQuery.java:440)
        at org.datanucleus.api.jdo.JDOQuery.deletePersistentAll(JDOQuery.java:419)
        at org.apache.hadoop.hive.metastore.ObjectStore.deleteRuntimeStats(ObjectStore.java:11996)
        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.$Proxy43.deleteRuntimeStats(Unknown Source)
        at org.apache.hadoop.hive.metastore.RuntimeStatsCleanerTask.run(RuntimeStatsCleanerTask.java:57)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:750)