On 2016/06/29 4:53 PM, Joe Pasquariello wrote:
On 6/29/2016 6:13 AM, R Smith wrote:
Very happy this is sorted for you. Note that Synchronous=OFF should
only ever be used on a read-only DB (or at least one where writing
only happens when you the user/DB Admin actively makes changes via
queries. For anything that should run autonomously, Synchronous
should preferably be FULL and at least be ON.
There is a time-penalty to be paid for Synchronous=FULL, but if it
isn't part of a user interface and no user to notice that specific
thread is lagging a second or two when writing, then it rarely matters.
Okay, thanks. I thought it was generally preferable to use "lower"
levels of synchronous. I am not using WAL, so I'll use FULL rather
than NORMAL. Seems like I lucked out in the table being corrupted, but
recoverable.
Synchronous (from Latin meaning more or less: At-Same-Time or In-Step)
in very short computer terms means something like "Wait for the OS to do
the actual writes before accepting that they are written", so the more
Synchronous you do things, the more safe-ish it is - with the downside
of a bit of extra time taken by the waiting for the OS to commit the
writes[1].
Is there a simple explanation as to why your query to find duplicates
worked correctly, despite the corrupted index, whereas the one I had
used did not? Does it mean that your query doesn't use the index?
It's simple really - there are 2 possible reasons:
1 - I did post another query before to demonstrate how grouping doesn't
group values together that differ in case (if you haven't added NOCASE
statements) and so it won't look like 2 similar rows using a GROUP BY,
but still can make a UNIQUE constraint fail. Ex. Smith and SMITH will be
in 2 different groupings in the query in the original table (if it did
not have NOCASE specified), but will be failing a UNIQUE constraint in
the new table where you definitely specified NOCASE. (this is the only
explanation if the DB was healthy, but wasn't likely in this case).
2 - More importantly, my Query checked specifically the row_id, not just
the uniqueness of the groupings, which means the Query-Planner had to
consult/walk the row_id index and found those rows which were not
showing up when using the normal other indices you had (like the one
that's been broken and causing the integrity check to fail).
These are the reasons I suggested the Query - but please note there is
nothing technically wrong with the query you found on StackOverflow - it
just assumes correct case, encoding and valid indices - basically a
valid working DB.
We did not assume such. That's the only difference.
Hope that makes it clear!
Ryan
[1] : I say Safe-ish because there's a whole other world in which hard
drives lie to the OS about what they have committed to platters and
such, but it's a long story, varies from manufacturer to manufacturer,
and in general doesn't need to be accounted for, if you use
Synchronous-full, you should be fine barring only the worst of
coincidences. There's been some posts on this forum discussing it if
you are more interested and feel like searching.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
