[sqlite] Integrity check with a SQL command

2015-12-12 22:13 GMT+01:00 Richard Hipp : > On 12/12/15, Cecil Westerhof wrote: > > I have the following tables: > > CREATE TABLE "projects" ( > > "projectID" TEXT PRIMARY KEY, > > ); > ?? > > CREATE INDEX subprojects_projectID_idx > > ON projects(projectID); > > Don't create indexes on p

[sqlite] Integrity check with a SQL command

I have the following tables: CREATE TABLE "projects" ( "projectID" TEXT PRIMARY KEY, "groupID" TEXT, "isPersonal"INTEGER NOT NULL CHECK(ispersonal in (0, 1)), "name" TEXT, "description" TEXT, "outcome" TEXT ); CREATE INDEX projects_groupID_idx

[sqlite] Integrity check with a SQL command

On 12/12/15, Cecil Westerhof wrote: > I have the following tables: > CREATE TABLE "projects" ( > "projectID" TEXT PRIMARY KEY, > ); > CREATE INDEX subprojects_projectID_idx > ON projects(projectID); Don't create indexes on primary keys. Doing so still gives a correct answer, but it waste

Re: [sqlite] Integrity check

On 2 Sep 2014, at 9:50am, Jan Slodicka 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

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, henc

Re: [sqlite] Integrity check

On 28 Aug 2014, at 4:57pm, Jan Slodicka 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 integr

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

Re: [sqlite] Integrity check

> On 28 Aug 2014, at 12:23pm, Jan Slodicka 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 > dat

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 Exclude

Re: [sqlite] Integrity check

On Thu, Aug 28, 2014 at 7:31 AM, Jan Slodicka 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 _

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 SQLit

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-bi

Re: [sqlite] Integrity check

Simon Slavin-3 wrote > On 27 Aug 2014, at 4:21pm, Jan Slodicka < > jano@ > > 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 activi

Re: [sqlite] Integrity check

On Thu, Aug 28, 2014 at 6:11 AM, Jan Slodicka 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'001C2300C5DF8BEA11DF8834FBDCD7

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

Re: [sqlite] Integrity check

On 27 Aug 2014, at 4:21pm, Jan Slodicka 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 ca

Re: [sqlite] Integrity check

On Wed, Aug 27, 2014 at 11:21 AM, Jan Slodicka 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: > -

[sqlite] Integrity check

A while ago I reported 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 entri

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 wrote: > On Thu, Jan 9, 2014 at 8:29 AM, dd wrote: > >> Hi all, >> >> Executed integrity check for database before application starts. >> Sometimes, it ta

Re: [sqlite] integrity check

On Thu, Jan 9, 2014 at 8:29 AM, dd 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_che

[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 maili

Re: [sqlite] Integrity Check Failure Handling

On 30 Jul 2013, at 5:04am, techi eth 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 doe

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

Re: [sqlite] Integrity Check Failure Handling

On 29 Jul 2013, at 10:21am, techi eth 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

[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-corrup

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 ju

[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 messa

[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_a

[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

[sqlite] integrity check

Hi, I've got corrupted database file. PRAGMA integrity_check result is: *** in database main *** On tree page 4957: initPage() returns error code 11 On page 344 cell 9: 2nd reference to page 4947 On page 2810 cell 2: 2nd reference to page 4957 On page 4820 cell 2: 2nd reference to page 5451 On page