[sqlite] Database corrupted after OS Out of memory exception
Hello, we are developing an application Using SQLite3 on Windows embedded compact 7 using C# 3.5. We have had no problems with corrupted databases so far, even though I always kill the running process in the debugger. The development process is now 2 Years. Also, some months ago we had a lot of out of memory exceptions and no problem with SqLite Now, in Kioskmode where there is no explorer running, when we change the system time, there comes a several MBytes big memory leak, which causes an Out of Memory Exception of the OS, and after a restart we have a corrupted Database. We had other Out of Memory Exceptions with no corrupted Database. Additionally, the journal File seems not to be handled before the first data request is handled after the restart, because at subsequent starts, the database allows access to data, that were not available upon first start. there is only one client, and only one thread trying to access the database. We are using: c# 3.5 .net 3.5 SQLite.Interop.085.dll File Version 1.0.85.0 Product Version 1.0.85.0 System.Data.SQLite.dll File Version 1.0.85.0 Product Version 1.0.85.0 Do you see any chance, to prevent this damage? If I close the database before this critical action, is this a synchronous process, means is the database really closed and everything written, when the close() Method comes back and the main program continous? If not, how can I make sure everything is written, before I continue with the critical action? Thank you for every help.
Re: [sqlite] Database corrupted 28 billion bytes
On 1/16/15, MayW wrote: > Pages on the freelist (per header) 2144 0.008% > Pages on the freelist (calculated) 3344382 12.1% The fact that the freelist size as reported by the header is different from the actual freelist size is troubling. A VACUUM should clear the problem. But I wish I understood how the problem arose in the first place -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
Thank you very much! /** Disk-Space Utilization Report For junk.db3 Page size in bytes 1024 Pages in the whole file (measured) 27606264 Pages in the whole file (calculated).. 24264026 Pages that store data. 2426188287.9% Pages on the freelist (per header) 2144 0.008% Pages on the freelist (calculated) 3344382 12.1% Pages of auto-vacuum overhead. 00.0% Number of tables in the database.. 5 Number of indices. 1 Number of defined indices. 1 Number of implied indices. 0 Size of the file in bytes. 28268814336 Bytes of user payload stored.. 18312217251 64.8% *** Page counts for all tables with their indices * MYTABLE... 2426058787.9% PARTS. 1292 0.005% QUERY. 10.0% SQLITE_MASTER. 10.0% SQLITE_SEQUENCE... 10.0% *** Page counts for all tables and indices separately * MYTABLE... 2426058787.9% PARTS. 1116 0.004% MANUF. 176 0.0% QUERY. 10.0% SQLITE_MASTER. 10.0% SQLITE_SEQUENCE... 10.0% *** All tables and indices Percentage of total database.. 87.9% Number of entries. 704297145 Bytes of storage consumed. 24844167168 Bytes of payload.. 18312362684 73.7% Average payload per entry. 26.00 Average unused bytes per entry 1.02 Average fanout 84.00 Maximum payload per entry. 426 Entries that use overflow. 00.0% Index pages used.. 285997 Primary pages used 23975885 Overflow pages used... 0 Total pages used.. 24261882 Unused bytes on index pages... 3471326711.9% Unused bytes on primary pages. 6833870342.8% Unused bytes on overflow pages 0 Unused bytes on all pages. 7181003012.9% *** All tables Percentage of total database.. 87.9% Number of entries. 704287145 Bytes of storage consumed. 24843986944 Bytes of payload.. 18312217804 73.7% Average payload per entry. 26.00 Average unused bytes per entry 1.02 Average fanout 84.00 Maximum payload per entry. 426 Entries that use overflow. 00.0% Index pages used.. 285997 Primary pages used 23975709 Overflow pages used... 0 Total pages used.. 24261706 Unused bytes on index pages... 3471326711.9% Unused bytes on primary pages. 6833837982.8% Unused bytes on overflow pages 0 Unused bytes on all pages. 7180970652.9% *** All indices *** Percentage of total database.. 0.0% Number of entries. 1 Bytes of storage consumed. 180224 Bytes of payload.. 144880 80.4% Average payload per entry. 14.49 Average unused bytes per entry 0.32 Maximum payload per entry. 35 Entries that use overflow. 00.0% Primary pages used 176 Overflow pages used... 0 Total pages used.. 176 Unuse
Re: [sqlite] Database corrupted 28 billion bytes
On 1/16/15, MayW wrote: > sqlite3_analyzer.exe ended with 16 INSERT statements showing in my Windows 7 > CMD console screen: > The interesting information was in the part that scrolled off the top of you console. I suggest you rerun the command, directing output into a file: sqlite3_analyzer bigdb.db3 >analysis.txt start analysis.txt -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
On 1/16/15, MayW wrote: > Found table that was huge, it was named MyTable. > It was created with: > Create Table MyTable(comment); > > select max(rowid),* from MyTable; > 80002 "This","is"," 4" > > Drop table Mytable; > Vacuum. > It went down to a little over 3,000,000 bytes. > > I'm going to include a filesize routine within my programs to > test the .DB3 to see if it grows by some great amount in a day. > > What caused it to become huge, maybe a run-away test program? > > I made a backup so will run sqlite3_analyzer.exe, again. > Not sure what I'll be looking for. > What sqlite3_analyzer would have told you is what you have already figured out - that MyTable was taking up most of the space. If you run sqlite3_analyzer on the new 3MB database, it will show you how much space is taken up by each of the remaining tables and indexes. That information might be interesting to you. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
sqlite3_analyzer.exe ended with 16 INSERT statements showing in my Windows 7 CMD console screen: They look similar to this one: INSERT INTO space_used VALUES(‘Deductions’,’Deductions’,0,0,0,0,0,0,16,0,0,1024); I remember helping someone try to get a UNION statement correct a few months ago. These were his statements, except I don’t remember anything about insert_into-space-used? - May -- View this message in context: http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048p80067.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
Last statement 2 statements were: insert into space_used values(‘MyTable,”MyTable,0,728251738,704277138,588,0,0,26,285986,23974601,0,34710955,683321040,0,12,24842841088); COMMIT; - May -- View this message in context: http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048p80068.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
>What is the output from the following: > PRAGMA page_size; > PRAGMA journal_mode; > PRAGMA freelist_count; > PRAGMA page_count; 1024 delete 0 27606264 The sqlite3_analyzer,exe is running. - May -- View this message in context: http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048p80062.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
Found table that was huge, it was named MyTable. It was created with: Create Table MyTable(comment); select max(rowid),* from MyTable; 80002 "This","is"," 4" Drop table Mytable; Vacuum. It went down to a little over 3,000,000 bytes. I'm going to include a filesize routine within my programs to test the .DB3 to see if it grows by some great amount in a day. What caused it to become huge, maybe a run-away test program? I made a backup so will run sqlite3_analyzer.exe, again. Not sure what I'll be looking for. Thank you! - May -- View this message in context: http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048p80061.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
On 16 Jan 2015, at 11:18am, MikeD wrote: > Just terminated sqlite3_analyzer. You can let it run. Overnight if need be. Its CPU usage will never increase much past what you've already seen and memory usage shouldn't be excessive. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
On 1/16/15, MikeD wrote: > I have a database that has become 28,268,814,336 bytes so > downloaded the sqlite3_analyzer and it has been running for over > 15-minutes. > > Task manager shows sqlite3_analyzer.exe using 13% and the memory stays > steady at 23,768K. > 19 handles, 1 thread(s). > > The database was a test database that has been used for some time. > I can't say when the file became huge. Vacuum was occassionaly run. > > Just terminated sqlite3_analyzer. That's too bad, because if you had let sqlite3_analyzer run to completion, it would have output some very useful information about the state of the database file. > Vacuum memory usage is between 11 and 13 percent using 24,564K. > I'll let vacuum continue to run. It has been running 15-minutes. > > The database is still working. What should I be doing? > > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
On 1/16/15, MikeD wrote: > I have a database that has become 28,268,814,336 bytes so > downloaded the sqlite3_analyzer and it has been running for over > 15-minutes. > > Task manager shows sqlite3_analyzer.exe using 13% and the memory stays > steady at 23,768K. > 19 handles, 1 thread(s). > > The database was a test database that has been used for some time. > I can't say when the file became huge. Vacuum was occassionaly run. > > Just terminated sqlite3_analyzer. > Vacuum memory usage is between 11 and 13 percent using 24,564K. > I'll let vacuum continue to run. It has been running 15-minutes. > > The database is still working. What should I be doing? What is the output from the following: PRAGMA page_size; PRAGMA journal_mode; PRAGMA freelist_count; PRAGMA page_count; > > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted 28 billion bytes
On Fri, Jan 16, 2015 at 12:18 PM, MikeD wrote: > I have a database that has become 28,268,814,336 bytes so > downloaded the sqlite3_analyzer and it has been running for over > 15-minutes. > ... > The database is still working. What should I be doing? > How about continue to use it? Why do you feel that something is broken just because it's big? -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database corrupted 28 billion bytes
I have a database that has become 28,268,814,336 bytes so downloaded the sqlite3_analyzer and it has been running for over 15-minutes. Task manager shows sqlite3_analyzer.exe using 13% and the memory stays steady at 23,768K. 19 handles, 1 thread(s). The database was a test database that has been used for some time. I can't say when the file became huge. Vacuum was occassionaly run. Just terminated sqlite3_analyzer. Vacuum memory usage is between 11 and 13 percent using 24,564K. I'll let vacuum continue to run. It has been running 15-minutes. The database is still working. What should I be doing? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Database-corrupted-28-billion-bytes-tp80048.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On 28 May 2013, at 8:30pm, Richard Hipp wrote: > On Tue, May 28, 2013 at 3:17 PM, Ryan Johnson > wrote: > >> Cheap (aka counterfeit) flash drives are notorious for advertizing more >> space to the OS than they actually have, and so at some point writes start >> to silently erase data that was written earlier. > > I've heard the same thing, except that the counterfeit flash drives are not > necessarily inexpensive. They are just counterfeits. My department ordered 500 low-capacity Pen Drives with our department logo on. The price was reasonable and we're part of a big organisation with a big name and big lawyers. I see no reason Purchasing should have gone to some noname dodgy cheap Chinese supplier. The drives came pre-loaded with our advertising material (PDFs, HTML, etc.) but were left writeable, with the intention that our potential recruits would use them for the next year or two and carry our logo around with them. Nobody thought to ask me (I'm theoretically in charge of tech) or even tell me about the order. These drives were terrible. About one in 20 you couldn't even get our own material off of without a sector failure. I picked up some that passed that test and us techies used them around the office for the next month. I don't think any of them stood up to more than a couple of weeks without sector faults, disk IO errors, and all that stuff. They had the right capacity, I think they just had very low-quality soldering and any heat expansion ruined them. Or maybe they'd used chips which failed testing. Of course, the company that supplied them were happy to replace any specific drives we returned as broken, but refused to replace the who lot, or return our money. I toyed with the idea of returning one drive a day, every working day for the next two years. But I settled for having my boss wave my job description under the noses of several other people in our department. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Tue, May 28, 2013 at 3:17 PM, Ryan Johnson wrote: > Cheap (aka counterfeit) flash drives are notorious for advertizing more > space to the OS than they actually have, and so at some point writes start > to silently erase data that was written earlier. I've heard the same thing, except that the counterfeit flash drives are not necessarily inexpensive. They are just counterfeits. > -- > D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On 27/05/2013 9:40 PM, Woody Wu wrote: On Mon, May 27, 2013 at 04:31:25PM +0100, Simon Slavin wrote: On 27 May 2013, at 4:22pm, Woody Wu wrote: If Yaffs2 is the cause, how can I write an effective test to exposure it? Do you have an opportunity to format the same drive in a different format ? I'm not telling you to change your long-term practises, just to try a different format for testing. Reformat it, run your identical testing program, and see if it fails in the same way. Also I liked Richard's point: are you testing on a cheap Flash drive ? Perhaps one intended only for use with FAT and therefore only tested for use with FAT ? Can you experts explains why a cheap Flash drive can harm an application such as sqlite? Does you mean these cheap drive was cheating with ECC? Otherwise, filesystem should be able to capture IO error (fix it or forward), right? Cheap (aka counterfeit) flash drives are notorious for advertizing more space to the OS than they actually have, and so at some point writes start to silently erase data that was written earlier. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Tue, May 28, 2013 at 06:31:47AM -0500, Thanumalayan Sankaranarayana Pillai wrote: > Woody, this mailing list might not be the best place to discuss problems > with YAFFS2. Saying that, a simple test could be to almost fully fill the > YAFFS2 partition with a bunch of files, then read those files and make sure > the files have the data they are supposed to have. Files should have > sensible content in them (and are not just filled with zeroes). Also, I'm > assuming that you already have some experience with YAFFS2, correct? No, I don't have any real experience in using YAFFS2. Today, I tried to subscribe its mailing list, but don't get the confirmation email, wired. > Otherwise, maybe there is some requirement you haven't satisfied, like > erasing all of your partition prior to using it? Interesting... Could you talk more 'bout that? As I said, the target system was built by our external vendor, I don't know their process. Can I check if it's correctly done? Erasing Yaffs2 means something like 'mke2fs' on a ext* file system? Thanks. > > > On Mon, May 27, 2013 at 11:44 PM, Woody Wu wrote: > > > On Tue, May 28, 2013 at 03:08:55AM +0100, Simon Slavin wrote: > > > > > > On 28 May 2013, at 2:37am, Woody Wu wrote: > > > > > > > How do you guys think about this: if NAND has an > > > > IO problem, Yaffs2 should recover it or forward the error to > > > > applications, right? > > > > > > Arguably. The file system can send an error back to the application. > > > If something does that to SQLite3 SQLite3 will then return the result > > > SQLITE_IOERR. If your program is correctly looking at the results > > > returned from every SQLite3 API call, and it's not seeing that error, > > > it would seem that yaffs2 is not doing the correct thing in this case. > > > And from what you wrote it seems that rather than return an error to > > > the program, yaffs2 prefers to write an error message to the console. > > > Which is not what these things are meant to do. > > > > > > > > > On 28 May 2013, at 2:40am, Woody Wu wrote: > > > > > > > Can you experts explains why a cheap Flash drive can harm an > > > > application such as sqlite? > > > > > > > Does you mean these cheap drive was cheating > > > > with ECC? > > > > > > It might just be so bad that it doesn't even realise something is > > > wrong. You write something to sector 2336 and later read sector 2336 > > > and expect to get the same thing back, but instead it returns the > > > contents of sector 2338. Faulty hardware, perhaps. > > > > > > > Otherwise, filesystem should be able to capture IO error (fix > > > > it or forward), right? > > > > > > If by 'forward' you mean to tell the application something went wrong, > > > then right. But the file system might not know anything is wrong. It > > > asked for sector 2336 and it got some data. How is it meant to know > > > the data it retrieved came from the wrong place on the chip ? > > > > > > On 28 May 2013, at 2:33am, Woody Wu wrote: > > > > > > > How can I tell what's the current VFS that I was using? Thanks. > > > > > > Actually, just ignore that. You couldn't do anything about it even if > > > you knew. From what Doctor Hipp wrote, I suspect that yaffs2 is > > > faulty. > > > > > > Simon. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > Very clear explained, Simon. Thanks. > > > > On the other hand, I still want to write some test code to exposure > > Yaffs2's defects on my target. I already wrote a simple one, which just > > repeatly write to a file and read back to compare its conents. Probably > > it's too simple to trigger an error. After 72 hours, it haven't reported > > any error. I really like to hear any suggestion on writing such a test > > from your experts. Anyway, if the original case was really caused by > > Yaffs2, I belive there must exist a test to capture it. Do you agree? > > > > -woody > > > > > > > > > > > > -- > > woody > > I can't go back to yesterday - because I was a different person then. > > ___ > > 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 -- woody I can't go back to yesterday - because I was a different person then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
Woody, this mailing list might not be the best place to discuss problems with YAFFS2. Saying that, a simple test could be to almost fully fill the YAFFS2 partition with a bunch of files, then read those files and make sure the files have the data they are supposed to have. Files should have sensible content in them (and are not just filled with zeroes). Also, I'm assuming that you already have some experience with YAFFS2, correct? Otherwise, maybe there is some requirement you haven't satisfied, like erasing all of your partition prior to using it? On Mon, May 27, 2013 at 11:44 PM, Woody Wu wrote: > On Tue, May 28, 2013 at 03:08:55AM +0100, Simon Slavin wrote: > > > > On 28 May 2013, at 2:37am, Woody Wu wrote: > > > > > How do you guys think about this: if NAND has an > > > IO problem, Yaffs2 should recover it or forward the error to > > > applications, right? > > > > Arguably. The file system can send an error back to the application. > > If something does that to SQLite3 SQLite3 will then return the result > > SQLITE_IOERR. If your program is correctly looking at the results > > returned from every SQLite3 API call, and it's not seeing that error, > > it would seem that yaffs2 is not doing the correct thing in this case. > > And from what you wrote it seems that rather than return an error to > > the program, yaffs2 prefers to write an error message to the console. > > Which is not what these things are meant to do. > > > > > > On 28 May 2013, at 2:40am, Woody Wu wrote: > > > > > Can you experts explains why a cheap Flash drive can harm an > > > application such as sqlite? > > > > > Does you mean these cheap drive was cheating > > > with ECC? > > > > It might just be so bad that it doesn't even realise something is > > wrong. You write something to sector 2336 and later read sector 2336 > > and expect to get the same thing back, but instead it returns the > > contents of sector 2338. Faulty hardware, perhaps. > > > > > Otherwise, filesystem should be able to capture IO error (fix > > > it or forward), right? > > > > If by 'forward' you mean to tell the application something went wrong, > > then right. But the file system might not know anything is wrong. It > > asked for sector 2336 and it got some data. How is it meant to know > > the data it retrieved came from the wrong place on the chip ? > > > > On 28 May 2013, at 2:33am, Woody Wu wrote: > > > > > How can I tell what's the current VFS that I was using? Thanks. > > > > Actually, just ignore that. You couldn't do anything about it even if > > you knew. From what Doctor Hipp wrote, I suspect that yaffs2 is > > faulty. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Very clear explained, Simon. Thanks. > > On the other hand, I still want to write some test code to exposure > Yaffs2's defects on my target. I already wrote a simple one, which just > repeatly write to a file and read back to compare its conents. Probably > it's too simple to trigger an error. After 72 hours, it haven't reported > any error. I really like to hear any suggestion on writing such a test > from your experts. Anyway, if the original case was really caused by > Yaffs2, I belive there must exist a test to capture it. Do you agree? > > -woody > > > > > > -- > woody > I can't go back to yesterday - because I was a different person then. > ___ > 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] Database Corrupted!
On Tue, May 28, 2013 at 03:08:55AM +0100, Simon Slavin wrote: > > On 28 May 2013, at 2:37am, Woody Wu wrote: > > > How do you guys think about this: if NAND has an > > IO problem, Yaffs2 should recover it or forward the error to > > applications, right? > > Arguably. The file system can send an error back to the application. > If something does that to SQLite3 SQLite3 will then return the result > SQLITE_IOERR. If your program is correctly looking at the results > returned from every SQLite3 API call, and it's not seeing that error, > it would seem that yaffs2 is not doing the correct thing in this case. > And from what you wrote it seems that rather than return an error to > the program, yaffs2 prefers to write an error message to the console. > Which is not what these things are meant to do. > > > On 28 May 2013, at 2:40am, Woody Wu wrote: > > > Can you experts explains why a cheap Flash drive can harm an > > application such as sqlite? > > > Does you mean these cheap drive was cheating > > with ECC? > > It might just be so bad that it doesn't even realise something is > wrong. You write something to sector 2336 and later read sector 2336 > and expect to get the same thing back, but instead it returns the > contents of sector 2338. Faulty hardware, perhaps. > > > Otherwise, filesystem should be able to capture IO error (fix > > it or forward), right? > > If by 'forward' you mean to tell the application something went wrong, > then right. But the file system might not know anything is wrong. It > asked for sector 2336 and it got some data. How is it meant to know > the data it retrieved came from the wrong place on the chip ? > > On 28 May 2013, at 2:33am, Woody Wu wrote: > > > How can I tell what's the current VFS that I was using? Thanks. > > Actually, just ignore that. You couldn't do anything about it even if > you knew. From what Doctor Hipp wrote, I suspect that yaffs2 is > faulty. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Very clear explained, Simon. Thanks. On the other hand, I still want to write some test code to exposure Yaffs2's defects on my target. I already wrote a simple one, which just repeatly write to a file and read back to compare its conents. Probably it's too simple to trigger an error. After 72 hours, it haven't reported any error. I really like to hear any suggestion on writing such a test from your experts. Anyway, if the original case was really caused by Yaffs2, I belive there must exist a test to capture it. Do you agree? -woody -- woody I can't go back to yesterday - because I was a different person then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On 28 May 2013, at 2:37am, Woody Wu wrote: > How do you guys think about this: if NAND has an > IO problem, Yaffs2 should recover it or forward the error to > applications, right? Arguably. The file system can send an error back to the application. If something does that to SQLite3 SQLite3 will then return the result SQLITE_IOERR. If your program is correctly looking at the results returned from every SQLite3 API call, and it's not seeing that error, it would seem that yaffs2 is not doing the correct thing in this case. And from what you wrote it seems that rather than return an error to the program, yaffs2 prefers to write an error message to the console. Which is not what these things are meant to do. On 28 May 2013, at 2:40am, Woody Wu wrote: > Can you experts explains why a cheap Flash drive can harm an > application such as sqlite? > Does you mean these cheap drive was cheating > with ECC? It might just be so bad that it doesn't even realise something is wrong. You write something to sector 2336 and later read sector 2336 and expect to get the same thing back, but instead it returns the contents of sector 2338. Faulty hardware, perhaps. > Otherwise, filesystem should be able to capture IO error (fix > it or forward), right? If by 'forward' you mean to tell the application something went wrong, then right. But the file system might not know anything is wrong. It asked for sector 2336 and it got some data. How is it meant to know the data it retrieved came from the wrong place on the chip ? On 28 May 2013, at 2:33am, Woody Wu wrote: > How can I tell what's the current VFS that I was using? Thanks. Actually, just ignore that. You couldn't do anything about it even if you knew. From what Doctor Hipp wrote, I suspect that yaffs2 is faulty. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 04:31:25PM +0100, Simon Slavin wrote: > > On 27 May 2013, at 4:22pm, Woody Wu wrote: > > > If Yaffs2 is the cause, how can I write an effective test to exposure it? > > Do you have an opportunity to format the same drive in a different format ? > I'm not telling you to change your long-term practises, just to try a > different format for testing. Reformat it, run your identical testing > program, and see if it fails in the same way. > > Also I liked Richard's point: are you testing on a cheap Flash drive ? > Perhaps one intended only for use with FAT and therefore only tested for use > with FAT ? Can you experts explains why a cheap Flash drive can harm an application such as sqlite? Does you mean these cheap drive was cheating with ECC? Otherwise, filesystem should be able to capture IO error (fix it or forward), right? > > > [snip] > > > > Another thing is very suspicious: after a while of running the test > > program, I can see from my syslog that there are a log of warning messages, > > something like: Yaffs2 trunk was not erased. > > Well, that sounds like it might be relevant. See if you can find why yaffs2 > should write a report like that. What does it indicate about what happened ? > > > On one hand, I am not > > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > > also cannot prove this really means bad things since the program at that > > moment was still running fine. > > The chances that this is a sign of a fault in SQLite3 are extremely low. > There are literally millions of SQLite3 installations on all sorts of > hardware. It's part of the OSes that run all the popular designs of > smartphone (iPhone, Android, Windows Phone) all of which, of course, use > Flash Memory for storage. It's possible that the VFS you're using is not > compatible with yaffs2, and the way to test that is what we've discussed > above. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- woody I can't go back to yesterday - because I was a different person then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 10:59:25AM -0500, Thanumalayan Sankaranarayana Pillai wrote: > Hi Woody, > > If the log messages that you see are "chunk nnn not erased", it might > probably be an error between your NAND device and YAFFS2 (according to a > couple of Google searches). > > Ref: http://osdir.com/ml/linux.file-systems.yaffs/2006-09/msg00033.html > > -- > Thanu > Thanks for the link. How do you guys think about this: if NAND has an IO problem, Yaffs2 should recover it or forward the error to applications, right? Otherwise, I cannot understand what's the right responsibilities of a fs or device driver. > > On Mon, May 27, 2013 at 10:22 AM, Woody Wu wrote: > > > Richard, > > > > If Yaffs2 is the cause, how can I write an effective test to exposure it? > > I straced my test sqlite test program and wanted to understand its IO > > behavior pattern. The difficulty is that I cannot run strace on my ARM > > target too long since the log will fill the limited memory, but if I don't > > run it enough long, I am not sure it expressed enough information. By far, > > as what I saw in my strace output after serveral minutes running of the > > program, I only saw a lot of llseek() + write() paire on the main database > > file as well as its journal. From your understanding of sqlite, could you > > tell me what's the file IO pattern as result of running the test program? > > > > By the way, although my test code contains a logic to delete some records > > after the table have already filled with enough many rows, but many of my > > tests results shown that the database corruption happened before the delete > > operation get chance to run. Maybe this can simply you analysis. > > > > Another thing is very suspicious: after a while of running the test > > program, I can see from my syslog that there are a log of warning messages, > > something like: Yaffs2 trunk was not erased. On one hand, I am not > > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > > also cannot prove this really means bad things since the program at that > > moment was still running fine. > > > > -woody > > > > > > > > > > On 27 May 2013 20:47, Richard Hipp wrote: > > > > > On Mon, May 27, 2013 at 8:28 AM, Simon Slavin > > > wrote: > > > > > > > > > > > On 27 May 2013, at 1:25pm, Clemens Ladisch wrote: > > > > > > > > > Woody Wu wrote: > > > > >> I have a testing code, attached in this email, if continuously run > > it > > > > for > > > > >> 20 - 40 hours, the sqlite database will be corrupted. > > > > >> > > > > >> The application is running on an ARM Linux system with Yaffs2 > > > filesystem > > > > >> on NAND flashes. > > > > > > > > > > I'd guess that the flash is not very reliable. > > > > > > > > I test on Flash drives. No problems. But I don't use yaffs2, I use > > FAT > > > > and HFS+. I'm not saying there's anything wrong with yaffs2. > > > > > > > > > > (1) Do a web search for "counterfeit flash". There are a lot of dodgy > > > flash drives and flash memory chips in circulation. Simon might be using > > > good chips whereas Woody might have a bad one. > > > > > > (2) I'm less willing to give yaffs2 a pass. Yaffs2 bypasses much of the > > > common filesystem code in the Linux kernel and attempts to do its own > > > thing. This has been a persistent source of issues. I recommend that > > Woody > > > try Ext4 instead. > > > > > > All that said: I have compiled Woody's test program and am running it > > now > > > on a Linux workstation. We'll see what happens after 40 hours > > > > > > -- > > > D. Richard Hipp > > > d...@sqlite.org > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > Life is the only flaw in an otherwise perfect nonexistence > > -- Schopenhauer > > > > narke > > public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com) > > ___ > > 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 -- woody I can't go back to yesterday - because I was a different person then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 11:33:08AM -0400, Richard Hipp wrote: > On Mon, May 27, 2013 at 11:22 AM, Woody Wu wrote: > > > Richard, > > > > If Yaffs2 is the cause, how can I write an effective test to exposure it? > > > > Do you really need to prove that yaffs2 is at fault? Try this experiment: > mention that you might be having problems with yaffs2 to anybody else who > has done a lot of work with yaffs2 and watch them nod knowingly Hope I can shot their nods and send pictures to my boss :) > > Android switched from yaffs2 to ext4 for a reason, you know. > > Another thing is very suspicious: after a while of running the test > > program, I can see from my syslog that there are a log of warning messages, > > something like: Yaffs2 trunk was not erased. On one hand, I am not > > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > > also cannot prove this really means bad things since the program at that > > moment was still running fine. > > > > SQLite never writes to the system log. And even if it did, SQLite does not > know what filesystem it is talking to. Hence, any messages you see in the > system log that mention "yaffs2" are very likely coming from yaffs2 itself. > > I'm thinking you need to take a clue from the (very smart and very capable) > Android team and change to ext4 on your embedded system. > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- woody I can't go back to yesterday - because I was a different person then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 04:31:25PM +0100, Simon Slavin wrote: > > On 27 May 2013, at 4:22pm, Woody Wu wrote: > > > If Yaffs2 is the cause, how can I write an effective test to exposure it? > > Do you have an opportunity to format the same drive in a different > format ? I'm not telling you to change your long-term practises, just > to try a different format for testing. Reformat it, run your > identical testing program, and see if it fails in the same way. > At this moment, reformating to other format (e.g, ext4) is not an option. Since the device and the system are built by external provider, we just put sqlite and apps on it and sell. But this is a valuable suggestion that I will consider to talk to my vendor. Just it can't rescue current situation in short time. > Also I liked Richard's point: are you testing on a cheap Flash drive ? > Perhaps one intended only for use with FAT and therefore only tested > for use with FAT ? > > > [snip] > > > > Another thing is very suspicious: after a while of running the test > > program, I can see from my syslog that there are a log of warning > > messages, something like: Yaffs2 trunk was not erased. > > Well, that sounds like it might be relevant. See if you can find why > yaffs2 should write a report like that. What does it indicate about > what happened ? > > > On one hand, I am not > > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > > also cannot prove this really means bad things since the program at that > > moment was still running fine. > The chances that this is a sign of a fault in SQLite3 are extremely > low. There are literally millions of SQLite3 installations on all > sorts of hardware. It's part of the OSes that run all the popular > designs of smartphone (iPhone, Android, Windows Phone) all of which, > of course, use Flash Memory for storage. It's possible that the VFS > you're using is not compatible with yaffs2, and the way to test that > is what we've discussed above. How can I tell what's the current VFS that I was using? Thanks. > > Simon. ___ sqlite-users > mailing list sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- woody I can't go back to yesterday - because I was a different person then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
Hi Woody, If the log messages that you see are "chunk nnn not erased", it might probably be an error between your NAND device and YAFFS2 (according to a couple of Google searches). Ref: http://osdir.com/ml/linux.file-systems.yaffs/2006-09/msg00033.html -- Thanu On Mon, May 27, 2013 at 10:22 AM, Woody Wu wrote: > Richard, > > If Yaffs2 is the cause, how can I write an effective test to exposure it? > I straced my test sqlite test program and wanted to understand its IO > behavior pattern. The difficulty is that I cannot run strace on my ARM > target too long since the log will fill the limited memory, but if I don't > run it enough long, I am not sure it expressed enough information. By far, > as what I saw in my strace output after serveral minutes running of the > program, I only saw a lot of llseek() + write() paire on the main database > file as well as its journal. From your understanding of sqlite, could you > tell me what's the file IO pattern as result of running the test program? > > By the way, although my test code contains a logic to delete some records > after the table have already filled with enough many rows, but many of my > tests results shown that the database corruption happened before the delete > operation get chance to run. Maybe this can simply you analysis. > > Another thing is very suspicious: after a while of running the test > program, I can see from my syslog that there are a log of warning messages, > something like: Yaffs2 trunk was not erased. On one hand, I am not > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > also cannot prove this really means bad things since the program at that > moment was still running fine. > > -woody > > > > > On 27 May 2013 20:47, Richard Hipp wrote: > > > On Mon, May 27, 2013 at 8:28 AM, Simon Slavin > > wrote: > > > > > > > > On 27 May 2013, at 1:25pm, Clemens Ladisch wrote: > > > > > > > Woody Wu wrote: > > > >> I have a testing code, attached in this email, if continuously run > it > > > for > > > >> 20 - 40 hours, the sqlite database will be corrupted. > > > >> > > > >> The application is running on an ARM Linux system with Yaffs2 > > filesystem > > > >> on NAND flashes. > > > > > > > > I'd guess that the flash is not very reliable. > > > > > > I test on Flash drives. No problems. But I don't use yaffs2, I use > FAT > > > and HFS+. I'm not saying there's anything wrong with yaffs2. > > > > > > > (1) Do a web search for "counterfeit flash". There are a lot of dodgy > > flash drives and flash memory chips in circulation. Simon might be using > > good chips whereas Woody might have a bad one. > > > > (2) I'm less willing to give yaffs2 a pass. Yaffs2 bypasses much of the > > common filesystem code in the Linux kernel and attempts to do its own > > thing. This has been a persistent source of issues. I recommend that > Woody > > try Ext4 instead. > > > > All that said: I have compiled Woody's test program and am running it > now > > on a Linux workstation. We'll see what happens after 40 hours > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Life is the only flaw in an otherwise perfect nonexistence > -- Schopenhauer > > narke > public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com) > ___ > 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] Database Corrupted!
On Mon, May 27, 2013 at 11:31 AM, Simon Slavin wrote: > > > On one hand, I am not > > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > > also cannot prove this really means bad things since the program at that > > moment was still running fine. > > The chances that this is a sign of a fault in SQLite3 are extremely low. > There are literally millions of SQLite3 installations on all sorts of > hardware. Nay, Simon. Billions, not millions! ;-) > It's part of the OSes that run all the popular designs of smartphone > (iPhone, Android, Windows Phone) all of which, of course, use Flash Memory > for storage. It's possible that the VFS you're using is not compatible > with yaffs2, and the way to test that is what we've discussed above. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 11:22 AM, Woody Wu wrote: > Richard, > > If Yaffs2 is the cause, how can I write an effective test to exposure it? > Do you really need to prove that yaffs2 is at fault? Try this experiment: mention that you might be having problems with yaffs2 to anybody else who has done a lot of work with yaffs2 and watch them nod knowingly Android switched from yaffs2 to ext4 for a reason, you know. Another thing is very suspicious: after a while of running the test > program, I can see from my syslog that there are a log of warning messages, > something like: Yaffs2 trunk was not erased. On one hand, I am not > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > also cannot prove this really means bad things since the program at that > moment was still running fine. > SQLite never writes to the system log. And even if it did, SQLite does not know what filesystem it is talking to. Hence, any messages you see in the system log that mention "yaffs2" are very likely coming from yaffs2 itself. I'm thinking you need to take a clue from the (very smart and very capable) Android team and change to ext4 on your embedded system. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On 27 May 2013, at 4:22pm, Woody Wu wrote: > If Yaffs2 is the cause, how can I write an effective test to exposure it? Do you have an opportunity to format the same drive in a different format ? I'm not telling you to change your long-term practises, just to try a different format for testing. Reformat it, run your identical testing program, and see if it fails in the same way. Also I liked Richard's point: are you testing on a cheap Flash drive ? Perhaps one intended only for use with FAT and therefore only tested for use with FAT ? > [snip] > > Another thing is very suspicious: after a while of running the test > program, I can see from my syslog that there are a log of warning messages, > something like: Yaffs2 trunk was not erased. Well, that sounds like it might be relevant. See if you can find why yaffs2 should write a report like that. What does it indicate about what happened ? > On one hand, I am not > sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I > also cannot prove this really means bad things since the program at that > moment was still running fine. The chances that this is a sign of a fault in SQLite3 are extremely low. There are literally millions of SQLite3 installations on all sorts of hardware. It's part of the OSes that run all the popular designs of smartphone (iPhone, Android, Windows Phone) all of which, of course, use Flash Memory for storage. It's possible that the VFS you're using is not compatible with yaffs2, and the way to test that is what we've discussed above. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
Richard, If Yaffs2 is the cause, how can I write an effective test to exposure it? I straced my test sqlite test program and wanted to understand its IO behavior pattern. The difficulty is that I cannot run strace on my ARM target too long since the log will fill the limited memory, but if I don't run it enough long, I am not sure it expressed enough information. By far, as what I saw in my strace output after serveral minutes running of the program, I only saw a lot of llseek() + write() paire on the main database file as well as its journal. From your understanding of sqlite, could you tell me what's the file IO pattern as result of running the test program? By the way, although my test code contains a logic to delete some records after the table have already filled with enough many rows, but many of my tests results shown that the database corruption happened before the delete operation get chance to run. Maybe this can simply you analysis. Another thing is very suspicious: after a while of running the test program, I can see from my syslog that there are a log of warning messages, something like: Yaffs2 trunk was not erased. On one hand, I am not sure if this is caused by sqlite or Yaffs2 itself, on the other hand, I also cannot prove this really means bad things since the program at that moment was still running fine. -woody On 27 May 2013 20:47, Richard Hipp wrote: > On Mon, May 27, 2013 at 8:28 AM, Simon Slavin > wrote: > > > > > On 27 May 2013, at 1:25pm, Clemens Ladisch wrote: > > > > > Woody Wu wrote: > > >> I have a testing code, attached in this email, if continuously run it > > for > > >> 20 - 40 hours, the sqlite database will be corrupted. > > >> > > >> The application is running on an ARM Linux system with Yaffs2 > filesystem > > >> on NAND flashes. > > > > > > I'd guess that the flash is not very reliable. > > > > I test on Flash drives. No problems. But I don't use yaffs2, I use FAT > > and HFS+. I'm not saying there's anything wrong with yaffs2. > > > > (1) Do a web search for "counterfeit flash". There are a lot of dodgy > flash drives and flash memory chips in circulation. Simon might be using > good chips whereas Woody might have a bad one. > > (2) I'm less willing to give yaffs2 a pass. Yaffs2 bypasses much of the > common filesystem code in the Linux kernel and attempts to do its own > thing. This has been a persistent source of issues. I recommend that Woody > try Ext4 instead. > > All that said: I have compiled Woody's test program and am running it now > on a Linux workstation. We'll see what happens after 40 hours > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Life is the only flaw in an otherwise perfect nonexistence -- Schopenhauer narke public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 8:28 AM, Simon Slavin wrote: > > On 27 May 2013, at 1:25pm, Clemens Ladisch wrote: > > > Woody Wu wrote: > >> I have a testing code, attached in this email, if continuously run it > for > >> 20 - 40 hours, the sqlite database will be corrupted. > >> > >> The application is running on an ARM Linux system with Yaffs2 filesystem > >> on NAND flashes. > > > > I'd guess that the flash is not very reliable. > > I test on Flash drives. No problems. But I don't use yaffs2, I use FAT > and HFS+. I'm not saying there's anything wrong with yaffs2. > (1) Do a web search for "counterfeit flash". There are a lot of dodgy flash drives and flash memory chips in circulation. Simon might be using good chips whereas Woody might have a bad one. (2) I'm less willing to give yaffs2 a pass. Yaffs2 bypasses much of the common filesystem code in the Linux kernel and attempts to do its own thing. This has been a persistent source of issues. I recommend that Woody try Ext4 instead. All that said: I have compiled Woody's test program and am running it now on a Linux workstation. We'll see what happens after 40 hours -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On 27 May 2013, at 1:25pm, Clemens Ladisch wrote: > Woody Wu wrote: >> I have a testing code, attached in this email, if continuously run it for >> 20 - 40 hours, the sqlite database will be corrupted. >> >> The application is running on an ARM Linux system with Yaffs2 filesystem >> on NAND flashes. > > I'd guess that the flash is not very reliable. I test on Flash drives. No problems. But I don't use yaffs2, I use FAT and HFS+. I'm not saying there's anything wrong with yaffs2. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
Woody Wu wrote: > I have a testing code, attached in this email, if continuously run it for > 20 - 40 hours, the sqlite database will be corrupted. > > The application is running on an ARM Linux system with Yaffs2 filesystem > on NAND flashes. I'd guess that the flash is not very reliable. Does the same happen on a desktop machine (with an HD, an SSD, or on a RAM disk)? > if (sql_code == SQLITE_BUSY || SQLITE_LOCKED) This is not how comparisons work in C. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Corrupted!
On Mon, May 27, 2013 at 11:08:57AM +0800, Woody Wu wrote:
> Hi, List
>
> Probably this is another case of database corrupted. I read the
> documents about this topic and think I did not make same mistakes
> described in that 'how to corrupt ...' documentation.
>
> I have a testing code, attached in this email, if continuously run it for
> 20 - 40 hours, the sqlite database will be corrupted. But the code
> itself is very simple, it just keeping inserting a lot of rows. After
> inserted some long time, it begin to delete some rows and continues to
> insert.
>
> There is no multi-threads, no concurrent database operations. Every
> instance of the application instance owns exclusively its database file.
>
> The application is running on an ARM Linux system with Yaffs2 filesystem
> on NAND flashes.
>
> Below are sample database error:
>
> --
> sqlite> pragma integrity_check;
> *** in database main ***
> Multiple uses for byte 752 of page 20506
> On tree page 21363 cell 27: invalid page number 16843521
> Multiple uses for byte 676 of page 21363
> Fragmentation of 8 bytes reported as 0 on page 21363
> Multiple uses for byte 392 of page 28676
> rowid 518219 missing from index testidx
> Error: database disk image is malformed
> -
>
> Please have a look at my attached code and be kindly give me a clue on
> what are possible causes?
>
> Thanks in advance.
>
> --
> woody
> I can't go back to yesterday - because I was a different person then.
Sorry, I forget to tell you, the sqlite version is 3.7.17 (autoconfig).
I only added one DEFS in the resulted Makefile: -DHAVE_POSIX_FALLOCATE=0
since my cross tool chain doesn't have posix_fallocate()
implementation. Attached also please refer my Makefile.
> #define _GNU_SOURCE
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
> #include
>
> #define MAX_SQL_LEN 150
> #define SQL_CREATE_TESTTBL \
> "create table if not exists testtbl(nComID number(2), nMeterID number(2),
> nMPID number(2), nData number, nStatus number, nTimeReq number);"
> #define SQL_CREATE_TESTIDX \
> "create index if not exists testidx on testtbl(nTimeReq asc,nComID
> asc,nMeterID asc,nMPID asc);"
> #define SQL_BEGIN_TRANS "BEGIN;"
> #define SQL_COMMIT_TRANS "COMMIT;"
> #define SQL_ROLLBACK_TRANS "ROLLBACK;"
>
> static char *db_fname = "test.db";
> static sqlite3 *conn;
> static int sql_code;
> static char *sql_msg;
> static int fill_time;
> static int max_rec_age = 2048;
> static bool auto_vacuum;
> static int cache_size = 0;
> static bool use_syslog;
> static bool verbose;
> static unsigned max_fill_count;
>
> static void mlog(int opt, const char *fmt, ...)
> {
> if (! verbose && LOG_PRI(opt) >= LOG_DEBUG)
> return;
>
> char s[256];
> snprintf(s, sizeof(s), "[%s] %s", basename(db_fname), fmt);
> fmt = s;
> va_list args;
> va_start(args, fmt);
> if (use_syslog)
> vsyslog(opt, fmt, args);
> else
> vprintf(fmt, args);
> va_end(args);
> }
>
> static bool sql_exec(const char *sql)
> {
> mlog(LOG_DEBUG, "sql_exec: %s\n", sql);
> while (true) {
> sql_code = sqlite3_exec(conn,
> sql, NULL, NULL, &sql_msg);
> if (sql_code == SQLITE_OK)
> return true;
> else {
> mlog(LOG_NOTICE, "%s: %s\n", sql_msg, sql);
> if (sql_msg)
> sqlite3_free(sql_msg);
> if (sql_code == SQLITE_BUSY || SQLITE_LOCKED)
> sleep(1);
> else
> return false;
> }
> }
> }
>
> static bool sql_select(const char *sql, char ***result)
> {
> mlog(LOG_DEBUG, "sql_select: %s\n", sql);
> while (true) {
> sql_code = sqlite3_get_table(conn, sql,
> result, NULL, NULL, &sql_msg);
> if (sql_code == SQLITE_OK)
> return true;
> else {
> mlog(LOG_NOTICE, "%s: %s\n", sql_msg, sql);
> if (sql_msg)
> sqlite3_free(sql_msg);
> if (*result)
> sqlite3_free_table(*result);
> *result = NULL;
> if (sql_code == SQLITE_BUSY || SQLITE_LOCKED)
> sleep(1);
> else
> return false;
> }
> }
> }
>
> static bool get_min_time(int *time)
> {
> char **result_tbl;
>
> if (! sql_select("SELECT MIN(nTimeReq) FROM testtbl;", &result_tbl))
> return false;
>
> if (! result_tbl[1])
> *time = 0;
> else
> *time = atoi(result_tbl[1]);
>
> sqlite3_free_table(result_tbl);
> return true;
> }
>
> static bool get_max_time(int *time)
> {
> char **result_tbl;
>
> if (! sql_select("SELECT MAX(nTimeReq) FROM testtbl;", &result_tbl))
> return false;
>
> if (!
[sqlite] Database Corrupted!
Hi, List
Probably this is another case of database corrupted. I read the
documents about this topic and think I did not make same mistakes
described in that 'how to corrupt ...' documentation.
I have a testing code, attached in this email, if continuously run it for
20 - 40 hours, the sqlite database will be corrupted. But the code
itself is very simple, it just keeping inserting a lot of rows. After
inserted some long time, it begin to delete some rows and continues to
insert.
There is no multi-threads, no concurrent database operations. Every
instance of the application instance owns exclusively its database file.
The application is running on an ARM Linux system with Yaffs2 filesystem
on NAND flashes.
Below are sample database error:
--
sqlite> pragma integrity_check;
*** in database main ***
Multiple uses for byte 752 of page 20506
On tree page 21363 cell 27: invalid page number 16843521
Multiple uses for byte 676 of page 21363
Fragmentation of 8 bytes reported as 0 on page 21363
Multiple uses for byte 392 of page 28676
rowid 518219 missing from index testidx
Error: database disk image is malformed
-
Please have a look at my attached code and be kindly give me a clue on
what are possible causes?
Thanks in advance.
--
woody
I can't go back to yesterday - because I was a different person then.
#define _GNU_SOURCE
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#define MAX_SQL_LEN 150
#define SQL_CREATE_TESTTBL \
"create table if not exists testtbl(nComID number(2), nMeterID number(2),
nMPID number(2), nData number, nStatus number, nTimeReq number);"
#define SQL_CREATE_TESTIDX \
"create index if not exists testidx on testtbl(nTimeReq asc,nComID
asc,nMeterID asc,nMPID asc);"
#define SQL_BEGIN_TRANS "BEGIN;"
#define SQL_COMMIT_TRANS "COMMIT;"
#define SQL_ROLLBACK_TRANS "ROLLBACK;"
static char *db_fname = "test.db";
static sqlite3 *conn;
static int sql_code;
static char *sql_msg;
static int fill_time;
static int max_rec_age = 2048;
static bool auto_vacuum;
static int cache_size = 0;
static bool use_syslog;
static bool verbose;
static unsigned max_fill_count;
static void mlog(int opt, const char *fmt, ...)
{
if (! verbose && LOG_PRI(opt) >= LOG_DEBUG)
return;
char s[256];
snprintf(s, sizeof(s), "[%s] %s", basename(db_fname), fmt);
fmt = s;
va_list args;
va_start(args, fmt);
if (use_syslog)
vsyslog(opt, fmt, args);
else
vprintf(fmt, args);
va_end(args);
}
static bool sql_exec(const char *sql)
{
mlog(LOG_DEBUG, "sql_exec: %s\n", sql);
while (true) {
sql_code = sqlite3_exec(conn,
sql, NULL, NULL, &sql_msg);
if (sql_code == SQLITE_OK)
return true;
else {
mlog(LOG_NOTICE, "%s: %s\n", sql_msg, sql);
if (sql_msg)
sqlite3_free(sql_msg);
if (sql_code == SQLITE_BUSY || SQLITE_LOCKED)
sleep(1);
else
return false;
}
}
}
static bool sql_select(const char *sql, char ***result)
{
mlog(LOG_DEBUG, "sql_select: %s\n", sql);
while (true) {
sql_code = sqlite3_get_table(conn, sql,
result, NULL, NULL, &sql_msg);
if (sql_code == SQLITE_OK)
return true;
else {
mlog(LOG_NOTICE, "%s: %s\n", sql_msg, sql);
if (sql_msg)
sqlite3_free(sql_msg);
if (*result)
sqlite3_free_table(*result);
*result = NULL;
if (sql_code == SQLITE_BUSY || SQLITE_LOCKED)
sleep(1);
else
return false;
}
}
}
static bool get_min_time(int *time)
{
char **result_tbl;
if (! sql_select("SELECT MIN(nTimeReq) FROM testtbl;", &result_tbl))
return false;
if (! result_tbl[1])
*time = 0;
else
*time = atoi(result_tbl[1]);
sqlite3_free_table(result_tbl);
return true;
}
static bool get_max_time(int *time)
{
char **result_tbl;
if (! sql_select("SELECT MAX(nTimeReq) FROM testtbl;", &result_tbl))
return false;
if (! result_tbl[1])
*time = 0;
else
*time = atoi(result_tbl[1]);
sqlite3_free_table(result_tbl);
return true;
}
static bool init_fill_time(void)
{
return get_max_time(&fill_time);
}
static bool prepare(void)
{
char sql_set_cache[MAX_SQL_LEN];
mlog(LOG_INFO, "prepare database ...\n");
if (auto_vacuum) {
mlog(LOG_INFO, "auto_vacuum = 1\n");
if (! sql_exec("PRAGMA auto_vacuum = 1;"))
return false;
}
if (cache_size > 0) {
mlog(LOG_INFO, "using cache_size = %d\n", cache_size);
snprintf(sql_set_cache, sizeof(sql_set_cache), "PRAGMA cache_size =
%d;"
Re: [sqlite] Sqlite database corrupted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/10/2011 07:58 AM, mcnamaragio wrote: > Could this have caused database corruption? SQLite does not have ordinary commands that will corrupt the database. How to corrupt the database is listed in the doc: http://www.sqlite.org/lockingv3.html#how_to_corrupt The chance of there being a bug in SQLite is extremely small. First of all some of the other billions of users would have noticed. Secondly the testing is extraordinary: http://www.sqlite.org/testing.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2h+vEACgkQmOOfHg372QRZtgCffHGOW9XxEiJC/r6A5Fw1l4b7 MNAAnj8mTgriEcxuLd5D6kaw8CCmYw/2 =nlG8 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite database corrupted
Thanks. I have found that the select statement was issued inside a transaction. Could this cause sqlite to make changes to the database? I also observed that if I mark the database as readonly and issue a select statement inside a transaction (what the application was doing) I get "attempt to write a readonly database" error so it looks like sqlite is trying to have write access to the database even thought it is not needed. Could this have caused database corruption? Simon Slavin-3 wrote: > > On 9 Apr 2011, at 02:47 PM, giorgi giorgi wrote: > >> The application is only issuing select statements (no >> insert/update/delete >> or vacuum) so I cannot understand what could have caused database >> corruption. > > Almost definitely a problem with your hardware, or possibly an > operating-system glitch. I'm not going to pretend that SQLite is bug-free > but if you're not executing write commands it has no reason to write > anything at all. > > Simon > -- > Sent while away from my computer. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Sqlite-database-corrupted-tp31359173p31364155.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite database corrupted
Thanks. I have found that the select statement was issued inside a transaction. Could this cause sqlite to make changes to the database? I also observed that if I mark the database as readonly and issue a select statement inside a transaction (what the application was doing) I get "attempt to write a readonly database" error so it looks like sqlite is trying to have write access to the database even thought it is not needed. Could this have caused database corruption? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite database corrupted
On 9 Apr 2011, at 02:47 PM, giorgi giorgi wrote: > The application is only issuing select statements (no insert/update/delete > or vacuum) so I cannot understand what could have caused database > corruption. Almost definitely a problem with your hardware, or possibly an operating-system glitch. I'm not going to pretend that SQLite is bug-free but if you're not executing write commands it has no reason to write anything at all. Simon -- Sent while away from my computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite database corrupted
Hi, I have developed an application which is using sqlite database for querying data from the database which ships with it. Several days ago the database somehow got corrupted. The application is only issuing select statements (no insert/update/delete or vacuum) so I cannot understand what could have caused database corruption. The database is accessed via .Net provider and is password protected. Database size is about 500MB. Sqlite version is 3.6.23.1 There is only one table, one index and one system table produced by Analyze command. No triggers, no joins, no foreign keys. The result from integrity check is: *** in database main *** Page 9: btreeInitPage() returns error code 11 On tree page 5 cell 13: Child page depth differs On tree page 5 cell 14: Child page depth differs Page 44355 is never used Page 44356 is never used Page 44357 is never used Page 44358 is never used Page 44359 is never used Page 44360 is never used . . . Page 44577 is never used Page 44578 is never used Page 44579 is never used Page 44580 is never used Could the corruption have been caused by sqlite engine? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corrupted
And your version is. Is your app a stand-alone you can share? If you 've discovered such a bug the community would be MUCH appreciative if you could share so it can be fixed (or at least identified to allay concerns we all may have over thread safety). Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Marco Era Sent: Thu 12/2/2010 4:37 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] Database corrupted Hello, I'm doing some tests to get the best out of the threading models for sqlite. All tests are done on a multicore processor, windows XP; sqlite is working in WAL mode. I've got a database image corrupted when using SQLITE_THREADSAFE=2 and two threads, each opening a private connection to the same database. sqlite3* pointers are not shared, each thread has his own. Compiling with SQLITE_THREADSAFE=1 seems to work well, but I will test it more. Am is missing something? Do I have to synchronize the threads? Thanks in advance. ___ 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
[sqlite] Database corrupted
Hello, I'm doing some tests to get the best out of the threading models for sqlite. All tests are done on a multicore processor, windows XP; sqlite is working in WAL mode. I've got a database image corrupted when using SQLITE_THREADSAFE=2 and two threads, each opening a private connection to the same database. sqlite3* pointers are not shared, each thread has his own. Compiling with SQLITE_THREADSAFE=1 seems to work well, but I will test it more. Am is missing something? Do I have to synchronize the threads? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R: [sqlite] Is this SQLite database corrupted or not ?
Thank you. After running VACUUM, "pragma integrity check" now simply returns "OK" (although some of the data in the DB appears to be corrupt). Anyway it was just a test database and no important information was lost, I posted my message just to learn about the odd output from "pragma integrity check". Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:mercoledì 18 gennaio 2006 12.50 A: sqlite-users@sqlite.org Oggetto: Re: [sqlite] Is this SQLite database corrupted or not ? Zibetti Paolo <[EMAIL PROTECTED]> wrote: > I have an SQLite 3 database for which the "pragma integrity check" returns: > > integrity_check > *** in database main *** > On tree page 17 cell 0: 2nd reference to page 164 > Page 50 is never used > Page 53 is never used > Page 57 is never used > Page 58 is never used > Page 59 is never used > Page 60 is never used > long list of similar rows... > Page 202 is never used > Page 203 is never used > Page 204 is never used > Page 205 is never used > ok > > The final word says "OK", so it seems the database is not corrupt, but what > do the previous lines mean ? Despite the "OK", is there something wrong in > my DB ? > Will the unused pages listed in the output be automatically reused in the > future ? Should I take some action to recover the database ? > > I'm using Sqlite 3.2.7 on a Windows platform. > > Can anybody help ? > You database is corrupt, but if all you see is "page never used" messages, a VACUUM will likely recover it. The extra "ok" a the end looks like a bug in the integrity_check pragma. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Is this SQLite database corrupted or not ?
Zibetti Paolo <[EMAIL PROTECTED]> wrote: > I have an SQLite 3 database for which the "pragma integrity check" returns: > > integrity_check > *** in database main *** > On tree page 17 cell 0: 2nd reference to page 164 > Page 50 is never used > Page 53 is never used > Page 57 is never used > Page 58 is never used > Page 59 is never used > Page 60 is never used > long list of similar rows... > Page 202 is never used > Page 203 is never used > Page 204 is never used > Page 205 is never used > ok > > The final word says "OK", so it seems the database is not corrupt, but what > do the previous lines mean ? Despite the "OK", is there something wrong in > my DB ? > Will the unused pages listed in the output be automatically reused in the > future ? Should I take some action to recover the database ? > > I'm using Sqlite 3.2.7 on a Windows platform. > > Can anybody help ? > You database is corrupt, but if all you see is "page never used" messages, a VACUUM will likely recover it. The extra "ok" a the end looks like a bug in the integrity_check pragma. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Is this SQLite database corrupted or not ?
I have an SQLite 3 database for which the "pragma integrity check" returns: integrity_check *** in database main *** On tree page 17 cell 0: 2nd reference to page 164 Page 50 is never used Page 53 is never used Page 57 is never used Page 58 is never used Page 59 is never used Page 60 is never used ...long list of similar rows... Page 202 is never used Page 203 is never used Page 204 is never used Page 205 is never used ok The final word says "OK", so it seems the database is not corrupt, but what do the previous lines mean ? Despite the "OK", is there something wrong in my DB ? Will the unused pages listed in the output be automatically reused in the future ? Should I take some action to recover the database ? I'm using Sqlite 3.2.7 on a Windows platform. Can anybody help ? Thanks, bye
