[IGN-3829] Error executing system.db.commitTransaction

Ignition
21

I'm not sure what driver the prod server in running, I'll check it out and make mine match - Probably should have done that long ago.

I doubt they'll allow a driver update on prod.

22

Actually I'm a bit confused.
I said 4.2 because of this:

image

But looking in ignition/user-lib/jdbc, I find mssql-jdbc-9.4.0.jre11.jar, so I guess it's 9.4 after all ?

Could upgrading the JDBC change anything about my issue ?

23

4.2 is the version of the JDBC specification the driver supports.
9.4.0 is most likely the version of the driver, but a filename is not a guarantee that's actually the version you have.

24

I think you need to return ret after closing transaction, not after committing. Seems like for a set of good queries without error, you commit the transaction but then return the result of your function, and I don't think your closeTransaction in your finally ever executes. I don't think that's necessarily your issue here, but I think it could become one.

25

The whole point of a finally: clause is to ensure it executes no matter what goes on in the try: block. So, no, that isn't it.

26

The close call is in a finally, it will be called whether the function reaches a return or not.

27

I had a similar problem when I was trying to set up an all or nothing write to our DB. What fixed it for me was the first query in the transaction (after getting the transaction ID) needed to have the project argument filled. Subsequent named query calls do not need that parameter filled.

This worked:

txID = system.db.beginNamedQueryTransaction(project="project1", database="database1")
try:
    system.db.runNamedQuery(project="project1", path="query1", params=params1, tx=txID)
    #do some stuff

    system.db.runNamedQuery(path="query2", params=params2, tx=txID)
    # do more stuff
    system.db.commitTransaction(txID)

except:
    system.db.rollbackTransaction(txID)

finally:
    system.db.closeTransaction(txID)

but this would not work, for whatever reason:

txID = system.db.beginNamedQueryTransaction(project="project1", database="database1")

try:
    system.db.runNamedQuery(path="query1", params=params1, tx=txID)
    #do some stuff

    system.db.runNamedQuery(path="query2", params=params2, tx=txID)
    # do more stuff
    system.db.commitTransaction(txID)

except:
    system.db.rollbackTransaction(txID)

finally:
    system.db.closeTransaction(txID)
2 Likes
28

Is this STILL a bug???
I'm on 8.1.37 and getting apparently similar behaviour.

I CAN'T add project argument as mentioned above, because as far as I can tell if its not called by gateway scope it won't like that..
Which is why i have this sort of thing everywhere:

	# system.db.runNamedQuery requires project name if called from Gateway Scope. 
	scope = shared.util.getScope()	# 0 = Gateway scope, > 0 = Client scope	

	if scope > 0:
		txId = system.db.beginNamedQueryTransaction(iolistDB, isolationLevel, timeout)
	else:
		txId = system.db.beginNamedQueryTransaction("G1-Master", iolistDB, isolationLevel, timeout)

I am also finding that the INSERT queries are in fact being committed, and i can see the new rows show up in the database, but i still get the following error:

19:31:31.892 [Thread-76] ERROR Tamaki IO Checking - updateIOCheck_new - caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Transaction "331efe66-a98f-4c72-a617-d41ba830c214" is closed.
caused by: java.sql.SQLException: Transaction "331efe66-a98f-4c72-a617-d41ba830c214" is closed.
19:31:31.906 [Thread-76] ERROR ScriptLogger - updateIOCheck_new - caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Transaction "331efe66-a98f-4c72-a617-d41ba830c214" is closed.
caused by: java.sql.SQLException: Transaction "331efe66-a98f-4c72-a617-d41ba830c214" is closed.
Error: updateIOCheck_new - caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Transaction "331efe66-a98f-4c72-a617-d41ba830c214" is closed.
caused by: java.sql.SQLException: Transaction "331efe66-a98f-4c72-a617-d41ba830c214" is closed.

Pretty frustrating. Going to go home before and sleep on it. Hopefully someone can pitch in if they've managed to find a fix in the last year.