One of our systems suddenly started to play up after upgrading to 3.9.2
from 3.8.8 (so I am not sure exactly when the oddity was introduced).
SQLite:
v 3.9.2 linked on WIndows (various versions) in a 32 bit application via
the exact 32bit DLL published on the downloads page on sqlite.org.
The error:
During an update to an attached DB, the statement fails and reports
"Disk I/O Error" and leaves a hot journal (even though the statement
wasn't in an explicit transaction).
It's unfortunately not something I can make an SQL script for or send
code since it has to do with the creating of connections and the code is
quite involved and will cause more confusion than answers (The same SQL
succeeds on other connections), but I can lay out the exact steps
causing it:
The steps:
We create 2 different connections at program start-up, the main data DB
and a cache DB.
I will refer to them as conMain and conCache.
They are initialized as follows:
conCache:
PRAGMA journal_mode = TRUNCATE;
PRAGMA cache_size = 8000; -- 8K Pages cached
PRAGMA temp_store = 2; -- Memory
PRAGMA synchronous = 0; -- More Speed
conMain:
PRAGMA journal_mode = DELETE; -- Normal Mode
PRAGMA cache_size = 16000; -- 16K Pages cached
PRAGMA synchronous = 0; -- Speed Mode
PRAGMA soft_heap_limit = 0; -- No lim
PRAGMA temp_store = 2; -- Memory
(yes, these do not need to be very secure, they are copies of the real
data, but they are updated internally in the short term)
We then also attach the cache DB file to the main DB as "TmpDB" with the
usual SQL:
conMain.Execute("ATTACH 'd:\path\to\cache_db_file.db' AS TmpDB;");
Now, essentially 2 connections exist to the cache DB file, one direct
connection set to TRUNCATE journaling, and one as attachment to the main
DB connection with DELETE journaling (I mention this because my theory
is that this may cause the problem).
Then, upon user request, 2 tables are created (or dropped and recreated)
in the cache DB using the conCache connection object, and then they are
populated with data (without incident).
Some data for the second table depends on data in the main DB, so we
calculate and store it in a TEMP table (here called "local") using
conMain. We then add it to the second of those previously created cache
DB tables via an UPDATE query on the conMain connection to the attached
cache db as "TmpDB".
To be clear, this is the format of the query:
s = 'UPDATE TmpDB.Tbl2 SET
TmpDB.Tbl2.aa = (SELECT(local.a) FROM local WHERE local.id =
TmpDB.Tbl2.id);
TmpDB.Tbl2.bb = (SELECT(local.b) FROM local WHERE local.id =
TmpDB.Tbl2.id);
TmpDB.Tbl2.cc = (SELECT(local.c) FROM local WHERE local.id =
TmpDB.Tbl2.id);
// etc.
';
conMain.Execute(s); // Not explicitly transacted, and this simply
wraps the normal sqlite3_prepareV2() .. step() .. reset. chain.
This all worked perfectly in 3.8.8, but dropping in the 3.9.2 DLL, this
last UPDATE statement fails with "Disk I/O error" (which is just the
error translator's answer, I think sqlite_step() returns error code
2570) and leaves the hot journal (with about 360 kbytes in) on the CACHE
DB.
Operation can resume normally after it - apart from the fact that the
updates did not happen, everything else works without incident and the
data is fine.
If I change the conCache journal mode to DELETE in stead of TRUNCATE, it
works again - and that is indeed what I have done which solves my
problem, so the fix isn't urgent or even required. (There might even be
documentation stating not to mix journal modes like this, but I am
unaware of it).
Thanks,
Ryan
