cancelling statement due to user request getting an error in thingworx

[Ru]

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.

[VladimirRosu]

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

[Ru]

Hi @VladimirRosu ,

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

[VladimirRosu]

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

[Ru]

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.

[VladimirRosu]

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.

[Ru]

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

[nmutter]

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.

[VladimirRosu]

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