Re: [sqlite] 3.7.7.1 database corruption
Hi Alexandr, the mailing list doesn't allow attachments. Please send us the output of pragma integrity_check; on the corrupted database file. Also, is the database used in WAL mode? Best regards, Filip Navara 2011/11/14 Alexandr Němec a.ne...@atlas.cz: Dear all, we are using SQLite in our projects. We are using the 3.7.7.1 version compiled on Windows platform from the amalgamation file. One of our servers crashed badly (power outage) and after the server restarted, we saw that the database was corrupt (all queries we tried return SQLITE_CORRUPT). We had a look at http://www.sqlite.org/howtocorrupt.html and verified, that the hardware (disk, memory) of the server should be ok - we used some hardware tools to check. In the past we already had some crash situations and SQLite could recover from the crash nicely, so we were surprised that after this crash the database was corrupt. In fact, we cant create a repro, because we do not know, what the server was exactly doing, so it might be difficult to find the reason, but we saved the corrupted database in the hope, that somebody from the develpers (maybe) could put some light on this problem. Because the database is rather small and is used to save some configuration data only, I have att ached it to this message. Thanks for any suggestions. Alex ___ 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] 3.7.7.1 database corruption
Dear Filip, thanks for your reply. I thought that small attachments will go trough. If I run sqlite3 data.db pragma integrity_check; I receive this message only: Error: database disk image is malformed. Anyway, you can download the database here: http://www.u-consult.cz/db/server.db No, WAL mode is not used. Thanks very much for your help. Alex __ Od: Filip Navara Komu: General Discussion of SQLite Database Datum: 14.11.2011 11:17 Předmět: Re: [sqlite] 3.7.7.1 database corruption Hi Alexandr, the mailing list doesn't allow attachments. Please send us the output of pragma integrity_check; on the corrupted database file. Also, is the database used in WAL mode? Best regards, Filip Navara 2011/11/14 Alexandr Němec : Dear all, we are using SQLite in our projects. We are using the 3.7.7.1 version compiled on Windows platform from the amalgamation file. One of our servers crashed badly (power outage) and after the server restarted, we saw that the database was corrupt (all queries we tried return SQLITE_CORRUPT). We had a look at http://www.sqlite.org/howtocorrupt.html and verified, that the hardware (disk, memory) of the server should be ok - we used some hardware tools to check. In the past we already had some crash situations and SQLite could recover from the crash nicely, so we were surprised that after this crash the database was corrupt. In fact, we cant create a repro, because we do not know, what the server was exactly doing, so it might be difficult to find the reason, but we saved the corrupted database in the hope, that somebody from the develpers (maybe) could put some light on this problem. Because the database is rather small and is used to save some configuration data only, I have att ached it to this message. Thanks for any suggestions. Alex ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.7.1 database corruption
2011/11/14 Alexandr Němec a.ne...@atlas.cz Dear Filip, thanks for your reply. I thought that small attachments will go trough. If I run sqlite3 data.db pragma integrity_check; I receive this message only: Error: database disk image is malformed. Anyway, you can download the database here: http://www.u-consult.cz/db/** server.db http://www.u-consult.cz/db/server.db This database file has been truncated. The last 6 pages (6144 bytes) are missing. No, WAL mode is not used. Thanks very much for your help. Alex __**__**__ Od: Filip Navara Komu: General Discussion of SQLite Database Datum: 14.11.2011 11:17 Předmět: Re: [sqlite] 3.7.7.1 database corruption Hi Alexandr, the mailing list doesn't allow attachments. Please send us the output of pragma integrity_check; on the corrupted database file. Also, is the database used in WAL mode? Best regards, Filip Navara 2011/11/14 Alexandr Němec : Dear all, we are using SQLite in our projects. We are using the 3.7.7.1 version compiled on Windows platform from the amalgamation file. One of our servers crashed badly (power outage) and after the server restarted, we saw that the database was corrupt (all queries we tried return SQLITE_CORRUPT). We had a look at http://www.sqlite.org/**howtocorrupt.htmlhttp://www.sqlite.org/howtocorrupt.htmland verified, that the hardware (disk, memory) of the server should be ok - we used some hardware tools to check. In the past we already had some crash situations and SQLite could recover from the crash nicely, so we were surprised that after this crash the database was corrupt. In fact, we cant create a repro, because we do not know, what the server was exactly doing, so it might be difficult to find the reason, but we saved the corrupted database in the hope, that somebody from the develpers (maybe) could put some light on this problem. Because the database is rather small and is used to save some configuration data only, I have att ached it to this message. Thanks for any suggestions. Alex __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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-**usershttp://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 -- 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] 3.7.7.1 database corruption
Dear Richard, huh, thanks very much for the information, I see. But anybody having an idea how this can happen? There was a power outage and after restart 6 pages of the database file are missing... We checked the disk carefuly, no bad sector etc. What can be the reason for this? Thanks once more. Alex __ Od: Richard Hipp Komu: General Discussion of SQLite Database Datum: 14.11.2011 14:03 Předmět: Re: [sqlite] 3.7.7.1 database corruption 2011/11/14 Alexandr Němec Dear Filip, thanks for your reply. I thought that small attachments will go trough. If I run sqlite3 data.db pragma integrity_check; I receive this message only: Error: database disk image is malformed. Anyway, you can download the database here: http://www.u-consult.cz/db/** server.db This database file has been truncated. The last 6 pages (6144 bytes) are missing. No, WAL mode is not used. Thanks very much for your help. Alex __**__**__ Od: Filip Navara Komu: General Discussion of SQLite Database Datum: 14.11.2011 11:17 Předmět: Re: [sqlite] 3.7.7.1 database corruption Hi Alexandr, the mailing list doesn't allow attachments. Please send us the output of pragma integrity_check; on the corrupted database file. Also, is the database used in WAL mode? Best regards, Filip Navara 2011/11/14 Alexandr Němec : Dear all, we are using SQLite in our projects. We are using the 3.7.7.1 version compiled on Windows platform from the amalgamation file. One of our servers crashed badly (power outage) and after the server restarted, we saw that the database was corrupt (all queries we tried return SQLITE_CORRUPT). We had a look at http://www.sqlite.org/**howtocorrupt.htmland verified, that the hardware (disk, memory) of the server should be ok - we used some hardware tools to check. In the past we already had some crash situations and SQLite could recover from the crash nicely, so we were surprised that after this crash the database was corrupt. In fact, we cant create a repro, because we do not know, what the server was exactly doing, so it might be difficult to find the reason, but we saved the corrupted database in the hope, that somebody from the develpers (maybe) could put some light on this problem. Because the database is rather small and is used to save some configuration data only, I have att ached it to this message. Thanks for any suggestions. Alex __**_ 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-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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.7.1 database corruption
On Mon, 14 Nov 2011 14:16:46 +0100, Alexandr N?mec a.ne...@atlas.cz wrote: Dear Richard, huh, thanks very much for the information, I see. But anybody having an idea how this can happen? There was a power outage and after restart 6 pages of the database file are missing... We checked the disk carefuly, no bad sector etc. What can be the reason for this? The disk or the filesystem may have confirmed a write or sync while in fact the data wasn't on disk yet. Maybe the filesystem metadata is updated in a separate transaction, or your filesystem doesn't journal metadata. fsck only repairs inconsistencies it can find, depending on teh filesystem used there are quite a few corruptions it cannot detect nor repair. Did you use any dangerous PRAGMA to speed things up, like running without database journals? Does some startup script remove journal files before the database is connected to by an application? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.7.1 database corruption
Thanks again for your answer, no, no dangerous pragmas, no journal file removing. Our file system is NTFS so I hope that no stupid things are done by the file system :). In fact, we changed just two things only in the amalgamation file - we increased SQLITE_MAX_PAGE_COUNT to 2 000 000 000 and are using SQLITE_THREADSAFE 2 - we are using multiple database files from inside one application, but there are separate database connections for each file and every connection is accessed by a single thread only (but two queries for two different files using two different connections can run simultanously in our app). So I hope we are correct here. We tested a lot of crash situations in the past and never experienced a problem. Now we were very surprised about the missing pages. Thanks. Alex __ Od: Kees Nuyt Komu: Datum: 14.11.2011 15:52 Předmět: Re: [sqlite] 3.7.7.1 database corruption On Mon, 14 Nov 2011 14:16:46 +0100, Alexandr N?mec wrote: Dear Richard, huh, thanks very much for the information, I see. But anybody having an idea how this can happen? There was a power outage and after restart 6 pages of the database file are missing... We checked the disk carefuly, no bad sector etc. What can be the reason for this? The disk or the filesystem may have confirmed a write or sync while in fact the data wasn't on disk yet. Maybe the filesystem metadata is updated in a separate transaction, or your filesystem doesn't journal metadata. fsck only repairs inconsistencies it can find, depending on teh filesystem used there are quite a few corruptions it cannot detect nor repair. Did you use any dangerous PRAGMA to speed things up, like running without database journals? Does some startup script remove journal files before the database is connected to by an application? -- ( Kees Nuyt ) c[_] ___ 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] 3.7.7.1 database corruption
On Mon, 14 Nov 2011 18:24:46 +0100, Alexandr N?mec a.ne...@atlas.cz wrote: Thanks again for your answer, no, no dangerous pragmas, no journal file removing. Our file system is NTFS so I hope that no stupid things are done by the file system :). In fact, we changed just two things only in the amalgamation file - we increased SQLITE_MAX_PAGE_COUNT to 2 000 000 000 and are using SQLITE_THREADSAFE 2 - we are using multiple database files from inside one application, but there are separate database connections for each file and every connection is accessed by a single thread only (but two queries for two different files using two different connections can run simultanously in our app). So I hope we are correct here. That all sounds healthy to me. We tested a lot of crash situations in the past and never experienced a problem. Now we were very surprised about the missing pages. I can image. Soryy, I have no more ideas. Thanks. Alex __ Od: Kees Nuyt Komu: Datum: 14.11.2011 15:52 P?edm?t: Re: [sqlite] 3.7.7.1 database corruption On Mon, 14 Nov 2011 14:16:46 +0100, Alexandr N?mec wrote: Dear Richard, huh, thanks very much for the information, I see. But anybody having an idea how this can happen? There was a power outage and after restart 6 pages of the database file are missing... We checked the disk carefuly, no bad sector etc. What can be the reason for this? The disk or the filesystem may have confirmed a write or sync while in fact the data wasn't on disk yet. Maybe the filesystem metadata is updated in a separate transaction, or your filesystem doesn't journal metadata. fsck only repairs inconsistencies it can find, depending on teh filesystem used there are quite a few corruptions it cannot detect nor repair. Did you use any dangerous PRAGMA to speed things up, like running without database journals? Does some startup script remove journal files before the database is connected to by an application? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.7.1 database corruption
On Mon, 14 Nov 2011 21:03:56 +0100, Kees Nuyt k.n...@zonnet.nl wrote: I can image. Soryy, I have no more ideas. Oops. Make that: I can imagine. Sorry, I have no more ideas. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.7.1 database corruption
Dear all, we are using SQLite in our projects. We are using the 3.7.7.1 version compiled on Windows platform from the amalgamation file. One of our servers crashed badly (power outage) and after the server restarted, we saw that the database was corrupt (all queries we tried return SQLITE_CORRUPT). We had a look at http://www.sqlite.org/howtocorrupt.html and verified, that the hardware (disk, memory) of the server should be ok - we used some hardware tools to check. In the past we already had some crash situations and SQLite could recover from the crash nicely, so we were surprised that after this crash the database was corrupt. In fact, we cant create a repro, because we do not know, what the server was exactly doing, so it might be difficult to find the reason, but we saved the corrupted database in the hope, that somebody from the develpers (maybe) could put some light on this problem. Because the database is rather small and is used to save some configuration data only, I have att ached it to this message. Thanks for any suggestions. Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users