RE: [sqlite] Multiple connections - stale cache?
My final post on this issue...vxWorks has confirmed a bug in their fstat() implementation. I'm hoping for a quick resolution. Thanks for the help. Mark Just a follow-up for the group... Richard was able to determine that my platform is having trouble with an fstat() call to get the size of files on my file system. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Just a follow-up for the group... Richard was able to determine that my platform is having trouble with an fstat() call to get the size of files on my file system. Specifically, the call is returning a size of 0 when it should be 11K. I am investigating the vxWorks platform and have found that fstat() behaves correctly when I step through my code via a debugger but not when I run it without the debugger. This behavior is very similar to other problems we were seeing in the past where code seemed to work while stepping through the debugger but not when running straight out. I have called in vxWorks support, and I'll keep the group posted of the results. Thanks so much! Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Hi- No, I am not confident that my file locking code is working. For vxWorks, I needed to alter the .lock functionality, as it wouldn't compile. Pretty much changes such as calling stat() instead of lstat(), etc. However, for this example, I do know that the two threads operate serially (ie, it is never the case that more than one SQL operation will be happening at a time). I have some more diagnostic information. I'm now showing the database connection and the SQL *about* to be executed. I have tried to trim the output to only show relative data, as well as changing the actual SQL table/column names for privacy. Here is the diagnostic output at the point the counter value was unexpected: REPEAT TEST 1 - DBC: 10944678 DB Counter: -61 SQL:BEGIN; DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table1 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table2 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table3 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table4 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table5 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table6 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table7 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table8 DBC: 10944678 DB Counter: -61 SQL:COMMIT; DBC: 10944678 DB Counter: -68 SQL:INSERT INTO Table4(Col1, Col2, Col3) VALUES (1, 1, 32751) Here is some info around counter value -68 when it got to it during the first test (not during the repeat of the test): --- BEGIN TEST 1 - DBC: 10944678 DB Counter: -70 SQL:BEGIN; DBC: 10944678 DB Counter: -70 SQL:UPDATE Table1 SET Col1=1, Col2='Joe' DBC: 10944678 DB Counter: -70 SQL:UPDATE Table2 SET Col1=30, Col2=0 DBC: 10944678 DB Counter: -70 SQL:COMMIT; DBC: 10944678 DB Counter: -69 SQL:SELECT SessionNumber FROM MyTable DBC: 10944678 got sess num = 333000 DBC: 8650883 DB Counter: -69 SQL:SELECT SessionNumber, SessionState FROM MyTable DBC: 8650883 Got session number: 333000 DBC: 8650883 DB Counter: -69 SQL:BEGIN; DBC: 8650883 updating sess info, num = 35 DBC: 8650883 DB Counter: -69 SQL:UPDATE MyTable SET SessionNumber=?, SessionState=? DBC: 8650883 DB Counter: -69 SQL:UPDATE Table1 SET Col1=?, Col2=?, Col3=? DBC: 8650883 DB Counter: -69 SQL:DELETE FROM Table5 WHERE Id=(SELECT Id FROM Table8 WHERE Col4=?) DBC: 8650883 DB Counter: -69 SQL:SELECT Id FROM Table5 ORDER BY SequenceNumber DESC DBC: 8650883 DB Counter: -69 SQL:COMMIT; DBC: 8650883 DB Counter: -68 SQL:UPDATE Table8 SET Col1=? WHERE Col2=? AND Col3=? DBC: 8650883 DB Counter: -67 SQL:UPDATE Table8 SET Col1=? WHERE Col2=? AND Col3=? If you need the exact SQL statements without trimmed output, just let me know, and I can send it to you privately. Thanks for your help, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Multiple connections - stale cache?
Hi- We have a scenario where we have two different database connections to the same database. Each database connection is running on a separate thread, but in this situation, we are only using one connection at a time. We are finding that sometimes one database connection will do a select on the table and not get the correct value that the other database connection recently updated. The connections are obtained at the beginning of the program and not closed until the very end. Essentially, we run a series of tests that are successful, then we run the same tests again, this time failing. DBC1 - begin transaction DBC1 - sets value to 333000 DBC1 - commit transaction DBC1 - gets value out, is 333000 DBC2 - gets value out, is 333000 DBC2 - begin transaction DBC2 - sets value to 35 DBC2 - commit transaction DBC2 - gets value out, is 35 DBC2 - sets value to 350100 DBC2 - gets value out, is 350100 *Repeat test* DBC1 - begin transaction DBC1 - sets value to 333000 DBC1 - commit transaction DBC1 - gets value out, is 333000 DBC2 - gets value out, is 350100 I have noticed that if we make a call to sqlite3_enable_shared_cache just prior to each database connection being opened, the end result is that DBC2 will get the correct value at the beginning of the second test. Not really knowing the underlying details, it almosts looks like DBC2 doesn't realize the db was changed and uses what it last thought the db held for that value. Any thoughts on why we are seeing what we see? I could add the call to enable the shared cache, but I really want to understand why it doesn't work without it. Thanks, Mark
Re: [sqlite] Multiple connections - stale cache?
Mark Brown [EMAIL PROTECTED] wrote: Hi- We have a scenario where we have two different database connections to the same database. Each database connection is running on a separate thread, but in this situation, we are only using one connection at a time. We are finding that sometimes one database connection will do a select on the table and not get the correct value that the other database connection recently updated. See ticket #2458. http://www.sqlite.org/cvstrac/tktview?tn=2458 If you can get us a reproducible test case, that will be much appreciated. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Thanks for the quick reply. Unfortunately, we are developing code on the vxWorks platform, so I don't think sample code would be of use. We have seen the problem for some time now...at least from 3.3.12. The submitter of the ticket appears to have the exact same scenario as us. Hopefully he can submit same sample code that will help you track down the problem. Would there be any diagnostics (i.e. printfs) that I could enable that might be of use? See ticket #2458. http://www.sqlite.org/cvstrac/tktview?tn=2458 If you can get us a reproducible test case, that will be much appreciated. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Can you list all the compile flags you used to compile the sqlite3 library (including all -DOMIT_* defines)? --- Mark Brown [EMAIL PROTECTED] wrote: Thanks for the quick reply. Unfortunately, we are developing code on the vxWorks platform, so I don't think sample code would be of use. We have seen the problem for some time now...at least from 3.3.12. The submitter of the ticket appears to have the exact same scenario as us. Hopefully he can submit same sample code that will help you track down the problem. Would there be any diagnostics (i.e. printfs) that I could enable that might be of use? See ticket #2458. http://www.sqlite.org/cvstrac/tktview?tn=2458 If you can get us a reproducible test case, that will be much appreciated. Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games. http://sims.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections - stale cache?
Mark Brown [EMAIL PROTECTED] wrote: Thanks for the quick reply. Unfortunately, we are developing code on the vxWorks platform, so I don't think sample code would be of use. We have seen the problem for some time now...at least from 3.3.12. The logic in SQLite that handles cache synchronization was reworked in version 3.3.14. If you have been seeing the problem since 3.3.12, that suggests an application problem or possible a bug in your OS interface layer, not in the core SQLite. The submitter of the ticket appears to have the exact same scenario as us. Hopefully he can submit same sample code that will help you track down the problem. As I pointed out in comments on the #2458 ticket, I can think of countless bugs in the application that can produce the same symptoms. There is no guarantee that this is an SQLite problem. And, in fact, until I have some evidence to the contrary, I'm working under the theory that this is an application bug not an SQLite bug. Would there be any diagnostics (i.e. printfs) that I could enable that might be of use? Bytes 24-27 of the database file contain a counter that is incremented every time the database file changes. A change in that counter is what triggers a cache flush. You might consider instrumenting your OS interface layer and making sure those bytes really are being read at the beginning of every transaction and written at the end of every transaction. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Hi Richard and Joe- It probably is some application error in our code. What puzzles me is that calling sqlite3_enable_shared_cache(1) appears to fix (or at least change) the behavior. Not sure if that triggers any ideas as to what our problem might be. Your suggestion of looking at the database counter is a good one. I can change our app to simply read those bytes and print out the value prior to each statement we execute. Is that what you were thinking? We currently have the following command line switches: -DOS_OTHER=1 -DNO_TCL -DSQLITE_DISABLE_LFS -DTHREADSAFE -DSQLITE_ENABLE_LOCKING_STYLE Essentially, we are on vxWorks operating system, but our build looks just like OS_UNIX. The THREADSAFE and SQLITE_ENABLE_LOCKING_STYLE are new options we added recently, but we have the problem without these as well. I did have to comment out most of the locking styles except the .lock style (which we are using) due to unavailability of certain headers and functionality on vxWorks. Thanks for your help, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
I have some odd results to report on the db counter. I put some diagnostic code in our database wrapper class to write out the db counter whenever a statement is executed. It will print out for every statement executed whether the statement is a SELECT or UPDATE or BEGIN TRANSACTION. Richard had mentioned reading out bytes 24-27. Assuming this is a zero-based index, I was seeing the 28th byte in the file changing by 1, so I think I'm looking at the right value. And, not knowing if the counter was little or big endian, I just printed out all 4 bytes. Here's the truncated results of what I saw (removing a lot of duplicated DB Counter print outs) - the interesting part is what happens to the counter number in the second execution of the test, as it starts shifting around. Does this information help? BEGIN TEST 1 - DB Counter 0:0:42:-100 DB Counter 0:0:42:-100 DB Counter 0:0:42:-99 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DBC1: Re-initializing session number to 333000 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-97 DBC1: got sess num = 333000 DB Counter 0:0:42:-97 DBC2: Got session number: 333000 DB Counter 0:0:42:-97 DBC2: about to update sess info, num = 35 DB Counter 0:0:42:-97 DB Counter 0:0:42:-97 DB Counter 0:0:42:-96 DB Counter 0:0:42:-95 DB Counter 0:0:42:-94 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-92 DB Counter 0:0:42:-91 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DBC2: Got session number: 35 DB Counter 0:0:42:-90 DBC2: about to update sess info, num = 350100 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DBC2: Got session number: 350100 -- END TEST -- REPEAT TEST 1 - DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-96 -- What did my code do to cause this? I think we're doing a bunch of (delete from table) operations on several other tables in this database. DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DBC1: Re-initializing session number to 333000 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-94 DBC1: got sess num = 333000 DB Counter 0:0:42:-94 DBC2: Got session number: 350100 Wrong value DB Counter 0:0:42:-94 DB Counter 0:0:42:-88 --- counter went back to previous value DB Counter 0:0:42:-87 DB Counter 0:0:42:-86 DB Counter 0:0:42:-85 DB Counter 0:0:42:-85 DB Counter 0:0:42:-85 DB Counter 0:0:42:-85 DB Counter 0:0:42:-84 DB Counter 0:0:42:-83 DB Counter 0:0:42:-82 DB Counter 0:0:42:-82 DB Counter 0:0:42:-82 DB Counter 0:0:42:-82 DBC2: Got session number: 350100 Thanks for any help, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections - stale cache?
Mark Brown [EMAIL PROTECTED] wrote: REPEAT TEST 1 - DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-96 -- What did my code do to cause this? This is going to be the bug right here. I would very much like to know what your code is doing right here. Was it the same database connection that printed out the last two lines? Or different connections? What operations occurred in between these two last lines? Are you sure your file locking code is working? -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
