Re: [sqlite] Possible bug - journal_mode DELETE/TRUNCATE
Yes! The journal file was indeed there. I deleted it before openning the database, and read 50 records. I repeated the process; it's consistent. The way I see it that even though the synchronous setting is FULL, there is no API to tell the OS to physically delete a file from disk, just like there is no API to physically force a file to truncate. Well, maybe there is a way (or an API), but sqlite doesn't presently do it. The only way sqlite forces physical writes to disk is via FlushFileBuffers(). So with: journal_mode=DELETE: no buffers written, the OS delays in deleting the file, and during a hard reset, the journal file will remain -- and in my case, that's causing 1 record to get lost. journal_mode=TRUNCATE: no buffers written, the OS delays in truncating the file; same as above. journal_mode=PERSIST: header information is physically written to disk. journal_mode=WAL (synchronous=FULL): all data is physically written to disk, no journal files are truncated or deleted. I think your asking the question indicates you suspected what I would find, no? If I don't opt for WAL, I will use the PERSIST mode instead of the default DELETE mode. On Mon, Sep 10, 2012 at 6:47 AM, Dan Kennedy danielk1...@gmail.com wrote: On 09/10/2012 03:15 AM, Daniel Frimerman wrote: My apologies about the attachment; should have known better. This should be better: https://dl.dropbox.com/u/**50838941/SQLite3_Test.ziphttps://dl.dropbox.com/u/50838941/SQLite3_Test.zip I only get the problem with DELETE and TRUNCATE journal_mode (synchronous set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or FULL) or WAL (synchronous set to FULL). When using PRAGMA journal_mode=DELETE, after you reboot the system is there a *-journal file present in the directory next to your database file? If so and you rename it before opening the database, are all 50 records present? The reason I think there has to be 50 rows is because on FULL mode for example, the I/O buffers are flushed, and it's consistently missing 1 record as opposed to any other number of records. I insert 50 records, the sqlite3 command line utility executes the script, and I get to a stage where I can write commands to the console. What I mean is that as far as sqlite is concerned, it has written the data to disk and also instructed the OS to flush the buffers. Perhaps it finalises something from the last insert only when the next insert comes in? It could be a coincidence of some sort, by sheer difference of implementation of different journals that the problem doesn't show itself with PERSIST or WAL journals. I turned off host I/O cache in VirtualBox, so any writes by the guest OS have to be physically written to the virtual disk on the host. The guest has standard I/O caching on disk, but FlushFileBuffers() should have done its job. There is no reason why any link in the chain should report data written to disk without actually doing it, unless there is a problem. I suppose I should just go ahead and test it on the physical PC. If there is a problem with that also, then I suppose I could blame the OS for not flushing stuff to disk properly. Now I gotta find me a machine Regards, Dan On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanovpaiva...@gmail.com wrote: Note: attachments are stripped out of this list. So if you want for anybody else to see your zip file you need to put it on some website and post link here. About the problem you have: I wonder how are you sure that there should be 50 rows in the database and not 49? If you are resetting the OS before it has a chance to properly commit everything then it's okay for last transaction to be missing. But if you are sure that you are resetting the OS after everything is settled then maybe you are not committing your last transaction properly? Or maybe there's some bug in your virtualization layer and you need to try the same thing on a real hardware? Pavel On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman danielfrimer...@gmail.com wrote: I am fairly new to sqlite and as a result of not reading the manual and not doing some performance testing, I got punished somewhat. I did not anticipate that on journal_mode=DELETE and synchronous=FULL, I would get no more than 5 inserts (in auto-commit mode) per second. It crippled a certain batch operation on a live system. That's water under the bridge; it's the testing afterwards and a potential minor problem that I found is what I am now interested in. I tested all journal mode settings for sqlite, as well as the synchronous setting. Some things that I discovered were not so obvious from reading the docs, such as the WAL journal mode combined with NORMAL synchronous setting, which is nowhere near as durable as NORMAL setting for other journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves all inserted data - reproducing the slightest of
[sqlite] Possible bug - journal_mode DELETE/TRUNCATE
I am fairly new to sqlite and as a result of not reading the manual and not doing some performance testing, I got punished somewhat. I did not anticipate that on journal_mode=DELETE and synchronous=FULL, I would get no more than 5 inserts (in auto-commit mode) per second. It crippled a certain batch operation on a live system. That's water under the bridge; it's the testing afterwards and a potential minor problem that I found is what I am now interested in. I tested all journal mode settings for sqlite, as well as the synchronous setting. Some things that I discovered were not so obvious from reading the docs, such as the WAL journal mode combined with NORMAL synchronous setting, which is nowhere near as durable as NORMAL setting for other journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves all inserted data - reproducing the slightest of chances that consistency is compromised was rather hard. This is reflected in performance testing: NORMAL is only slightly faster than FULL mode for non-WAL journal settings (btw, journal_mode=OFF was never tested in any of my tests). But, I understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode where consistency/corruption is concerned. That is, the database cannot get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades off durability and in my tests I easily reproduced that. Okay, that was not really related to the possible bug I found. I've attached a ZIP file containing some batch files that create a table, insert some rows, at which point you hard-reset the OS, log back in and check if the number of rows in the DB matches what you inserted. Although the non-WAL journal modes are somewhat similar, the little problem that I've come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL. The problem is basically as follows: in DELETE and TRUNCATE journal mode combined with NORMAL/FULL synchronous mode, there is always 1 row missing during my simulated power-cut. I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14 (command line as well as through my testing application). In VirtualBox, under storage settings for the VM, I used IDE Controller (afaik it's single threaded), turned off host I/O cache. Inside the guest, write-cache should be enabled in device manager under policies for the default disk controller. To test this, set your VM as above, copy the files from the attached ZIP file, also download the latest sqlite3 command line shell. Restart the guest once to ensure your files are flushed out before you start resetting the guest :) Execute the following batch file: EXEC_DATA__DELETE_FULL.cmd, wait 2-3 seconds (or less) then hit HOST+R to hard reset the OS. When you reboot, run READ_DATA.cmd, you'll see 49 rows, but there should be 50. You can try the same with EXEC_DATA__DELETE_NORMAL.cmd, EXEC_DATA__TRUNCATE_FULL.cmd, EXEC_DATA__TRUNCATE_NORMAL.cmd 50 rows if you try with EXEC_DATA__PERSIST_FULL.cmd and EXEC_DATA__PERSIST_NORMAL.cmd and EXEC_DATA__WAL_FULL.cmd What's with that? Kind regards, Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug - journal_mode DELETE/TRUNCATE
Note: attachments are stripped out of this list. So if you want for anybody else to see your zip file you need to put it on some website and post link here. About the problem you have: I wonder how are you sure that there should be 50 rows in the database and not 49? If you are resetting the OS before it has a chance to properly commit everything then it's okay for last transaction to be missing. But if you are sure that you are resetting the OS after everything is settled then maybe you are not committing your last transaction properly? Or maybe there's some bug in your virtualization layer and you need to try the same thing on a real hardware? Pavel On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman danielfrimer...@gmail.com wrote: I am fairly new to sqlite and as a result of not reading the manual and not doing some performance testing, I got punished somewhat. I did not anticipate that on journal_mode=DELETE and synchronous=FULL, I would get no more than 5 inserts (in auto-commit mode) per second. It crippled a certain batch operation on a live system. That's water under the bridge; it's the testing afterwards and a potential minor problem that I found is what I am now interested in. I tested all journal mode settings for sqlite, as well as the synchronous setting. Some things that I discovered were not so obvious from reading the docs, such as the WAL journal mode combined with NORMAL synchronous setting, which is nowhere near as durable as NORMAL setting for other journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves all inserted data - reproducing the slightest of chances that consistency is compromised was rather hard. This is reflected in performance testing: NORMAL is only slightly faster than FULL mode for non-WAL journal settings (btw, journal_mode=OFF was never tested in any of my tests). But, I understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode where consistency/corruption is concerned. That is, the database cannot get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades off durability and in my tests I easily reproduced that. Okay, that was not really related to the possible bug I found. I've attached a ZIP file containing some batch files that create a table, insert some rows, at which point you hard-reset the OS, log back in and check if the number of rows in the DB matches what you inserted. Although the non-WAL journal modes are somewhat similar, the little problem that I've come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL. The problem is basically as follows: in DELETE and TRUNCATE journal mode combined with NORMAL/FULL synchronous mode, there is always 1 row missing during my simulated power-cut. I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14 (command line as well as through my testing application). In VirtualBox, under storage settings for the VM, I used IDE Controller (afaik it's single threaded), turned off host I/O cache. Inside the guest, write-cache should be enabled in device manager under policies for the default disk controller. To test this, set your VM as above, copy the files from the attached ZIP file, also download the latest sqlite3 command line shell. Restart the guest once to ensure your files are flushed out before you start resetting the guest :) Execute the following batch file: EXEC_DATA__DELETE_FULL.cmd, wait 2-3 seconds (or less) then hit HOST+R to hard reset the OS. When you reboot, run READ_DATA.cmd, you'll see 49 rows, but there should be 50. You can try the same with EXEC_DATA__DELETE_NORMAL.cmd, EXEC_DATA__TRUNCATE_FULL.cmd, EXEC_DATA__TRUNCATE_NORMAL.cmd 50 rows if you try with EXEC_DATA__PERSIST_FULL.cmd and EXEC_DATA__PERSIST_NORMAL.cmd and EXEC_DATA__WAL_FULL.cmd What's with that? Kind regards, Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug - journal_mode DELETE/TRUNCATE
My apologies about the attachment; should have known better. This should be better: https://dl.dropbox.com/u/50838941/SQLite3_Test.zip I only get the problem with DELETE and TRUNCATE journal_mode (synchronous set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or FULL) or WAL (synchronous set to FULL). The reason I think there has to be 50 rows is because on FULL mode for example, the I/O buffers are flushed, and it's consistently missing 1 record as opposed to any other number of records. I insert 50 records, the sqlite3 command line utility executes the script, and I get to a stage where I can write commands to the console. What I mean is that as far as sqlite is concerned, it has written the data to disk and also instructed the OS to flush the buffers. Perhaps it finalises something from the last insert only when the next insert comes in? It could be a coincidence of some sort, by sheer difference of implementation of different journals that the problem doesn't show itself with PERSIST or WAL journals. I turned off host I/O cache in VirtualBox, so any writes by the guest OS have to be physically written to the virtual disk on the host. The guest has standard I/O caching on disk, but FlushFileBuffers() should have done its job. There is no reason why any link in the chain should report data written to disk without actually doing it, unless there is a problem. I suppose I should just go ahead and test it on the physical PC. If there is a problem with that also, then I suppose I could blame the OS for not flushing stuff to disk properly. Now I gotta find me a machine Regards, Dan On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanov paiva...@gmail.com wrote: Note: attachments are stripped out of this list. So if you want for anybody else to see your zip file you need to put it on some website and post link here. About the problem you have: I wonder how are you sure that there should be 50 rows in the database and not 49? If you are resetting the OS before it has a chance to properly commit everything then it's okay for last transaction to be missing. But if you are sure that you are resetting the OS after everything is settled then maybe you are not committing your last transaction properly? Or maybe there's some bug in your virtualization layer and you need to try the same thing on a real hardware? Pavel On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman danielfrimer...@gmail.com wrote: I am fairly new to sqlite and as a result of not reading the manual and not doing some performance testing, I got punished somewhat. I did not anticipate that on journal_mode=DELETE and synchronous=FULL, I would get no more than 5 inserts (in auto-commit mode) per second. It crippled a certain batch operation on a live system. That's water under the bridge; it's the testing afterwards and a potential minor problem that I found is what I am now interested in. I tested all journal mode settings for sqlite, as well as the synchronous setting. Some things that I discovered were not so obvious from reading the docs, such as the WAL journal mode combined with NORMAL synchronous setting, which is nowhere near as durable as NORMAL setting for other journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves all inserted data - reproducing the slightest of chances that consistency is compromised was rather hard. This is reflected in performance testing: NORMAL is only slightly faster than FULL mode for non-WAL journal settings (btw, journal_mode=OFF was never tested in any of my tests). But, I understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode where consistency/corruption is concerned. That is, the database cannot get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades off durability and in my tests I easily reproduced that. Okay, that was not really related to the possible bug I found. I've attached a ZIP file containing some batch files that create a table, insert some rows, at which point you hard-reset the OS, log back in and check if the number of rows in the DB matches what you inserted. Although the non-WAL journal modes are somewhat similar, the little problem that I've come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL. The problem is basically as follows: in DELETE and TRUNCATE journal mode combined with NORMAL/FULL synchronous mode, there is always 1 row missing during my simulated power-cut. I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14 (command line as well as through my testing application). In VirtualBox, under storage settings for the VM, I used IDE Controller (afaik it's single threaded), turned off host I/O cache. Inside the guest, write-cache should be enabled in device manager under policies for the default disk controller. To test this, set your VM as above, copy the files
Re: [sqlite] Possible bug - journal_mode DELETE/TRUNCATE
On 9 Sep 2012, at 9:15pm, Daniel Frimerman danielfrimer...@gmail.com wrote: The reason I think there has to be 50 rows is because on FULL mode for example, the I/O buffers are flushed, and it's consistently missing 1 record as opposed to any other number of records. I insert 50 records, the sqlite3 command line utility executes the script, and I get to a stage where I can write commands to the console. What I mean is that as far as sqlite is concerned, it has written the data to disk and also instructed the OS to flush the buffers. Are you checking the result codes returned by every SQLite call you execute ? I'm not talking about the one that is causing the error but every one in your app before it executes that one. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug - journal_mode DELETE/TRUNCATE
Actually, I am not using sqlite at the API level. That's why prepared a bunch of batch files that reproduce the issue with sqlite command line shell. To answer your question, the queries complete in sqlite3 command line, and they also complete successfully using the API, albeit, through a wrapper that I am using. Regards, Dan On Sun, Sep 9, 2012 at 9:31 PM, Simon Slavin slav...@bigfraud.org wrote: On 9 Sep 2012, at 9:15pm, Daniel Frimerman danielfrimer...@gmail.com wrote: The reason I think there has to be 50 rows is because on FULL mode for example, the I/O buffers are flushed, and it's consistently missing 1 record as opposed to any other number of records. I insert 50 records, the sqlite3 command line utility executes the script, and I get to a stage where I can write commands to the console. What I mean is that as far as sqlite is concerned, it has written the data to disk and also instructed the OS to flush the buffers. Are you checking the result codes returned by every SQLite call you execute ? I'm not talking about the one that is causing the error but every one in your app before it executes that one. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug - journal_mode DELETE/TRUNCATE
On 09/10/2012 03:15 AM, Daniel Frimerman wrote: My apologies about the attachment; should have known better. This should be better: https://dl.dropbox.com/u/50838941/SQLite3_Test.zip I only get the problem with DELETE and TRUNCATE journal_mode (synchronous set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or FULL) or WAL (synchronous set to FULL). When using PRAGMA journal_mode=DELETE, after you reboot the system is there a *-journal file present in the directory next to your database file? If so and you rename it before opening the database, are all 50 records present? The reason I think there has to be 50 rows is because on FULL mode for example, the I/O buffers are flushed, and it's consistently missing 1 record as opposed to any other number of records. I insert 50 records, the sqlite3 command line utility executes the script, and I get to a stage where I can write commands to the console. What I mean is that as far as sqlite is concerned, it has written the data to disk and also instructed the OS to flush the buffers. Perhaps it finalises something from the last insert only when the next insert comes in? It could be a coincidence of some sort, by sheer difference of implementation of different journals that the problem doesn't show itself with PERSIST or WAL journals. I turned off host I/O cache in VirtualBox, so any writes by the guest OS have to be physically written to the virtual disk on the host. The guest has standard I/O caching on disk, but FlushFileBuffers() should have done its job. There is no reason why any link in the chain should report data written to disk without actually doing it, unless there is a problem. I suppose I should just go ahead and test it on the physical PC. If there is a problem with that also, then I suppose I could blame the OS for not flushing stuff to disk properly. Now I gotta find me a machine Regards, Dan On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanovpaiva...@gmail.com wrote: Note: attachments are stripped out of this list. So if you want for anybody else to see your zip file you need to put it on some website and post link here. About the problem you have: I wonder how are you sure that there should be 50 rows in the database and not 49? If you are resetting the OS before it has a chance to properly commit everything then it's okay for last transaction to be missing. But if you are sure that you are resetting the OS after everything is settled then maybe you are not committing your last transaction properly? Or maybe there's some bug in your virtualization layer and you need to try the same thing on a real hardware? Pavel On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman danielfrimer...@gmail.com wrote: I am fairly new to sqlite and as a result of not reading the manual and not doing some performance testing, I got punished somewhat. I did not anticipate that on journal_mode=DELETE and synchronous=FULL, I would get no more than 5 inserts (in auto-commit mode) per second. It crippled a certain batch operation on a live system. That's water under the bridge; it's the testing afterwards and a potential minor problem that I found is what I am now interested in. I tested all journal mode settings for sqlite, as well as the synchronous setting. Some things that I discovered were not so obvious from reading the docs, such as the WAL journal mode combined with NORMAL synchronous setting, which is nowhere near as durable as NORMAL setting for other journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves all inserted data - reproducing the slightest of chances that consistency is compromised was rather hard. This is reflected in performance testing: NORMAL is only slightly faster than FULL mode for non-WAL journal settings (btw, journal_mode=OFF was never tested in any of my tests). But, I understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode where consistency/corruption is concerned. That is, the database cannot get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades off durability and in my tests I easily reproduced that. Okay, that was not really related to the possible bug I found. I've attached a ZIP file containing some batch files that create a table, insert some rows, at which point you hard-reset the OS, log back in and check if the number of rows in the DB matches what you inserted. Although the non-WAL journal modes are somewhat similar, the little problem that I've come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL. The problem is basically as follows: in DELETE and TRUNCATE journal mode combined with NORMAL/FULL synchronous mode, there is always 1 row missing during my simulated power-cut. I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14 (command line as well as through my testing application). In VirtualBox, under storage settings for the VM, I used IDE Controller (afaik it's single
