MySQL Database Locked

I have seen reference to this issue with a remote SQL database but this is a simple local MySQL set up.
Client contacted me that he was seeing a “Red Screen” when accessing a historical “Power Chart”

When I Iooked at the Gateway the error logs shows:

java.lang.Exception: Error running query:
TagHistory(paths=[histprov:default:/prov:default:/tag:Aseptic Barriers/UB-6/TT2, histprov:default:/prov:default:/tag:Aseptic Barriers/UB-6/TT3, histprov:default:/prov:default:/tag:Aseptic Barriers/UB-6/TT4, histprov:default:/prov:default:/tag:Aseptic Barriers/UB-6/TT5], start=Sat Sep 03 07:02:30 CDT 2022, end=Sat Sep 03 19:02:30 CDT 2022, flags=0)@5000ms
On: UltrBarriers UB6.Root Container.Easy Chart
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:311)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: [SQLITE_BUSY] The database file is locked (database is locked)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runTagHistoryQuery(GatewayInterface.java:780)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runTagHistoryQuery(GatewayInterface.java:748)
at com.inductiveautomation.factorypmi.application.gateway.GwiTagHistoryProvider.query(GwiTagHistoryProvider.java:16)
at com.inductiveautomation.ignition.common.sqltags.history.cache.StandardCacheBucket.loadData(StandardCacheBucket.java:232)
at com.inductiveautomation.ignition.common.sqltags.history.cache.StandardCacheBucket.getData(StandardCacheBucket.java:192)
at com.inductiveautomation.ignition.common.sqltags.history.cache.SubCache$BucketHit.getData(SubCache.java:215)
at com.inductiveautomation.ignition.common.sqltags.history.cache.SubCache.getData(SubCache.java:158)
at com.inductiveautomation.ignition.common.sqltags.history.cache.TagHistoryCache.query(TagHistoryCache.java:273)
at com.inductiveautomation.factorypmi.application.gateway.TagHistoryQuery.execute(TagHistoryQuery.java:34)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:668)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:308)
… 1 more
Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.execute(DB.java:822)
at org.sqlite.jdbc3.JDBC3PreparedStatement.executeQuery(JDBC3PreparedStatement.java:80)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeQuery(SRConnectionWrapper.java:973)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:167)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepLimitQuery(SRConnectionWrapper.java:212)
at com.inductiveautomation.gateway.tags.history.query.DatasourceQueryExecutor.runSpecialValueQuery(DatasourceQueryExecutor.java:863)
at com.inductiveautomation.gateway.tags.history.query.DatasourceQueryExecutor.readCompletionValues(DatasourceQueryExecutor.java:736)
at com.inductiveautomation.gateway.tags.history.query.DatasourceQueryExecutor.primeRead(DatasourceQueryExecutor.java:904)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.AbstractHistoryLoader.startReading(AbstractHistoryLoader.java:116)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.AggregateHistoryQueryExecutor.startReading(AggregateHistoryQueryExecutor.java:85)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.readData(HistoryWriter.java:313)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.execute(HistoryWriter.java:232)
at com.inductiveautomation.ignition.gateway.sqltags.history.TagHistoryManagerImpl.queryHistory(TagHistoryManagerImpl.java:559)
at com.inductiveautomation.ignition.gateway.tags.model.ProjectDefaultTagManagerFacade.queryHistory(ProjectDefaultTagManagerFacade.java:445)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.QueryTagHistory.invoke(QueryTagHistory.java:99)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:431)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
at java.lang.Thread.run(null)

Ignition v8.1.16 (b2022040511)
Java: Azul Systems, Inc. 11.0.14.1

SQLite is a single-threaded database. It is only suitable for light-duty applications, like configuration data or extremely light history loads.

Use a real database.

Hmmm. This is Ignition’s configuration database that is locked, not your history. Look for log entries about something holding the internal DB open for more than 30 seconds. That will be the real culprit.

Thanks…I’ll see what I can find in there…

There are many of these errors:

