System.Data.SQLite

i'm having this problem, and i'm not sure if it's because of a locked file or not.

here's the basic code:

public void Append(object entry) {
    _queue.Enqueue(entry);
}
private void WriteThread() {
    while (_running) {
      using (DbTransaction t = _c.BeginTransaction()) {
        while (_queue.Count > 0) {
          object e = _queue.Dequeue();
          SetCachedInsertCommand(e);
          _cmdinsert.Transaction = t;
          _cmdinsert.ExecuteNonQuery();
        }
        t.Commit();
        Thread.Sleep(5000);
      }
    }
}

ok, so that's the basic code, pretty simple.  my program appends to the log, and every 5 seconds the write thread dumps everything in the queue into a transaction using a cached insert command, and commits it to the database.

the customer is having the error described in this thread, but i'm unable to reproduce it.  i've even tried decreasing the sleep to 2ms, and having the program log 2 inputs to the same file.  i'm still unable to reproduce the crash.

any ideas?  thanks.

the journal file is still appearing when i've removed all transactions and inserting them individually.  why is the journal file still appearing?

Because every insert is transacted, whether you use an explicit transaction or not.  This implicit transaction surrounding every insert is what fulfills the "Durable" portion of SQLite's ACID compliance.  This makes sure even if there's a power failure or a critical error during an insert (even one not explicitly transacted) the database is not corrupted.

Robert

 

oic...
i've temporarily "fixed" the problem by removing transactions, and i have a try/catch in the insert, but if there are problems obtaining a file lock that would mean entries are not getting appended and the errors are swallowed, which is not good.

that write thread is the only part of my program that is writing to the database.  are there any ideas you might have that would "stress" the code out to make failures more likely?

thanks.

 Hi,

 I'm getting this message now with the new version (released today, Dec. 5th). I'm trying to execute ~40,000 INSERTS (was an SQL dump of an existing database) and ocasionally one insert produces the error exception.

 It's reproducable because it's everytime when I try to execute those INSERTs, but it's not a specific INSERT-statement where the exception is thrown.

 I can provide the SQL dump for you to test on, if that would be of any help.

 

Best regards,

Stevie 

That'd be helpful -- e-mail to robert at blackcastlesoft dot com

 

Using 1.0.48.0, I'm basically doing what Stevie77 is doing: filling a database table with data. And I'm getting this error with every run - sometimes after a few minutes and sometimes after half an hour!

Having set breakpoints in the code whereever SQLITE_CANTOPEN is returned, I've found this error to occur when SQLite needs to create the journal file. The breakpoint hit is in os_win.c:1151.

Callstack is:
     SQLite.Interop.DLL!winOpen(sqlite3_vfs * pVfs=0x00000000, const char * zName=0x046435e4, sqlite3_file * id=0x04643560, int flags=2069, int * pOutFlags=0x00000000)  Line 1151    C
>    SQLite.Interop.DLL!winOpen(sqlite3_vfs * pVfs=0x10071788, const char * zName=0x046435e4, sqlite3_file * id=0x04643560, int flags=2070, int * pOutFlags=0x00000000)  Line 1149 + 0x1d bytes    C
     SQLite.Interop.DLL!sqlite3OsOpen(sqlite3_vfs * pVfs=0x10071788, const char * zPath=0x046435e4, sqlite3_file * pFile=0x04643560, int flags=2070, int * pFlagsOut=0x00000000)  Line 134 + 0x1c bytes    C
     SQLite.Interop.DLL!pager_open_journal(Pager * pPager=0x04643458)  Line 3566 + 0x1d bytes    C
     SQLite.Interop.DLL!sqlite3PagerBegin(PgHdr * pPg=0x0464aef8, int exFlag=0)  Line 3661 + 0x9 bytes    C
     SQLite.Interop.DLL!sqlite3BtreeBeginTrans(Btree * p=0x04643208, int wrflag=1)  Line 1862 + 0x19 bytes    C
     SQLite.Interop.DLL!sqlite3VdbeExec(Vdbe * p=0x08b8bd98)  Line 2534 + 0x13 bytes    C
     SQLite.Interop.DLL!sqlite3Step(Vdbe * p=0x08b8bd98)  Line 330 + 0x9 bytes    C
     SQLite.Interop.DLL!sqlite3_step(sqlite3_stmt * pStmt=0x08b8bd98)  Line 397 + 0x9 bytes    C
     SQLite.Interop.DLL!sqlite3_step_interop(sqlite3_stmt * stmt=0x08b8bd98)  Line 454 + 0x9 bytes    C
     [External Code]  
     System.Data.SQLite.dll!System.Data.SQLite.SQLite3.Step(System.Data.SQLite.SQLiteStatement stmt = {System.Data.SQLite.SQLiteStatement}) Line 121 + 0x13 bytes    C#
     System.Data.SQLite.dll!System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() Line 571 + 0x16 bytes    C#
    <snip...>

