[teiid-issues] [JBoss JIRA] (TEIID-3904) TEIID31100 Parsing error: Encountered "[*]move[*] backward 5831" on openquery update command

[ https://issues.jboss.org/browse/TEIID-3904?page=com.atlassian.jira.plugin... ] Steve Tran commented on TEIID-3904: ----------------------------------- Just some more background just in case anybody is interested. The original root cause of this ticket was due to enable the "Use Declare/Fetch" setting on the ODBC driver. I unchecked it and the query ran fine, albeit 3 minutes to update 4000 rows. I was tailing the server.log and saw that it was doing work, so it wasn't like it was hanging. {code} 15:02:41,541 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) after getOrCreateTransactionContext : E5auO50CNNZM LOCAL ID:TransactionImple < ac, BasicAction: 0:ffffa22c73d5:25363a56:5698eec3:5c07 status: ActionStatus.RUNNING > 15:02:41,541 DEBUG [org.teiid.BUFFER_MGR] (Worker784_QueryProcessorQueue7520537) Creating TupleBuffer: 246901 [Count] [class java.lang.Integer] batch size 2048 of type PROCESSOR 15:02:41,541 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) before resume:org.teiid.dqp.internal.process.TransactionServerImpl@2cbb178f(E5auO50CNNZM LOCAL ID:TransactionImple < ac, BasicAction: 0:ffffa22c73d5:25363a56:5698eec3:5c07 status: ActionStatus.RUNNING >) 15:02:41,541 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) after resume : null 15:02:41,541 TRACE [org.teiid.BUFFER_MGR] (Worker784_QueryProcessorQueue7520537) Reserving buffer space 114688 FORCE 15:02:41,541 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Create State 15:02:41,541 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Processing NEW request: UPDATE Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST SET DO_NOT_USE = -1 WHERE (Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST.TM_PD_ID = 4491) AND (Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST.TM_PD_TYP_ID = 5) AND (Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST.DO_NOT_USE IS NULL) 15:02:41,541 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Obtained execution 15:02:41,541 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) Source-specific command: UPDATE "HSIDBA"."HSI_TM_PD_TEST" SET "DO_NOT_USE" = ? WHERE "HSIDBA"."HSI_TM_PD_TEST"."TM_PD_ID" = ? AND "HSIDBA"."HSI_TM_PD_TEST"."TM_PD_TYP_ID" = ? AND "HSIDBA"."HSI_TM_PD_TEST"."DO_NOT_USE" IS NULL 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Executed command 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Processing MORE request 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Getting results from connector 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Obtained last batch, total row count: 1 15:02:41,542 TRACE [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) closed atomic-request: E5auO50CNNZM.4596.0.171982 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Remove State 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Processing Close : UPDATE Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST SET DO_NOT_USE = -1 WHERE (Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST.TM_PD_ID = 4491) AND (Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST.TM_PD_TYP_ID = 5) AND (Oracle_cdtsorl232d_DEV.HSI_TM_PD_TEST.DO_NOT_USE IS NULL) 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Closed execution 15:02:41,542 DEBUG [org.teiid.CONNECTOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596.0.171982 Closed connection 15:02:41,542 DEBUG [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) QueryProcessor: closing processor 15:02:41,542 TRACE [org.teiid.BUFFER_MGR] (Worker784_QueryProcessorQueue7520537) Releasing buffer space 114688 15:02:41,542 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@2cbb178f(E5auO50CNNZM LOCAL ID:TransactionImple < ac, BasicAction: 0:ffffa22c73d5:25363a56:5698eec3:5c07 status: ActionStatus.RUNNING >) 15:02:41,542 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) after suspend : null 15:02:41,542 DEBUG [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) [RequestWorkItem.sendResultsIfNeeded] requestID: E5auO50CNNZM.4596 resultsID: 246901 done: true 15:02:41,542 FINER [org.teiid.jdbc] (Worker784_QueryProcessorQueue7520537) Recieved update counts: [1] 15:02:41,542 TRACE [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) before closeRequest:org.teiid.dqp.internal.process.DQPCore@1db340aa(4596) 15:02:41,542 DEBUG [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) closeQuery for requestID=E5auO50CNNZM.4596 15:02:41,542 TRACE [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596 more work WORKING 15:02:41,542 TRACE [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) after closeRequest : org.teiid.client.util.ResultsFuture@3021d036 15:02:41,542 FINE [org.teiid.jdbc] (Worker784_QueryProcessorQueue7520537) Successfully executed a query UPDATE TestVBL.HSI_TM_PD_TEST SET DO_NOT_USE=$1 WHERE (TM_PD_ID=$2 AND TM_PD_TYP_ID=$3 AND DO_NOT_USE IS NULL ) and obtained results 15:02:41,542 TRACE [org.teiid.ODBC] (Worker784_QueryProcessorQueue7520537) invoking client method: sendUpdateCount [UPDATE TestVBL.HSI_TM_PD_TEST SET DO_NOT_USE=$1 WHERE (TM_PD_ID=$2 AND TM_PD_TYP_ID=$3 AND DO_NOT_USE IS NULL ), 1] 15:02:41,543 TRACE [org.teiid.ODBC] (Worker784_QueryProcessorQueue7520537) invoking client method: setEncoding [UTF8, false] 15:02:41,543 FINE [org.teiid.jdbc] (Worker784_QueryProcessorQueue7520537) Statement closed successfully. 15:02:41,543 TRACE [org.teiid.ODBC] (Worker784_QueryProcessorQueue7520537) invoking client method: statementClosed null 15:02:41,543 TRACE [org.teiid.ODBC] (Worker784_QueryProcessorQueue7520537) invoking client method: ready [true, false] 15:02:41,543 DEBUG [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) Removing tuplesource for the request E5auO50CNNZM.4596 15:02:41,543 DEBUG [org.teiid.BUFFER_MGR] (Worker784_QueryProcessorQueue7520537) Removing TupleBuffer: 246901 15:02:41,543 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@2cbb178f(E5auO50CNNZM LOCAL ID:TransactionImple < ac, BasicAction: 0:ffffa22c73d5:25363a56:5698eec3:5c07 status: ActionStatus.RUNNING >) 15:02:41,543 DEBUG [org.teiid.TXN_LOG] (Worker784_QueryProcessorQueue7520537) after suspend : null 15:02:41,543 TRACE [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596 end processing MORE_WORK 15:02:41,543 TRACE [org.teiid.PROCESSOR] (Worker784_QueryProcessorQueue7520537) E5auO50CNNZM.4596 done processing - ignoring more MORE_WORK {code} According to https://docs.jboss.org/author/display/TEIID/Configuring+the+Data+Source+N... {quote} # enable below when dealing large resultsets to enable cursoring(note prepared stmts will not work) #UseDeclareFetch=1 {quote} That's why we enabled declare/fetch in the first place. I'll have to go back to the client and figure out what the original query was and why we needed to enable that in the first place. But if the description about the handling large resultset is true, we're going to still need this backward move functionality in the future. -- This message was sent by Atlassian JIRA (v6.4.11#64026)