Issue with REST web service for specific calls (TEIID30151)

[andreas.krueck]

Hi all,

I'm using an existing Teiid 10 instance to retrieve data using automatically created statements from an Oracle database and a REST web service. Based on the created query, it works or creates this error message:

TEIID30020 Processing exception for request XOEEB85RAoHm.10 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException XMLSystemFunctions.java:911.: org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.

This is a sample SQL producing this error (key SQL marked in red):

SELECT MY_SCHEMA.ID,

  MY_REST.FILE_ID,

  MY_REST.FILE_NAME

FROM ORACLE_DB.SCHEMA MY_SCHEMA

LEFT OUTER JOIN REST_WS.WS_VIEW MY_REST ON MY_SCHEMA.ID = MY_REST.SEARCH_STRING

WHERE MY_SCHEMA.ID IN (

  SELECT MY_SCHEMA.ID

  FROM ORACLE_DB.SCHEMA MY_SCHEMA

  INNER JOIN ORACLE_DB.SCHEMA2

  ON MY_SCHEMA.ID = ORACLE_DB.SCHEMA2.ID_FK

  INNER JOIN ORACLE_DB.SCHEMA3 MY_SCHEMA3

  ON ORACLE_DB.SCHEMA2.ID_FK = MY_SCHEMA3.BIA_SID

  WHERE MY_SCHEMA3.ID = 'ABC' AND ORACLE_DB.SCHEMA2.ID_FK = 12345

)

OPTION MAKEDEP REST_WS.WS_VIEW

This is the same statement with a pre-defined key and this produces no errors:

SELECT MY_SCHEMA.ID,

  MY_REST.FILE_ID,

  MY_REST.FILE_NAME

FROM ORACLE_DB.SCHEMA MY_SCHEMA

LEFT OUTER JOIN REST_WS.WS_VIEW MY_REST ON MY_SCHEMA.ID = MY_REST.SEARCH_STRING

WHERE MY_SCHEMA.ID IN (56789)

OPTION MAKEDEP REST_WS.WS_VIEW

What can I do to find the origin of this error?

Many thanks in advance.

[rareddy]

Typically the rest access will be in procedural forms, like "exec REST_WS.WS_VIEW(...)", above you show in view form. Typically one can use relational procedure transformation where the criteria get converted back into procedure parameters at the time of execution.

What happens when you provide multiple IN clause values as shown in the statement without dynamic select expression? Also, try moving the MAKEDEP right after "LEFT OUTER JOIN" in the comment form and see if that makes any difference.

[andreas.krueck]

The EXEC part is done in the procedure, the REST_WS.WS_VIEW is defined like this:

CREATE VIEW WS_VIEW AS SELECT * FROM REST_WS.WS_PROCEDURE;

with the procedure defined like this:

CREATE VIRTUAL PROCEDURE WS_PRECEDURE(SEARCH_INPUT varchar) RETURNS (

  FILE_ID varchar,

  FILE_NAME string

)

AS

SELECT

  A.FILE_ID, A.FILE_NAME

FROM

(

  EXEC MY_WS.INVOKEHTTP(

  ...

  )

) AS F,

XMLTABLE(

  '/myxml/response/docs' PASSING JSONTOXML('myxml', F.RESULT) COLUMNS

  FILE_ID string PATH 'fileid',

  FILE_NAME string PATH 'filename'

)

AS A;

With two or three values in the IN clause, the results are returned as usual, just like for one.

The position of the MAKEDEP seems to be not possible, since no other option worked for me.

[rareddy]

I guess procedure gets called for each SCHEMA_ID. Can you provide query plan for this?

[andreas.krueck]

I noticed, when changing the identifier in yellow, the query plan is identical, but sometimes it works, sometimes I get the mentioned error message. The only difference was that the statement in red delivered more or less identifiers. With ~800 it works, with ~3000 not.