Added a breakpoint where winOpen is called from winOpen (oswin.c:1149), and had another go.

This time, hitting the breakpoint at 1149, I found the last error to be 5:Access is denied.

Just for the fun of it, I did another execution of CreateFileW. This time it succeded.

So, the $10000 question is: why the occationally 'access denied error'?

I'm running the app using the default data directory, i.e. where the exe resides

Any clues, anyone?


Be the way, if you wonder... 'os_win.c.... that's not included in the 1.0.48.0 sources'... you are right. Apparently, one is not allowed to set breakpoints in a source file being included by another source file. That's exactly the case with the amalgamation source (stupid debugger), so I had to bring in and compile the complete SQLite source tree. I found it necessary to move some structs from c to h, because otherwise crypt.c could not find them.

I think it would be a clever approach to switch over to compile the source tree instead of the amalgamation, allowing for deep debugging.
 

Is it possible that your directory is full?  Alternateively, I wonder if there's a multi-threading race condition in SQLite when multiple threads try and clean up/create the journal file...

Is there a virus scanner on the machine in question?  It could be that the journal file is hanging around after it gets deleted due to the virus scanner kicking on it ...

 When you get the AccessDenied error ... does the journal file exist on disk?

Robert

 

 Hi Robert,

 I've 5 gigs of space left on the drive, so that's not it.

Don't know about the race condition - I'm accessing the db from one thread only. So unless SQLite runs multiple threads on its own....

Yes, I have a virus scanner running. Unfortunately, I can't tell if what you describe applies, as I have no means to check if the file actually exists in the very moment that SQLite tries to create the journal file, because the debugger takes a few secs to notify me of the breakpoint hit... 

But the journal file definitely does not exists when I check, after the breakpoint hit. 

Right now, I'm trying a different approach. Create the db in memory, then move it to disk. That's times 28 faster. Don't know if this will make the issue obsolete, though. I've yet to try it in full scale.

Jacob

 

 

 

Before you implement a workaround ... try disabling the virus scanner and rerunning it ... tell me if that makes the issue go away. 

If it does, also let me know what virus scanner you are using.

Robert

 

Well, the in-memory approach is needed never the less, to speed up things - I can't really expect the user to sit and wait for an hour or so for the db creation to finish.

I've already moved on to that approach, but of course I'll try and run the initial approach without Kaspersky AV6 running. I'll let you know of the result tomorrow.

Jacob

 

 

 It appears turning of Kaspersky did the trick.

 Jacob

Hi,

i also get this exception running version 1.0.48.0 on Windows Vista. I have a multithreaded application with multiple connections to the db. Each thread has its own connection and executes scalars/queries/non queries inside of the lock(connection) block without explicit transactions defined. When this exception occurs i have journal file on disk and i cannot access the database in server explorer view of VS (database locked). I have noticed, that at the moment of exception other threads (5 of them) are trying to execute scalars/non queries/readers but it should be ok because they use their own connection instances. Any ideas why this exception occurs?

Are some of these threads writing and some are reading?  If you can put together a small program that makes this happen, I can run it here under the debugger and see if I can drill down to the core issue.

Robert

 

Thanks for your quick reply Robert,

i have one gui thread that uses SQLiteDataAdapter to fill data table and display it in a data grid. This thread also executes scalar requests with count(*) statement. Other threads execute various sql requests (selects, creating temporary tables, filling it, inserting records etc.). I have created a small program to reproduce this exception using sql logs that my program executes, but it seems that only executing these requests does not trigger this exception. Ill keep trying to reproduce it using data adapter.