SQLite database locked

Alexi_Andrzejewski · July 15, 2024, 8:46am

Hi all,

I have a perspective project using a SQLite DB for storing sensors infos and historian running on my gateway.

However, from time to time the DB juste refuse to operate any changes and only SELECT querys are accepted.

Here are the details of the logs :

Logs summary

From the logs I could get the following error when running this from a web browser.

11. system.db.runNamedQuery("sensorActions/maintenanceMode", {"code":payload["num_capteur"]})

Calling this named query :

UPDATE Capteurs SET mode = "m"
WHERE code = :code AND mode = 'a'

On the following Capteurs table :

code	temp	press	hygro	room	spot	mode
sens_0	21.5	32.6	-29.6	PA6.00001	spot 0	a
sens_1	19.4	31.3	-30.6	PA6.00004	spot 1	a
sens_2	17.5	30.8	-28.0	PA6.00004	spot 2	a
sens_3	51.7	30.1	-29.7	PA6.00004	spot 3	a
sens_4	19.6	31.3	-69.6	PA6.00004		a
sens_5	20.2	32.3	-30.7	PA6.00004		a
sens_6	21.3	30.0	-28.6	PA5.00005		a
sens_7	23.5	23.5	-29.7	PA5.00005		a
sens_8	20.4	55.7	-44.1	PA6.00004		a
sens_9	61.4	29.5	-26.0	PA6.00004		a

I don't think my error comes from the request though because half-way writing this Topic, my request have finaly worked for some reasons.

INFO   | jvm 1    | 2024/07/15 09:12:56 | W [c.i.p.ComponentModel          ] [09:12:56.636]: Error running sensor/tableau_labos@C/root/Table.onMessageReceived(self, payload): Traceback (most recent call last):
INFO   | jvm 1    | 2024/07/15 09:12:56 |   File "<function:onMessageReceived>", line 11, in onMessageReceived
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | java.lang.Exception: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
INFO   | jvm 1    | 2024/07/15 09:12:56 |  
INFO   | jvm 1    | 2024/07/15 09:12:56 | com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):
INFO   | jvm 1    | 2024/07/15 09:12:56 |   File "<function:onMessageReceived>", line 11, in onMessageReceived
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | java.lang.Exception: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.Py.JavaError(Py.java:545)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.Py.JavaError(Py.java:536)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyObject.__call__(PyObject.java:477)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyObject.__call__(PyObject.java:481)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.pycode._pyx699.onMessageReceived$1(<function:onMessageReceived>:21)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.pycode._pyx699.call_function(<function:onMessageReceived>)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyTableCode.call(PyTableCode.java:173)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyFunction.function___call__(PyFunction.java:474)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyFunction.__call__(PyFunction.java:469)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyFunction.__call__(PyFunction.java:464)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:847)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:829)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:868)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1010)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:950)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:161)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:98)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.perspective.gateway.model.MessageHandlerCollection$MessageHandlerImpl$1.lambda$invoke$0(MessageHandlerCollection.java:86)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.lang.Thread.run(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	... 26 common frames omitted
INFO   | jvm 1    | 2024/07/15 09:12:56 | Caused by: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	... 23 common frames omitted
INFO   | jvm 1    | 2024/07/15 09:12:56 | Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.core.DB.newSQLException(DB.java:1179)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.core.DB.newSQLException(DB.java:1190)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.core.DB.execute(DB.java:985)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.core.DB.executeUpdate(DB.java:1054)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.jdbc3.JDBC3PreparedStatement.lambda$executeLargeUpdate$2(JDBC3PreparedStatement.java:119)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.jdbc3.JDBC3PreparedStatement.executeLargeUpdate(JDBC3PreparedStatement.java:118)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:100)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:981)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:181)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.runPrepQuery(NamedQueryExecutor.java:507)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:399)
INFO   | jvm 1    | 2024/07/15 09:12:56 | 	... 31 common frames omitted

File locked

When I searched which process was locking my file.db i found it was opened by javaw.exe (and not from DB Browser SQLite which already happend to me lol and would have locked the file).

I understand SQLite use a single-threaded query method.
Therefore I guess I may have a query that is either taking ages or beeing executed a lot in a short time.

This is what i get by checking
Status> Connections> Databases> Details :

Before the red line is when I had issues after is when I don't anymore :

DB Connections

I also noticed the amount of Database connections was 1 when having my errors and now it has changed to 0.

Maybe is there a way to check who exactly is using a connection for debugging purposes ?

My database connection connect URL is jdbc:sqlite:${data}/db/laboratoires.db
So it's in localhost.

Any suggestions for how to resolve this issue would be greatly appreciated.

Thank you!

Martin_Chauveliere · July 15, 2024, 9:24am

Hi Alexi, I have the same issue.

If someone could help, it would be very appericated

Martin.

Hayden_Watson · July 15, 2024, 9:42am

You may want to configure the max connections to 1 in this case as SQLite only allows 1 connection at any time.

Is there any other program trying to connect to the database maybe, that is taking its only available connection?

Samuel_Sueur · July 15, 2024, 9:42am

Hi Alexi and Martin, (your names sound familiar )

Perhaps try to limit the number of connections to the DB by following this :

Go to your DB connection in the "Config" Tab, then scroll at the bottom and tick "Advanced Properties", then change the "Max Active" field in the Connection Pooling to 1 ? Do the same for Max Idle

Not sure if that's gonna solve your issue.

Alexi_Andrzejewski · July 15, 2024, 9:59am

This is what i thought at first but only Ignition seem to be editing the *.db file

Alexi_Andrzejewski · July 15, 2024, 10:06am

Hey Samuel,
Tanks for showing the location,
Unfortunately, I found out setting the max connection to 1 disable the ability to have both designer and web browser displaying the data fetched in my DB,
My bad

Samuel_Sueur · July 15, 2024, 10:42am

Honestly, I know what you're working on as I'm doing the same thing ahah

You should really move to a POSTGRESQL database, for tests you can set it up on the same machine as you're currently using and also download pgadmin for easy visualisation.

I checked a little, you're also storing history data to the SQLite DB, this is far from ideal as SQLite DB is better suited for storing configuration data only. I suspect performing inserts or updates while also storing history tags might cause issues.

When storing tag history to a DB, consider moving to something better suited, such as postgre (free) or MySQL/SQL Server.

+ if at some point we have to merge our work together, postgresql will be our go-to.

pturmel · July 15, 2024, 12:17pm

This is the problem. SQLite is a single-threaded database designed to store slowly-changing (or never-changing) configuration information. It is utterly unsuited to use as a historian.

You cannot "fix" this.

If you need something free, use PostgreSQL or MariaDB.

Full stop.