topic Re: cancelling statement due to user request getting an error in thingworx in ThingWorx Developers

cancelling statement due to user request getting an error in thingworx

Ru — Fri, 28 Apr 2023 04:28:10 GMT

Hi,

i am trying to execute postgres database store procedure in thingworx but unable to get the data, it is showing an error in database logs like "cancelling statement due to user request", but if i am trying to execute the same store procedure in postgres it is giving me the data. i checked some thingworx articles and there they are saying to refresh the database connection or increase the database thing time limit, i tried these two approaches also but not getting the data. Please give me the solution on this as soon as possible.

Re: cancelling statement due to user request getting an error in thingworx

VladimirRosu — Fri, 28 Apr 2023 05:57:57 GMT

What exactly is the average execution time of that stored procedure?

Re: cancelling statement due to user request getting an error in thingworx

Ru — Fri, 28 Apr 2023 06:29:36 GMT

Hi @VladimirRosu ,

the avg time to execute the store procedure is 30 sec to 60 sec.

Re: cancelling statement due to user request getting an error in thingworx

VladimirRosu — Fri, 28 Apr 2023 07:12:28 GMT

1. First, I presume you modified the database Thing timeout already right? What value did you use there?

2. Second, the Database Thing removes by default any existing connections that spend more than 60 seconds without receiving any data. This means any query that spends >= 60 seconds on the SQL server side, without sending data to ThingWorx. If you are curious how it's doing that, it is by using the standard JDBC parameter named "removeAbandoned".This parameter is set to "true", and also its value is 60 seconds, so if you set a big timeout, but the connection does not receive data because the SQL server spends time to process data internally, this will kick in. Typically, such a big time to process data is seen as way too high, and could overload the server fast, that's why this parameter is set to true.

3. However, in 9.3.3 we introduced a new way to interact with SQL databases, that is using the SQLThing etc (not DatabaseThing). Info here.

Most important for you, this new interaction point does not enable the flag above, effectively removing that limitation. That being said, with great power comes great responsibility, so I would really optimize first that stored procedure, just in case it can be called too many times.

I would suggest overall to switch to the new 9.3.3 SQL Thing way of interacting with the DB - if this is really the core issue you experience.

Please let us know the results of your test

Re: cancelling statement due to user request getting an error in thingworx

Ru — Tue, 02 May 2023 12:43:20 GMT

yes i already modify database thing timeout already please check below Screenshot

also i tried to add scripttimeout on platform-settings.json but unable to execute the script like below

"BasicSettings": {
            ...
            "ScriptTimeout": 600
}

please let me know how i can resolve this issue.

Re: cancelling statement due to user request getting an error in thingworx

VladimirRosu — Tue, 02 May 2023 12:49:03 GMT

Hi @Ru ,

Could you please confirm if you tried using the SQL Thing approach I described? Or you are still using the Database Thing?

It's not clear from your reply if you implemented the changes I suggested.

Re: cancelling statement due to user request getting an error in thingworx

nmutter — Tue, 02 May 2023 17:07:17 GMT

Maybe also check the Docs -> Persistence Provider Settings -> PostgresPersistenceProviderPackage settings. E.g. "Query Timeout" - "The amount of time (in milliseconds) a query will wait to complete before it is cancelled." But by default it seems to be 600seconds. You should see the setting also in ther Configuration of Peristence Provider.

I would assume there is some error in some TWX logs when the query is cancelled? Is there anything in Application/Communication/Script..Log? Maybe it tells what the issue is.

Re: cancelling statement due to user request getting an error in thingworx

VladimirRosu — Tue, 02 May 2023 17:55:13 GMT

That setting would not apply to queries done through the Database Thing. It's valid only for queries the platform generates directly (QueryPropertyHistory etc.).

Re: cancelling statement due to user request getting an error in thingworx

Ru — Wed, 03 May 2023 08:21:13 GMT

Hi @VladimirRosu , i didn't try the approach you suggested.

Re: cancelling statement due to user request getting an error in thingworx

nmutter — Thu, 22 May 2025 08:48:02 GMT

I did use the SQLThing but still got the cancelling due to user request error. Support helped me and informed that there is a timeout I can set myself on my service/query (default 60 seconds): Article reference https://www.ptc.com/en/support/article/CS345780