Teiid cancel exception for insert query

[mtawk]

i'm using Teiid 9 with oracle translator.

i'm executing a query insert into select, that writes around 5 million records.

After randomly around10-20 minutes and around 2 millions records inserted, Teiid is returning a query cancelled exception.

After inspection i find out that the insertion is continuing at Oracle level and the table is locked till the insertion finishes.

What could be the cause of such an exception and is there anything that can be done to prevent such exceptions?

[shawkins]

> What could be the cause of such an exception and is there anything that can be done to prevent such exceptions?

Ideally there would be a root cause for the cancellation shown in the logs.  The most common issue would be a transaction timeout.  If there is no discernible root exception in the log, is it possible that your client is cancelling the query?

[mtawk]

No client is cancelling the query.

as for transaction timeout, you mean at oracle level or teiid level?

Because I tried executing the query with a direct jdbc connection and it executed with success.

Could some network cuts between teiid and oracle server while execution raise such an exception?

[mtawk]

You find attached a screenshot of the exception stack

[shawkins]

> as for transaction timeout, you mean at oracle level or teiid level?

Teiid/WildFly level.  If this is running under a transaction, then the transaction manager in WildFly can cause a timeout.

> Could some network cuts between teiid and oracle server while execution raise such an exception?

Generally source errors should bubble up to the client.  But at the very least you would see an entry in the server log related to that failure.

Are there any warning/error level entries in the server log related to this request?

[mtawk]

I guess it is not a timeout since the exception is reproduced at a random time each time and sometimes it executes without a problem.

As for the log it is set to error and it did not give any error.

I tried pinging the oracle server while executing the query and noticed a request timeout around every 20 pings, could this lead to such an exception?

[shawkins]

> As for the log it is set to error and it did not give any error.

It's best to have warnings enabled as well, as "expected" errors - including issues with the sources - will be logged as warnings.

> I tried pinging the oracle server while executing the query and noticed a request timeout around every 20 pings, could this lead to such an exception?

It's possible that it would lead to a source error, yes.  You'd ideally see that as the root exception rather than a cancellation.  Reporting the request as cancelled is a bit of a catch all as once a request fails, the engine issues a cancel to terminate any pending work.

[mtawk]

I have enabled warning but still did not get any error in logs.

I have enabled "All" on DQP log and you find attached the logs written starting from execution till cancellation.

Is there any other log that could help in this concern?

The cancel to follow in the attached log is for the request olYYk5j+qu0r

57014 TEIID30160 The request olYYk5j+qu0r.0 has been cancelled

[shawkins]

The cancel message you are seeing:

2017-05-30 14:44:15,351 DEBUG TEIID_DQP_LOGGER:? - [cancelQuery for requestID=pxMXe5BVUfp+.0]

2017-05-30 14:44:15,366 DEBUG TEIID_DQP_LOGGER:? - [cancelQuery for requestID=Pgf3ovT6GK6I.0]

2017-05-30 14:44:15,366 DEBUG TEIID_DQP_LOGGER:? - [cancelQuery for requestID=olYYk5j+qu0r.0]

Can only come from one of three events:

-jdbc client issues cancel.  This would be either from a manual invocation, or from the query timeout setting.

-the admin interface is used to cancel the query/session

-engine is shutting down

It looks like some more logging would be good to differentiate these events, but at least we know that this is not from shutdown correct?

We need to determine if it's your client or an admin client that is issuing the cancel.  You should be able to see org.teiid.jdbc context warnings if this is related to timeouts.

[mtawk]

We are using Teiid Embedded.

I confirm that the engine is not shutting down and no manual cancellation is done.

Also I guess this is not related to timeout since the cancel is happening randomly and mainly during peak network bandwidth.

As for Warn logs, i confirm that nothing is written in all logs.

Could it help to set log level to All to any other log then DQP?

[shawkins]

> Could it help to set log level to All to any other log then DQP?

The client code will log the timeout initiating the cancel here - teiid/StatementImpl.java at 131a8d589339dee6d8ef2d40c0ff7223bd4f0ed2 · teiid/teiid · GitHub

That should show up as a warning.

Otherwise any other invocation of the DQP cancel will be from a manual intervention - are you utilizing a framework of some kind on the client side that may be issuing a cancel?