SQLite database locked

Ignition
, , ,
1

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 :

Longest Recent Queries
Longest Recent Queries1420Ɨ600 80.5 KB

DB Connections

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

DB Connections count
DB Connections count1320Ɨ417 19 KB

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!

2

Hi Alexi, I have the same issue.

If someone could help, it would be very appericated

Martin.

1 Like
3

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?

1 Like
4

Hi Alexi and Martin, (your names sound familiar :wink: )

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.

1 Like
5

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

6

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 :sweat_smile:

7

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.

2 Likes
8

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.

2 Likes