search

OpenInformix / IfxPy

Informix native Python driver for scalable enterprise and IoT solutions.
Other
46 stars 22 forks source link

Connection timeout #39

Closed flakobatako closed 4 years ago

flakobatako commented 4 years ago

I have a long time running SP call, the problem is that because it can take up to a couple of minutse, the connection gets lost somewhere in the middle.

I'm getting the following error saying that the transaction got aborted. Fetch Failure: [Informix][Informix ODBC Driver][Informix]Long transaction aborted. SQLCODE=-458

Normally in other divers you can specify a timeout in the connection string so there won't be timeouts.

Is it possibly to do the same with IfxPy?

jsagrera commented 4 years ago

Timeout in the connection string is usually just for the connection. The only timeout that can be a factor during a SQL execution is the SQL_ATTR_QUERY_TIMEOUT (Statement timeout) which from ODBC can be set with a call to SQLSetStmtAttr() call, but there is no option to set it up from IfxPy. I'm pretty sure the default with ODBC is 0 (no TIMEOUT) so a SQL execution will wait forever (or until someone aborts the execution)

If the execution failed with a -458, the abort was not caused by a timeout, but due a long transaction. You need to check why on the engine side, I suspect your SP is doing a lot of work that is filling up the logs. You won't be able to solve that on the client side (IfxPy/ODBC).

flakobatako commented 4 years ago

Thanks.

I did some research, and you are right.

I'm running an ETL, that inserts like 90000 + rows, but I empty the destination table in the begining of the SP execution, so i really don't need a transaction in this case. I activated the AUTOCOMMIT option and the problem was resolved.

Thank you very much.