SELECT MY_SCHEMA.ID,

  MY_REST.FILE_ID,

  MY_REST.FILE_NAME

FROM ORACLE_DB.SCHEMA MY_SCHEMA

LEFT OUTER JOIN REST_WS.WS_VIEW MY_REST ON MY_SCHEMA.ID = MY_REST.SEARCH_STRING

WHERE MY_SCHEMA.ID IN (

  SELECT MY_SCHEMA.ID

  FROM ORACLE_DB.SCHEMA MY_SCHEMA

  INNER JOIN ORACLE_DB.SCHEMA2

  ON MY_SCHEMA.ID = ORACLE_DB.SCHEMA2.ID_FK

  INNER JOIN ORACLE_DB.SCHEMA3 MY_SCHEMA3

  ON ORACLE_DB.SCHEMA2.ID_FK = MY_SCHEMA3.BIA_SID

  WHERE MY_SCHEMA3.ID = 'ABC' AND ORACLE_DB.SCHEMA2.ID_FK = 12345

)

OPTION MAKEDEP REST_WS.WS_VIEW

Any idea?

[rareddy]

As a stop-gap measure, you can always control how many IN values you want to support in one go by setting the translator property for your Oracle to control the number at 800.

<translator name="oracle-override" type="oracle">

     <property name="MaxInCriteriaSize" value="800"/>

</translator>

And then use "oracle-override" as your translator name on your Oracle model.

Ramesh..

[andreas.krueck]

The Oracle connection seems to be not the issue. The combination of the Oracle with the REST service data source is.

I can retrieve multiple million rows from the Oracle connection without any issue, but as soon as I add the related web service information, the statement fails.

Any idea why it is only failing for a larger number of records?

[rareddy]

Provide full error stack from logs. If needs to be run in DEBUG mode to get it.

[andreas.krueck]

13:13:19,994 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Created intermediate sort buffer 3301

13:13:20,010 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Created intermediate sort buffer 3302

13:13:20,010 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Created intermediate sort buffer 3303

13:13:20,025 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1718) Running task for parent thread Worker1_QueryProcessorQueue1717

13:13:20,025 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Request Thread ikch7WEdCSyt.10 - processor blocked

13:13:21,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) Request Thread ikch7WEdCSyt.10 with state PROCESSING

13:13:21,478 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1720) Using stream processing for evaluation of /myxml/response/docs

13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) QueryProcessor: closing processor

13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) QueryProcessor: closing processor

13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1721) Running task for parent thread Worker1_QueryProcessorQueue1719

13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) Request Thread ikch7WEdCSyt.10 - processor blocked

13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) Request Thread ikch7WEdCSyt.10 with state PROCESSING

13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1723) Using stream processing for evaluation of /myxml/response/docs

13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) QueryProcessor: closing processor

13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) QueryProcessor: closing processor

13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1724) Running task for parent thread Worker1_QueryProcessorQueue1722

13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) Request Thread ikch7WEdCSyt.10 - processor blocked

13:13:24,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) Request Thread ikch7WEdCSyt.10 with state PROCESSING

13:13:24,478 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1726) Using stream processing for evaluation of /myxml/response/docs

13:13:24,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) QueryProcessor: closing processor

13:13:24,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) Created intermediate sort buffer 3322

13:13:24,494 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1727) Running task for parent thread Worker1_QueryProcessorQueue1725

13:13:24,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) Request Thread ikch7WEdCSyt.10 - processor blocked

13:13:24,557 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) Request Thread ikch7WEdCSyt.10 with state PROCESSING

13:13:24,557 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1729) Using stream processing for evaluation of /myxml/response

13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) QueryProcessor: closing processor

13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) QueryProcessor: closing processor

13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) QueryProcessor: closing processor

13:13:24,572 WARN  [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) TEIID30020 Processing exception for request ikch7WEdCSyt.10 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException '12' XMLSystemFunctions.java:911.: org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.

at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:148)

