Query 20231207_150147_00002_2asaf failed: All operations other than the following update operations were completed: replace table parameters default.test io.trino.spi.TrinoException: All operations other than the following update operations were completed: replace table parameters default

[parimalpatil28]

I am getting this issue when i am trying to insert table from clear table which is in hive.

If similar issue is already added please let me know the issue.

I am using Starburst trino 429. I am using trino command with - f test.hql while running query

[hirolee88]

Transactional table？ what is your sql

[parimalpatil28]

Hello @hirolee88, Yes it is transactional. Sql :: Insert into testtable(col1) select OurproductUDF from clearTable;

It looks like whole process is not able to complete before that it is trying to get out from query. We are using hive connector in between CDP PVC base 718 and trino 429

Thanks, Parimal

[parimalpatil28]

Hello @hirolee88, Waiting for your reply... Kindly let us know what should i do to fix this.

Thanks, Parimal

[hirolee88]

any more stack error message？ show create table default.test

[parimalpatil28]

@hirolee88,

Query 20231207_150147_00002_2asaf failed: All operations other than the following update operations were completed: replace table parameters default.protected_hive io.trino.spi.TrinoException: All operations other than the following update operations were completed: replace table parameters default.protected_hive at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$Committer.executeUpdateStatisticsOperations(SemiTransactionalHiveMetastore.java:2185) at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commitShared(SemiTransactionalHiveMetastore.java:1589) at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commit(SemiTransactionalHiveMetastore.java:1286) at io.trino.plugin.hive.HiveMetadata.commit(HiveMetadata.java:3722) at io.trino.plugin.hive.HiveTransactionManager.lambda$commit$0(HiveTransactionManager.java:58) at java.base/java.util.Optional.ifPresent(Optional.java:178) at io.trino.plugin.hive.HiveTransactionManager.commit(HiveTransactionManager.java:56) at io.trino.plugin.hive.HiveConnector.commit(HiveConnector.java:220) at io.trino.metadata.CatalogTransaction.commit(CatalogTransaction.java:85) at io.trino.metadata.CatalogMetadata.commit(CatalogMetadata.java:154)

Above is the stack... Query failing at first run and its passing when we ran it again.

[hirolee88]

What is the version of Hive in CDP PVC base 718

[hirolee88]

@hirolee88,

Query 20231207_150147_00002_2asaf failed: All operations other than the following update operations were completed: replace table parameters default.protected_hive io.trino.spi.TrinoException: All operations other than the following update operations were completed: replace table parameters default.protected_hive at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$Committer.executeUpdateStatisticsOperations(SemiTransactionalHiveMetastore.java:2185) at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commitShared(SemiTransactionalHiveMetastore.java:1589) at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commit(SemiTransactionalHiveMetastore.java:1286) at io.trino.plugin.hive.HiveMetadata.commit(HiveMetadata.java:3722) at io.trino.plugin.hive.HiveTransactionManager.lambda$commit$0(HiveTransactionManager.java:58) at java.base/java.util.Optional.ifPresent(Optional.java:178) at io.trino.plugin.hive.HiveTransactionManager.commit(HiveTransactionManager.java:56) at io.trino.plugin.hive.HiveConnector.commit(HiveConnector.java:220) at io.trino.metadata.CatalogTransaction.commit(CatalogTransaction.java:85) at io.trino.metadata.CatalogMetadata.commit(CatalogMetadata.java:154)

Above is the stack... Query failing at first run and its passing when we ran it again.

@hirolee88,

Query 20231207_150147_00002_2asaf failed: All operations other than the following update operations were completed: replace table parameters default.protected_hive io.trino.spi.TrinoException: All operations other than the following update operations were completed: replace table parameters default.protected_hive at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$Committer.executeUpdateStatisticsOperations(SemiTransactionalHiveMetastore.java:2185) at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commitShared(SemiTransactionalHiveMetastore.java:1589) at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commit(SemiTransactionalHiveMetastore.java:1286) at io.trino.plugin.hive.HiveMetadata.commit(HiveMetadata.java:3722) at io.trino.plugin.hive.HiveTransactionManager.lambda$commit$0(HiveTransactionManager.java:58) at java.base/java.util.Optional.ifPresent(Optional.java:178) at io.trino.plugin.hive.HiveTransactionManager.commit(HiveTransactionManager.java:56) at io.trino.plugin.hive.HiveConnector.commit(HiveConnector.java:220) at io.trino.metadata.CatalogTransaction.commit(CatalogTransaction.java:85) at io.trino.metadata.CatalogMetadata.commit(CatalogMetadata.java:154)

Above is the stack... Query failing at first run and its passing when we ran it again.

any more? some word like Suppressed or org.apache.hadoop.hive

[parimalpatil28]

VERSIONS: CDP 7.1.8 HIVE 3.1 TRINO 429

I am getting above stack trace only.

[hirolee88]

There should be more below

[parimalpatil28]

Here is the log captured from UI @hirolee88

io.trino.spi.TrinoException: All operations other than the following update operations were completed: replace table parameters default.pr_hive   at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$Committer.executeUpdateStatisticsOperations(SemiTransactionalHiveMetastore.java:2186)   at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commitShared(SemiTransactionalHiveMetastore.java:1590)   at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commit(SemiTransactionalHiveMetastore.java:1287)   at io.trino.plugin.hive.HiveMetadata.commit(HiveMetadata.java:3774)   at io.trino.plugin.hive.HiveTransactionManager.lambda$commit$0(HiveTransactionManager.java:58)   at java.base/java.util.Optional.ifPresent(Optional.java:178)   at io.trino.plugin.hive.HiveTransactionManager.commit(HiveTransactionManager.java:56)   at io.trino.plugin.hive.HiveConnector.commit(HiveConnector.java:220)   at io.trino.metadata.CatalogTransaction.commit(CatalogTransaction.java:85)   at io.trino.metadata.CatalogMetadata.commit(CatalogMetadata.java:154)   at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)   at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)   at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:75)   at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)   at io.airlift.concurrent.BoundedExecutor.drainQueue(BoundedExecutor.java:79)   at io.trino.$gen.Trino_429_e_0____20240122_073928_2.run(Unknown Source)   at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)   at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)   at java.base/java.lang.Thread.run(Thread.java:833)   Suppressed: io.trino.spi.TrinoException: Insert of object "org.apache.hadoop.hive.metastore.model.MTableColumnStatistics@f511a59" using statement "INSERT INTO "TAB_COL_STATS" ("CS_ID","AVG_COL_LEN","BIT_VECTOR","CAT_NAME","COLUMN_NAME","COLUMN_TYPE","DB_NAME","BIG_DECIMAL_HIGH_VALUE","BIG_DECIMAL_LOW_VALUE","DOUBLE_HIGH_VALUE","DOUBLE_LOW_VALUE","ENGINE","LAST_ANALYZED","LONG_HIGH_VALUE","LONG_LOW_VALUE","MAX_COL_LEN","NUM_DISTINCTS","NUM_FALSES","NUM_NULLS","NUM_TRUES","TBL_ID","TABLE_NAME") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" failed : ERROR: null value in column "ENGINE" violates not-null constraint Detail: Failing row contains (481, hive, default, pr_hive, col8, string, 928, null, null, null, null, null, null, 1, 18, \x484c, 467.555555555555543, 4096, null, null, 12121, null).     at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.setTableColumnStatistics(ThriftHiveMetastore.java:565)     at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.updateTableStatistics(ThriftHiveMetastore.java:542)     at io.trino.plugin.hive.metastore.thrift.BridgingHiveMetastore.updateTableStatistics(BridgingHiveMetastore.java:129)     at io.trino.plugin.hive.metastore.ForwardingHiveMetastore.updateTableStatistics(ForwardingHiveMetastore.java:91)     at io.trino.plugin.hive.metastore.cache.CachingHiveMetastore.updateTableStatistics(CachingHiveMetastore.java:693)     at io.trino.plugin.hive.metastore.cache.CachingHiveMetastore.updateTableStatistics(CachingHiveMetastore.java:693)     at io.trino.plugin.hive.HiveMetastoreClosure.updateTableStatistics(HiveMetastoreClosure.java:127)     at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$UpdateStatisticsOperation.run(SemiTransactionalHiveMetastore.java:3445)     at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$Committer.lambda$executeUpdateStatisticsOperations$16(SemiTransactionalHiveMetastore.java:2169)     at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)     at io.airlift.concurrent.BoundedExecutor.drainQueue(BoundedExecutor.java:79)     ... 3 more   Caused by: io.trino.hive.thrift.metastore.MetaException: Insert of object "org.apache.hadoop.hive.metastore.model.MTableColumnStatistics@f511a59" using statement "INSERT INTO "TAB_COL_STATS" ("CS_ID","AVG_COL_LEN","BIT_VECTOR","CAT_NAME","COLUMN_NAME","COLUMN_TYPE","DB_NAME","BIG_DECIMAL_HIGH_VALUE","BIG_DECIMAL_LOW_VALUE","DOUBLE_HIGH_VALUE","DOUBLE_LOW_VALUE","ENGINE","LAST_ANALYZED","LONG_HIGH_VALUE","LONG_LOW_VALUE","MAX_COL_LEN","NUM_DISTINCTS","NUM_FALSES","NUM_NULLS","NUM_TRUES","TBL_ID","TABLE_NAME") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" failed : ERROR: null value in column "ENGINE" violates not-null constraint Detail: Failing row contains (481, hive, default, pr_hive, col8, string, 928, null, null, null, null, null, null, 1, 18, \x484c, 467.555555555555543, 4096, null, null, 1705916234, null).     at io.trino.hive.thrift.metastore.ThriftHiveMetastore$update_table_column_statistics_result$update_table_column_statistics_resultStandardScheme.read(ThriftHiveMetastore.java)     at io.trino.hive.thrift.metastore.ThriftHiveMetastore$update_table_column_statistics_result$update_table_column_statistics_resultStandardScheme.read(ThriftHiveMetastore.java)     at io.trino.hive.thrift.metastore.ThriftHiveMetastore$update_table_column_statistics_result.read(ThriftHiveMetastore.java)     at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:93)     at io.trino.hive.thrift.metastore.ThriftHiveMetastore$Client.recvUpdateTableColumnStatistics(ThriftHiveMetastore.java:4059)     at io.trino.hive.thrift.metastore.ThriftHiveMetastore$Client.updateTableColumnStatistics(ThriftHiveMetastore.java:4046)     at com.starburstdata.presto.plugin.hive.metastore.thrift.StarburstThriftHiveMetastoreClient.setTableColumnStatistics(StarburstThriftHiveMetastoreClient.java:130)     at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.lambda$setTableColumnStatistics$15(FailureAwareThriftMetastoreClient.java:189)     at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.runWithHandle(FailureAwareThriftMetastoreClient.java:475)     at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.setTableColumnStatistics(FailureAwareThriftMetastoreClient.java:189)     at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.lambda$setTableColumnStatistics$19(ThriftHiveMetastore.java:556)     at io.trino.plugin.hive.metastore.thrift.ThriftMetastoreApiStats.lambda$wrap$0(ThriftMetastoreApiStats.java:41)     at io.trino.plugin.hive.util.RetryDriver.run(RetryDriver.java:116)     at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.setTableColumnStatistics(ThriftHiveMetastore.java:554)     ... 13 more

[hirolee88]

https://github.com/trinodb/trino-hive-apache/pull/43 try with trino 436 thanks this is a bug between trino and hive 3 (uncertain about the detailed version), hive metastore‘s mysql table TAB_COL_STATS column "ENGINE" changed to not null

https://github.com/trinodb/trino-hive-apache/pull/43#issuecomment-1190060212

@ebyhr is this fixed in trino？

[parimalpatil28]

@hirolee88 @ebyhr How can I make it work? Any suggestions? Workaround?

[hirolee88]

see above https://github.com/trinodb/trino-hive-apache/pull/43#issuecomment-1190060212

[parimalpatil28]

Hello @hirolee88, Thanks for your help... This workaround worked for me.... Thanks for your guidance.. 🙂

Thanks, Parimal