SQLite error (7): failed to HeapReAlloc 465625658 bytes (8), heap=7A20000
(1) By Boston (lannetscan) on 2021-06-14 08:27:30 [source]
Hi everyone, I'm facing a problem when inserting rows into a newly created database. In the failing process a conversion from Oracle to SQLite is made. Looping through around 260k of Oracle rows, sqlite throws an exception on insert, close to the 260k mark: SQLite error (7): failed to HeapReAlloc 465625658 bytes (8), heap=7A20000 Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt) at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() The process stopped working when a change was introduced to the target sqlite table: the table is now virtual and uses the FTS v4 module. Some more details: - code is written in C#, using System.Data.SQLite.Core nuget package (latest - 126.96.36.199 was tested as well) - entire process is wrapped in a transaction - one of the source column is an XML containing Thai script strings What I've tried: - splitting the process into smaller transactions (10k, 1k) - disposing and creating a new SQLiteCommand and SQLiteConnection per transaction - running SQLiteConnection.ReleaseMemory() - closing and openning a new connection to sqlite db with each new transaction start (also running SQLiteConnection.Shutdown()) I'm really running out of ideas here. Any help is appreciated.
(2.1) By Scott Robison (casaderobison) on 2021-06-14 13:23:35 edited from 2.0 in reply to 1 [link] [source]
I had a program some years ago that did a very similar pattern, and the problem is that that version of FTS needs to create some really large blobs when merging FTS nodes. I don't remember all the details, but my solution at the time was to hand shard the data to keep the size of FTS tables smaller, then run multiple queries to gather the same data.
http://sqlite.1065341.n5.nabble.com/Segfault-during-FTS-index-creation-from-huge-data-td81465.html seems to be a link to a six year old thread on the same or very similar / adjacent topic.
(3) By Boston (lannetscan) on 2021-06-29 18:14:27 in reply to 1 [link] [source]
I never found a real cause for these exceptions. I am trying to apply workaround for each and every one though. Maybe it is helpful for someone - things that can help: - when inserting data, make sure it's sorted first (by ID column in my case) - try to reduce number of queries (seems obvious, but not always is) - when dealing with large column contents (like huge xml) try to retrieve the data row by row instead of an entire batch
I'm still amazed how many problems with memory dealing with virtual table brings here.
PS. I found out that most (or maybe even all) of the memory problems disappear when updating to FTS5.