at org.teiid.query.processor.relational.XMLTableNode$1.run(XMLTableNode.java:269)

at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Caused by: nu.xom.ParsingException: 12

at nu.xom.Builder.build(Unknown Source)

at nu.xom.Builder.build(Unknown Source)

at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:142)

... 7 more

Caused by: java.lang.ArrayIndexOutOfBoundsException: 12

at org.teiid.query.function.source.XMLSystemFunctions.escapeName(XMLSystemFunctions.java:911)

at org.teiid.query.function.source.XMLSystemFunctions$JsonToXmlContentHandler.startObjectEntry(XMLSystemFunctions.java:226)

at org.teiid.json.simple.JSONParser.parse(JSONParser.java:232)

at org.teiid.query.function.source.XMLSystemFunctions$JsonToXmlContentHandler.nextEvent(XMLSystemFunctions.java:351)

at org.teiid.query.xquery.saxon.XMLEventStreamReader.next(XMLEventStreamReader.java:194)

at net.sf.saxon.evpull.StaxToEventBridge.next(StaxToEventBridge.java:159)

at net.sf.saxon.evpull.EventStackIterator.next(EventStackIterator.java:61)

at net.sf.saxon.evpull.EventIteratorToReceiver.copy(EventIteratorToReceiver.java:44)

at net.sf.saxon.event.Sender.sendPullEventSource(Sender.java:559)

at net.sf.saxon.event.Sender.send(Sender.java:132)

at net.sf.saxon.Configuration.buildDocument(Configuration.java:3361)

at net.sf.saxon.Configuration.buildDocument(Configuration.java:3303)

at org.teiid.query.xquery.saxon.SaxonReader.parse(StreamingUtils.java:179)

... 10 more

13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) Removing tuplesource for the request ikch7WEdCSyt.10

13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) ikch7WEdCSyt.10 Finished Processing

13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item. Sending error to client ikch7WEdCSyt.10

[shawkins]

Andreas,

This looks like a bug with the name correction logic - if the json name ends in an underscore this exception will be thrown.  Can you log an issue for this? - JBoss Issue Tracker

Steve

[andreas.krueck]

[TEIID-5294] Bug with the name correction logic (TEIID30151 eror) - JBoss Issue Tracker

[andreas.krueck]

Is there a workaround I can implement for now?

[rareddy]

Looks like the issue has been resolved, can you not use the one the fixed versions?

[andreas.krueck]

We recently upgraded to v10.2, but still getting TEIID30151 with slightly different error:

09:15:44,117 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2475) Request Thread Fk5ykF/8Bdl6.15 - processor blocked

09:15:44,539 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2478) Request Thread Ri4uuB5w1vOK.35 with state PROCESSING

09:15:44,539 DEBUG [org.teiid.PROCESSOR] (Worker6_QueryProcessorQueue2479) Using stream processing for evaluation of /myxml/response/docs

09:15:44,555 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2478) QueryProcessor: closing processor

09:15:44,555 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2478) QueryProcessor: closing processor

09:15:44,555 DEBUG [org.teiid.PROCESSOR] (Worker6_QueryProcessorQueue2480) Running task for parent thread Worker15_QueryProcessorQueue2478

09:15:44,555 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2478) Request Thread Ri4uuB5w1vOK.35 - processor blocked

09:15:45,211 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2481) Request Thread Fk5ykF/8Bdl6.15 with state PROCESSING

09:15:45,211 DEBUG [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue2482) Using stream processing for evaluation of /myxml/response/docs

09:15:45,211 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2481) QueryProcessor: closing processor

09:15:45,211 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2481) Created intermediate sort buffer 1570

09:15:45,211 DEBUG [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue2483) Running task for parent thread Worker15_QueryProcessorQueue2481

09:15:45,211 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2481) Request Thread Fk5ykF/8Bdl6.15 - processor blocked

09:15:45,367 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) Request Thread Fk5ykF/8Bdl6.15 with state PROCESSING

