Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-13856

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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Blocker
    • Resolution: Fixed
    • 1.3.0, 2.1.0, 2.2.0
    • 1.3.0, 2.1.0
    • Transactions
    • None

    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

        1. HIVE-13856.patch
          78 kB
          Eugene Koifman
        2. HIVE-13856.1.patch
          86 kB
          Eugene Koifman

        Issue Links

          Activity

            People

              ekoifman Eugene Koifman
              deepesh Deepesh Khandelwal
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: