hive 1.2.1 error on delete command

Question

I use apache hive 1.2.1 . hiveserver2's metastore is in embedded mode. in hive-default.xml file I have following properties:

<property>
  <name>hive.support.concurrency</name>
  <value>true</value>
  <description>    
  </description>
</property>
<property>
  <name>hive.enforce.bucketing</name>
  <value>true</value>
  <description></description>
</property>
<property>
  <name>hive.exec.dynamic.partition.mode</name>
  <value>nonstrict</value>
  <description>
  </description>
</property>
<property>
  <name>hive.txn.manager</name>
  <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
  <description>
  </description>
</property>
<property>
  <name>hive.compactor.initiator.on</name>
  <value>true</value>
  <description>
  </description>
</property>
<property>
  <name>hive.compactor.worker.threads</name>
  <value>1</value>
  <description>
  </description>
</property>

I created table:

create table test (a string, b int) clustered by (b) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

table created

then insert test data:

insert into table test values ("aa", 1), ("bb", 2);

inserted

then run delete command:

delete from test where b = 1;

and it throws error:

Error: Error while compiling statement: FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations. (state=42000,code=10294)

I run these commands from beeline.

for information: when I run command show transactions;, it throws error:

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Unable to select from transaction database: Table/View 'NEXT_TXN_ID' does not exist.(SQLState=42X05,ErrorCode=20000)java.sql.SQLSyntaxErrorException: Table/View 'NEXT_TXN_ID' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
at com.jolbox.bonecp.StatementHandle.executeQuery(StatementHandle.java:464)
at org.apache.hadoop.hive.metastore.txn.TxnHandler.getOpenTxnsInfo(TxnHandler.java:154)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_open_txns_info(HiveMetaStore.java:5537)
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.invoke(RetryingHMSHandler.java:107)
at com.sun.proxy.$Proxy8.get_open_txns_info(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.showTxns(HiveMetaStoreClient.java:1857)
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.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:156)
at com.sun.proxy.$Proxy9.showTxns(Unknown Source)
at org.apache.hadoop.hive.ql.metadata.Hive.showTransactions(Hive.java:3201)
at org.apache.hadoop.hive.ql.exec.DDLTask.showTxns(DDLTask.java:2622)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:425)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)
at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)
at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)
at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
  Caused by: java.sql.SQLException: Table/View 'NEXT_TXN_ID' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 46 more
Caused by: ERROR 42X05: Table/View 'NEXT_TXN_ID' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 40 more
) (state=08S01,code=1)

can anyone help me?

thanks in advance.

Answer 1

I would say that depending on Hive Transaction for now is not a good idea. I have observed that when you run the first insert/delete command it works from all places like JDBC (squirrel, DBeaver), Once you have run the first command the second command onwards you will get error.

If you run the second command in hive cli (from edge node ) it seems to work.