09:15:45,367 DEBUG [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue2485) Using stream processing for evaluation of /myxml/response

09:15:45,367 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) QueryProcessor: closing processor

09:15:45,367 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) QueryProcessor: closing processor

09:15:45,367 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) QueryProcessor: closing processor

09:15:45,383 WARN  [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) TEIID30020 Processing exception for request Fk5ykF/8Bdl6.15 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException '0xb is not allowed in XML content' nu.xom.Verifier.throwIllegalCharacterDataException(Unknown Source).: org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.

at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:174)

at org.teiid.query.processor.relational.XMLTableNode$1.run(XMLTableNode.java:266)

at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

Caused by: nu.xom.ParsingException: 0xb is not allowed in XML content

at nu.xom.Builder.build(Unknown Source)

at nu.xom.Builder.build(Unknown Source)

at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:168)

... 7 more

Caused by: nu.xom.IllegalCharacterDataException: 0xb is not allowed in XML content

at nu.xom.Verifier.throwIllegalCharacterDataException(Unknown Source)

at nu.xom.Verifier.checkPCDATA(Unknown Source)

at nu.xom.Text._setValue(Unknown Source)

at nu.xom.Text.<init>(Unknown Source)

at nu.xom.NodeFactory.makeText(Unknown Source)

at nux.xom.pool.XOMUtil$1.makeText(XOMUtil.java:92)

at nux.xom.xquery.StreamingPathFilter$StreamingPathFilterNodeFactory.makeText(StreamingPathFilter.java:492)

at nu.xom.XOMHandler.flushText(Unknown Source)

at nu.xom.XOMHandler.endElement(Unknown Source)

at net.sf.saxon.event.ContentHandlerProxy.endElement(ContentHandlerProxy.java:414)

at org.teiid.query.xquery.saxon.ContentHandlerProxyReceiver.endElement(StreamingUtils.java:258)

at net.sf.saxon.event.ProxyReceiver.endElement(ProxyReceiver.java:182)

at org.teiid.query.xquery.saxon.PathMapFilter.endElement(PathMapFilter.java:219)

at net.sf.saxon.pull.PullPushTee.copyEvent(PullPushTee.java:134)

at net.sf.saxon.pull.PullPushTee.next(PullPushTee.java:72)

at net.sf.saxon.pull.PullConsumer.consume(PullConsumer.java:42)

at net.sf.saxon.pull.PullPushCopier.copy(PullPushCopier.java:44)

at net.sf.saxon.event.Sender.sendPullSource(Sender.java:520)

at net.sf.saxon.event.Sender.send(Sender.java:182)

at net.sf.saxon.Configuration.buildDocumentTree(Configuration.java:4032)

at net.sf.saxon.Configuration.buildDocumentTree(Configuration.java:3970)

at org.teiid.query.xquery.saxon.SaxonReader.parse(StreamingUtils.java:176)

... 10 more

09:15:45,383 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) Removing tuplesource for the request Fk5ykF/8Bdl6.15

09:15:45,383 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) Fk5ykF/8Bdl6.15 Finished Processing

09:15:45,383 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2484) org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item. Sending error to client Fk5ykF/8Bdl6.15

09:15:45,430 DEBUG [org.teiid.PROCESSOR] (NIO6) closeQuery for requestID=Fk5ykF/8Bdl6.13

09:15:45,430 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2486) Request Thread Fk5ykF/8Bdl6.13 with state CLOSE

09:15:45,430 DEBUG [org.teiid.PROCESSOR] (Worker15_QueryProcessorQueue2486) Removing tuplesource for the request Fk5ykF/8Bdl6.13

09:15:45,430 DEBUG [org.teiid.PROCESSOR] (NIO6) closeQuery for requestID=Fk5ykF/8Bdl6.14

[rareddy]

Can you log an issue with a reproducer to Teiid JIRA?