[HIVE-13856] Fetching transaction batches during ACID streaming against Hive Metastore using Oracle DB fails - ASF JIRA

Details

Type: Bug
Status: Closed
Priority: Blocker
Resolution: Fixed
Affects Version/s: 1.3.0, 2.1.0, 2.2.0
Fix Version/s: 1.3.0, 2.1.0
Component/s: Transactions
Labels:
None

Target Version/s:

1.3.0, 2.1.0

Description

2016-05-25 00:43:49,682 INFO  [pool-4-thread-5]: txn.TxnHandler (TxnHandler.java:checkRetryable(1585)) - Non-retryable error: ORA-00933: SQL command not properly ended
 (SQLState=42000, ErrorCode=933)
2016-05-25 00:43:49,685 ERROR [pool-4-thread-5]: metastore.RetryingHMSHandler (RetryingHMSHandler.java:invoke(159)) - MetaException(message:Unable to select from transaction database java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
	at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.openTxns(TxnHandler.java:429)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.open_txns(HiveMetaStore.java:5647)
	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.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at com.sun.proxy.$Proxy15.open_txns(Unknown Source)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11604)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11589)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.openTxns(TxnHandler.java:438)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.open_txns(HiveMetaStore.java:5647)
	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.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at com.sun.proxy.$Proxy15.open_txns(Unknown Source)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11604)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11589)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)

2016-05-25 00:43:49,685 ERROR [pool-4-thread-5]: thrift.ProcessFunction (ProcessFunction.java:process(41)) - Internal error processing open_txns
MetaException(message:Unable to select from transaction database java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
	at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.openTxns(TxnHandler.java:429)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.open_txns(HiveMetaStore.java:5647)
	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.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at com.sun.proxy.$Proxy15.open_txns(Unknown Source)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11604)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11589)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.openTxns(TxnHandler.java:438)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.open_txns(HiveMetaStore.java:5647)
	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.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at com.sun.proxy.$Proxy15.open_txns(Unknown Source)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11604)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$open_txns.getResult(ThriftHiveMetastore.java:11589)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)

I think the reason here is that

metastore/src/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java

  public OpenTxnsResponse openTxns(OpenTxnRequest rqst) throws MetaException {
...
        String query;
        String insertClause = "insert into TXNS (txn_id, txn_state, txn_started, txn_last_heartbeat, txn_user, txn_host) values ";
        StringBuilder valuesClause = new StringBuilder();

        for (long i = first; i < first + numTxns; i++) {
          txnIds.add(i);

          if (i > first &&
              (i - first) % conf.getIntVar(HiveConf.ConfVars.METASTORE_DIRECT_SQL_MAX_ELEMENTS_VALUES_CLAUSE) == 0) {
            // wrap up the current query, and start a new one
            query = insertClause + valuesClause.toString();
            queries.add(query);

            valuesClause.setLength(0);
            valuesClause.append("(").append(i).append(", 'o', ").append(now).append(", ").append(now)
                .append(", '").append(rqst.getUser()).append("', '").append(rqst.getHostname())
                .append("')");

            continue;
          }

          if (i > first) {
            valuesClause.append(", ");
          }

          valuesClause.append("(").append(i).append(", 'o', ").append(now).append(", ").append(now)
              .append(", '").append(rqst.getUser()).append("', '").append(rqst.getHostname())
              .append("')");
        }

        query = insertClause + valuesClause.toString();
...
}

ends up building a query of the form

INSERT INTO TXNS (...) VALUES (...), (...)

Oracle doesn't like this way of inserting multiple rows of data. Couple of ways the following post describe is either inserting each row individually or use the INSERT ALL semantics.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending

HIVE-13856.patch
31/May/16 16:06
78 kB
Eugene Koifman
HIVE-13856.1.patch
31/May/16 20:43
86 kB
Eugene Koifman

Issue Links

is blocked by

HIVE-11956 SHOW LOCKS should indicate what acquired the lock

Closed

Fetching transaction batches during ACID streaming against Hive Metastore using Oracle DB fails

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates