Re: [sqlite] Integrity check
Thanks, Simon. Simon Slavin-3 wrote If possible, you should try to do your synchronisation when your app is frontmost only. However, I understand that this may not be appropriate for your app. Exactly, under normal circumstances the synchronization of our app is the topmost priority, hence we have to continue. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77644.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] Integrity check
On 2 Sep 2014, at 9:50am, Jan Slodicka j...@resco.net wrote: Simon Slavin-3 wrote If possible, you should try to do your synchronisation when your app is frontmost only. However, I understand that this may not be appropriate for your app. Exactly, under normal circumstances the synchronization of our app is the topmost priority, hence we have to continue. You may have done this already but I suspect that you need to read the section called 'Implementing Long-Running Background Tasks' from https://developer.apple.com/library/ios/documentation/iphone/conceptual/iphoneosprogrammingguide/ManagingYourApplicationsFlow/ManagingYourApplicationsFlow.html#//apple_ref/doc/uid/TP40007072-CH4-SW24 carefully if you expect to do File IO and network traffic in the background. What you describe seems to be closest to 'Background fetch'. iOS expects your application to call 'beginBackgroundTaskWithName:expirationHandler:' when appropriate and to provide the expiration handler. This allows you to have a task such as synchronisation executed irrelevant to whether the app is frontmost or not. You can then use 'backgroundTimeRemaining' to find out whether iOS is going to force-quit your app. That way your background activities won't be terminated without warning. Unfortunately I have no experience of implementing something like this but this page http://www.devfright.com/ios-7-background-app-refresh-tutorial/ looks interesting. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Thank you for the answer. Your custom collation function would be my prime suspect here. Yes, it was. Some time ago we really corrected a bug in the collation, which resulted in decreased number of user reports. Even later we switched to the ICU library, which - I suppose - should be relatively safe. I know that the last statement may not be 100% true (under Windows I found some exotic strings that violate CompareString() transitivity; I can't remember if this confirmed for ICU), but: a) I got access to the content that caused the corruption and found that no unusual strings are used. For example one indexed column with a corrupted index contained only ascii strings. b) Suppose there was a bug in the collation. Could that bug cause a corruption of an index that does not use that collation? Here is a real-life example: CREATE TABLE [account] ( [accountid] UNIQUEIDENTIFIERNOT NULL CONSTRAINT PK_account PRIMARY KEY ROWGUIDCOL DEFAULT (newid()), [address1_city] NVARCHAR(160) NULL COLLATE NOCASE, [address1_country] NVARCHAR(160)NULL COLLATE NOCASE, [address1_latitude] FLOAT NULL, [address1_line1] NVARCHAR(500) NULL COLLATE NOCASE, [address1_line2] NVARCHAR(500) NULL COLLATE NOCASE, [address1_line3] NVARCHAR(500) NULL COLLATE NOCASE, [address1_longitude] FLOAT NULL, [address1_postalcode] NVARCHAR(40) NULL COLLATE NOCASE, [address1_stateorprovince] NVARCHAR(100)NULL COLLATE NOCASE, [createdon] DATETIMENULL, [defaultpricelevelid] UNIQUEIDENTIFIER NULL, [emailaddress1] NVARCHAR(200) NULL COLLATE NOCASE, [fax] NVARCHAR(100) NULL COLLATE NOCASE, [modifiedon] DATETIME NULL, [name] NVARCHAR(320)NULL COLLATE NOCASE, [ownerid] UNIQUEIDENTIFIER NULL, [statuscode] INTNOT NULL DEFAULT(1), [telephone1] NVARCHAR(100) NULL COLLATE NOCASE, [transactioncurrencyid] UNIQUEIDENTIFIERNULL, [websiteurl] NVARCHAR(400) NULL COLLATE NOCASE, [defaultpricelevelidTarget] NVARCHAR(100) NULL COLLATE NOCASE, [owneridTarget] NVARCHAR(100) NULL COLLATE NOCASE, [transactioncurrencyidTarget] NVARCHAR(100) NULL COLLATE NOCASE ); CREATE INDEX [FK_account_defaultpricelevelid] ON [account](defaultpricelevelid); CREATE INDEX [FK_account_ownerid] ON [account](ownerid); CREATE INDEX [FK_account_transactioncurrencyid] ON [account](transactioncurrencyid); CREATE INDEX [FK_account_name] ON [account](name COLLATE NOCASE); If NOCASE is a custom collation, could a bug in that collation explain corruption of indexes FK_account_transactioncurrencyid and FK_account_ownerid? (Both have NUMERIC affinity and store blobs such as X'001C2300C5DF8BEA11DF8834FBDCD77E'.) So far I supposed that the answer is NOT and consequently excluded a collation bug. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77554.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] Integrity check
On Thu, Aug 28, 2014 at 6:11 AM, Jan Slodicka j...@resco.net wrote: If NOCASE is a custom collation, could a bug in that collation explain corruption of indexes FK_account_transactioncurrencyid and FK_account_ownerid? (Both have NUMERIC affinity and store blobs such as X'001C2300C5DF8BEA11DF8834FBDCD77E'.) So far I supposed that the answer is NOT and consequently excluded a collation bug. Correct. If corruptions are appearing in indexes that do not use custom collations, that would tend to rule out a problem with the collation function. -- 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] Integrity check
Simon Slavin-3 wrote On 27 Aug 2014, at 4:21pm, Jan Slodicka lt; jano@ gt; wrote: - There is one potentionally risky operation that our app performs: The data exchange with a remote WebService. This can take long (10+ min). Users often switch to email reading or similar activity, the app then runs on the background and can be eventually killed by OS. This might happen during an unfinished transaction. Does this happen while the database is open and in use by the app ? Yes When you exchange data, do you exchange data accessed as records (e.g. accessed using the SQLite API) or do you exchange files ? DB tables are being synchronized. The server sends xml data over http. This data is parsed and converted into a collection of high level data records. These records are then stored in the DB. At the same time some of the local DB records are sent to the server. The details are complex. Main point is that during a single transaction (which may take a long time) new records are added (deleted, updated) for a single table. If this succeeds, the transaction is commited and the whole process is repeated with a new table. In case of any failure, the whole syncronization process is aborted. What is strange - the corruption may affect tables that were accessed through different transactions. However, this may be explained by this scenario: - App is killed = synchronization is killed in the middle = DB gets first corruption. - App is restarted, DB corruption unnoticed and the user starts synchronization again. Synchronization resumes with the table where it stopped the last time. (I would expect that the DB corruption is discovered now, but it does not happen for some reason.) Synchronization updates a few tables, when the app is killed again causing another DB corruption etc. If you exchange files do you exchange just the database file or also journal files ? Should be answered above. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77556.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] Integrity check
Have you reviewed the list of corruption causes at http://www.sqlite.org/howtocorrupt.html and eliminated them all as possibilities? -- 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] Integrity check
Thanks, may I ask about PRAGMA synchronous=Normal? The worst-case scenario I can imagine is that the app is killed by the OS when a checkpoint operation is in process... -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77558.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] Integrity check
On Thu, Aug 28, 2014 at 7:31 AM, Jan Slodicka j...@resco.net wrote: Thanks, may I ask about PRAGMA synchronous=Normal? The worst-case scenario I can imagine is that the app is killed by the OS when a checkpoint operation is in process... That should be safe. -- 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] Integrity check
Richard Hipp-3 wrote Have you reviewed the list of corruption causes at http://www.sqlite.org/howtocorrupt.html and eliminated them all as possibilities? Multiple times, but I did it again. In general I can exclude only a few points... 1.0 File overwrite by a rogue thread or process Excluded on iPhone 2.0 File locking problems Excluded - just a single app accesses the database 3.0 Failure to sync In general I doubt that this would be the reason on iPhone/iPad, but you certainly know more about the subject than I do. 4.0 Disk Drive and Flash Memory Failures Hardly possible, I think the problem would manifest in many other ways 5.0 Memory corruption In managed environment? Hardly. 6.0 Other operating system problems Don't believe so -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77561.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] Integrity check
On 28 Aug 2014, at 12:23pm, Jan Slodicka j...@resco.net wrote: Simon Slavin-3 wrote When you exchange data, do you exchange data accessed as records (e.g. accessed using the SQLite API) or do you exchange files ? DB tables are being synchronized. The server sends xml data over http. This data is parsed and converted into a collection of high level data records. These records are then stored in the DB. At the same time some of the local DB records are sent to the server. Okay, thanks for that. It tells me several things not to worry about, and several things that won't matter at all. Most people who think they do sync do it by exchanging files and run into problems. Your solution seems to do it by exchanging data accessed using the SQLite API so you shouldn't have that sort of problem. What is strange - the corruption may affect tables that were accessed through different transactions. However, this may be explained by this scenario: - App is killed = synchronization is killed in the middle = DB gets first corruption. - App is restarted, DB corruption unnoticed I assume that you aren't using any PRAGMAs which speed up SQLite at the expense of safety, for instance, keeping journals in memory or turning off synchrony. If you're letting SQLite handle journalling properly then DB corruption caused by crashes should always be noticed and rectified, with the database restored to the most recent transaction boundary. Any failure to do that properly is a fault in SQLite and the dev team will fix it. and the user starts synchronization again. Synchronization resumes with the table where it stopped the last time. (I would expect that the DB corruption is discovered now, but it does not happen for some reason.) Synchronization updates a few tables, when the app is killed again causing another DB corruption etc. It would be nice to think that corruption which happens while writing to a particular table would corrupt only that table. Unfortunately, that's not the case. You may have an app which writes to only one table in the database, and then suffer power-loss. If the database is corrupted because your hardware flipped bits during power-loss and overwrote the wrong part of the disk, the corruption might well be in a different table. Or a different file ! I am slightly concerned about your mention of App is killed. Is this part of your routine operations or are you just being careful to mention that it's possible ? If your app runs on an iPhone it should always get notification before it is put into the background or terminated, even in low-battery conditions. You should respond to those notifications appropriately. Normal practise these days is to close database connections when your app is backgrounded and reopen them when they are needed again, unless your app has functionality which needs to continue when it's in the background. And you need to call _close() when your gets termination notification, of course. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Thanks, Simon. Simon Slavin-3 wrote Your solution seems to do it by exchanging data accessed using the SQLite API so you shouldn't have that sort of problem. Yes, only standard SQLite API is used. I assume that you aren't using any PRAGMAs which speed up SQLite at the expense of safety, for instance, keeping journals in memory or turning off synchrony.. No tricks are done. My first post contains all SQLite settings used. ...If the database is corrupted because your hardware flipped bits during power-loss and overwrote the wrong part of the disk, the corruption might well be in a different table... Clear. I just wonder that integrity_check first performs a number of checks that validate that all tables are basically ok. As part of this process also the indexes are formally declared as correct tables. Only then, when the contents of the indexes is checked (thing that cannot be done for ordinary tables), some problems are found. I would expect a different report if some random bits are overwritten. Moreover, the error report suggests the hypothesis as if the the data transaction was correctly completed on all data tables, but the indexes were for some reason not updated. I am just looking for a feasible explanation, I don't know how SQLite works internally. I am slightly concerned about your mention of App is killed. Is this part of your routine operations or are you just being careful to mention that it's possible ? Killing can be done only by iOS. Suppose our app is running and the user switches to email reading. The app continues running on background and roughly after 10 min the system sends a notification that it is going to be frozen. In reaction our app tries to interrupt the data communication with the server (Problem1 - this may fail), closes the database (Problem2 - possible NullReferenceException some time later) etc. Then iOS freezes the application. What happens then, depends on user activities. If he opens say huge PDF, iOS may decide to kill the app. If not, the app will be resumed after some time. Many things can happen in this process (lousy programming, I have to improve lots of things...). The worst scenario is that the app is killed by OS during some SQLite action. -- I apologize in advance: I'll be out of office until next Tuesday. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77575.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] Integrity check
On 28 Aug 2014, at 4:57pm, Jan Slodicka j...@resco.net wrote: Simon Slavin-3 wrote ...If the database is corrupted because your hardware flipped bits during power-loss and overwrote the wrong part of the disk, the corruption might well be in a different table... Clear. I just wonder that integrity_check first performs a number of checks that validate that all tables are basically ok. As part of this process also the indexes are formally declared as correct tables. Only then, when the contents of the indexes is checked (thing that cannot be done for ordinary tables), some problems are found. I'm answering only some points here. If I don't comment on something assume I can't see why it might be causing your problem or anything wrong with what you're doing. It might be worth knowing that PRAGMA integrity_check is not perfect. It doesn't check every single little thing that might be wrong with the database file. It concentrates on making sure that if you wrote a program that read every row of every database, using any index for each table, you would get every row of data. It is good at checking that you could DUMP your data to SQL commands and read it back in again, but it's not a good exhaustive check that every byte of your database file is what it should be. That would take a lot longer to execute. Killing can be done only by iOS. Suppose our app is running and the user switches to email reading. The app continues running on background and roughly after 10 min the system sends a notification that it is going to be frozen. iOS expects only the frontmost app to be using significant CPU or IO. It is normal in iOS applications not to do heavy work when your application is backgrounded unless doing so is a mainstay of your application, e.g. an email program checking for new mail. If possible, you should try to do your synchronisation when your app is frontmost only. However, I understand that this may not be appropriate for your app. In reaction our app tries to interrupt the data communication with the server (Problem1 - this may fail) Exactly. If your app is backgrounded when this happens you cannot give feedback to your user because your app is backgrounded. The interruption of communication with the server would normally come when your app is notified it's going to be backgrounded. This means that iOS will wait patiently for your app to cope with its problems before doing something intense like displaying a PDF. Similar problem: on an iPhone iOS will also kill your process if it tries to hog CPU or IO during a phone call. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Integrity check
A while ago I reported http://sqlite.1065341.n5.nabble.com/What-can-be-deduced-from-integrity-check-td70451.html about DB corruption issues that we occasionally receive from our users. They always have the same pattern: - A few rowid's missing from a few indexes, and - A few wrong # of entries in index xxx errors. No other integrity errors, just these two types. In an earlier reply http://sqlite.1065341.n5.nabble.com/What-can-be-deduced-from-integrity-check-tp70451p70454.html Mr. Hipp said that the fact that the corruption is restricted to an index is probably a coincidence. Well, based on the number of reports we received it hardly can be a coincidence. Does anybody have any theory how these things happen? Based on my limited understanding of the sqlite source code it looks like (my speculation) as if all b-trees (tables and indexes) were stored correctly, except the contents of some indexes referred to some point in the past. A few details: - We use sqlite 3.7.15.2, custom encryption and custom collation. The collation shouldn't be the culprit because the majority of the indexes corrupted relate to non-textual columns. - Sqlite setup: -- WAL mode -- PRAGMA synchronous=Normal// Should we change this to FULL? -- iOS only: PRAGMA fullfsync=1 // Important. Substantially decreases # of corruptions. - In case we get sqlite-related exception containing the word malformed, we perform an integrity_check and log its output. - Problems happen on iOS platform. This may be a coincidence because the majority of our users use that platform. - DB writes are grouped into transactions. What is strange - corrupted indexes do not relate to a single transaction. In other words, several write transaction would have to fail in order to produce described corruption. - There is one potentionally risky operation that our app performs: The data exchange with a remote WebService. This can take long (10+ min). Users often switch to email reading or similar activity, the app then runs on the background and can be eventually killed by OS. This might happen during an unfinished transaction. - (Continution of the previous point) What is worse, WAL logs can be huge: In one case I saw a 900MB log file (the whole DB was roughly 2/3 of that). This means that we cannot exclude that the OS kills the application while running a checkpoint operation. On another note. If we cannot avoid occasional corruption, it would be great if we had a kind of autorepair. Specifically in our case, when the integrity checks indicate that a REINDEX operation should help. Suppose that integrity_check a) does not fail with a fatal error b) lists only 2 types of messages: - rowid ... missing from index ... - wrong # of entries in index ... then rebuilding of all indexes listed in the report should repair the database. (Note that I am trying to avoid full REINDEX because it is a slow operation.) Does this make sense? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519.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] Integrity check
On Wed, Aug 27, 2014 at 11:21 AM, Jan Slodicka j...@resco.net wrote: A while ago I reported http://sqlite.1065341.n5.nabble.com/What-can-be-deduced-from-integrity-check-td70451.html about DB corruption issues that we occasionally receive from our users. They always have the same pattern: - A few rowid's missing from a few indexes, and - A few wrong # of entries in index xxx errors. No other integrity errors, just these two types. ... - We use sqlite 3.7.15.2, custom encryption and custom collation. Your custom collation function would be my prime suspect here. A subtle bug in a collation function can result in the kinds of errors you are seeing. ... Suppose that integrity_check a) does not fail with a fatal error b) lists only 2 types of messages: - rowid ... missing from index ... - wrong # of entries in index ... then rebuilding of all indexes listed in the report should repair the database. (Note that I am trying to avoid full REINDEX because it is a slow operation.) Correct. Note that you can add an argument to the REINDEX command telling it what to reindex. The argument can be either the name of an index, or it can be the name of a collating function, in which case all indices using that collation will be recomputed. -- 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] Integrity check
On 27 Aug 2014, at 4:21pm, Jan Slodicka j...@resco.net wrote: - There is one potentionally risky operation that our app performs: The data exchange with a remote WebService. This can take long (10+ min). Users often switch to email reading or similar activity, the app then runs on the background and can be eventually killed by OS. This might happen during an unfinished transaction. Does this happen while the database is open and in use by the app ? When you exchange data, do you exchange data accessed as records (e.g. accessed using the SQLite API) or do you exchange files ? If you exchange files do you exchange just the database file or also journal files ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] integrity check
Hi all, Executed integrity check for database before application starts. Sometimes, it takes 1 minute. Other times, it finishes within 2 seconds. How integrity check works? can somebody explain why it takes less time. Thanks, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integrity check
On Thu, Jan 9, 2014 at 8:29 AM, dd durga.d...@gmail.com wrote: Hi all, Executed integrity check for database before application starts. Sometimes, it takes 1 minute. Other times, it finishes within 2 seconds. How integrity check works? can somebody explain why it takes less time. PRAGMA integrity_check is suppose to do exactly the same thing every time it is run. I don't know why you are seeing a 30x timing difference. Can you provide us with a test case? -- 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] integrity check
Applied encryption on top of sqlite. Now, I suspect on encryption. Thanks for prompt response. On Thu, Jan 9, 2014 at 5:38 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Jan 9, 2014 at 8:29 AM, dd durga.d...@gmail.com wrote: Hi all, Executed integrity check for database before application starts. Sometimes, it takes 1 minute. Other times, it finishes within 2 seconds. How integrity check works? can somebody explain why it takes less time. PRAGMA integrity_check is suppose to do exactly the same thing every time it is run. I don't know why you are seeing a 30x timing difference. Can you provide us with a test case? -- 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] Integrity Check Failure Handling
On 30 Jul 2013, at 5:04am, techi eth techi...@gmail.com wrote: Could be many rows in many tables were corrupted. If SQLite3 knew exactly what had been corrupted it could just go and fix it without even needing your help. Here i am thinking of getting details about table infected row,SQLite3 doesn't need to fix them.Calling Application will understand do the required needful to change that part of database. It might be helpful to get return data structure with infected row in corresponding table. Sorry, but the idea that one piece of corruption corresponds to one row of one table doesn't work. Sometimes it's obvious that a block of data in the database was corrupted but there's no way to tell what part of the block was changed. Sometimes the corruption is in the form of a database file cut off at a certain point: a file which was originally 123456 bytes long is suddenly 10 bytes long. Sometimes schema details near the beginning of the file become corrupt and it's not possible to tell even which tables were in the database or what rows and columns were in each table. Sometimes the corruption occurs purely in indexes: values stored in tables are correct but SQLite malfunctions while trying to find particular values and ranges. The full details of the SQLite file format are widely available ... http://www.sqlite.org/fileformat.html and you could write a very thorough program which might pick through a file and try to guess what data could be rescued, asking a user which possibilities are the most plausible at each stage. But it would be a great deal of work. You might be interested in the sqlite-analyzer programs you can find on the SQLite download page which do some of the work involved. The basic message here is that if a database file has been corrupted it's no longer appropriate to use any data from it in a working system. The good news is that corruption of SQLite databases on working hardware is very rare. With literally billions (thanks DRH) of installations of SQLite it has been debugged very thoroughly and incidents of corrupted files are rare. The best ways to corrupt a SQLite database are listed here: http://www.sqlite.org/howtocorrupt.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Integrity Check Failure Handling
Reference from below link gave me hint about integrity check failure case recovery by Export/Import of database. Please let me know is this is correct way to handle integrity failure check. http://blog.niklasottosson.com/?p=852 http://community.spiceworks.com/how_to/show/1468-how-to-fix-corrupt-sqlite-database In failure case integrity check return say “If any problems are found, then strings are returned (as multiple rows with a single column per row)” With this how can I found in which table, row got issue? Cheers- techi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity Check Failure Handling
On 29 Jul 2013, at 10:21am, techi eth techi...@gmail.com wrote: Reference from below link gave me hint about integrity check failure case recovery by Export/Import of database. Please let me know is this is correct way to handle integrity failure check. This is not the correct way to handle integrity failure check. http://blog.niklasottosson.com/?p=852 http://community.spiceworks.com/how_to/show/1468-how-to-fix-corrupt-sqlite-database This process, if it works, will present you with a /usable/ database: one which won't cause SQLite to crash. But the database A) may have incorrect data in it if some corruption affected only the contents of fields B) may have inconsistent data in it, e.g. an entry in your 'sales' table for a customer who is not in the 'customer' table C) may be missing all data added after the point of corruption, or worse still just /some/ of the data added after the point of corruption. It all depends on exactly which bytes of the file got corrupted. In failure case integrity check return say “If any problems are found, then strings are returned (as multiple rows with a single column per row)” With this how can I found in which table, row got issue? Could be many rows in many tables were corrupted. If SQLite knew exactly what had been corrupted it could just go and fix it without even needing your help. This is not the correct way to handle integrity failure check. The correct way to handle integrity failure check it to figure out what caused it (probably a hardware or low-level programming issue), try to make sure it doesn't happen again, then to restore the database file(s) from the last good backup you took. The method of rescue described on those pages /can/ be useful if you have a programmer who understands the data structure who has been told to spend hours desperately recovering all available data. It might be useful to reassemble new data files which could be printed out, then inspected for useful information. But I would not just recover datafiles that way and continue to use them in an operating system: you are running the risk of accumulating missing and incorrect data which will cause you problems later. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity Check Failure Handling
Many Thanks for comment. I shall agree with your view. Could be many rows in many tables were corrupted. If SQLite3 knew exactly what had been corrupted it could just go and fix it without even needing your help. Here i am thinking of getting details about table infected row,SQLite3 doesn't need to fix them.Calling Application will understand do the required needful to change that part of database. It might be helpful to get return data structure with infected row in corresponding table. On Mon, Jul 29, 2013 at 4:22 PM, Simon Slavin slav...@bigfraud.org wrote: On 29 Jul 2013, at 10:21am, techi eth techi...@gmail.com wrote: Reference from below link gave me hint about integrity check failure case recovery by Export/Import of database. Please let me know is this is correct way to handle integrity failure check. This is not the correct way to handle integrity failure check. http://blog.niklasottosson.com/?p=852 http://community.spiceworks.com/how_to/show/1468-how-to-fix-corrupt-sqlite-database This process, if it works, will present you with a /usable/ database: one which won't cause SQLite to crash. But the database A) may have incorrect data in it if some corruption affected only the contents of fields B) may have inconsistent data in it, e.g. an entry in your 'sales' table for a customer who is not in the 'customer' table C) may be missing all data added after the point of corruption, or worse still just /some/ of the data added after the point of corruption. It all depends on exactly which bytes of the file got corrupted. In failure case integrity check return say “If any problems are found, then strings are returned (as multiple rows with a single column per row)” With this how can I found in which table, row got issue? Could be many rows in many tables were corrupted. If SQLite knew exactly what had been corrupted it could just go and fix it without even needing your help. This is not the correct way to handle integrity failure check. The correct way to handle integrity failure check it to figure out what caused it (probably a hardware or low-level programming issue), try to make sure it doesn't happen again, then to restore the database file(s) from the last good backup you took. The method of rescue described on those pages /can/ be useful if you have a programmer who understands the data structure who has been told to spend hours desperately recovering all available data. It might be useful to reassemble new data files which could be printed out, then inspected for useful information. But I would not just recover datafiles that way and continue to use them in an operating system: you are running the risk of accumulating missing and incorrect data which will cause you problems later. 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
[sqlite] Integrity check reliability
Dear SQLite users, Can anyone confirm that the command PRAGMA integrity_check; will not crash the SQLite code on a corrupt database? I made a corrupt database on purpose to test, it seems to run ok. The command neatly reports the corruption. But this is just one try. Thank you! Jos This message and attachment(s) are intended solely for use by the addressee and may contain information that is privileged, confidential or otherwise exempt from disclosure under applicable law. If you are not the intended recipient or agent thereof responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by telephone and with a 'reply' message. Thank you for your co-operation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check reliability
On 14 Nov 2011, at 1:19pm, Verstappen, Jos wrote: Can anyone confirm that the command PRAGMA integrity_check; will not crash the SQLite code on a corrupt database? I made a corrupt database on purpose to test, it seems to run ok. The command neatly reports the corruption. But this is just one try. The code is designed to do lots of testing and not make any assumptions. However, it's possible that you'll find some weird situation the programmers never thought of. There's no way you're ever going to get the SQLite team to say that it's completely bug-free. However, there are very few reasons to put that call in to production code anyway. I would expect to see it only in utilities used by whoever wrote the SQLite software. If your software or hardware is so poor that it continually corrupts the SQLite database you should be paying personal attention. So of all the places in SQLite to put code which can crash, this is probably the best ! Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Integrity Check error
I was trying to insert records into sqlite database. I observed that there was a mismatch in the data retrieved. I went ahead checking for the integrity ( with PRAGMA integrity_check ) I got the following warnings: rowid 1 missing from index album_title_idx rowid 1 missing from index genre_album_title_idx rowid 36 missing from index genre_album_title_idx rowid 36 missing from index genre_artist_title_idx Can someone point out what might be going wrong?. When does the integrity fail with these errors?. Thanks in advance Kalyani ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify [EMAIL PROTECTED] **
[sqlite] Integrity Check Pragma
Hi, Sorry to ask a stupid question, but how exactly do I get the return value from this pragma? Thanks in advance, Steve