MemoryForwardTransaction 07Sep2022 17:31:26 Error forwarding data
org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

at org.sqlite.core.DB.newSQLException(DB.java:909)

at org.sqlite.core.DB.newSQLException(DB.java:921)

at org.sqlite.core.DB.throwex(DB.java:886)

at org.sqlite.core.DB.exec(DB.java:155)

at org.sqlite.jdbc3.JDBC3Connection.commit(JDBC3Connection.java:174)

at org.apache.commons.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:367)

at org.apache.commons.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:367)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.commit(SRConnectionWrapper.java:347)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:190)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:156)

at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:180)

Any hints on finding specific log entries would be a great help.. I don't see any reference to either the "internal Database" or "more than 30 seconds"

The stack traces indicate history storage, but contrary to this post title they also indicate the target DB is SQLite and not MySQL.

@Keith_Magill I suggest that you give support a call and let them get you sorted out.

1 Like

Kevin,

Thanks. I will call in the morning.

Something’s odd.

Ok. Mystery solved.
Issue was due to the configuration of multiple SQLITE databases with the same URLs.
When you create new databases based on SQLITE, the configuration defaults to the same URL each time. This is not allowed with SQLITE. I had to edit the second occurrence to use a different path.
It would be nice if the configuration would automatically concatenate the new database name or automatically index the file name into the URL path so this could not occur…

2 Likes

What are you using SQLite for?

I struck this out above, but it sounds like it is applicable:

Really. ):

1 Like

Phil,

Well now that statement makes sense. I am not a huge SQL user other than with Ignition. I read “single-threaded” but I just figured it meant “slow”.
Some of us just need to learn the “hard way”.

System is being migrated to a full SQL server system as soon as the equipment “arrives” .

Is there a way to import the SQLITE data into real SQL??

Some of us just need to learn the “hard way”.

Nothing wrong with that, I kind of prefer it as the lesson's stick much better.

You can transfer from SQLite (and other SQL Databases) to SQL Server provided you get the right driver. Here's a short walkthrough listing the steps (bottom of the article) https://hevodata.com/learn/sqlite-to-sql-server/

If you don't have many tables and no live data, it might be easier to just recreate the tables in sql server with new create statements.

2 Likes

What Brian said.

In the future, if you need a free database product that can handle real workloads, install PostgreSQL (my fave) or MariaDB.

2 Likes

Yea it should be noted that SQL Server, in line with Microsofts general philosophy, tries to lock you in.

A simple example is that every sane SQL Database lets you limit a query with
SELECT * FROM someTable LIMIT 10 to limit it to 10 rows with a LIMIT clause at the end.

SQL Server decided to get the same behavior to do SELECT TOP 10 * FROM someTable. You may think they just made a mistake with their syntaxing, but the real reason (imo, I don’t know if this is confirmed or just my tin foil hat) is if you have a working application in SQL Server with all their specific syntax around, now converting it to any other database is a much bigger hassle so you’re more likely to just stick with SQL Server.

PostGres, MariaDB and MySQL do not try to lock you in, at least not in such an obvious and hard to work around manner.

Another option to consider, that I use in my own office, is to install and run MS SQL Server for Linux with the development license selected. No programmatic limits, but do strictly obey the development-only rules. The win is that it can make proper MS DB backups (v2019 at the moment) that can be loaded into the future production system. (And vice versa, which is what I mostly do.)

Microsoft maintains an Ubuntu-compatible repo that makes its installation astonishingly easy, along with a variety of command line tools. Cudos to Microsoft for this. (Gah! It burns!)

4 Likes

Phil, is everything ok?

3 Likes

My tongue is scorched. :crazy_face:

4 Likes

Phil,
Thanks for all of your help.
I will look into PostgreSQL..

MariaDB is more user friendly, for those starting out, in my opinion

3 Likes

I didn't think those two things together was actually possible. :laughing:

They also offer container images for SQL Server on Linux, pretty handy as well: https://hub.docker.com/_/microsoft-mssql-server

3 Likes