Error: stepping, unable to open database file (14)
(1) By anonymous on 2021-12-04 23:57:07 [link] [source]
I'm getting this error when doing
SELECT on some views of a database that was working for years until a little after the recent move to v3.37
I don't know if the db was corrupted but now even v3.36 will give the same error.
I haven't touched the schema or the app at all. I only inserted more data as I've doing on a daily basis for years.
select from a couple of views independently I get no errors.
select from a view (and any view based on that or the original views) that is based on the other two views that work independently, I get the above error.
vacuum stopped working giving the same error.
pragma integrity_check shows 'ok'
Any ideas what could be wrong?
(2.1) By Keith Medcalf (kmedcalf) on 2021-12-05 02:17:44 edited from 2.0 in reply to 1 [link] [source]
Have you checked permissions? That is file permissions as controlled and implemented by the Operating System. Unfortunately the CLI does not tell you the extended error code, which might be informative in this situation.
(3) By anonymous on 2021-12-05 13:08:51 in reply to 2.1 [link] [source]
My first thought was something OS related.
And, it must have been as after some time (~20 minutes) the file was back to normal use.
But what made no sense to me is that some views would work and others not. If it were an OS lock wouldn't it be for the whole file and not parts of it?
Or, is it possible it was an OS lock only on some temp file SQLite3 may create during
INSERT which was done right before I noticed the problem and there was an OS delay in the release of the lock? Would this make this behavior plausible?
I've been using SQLite databases for years and this is the first and only time this ever happened. That's why my thought was it must be something related to the new version.
Anyway, problem disappeared for now. I'll report again if it happens.
(4) By Gunter Hick (gunter_hick) on 2021-12-06 07:50:05 in reply to 1 [link] [source]
SQLITE_CANTOPEN is returned mainly in the OS abstraction layer (meaning something with file/directory access permissions is probably broken) and in the Pager and the WAL code. Apart from that the only thing that comes to mind with views is the not so recent limitation that views may only reference tables residing in the same file. E.g. CREATE VIEW a.myview ... AS SELECT ... FROM b.mytable ... is only valid if database b is attached before database a, making database a "unopenable" alone (or after mytable is dropped).
(5) By Dan Kennedy (dan) on 2021-12-06 10:55:18 in reply to 1 [source]
Wherever SQLite is trying to store temp files is not writable. Fix it by tinkering with the environment variables or by configuring SQLite to store temp files in main memory:
(6) By Merijn Bosma (MerijnB) on 2021-12-07 17:17:00 in reply to 5 [link] [source]
Just to chime in on this one, I'm seeing the exact same behavior on two VM's running Windows Server 2019. I have no idea (yet) how to trigger the behavior, but once it 'happens' I can reliable reproduce (see details below). Then at some point (some hourse), the behavior is gone, a little while later (some hours) it's back, etc. It's not user restriction related, I have tried several directories, all showing the same behavior. I've not been able to reproduce since I found this thread, so I haven't tried the suggestions given by Dan yet. I have two tables which look like this: -- Table: Items CREATE TABLE Items ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Status TEXT, LogTime DATETIME, ItemID TEXT ); -- Table: Data CREATE TABLE Data ( DataID REFERENCES Items (ID) ON DELETE CASCADE, Name TEXT, Value TEXT ); When this is occuring, this query doesn't work: SELECT ItemID FROM ( select * FROM Items a INNER JOIN Data g ON a.ID = g.DataID WHERE a.Status in ('Some status') ) r1 GROUP by r1.ID ; It does work however when I change it to: SELECT ID FROM ( select * FROM Items a INNER JOIN Data g ON a.ID = g.DataID WHERE a.Status in ('Some status') ) r1 GROUP by r1.ID ;
(7) By anonymous on 2021-12-13 19:30:26 in reply to 1 [link] [source]
Issue still present (Win7).
After inserting more data the db gets OS locked. The app hasn't changed at all except for being rebuilt with v3.37 SQLite3.
The lock is automatically released after many minutes, or immediately if I Log Off and On.
(8) By anonymous on 2021-12-14 11:21:38 in reply to 7 [link] [source]
(9) By Merijn Bosma (MerijnB) on 2021-12-15 14:38:21 in reply to 6 [link] [source]
What I've figured out now is that the reason this stops working for me (this time) is that TEMP environment variable points to c:\users\<username>\AppData\Local\Temp\1 The number at the end may vary. When this isn't working it is because that subdir (1) in the 'normal' temp dir isn't there (c:\users\<username>\AppData\Local\Temp exists, c:\users\<username>\AppData\Local\Temp\1 does not). When I create the sub dir the error goes away. I have no clue (yet) why this subdir sometimes isn't, and sometimes is there. When I log out and back in again, the environment variable is set to something else (other number in the end) and the directory is there. For other people finding themselves in this situation, can you please check if this is the same for you?
(10) By anonymous on 2021-12-15 15:19:03 in reply to 9 [link] [source]
(OP) Not the case for me.
(11) By TripeHound on 2021-12-15 15:33:58 in reply to 9 [link] [source]
I think I've seen that in the past when using Citrix / Windows Terminal Services, and this article picked more-or-less at random from a web-search seems to suggest it can also happen with remote desktops and (personal conjecture) if you make use of multiple desktops. It also mentions a way of disabling this behaviour, but I have no knowledge of what, if any, problems that might cause.
(12) By Merijn Bosma (MerijnB) on 2021-12-15 22:11:10 in reply to 1 [link] [source]
Thanks TripeHound, you led me into the right direction.
I think I have found the cause of the trouble I'm seeing, it seems to be a combination of