# MySQL Database Locked **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [September 6, 2022, 9:15pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805 "2022-09-06T21:15:50Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 6, 2022, 9:15pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/1 "2022-09-06T21:15:50Z")
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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 6, 2022, 9:17pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/2 "2022-09-06T21:17:52Z")
~~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. ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 7, 2022, 9:21pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/3 "2022-09-07T21:21:26Z")
Thanks…I’ll see what I can find in there… ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 7, 2022, 9:30pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/4 "2022-09-07T21:30:38Z")
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) ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 7, 2022, 9:51pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/5 "2022-09-07T21:51:11Z")
> [@pturmel](#): > > holding the internal DB open for more than 30 seconds 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" ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [September 7, 2022, 10:06pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/6 "2022-09-07T22:06:31Z")
> [@pturmel](#): > > 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. 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. ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 8, 2022, 12:46am UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/7 "2022-09-08T00:46:28Z")
Kevin, Thanks. I will call in the morning. Something’s odd. ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 9, 2022, 11:59am UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/8 "2022-09-09T11:59:19Z")
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… ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 9, 2022, 12:06pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/9 "2022-09-09T12:06:47Z")
What are you using SQLite for? I struck this out above, but it sounds like it is applicable: > [@](#): > > 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 Really. ): ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 9, 2022, 1:29pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/10 "2022-09-09T13:29:17Z")
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?? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [September 9, 2022, 1:35pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/11 "2022-09-09T13:35:32Z")
> 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/](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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 9, 2022, 1:47pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/12 "2022-09-09T13:47:26Z")
What Brian said. In the future, if you need a free database product that can handle real workloads, install PostgreSQL (my fave) or MariaDB. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [September 9, 2022, 1:51pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/13 "2022-09-09T13:51:02Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 9, 2022, 1:53pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/14 "2022-09-09T13:53:21Z")
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!) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [September 9, 2022, 2:01pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/15 "2022-09-09T14:01:56Z")
> [@pturmel](#): > > Cudos to Microsoft for this. Phil, is everything ok? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 9, 2022, 2:08pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/16 "2022-09-09T14:08:03Z")
> [@bkarabinchak.psi](#): > > Phil, is everything ok? My tongue is scorched. 🤪 ---
**Author:** ![Keith\_Magill](https://avatars.discourse-cdn.com/v4/letter/k/85e7bf/32.png) [@Keith\_Magill](https://forum.inductiveautomation.com/u/Keith_Magill)\ **Post date:** [September 9, 2022, 4:07pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/17 "2022-09-09T16:07:33Z")
> [@pturmel](#): > > PostgreSQL Phil, Thanks for all of your help. I will look into PostgreSQL.. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 9, 2022, 5:52pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/18 "2022-09-09T17:52:17Z")
> [@Keith\_Magill](#): > > I will look into PostgreSQL… MariaDB is more user friendly, for those starting out, in my opinion ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [September 9, 2022, 6:00pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/19 "2022-09-09T18:00:19Z")
> [@pturmel](#): > > Microsoft...astonishingly easy I didn't think those two things together was actually possible. 😆 ---
**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\ **Post date:** [September 9, 2022, 6:31pm UTC](https://forum.inductiveautomation.com/t/mysql-database-locked/63805/20 "2022-09-09T18:31:17Z")
> [@pturmel](#): > > Microsoft maintains an Ubuntu-compatible repo that makes its installation astonishingly easy, along with a variety of command line tools. They also offer container images for SQL Server on Linux, pretty handy as well: [https://hub.docker.com/\_/microsoft-mssql-server](https://hub.docker.com/_/microsoft-mssql-server)