Re: [sqlite] SQLITE 3.7.3 bug report (shell) - outp ut in column mode does not align UTF8-strings correctl y

[Niklas Bäckman]

Igor Tandetnik  writes:

> 
> Niklas Bäckman  wrote:
> > Columns with special characters like ("å" "ä" "å") get too short widths when
> > output.
> > 
> > I guess this is due to the shell not counting actual UTF8 *characters/code
> > points* when calculating the widths, but instead only
> > counting the plain bytes in the strings, so they will seem longer until they
> > are actually printed to the console.
> 
> Note that counting codepoints, while it happens to help with your particular
data, won't help in general.
> Consider combining diacritics: U+00E4 (small A with diaeresis) looks the same
as U+0061 U+0308 (small
> letter A + combining diaeresis) when printed on the console.

You are right of course. The shell should not count code points, but graphemes.

http://unicode.org/faq/char_combmark.html#7

I guess that this probably falls out of the "lite" scope of SQLITE though? Like
how it does not support case-insensitive comparison of non-ASCII characters.

Or would it be possible to write such a graphemelen(s) function in not too many
lines of C code without needing any external Unicode libraries? 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

Hi All,

I am looking at a database that someone created earlier and for one of 
the table, the schema shows:
CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, 
...);
CREATE INDEX IDX1 on tx(name ASC);
CREATE INDEX IDX2 on tx(type, search_name ASC);

I am under the impression that IDX1 is not necessary in this case.  
Would there be any (significant) benefit in retaining that index?

Thanks,
Mohit.
26/11/2010 | 2:54 PM.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Queries getting slow with concurrency

[Simon Slavin]

On 25 Nov 2010, at 10:15pm, Prakash Reddy Bande wrote:

> I have observed that when multiple processes from "different" hosts are 
> concurrently doing the queries, the performance of sqlite degrades 
> considerably.
> Queries are read only hence I believe that there should not be any locking 
> issues (no busy handler called).

Is there a chance you have network congestion ?  Your server can only send out 
so many bytes per second to its clients and it might have reached the number of 
bytes its network card can handle.

Your operating system should have some tool that tells you how many bytes per 
second input and output your network card is handling.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Queries getting slow with concurrency

[Prakash Reddy Bande]

Hi,

I have observed that when multiple processes from "different" hosts are 
concurrently doing the queries, the performance of sqlite degrades considerably.
Queries are read only hence I believe that there should not be any locking 
issues (no busy handler called).

I will dig a little more in my application (not much I can do except check if 
my busy handler is getting called).

Only other problem I can think of is file system does not like being read 
concurrently, but we use this file system (NTFS network drive) for lot of other 
operations which happen concurrently.

Regards,

Prakash Bande

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file size

[Pavel Ivanov]

> So, if you have a large scale system, you have two choices: block during
> the checkpoint, or suffer from huge WAL file (and a crash if the file is
> too big).

For a large scale system you have a third choice: use some other RDBMS
which is implemented in one process and has a much better control over
its data and much better communication between readers and writers.


Pavel

On Thu, Nov 25, 2010 at 4:07 AM, Yoni  wrote:
> Hi,
>
>  > In which case the writer would have to block on the reader,
>  > which is something that the current system doesn't suffer from.
> I agree when we are talking about small systems, which does not
> read/write allot. But, on a larger scale system, who have a several
> threads reading and writing to the DB non-stop, there always will be at
> least one reader who block the checkpoint from being completed, thus
> making the WAL file grow forever (unless of course, you block during the
> checkpoint - but then you make your system unresponsive).
>
> So, if you have a large scale system, you have two choices: block during
> the checkpoint, or suffer from huge WAL file (and a crash if the file is
> too big).
>
>  > You can never be sure when is the right time to wrap around.
>  > If you wrap around too early, you run the risk of hitting the wall
>  > put up by the earliest reader, who is still using the end of the
>  > WAL file.
> If we choose a large enough size to be free at the beginning of the file
> (can be proportional to the WAL file size of just a constant), the
> chances that this will happen a very low. and even when this will
> happen, the write wont have to block the reader, it just have to force
> running checkpoint (while they are reading a page), and wait until they
> finish read the specific page (not the whole read transaction), since
> the next page they need to read is already in the main DB file.
>
>  > The only solution around this would be to have more than 1 WAL file.
>  > To avoid hitting this same problem with multiple WAL files, you'd
>  > need to support an arbitrary N number of WAL files.
> I think my solution will work too. on PostgreSQL they already attend the
> problem of how many WAL files should exist. see here:
> http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html.
>
>  > On one system of mine, where blocking is an issue, I
>  > buffer up the write messages, and flush them on a background thread.
>  > Of course this may not be practical for you...
> I think this kind of a solution can work of me too (I dont want to block
> the main thread EVER, and I have a steady stream of writes to the DB).
> Can you please elaborate how did you implemented your solution?
>
> Yoni.
>
>
> On 25/11/2010 8:42 AM, Ben Harper wrote:
>> A cyclical WAL file has this problem:
>>
>> You can never be sure when is the right time to wrap around.
>> If you wrap around too early, you run the risk of hitting the wall
>> put up by the earliest reader, who is still using the end of the
>> WAL file. In which case the writer would have to block on the reader,
>> which is something that the current system doesn't suffer from.
>> My guess is that a very high proportion of use cases would never
>> suffer this issue, provided they set an appropriate wrap around size
>> but this does place that burden on the implementer - of picking
>> the right heuristics.
>>
>> It is a pathological case, but imagine an implementer never tests
>> for this wraparound issue, and builds a deadlock into his app
>> that arises when a writer blocks on a reader. This is the kind of
>> unexpected behaviour that could really bite you.
>>
>> The only solution around this would be to have more than 1 WAL file.
>> To avoid hitting this same problem with multiple WAL files, you'd
>> need to support an arbitrary N number of WAL files. And that,
>> undoubtedly, is a more complex implementation than what currently
>> exists. I imagine it's quite a task for the SQLite developers to
>> get 100% branch test coverage.
>>
>> On one system of mine, where blocking is an issue, I
>> buffer up the write messages, and flush them on a background thread.
>> Of course this may not be practical for you...
>>
>> Ben
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yoni
>> Sent: 24 November 2010 04:46 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] WAL file size
>>
>> Hi,
>>
>> I am currently developing a system, which have the following requirements:
>> 1. should be very fast and responsive
>> 2. run forever (or at least a very long time) without restart.
>> 3. have steady stream of writes to the DB.
>>
>> Currently I am using sqlite with one sql connection, and I run sql
>> checkpoint every once in a while (e.g. after 5 minutes, or after 1
>> inserts to the DB) - I don't use auto checkpoint.
>> Since I run the checkpoint in the main thread, the system is
>> unresponsive for a long time (can take 

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

[Simon Slavin]

On 25 Nov 2010, at 5:25pm, Tito Ciuro wrote:

> 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would 
> fail right away is it wasn't a valid database.
> 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds.
> 
> However, I'm wondering whether (1) is redundant. If the integrity check 
> detects right away that the file is not a SQLite3 database, then we're good. 
> But if the integrity check reads the whole file before failing, perhaps 
> running (1) would not be such a bad after all.
> 
> Also, if there is a need to know whether a database is corrupted, an 
> optimization seems to be to issue "PRAGMA integrity_check (1)" statement, 
> which would bail out at the first sign of trouble. As stated in the docs: 
> http://www.sqlite.org/pragma.html

I believe that if Nick's going to do (2) anyway, (1) is redundant.  In my own 
case I couldn't do (2) because some of the databases I was dealing with would 
have made it take too long.  And my hardware were extremely trustworthy anyway: 
I was really just checking that the files were the right files, and someone 
hadn't (for example) renamed a journal file as the database file WHICH IS 
SOMETHING SOMEONE ACTUALLY DID TO ME ONCE ARGH !

And I agree Nick only needs to see the first error from 'PRAGMA 
integrity_check' so he can use 1 as a parameter.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

[Tito Ciuro]

Hi Nick,

On Nov 25, 2010, at 2:09 PM, Nick Shaw wrote:

> As Igor points out, that may not fail if the corruption is in a table
> you don't query.
> 
> In this case, the databases I'm working with are either small enough for
> the speed of an integrity check not to be an issue, or the integrity of
> the database is more important than a delayed DB open (which happens in
> a seprate thread to my apps' main GUI, so the user won't notice an issue
> anyway).
> 
> Thanks for all the help peeps,
> Nick.

Agreed.

However, the original email looks like it's a two-part question: opening 
non-SQLite files and detecting corrupted databases. Looks like the solution 
would be to integrate a method combining both Igor and Simon's solution:

1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would fail 
right away is it wasn't a valid database.
2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds.

However, I'm wondering whether (1) is redundant. If the integrity check detects 
right away that the file is not a SQLite3 database, then we're good. But if the 
integrity check reads the whole file before failing, perhaps running (1) would 
not be such a bad after all.

Also, if there is a need to know whether a database is corrupted, an 
optimization seems to be to issue "PRAGMA integrity_check (1)" statement, which 
would bail out at the first sign of trouble. As stated in the docs: 
http://www.sqlite.org/pragma.html

> PRAGMA integrity_check; 
> PRAGMA integrity_check(integer)
> 
> This pragma does an integrity check of the entire database. It looks for 
> out-of-order records, missing pages, malformed records, and corrupt indices. 
> If any problems are found, then strings are returned (as multiple rows with a 
> single column per row) which describe the problems. At most integer errors 
> will be reported before the analysis quits. The default value for integer is 
> 100. If no errors are found, a single row with the value "ok" is returned.


Cheers,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

[Nick Shaw]

Tito Ciuro wrote:
> On 25 Nov 2010, at 12:51, Igor Tandetnik  wrote:
> > Run "PRAGMA integrity_check" right after opening.
>
> That could be a potentially slow operation if the database is valid
and contains lots of records.
> Wouldn't be better to issue a SELECT statement and let SQLite come
back with a SQLITE_NOTADB error right away?

As Igor points out, that may not fail if the corruption is in a table
you don't query.

In this case, the databases I'm working with are either small enough for
the speed of an integrity check not to be an issue, or the integrity of
the database is more important than a delayed DB open (which happens in
a seprate thread to my apps' main GUI, so the user won't notice an issue
anyway).

Thanks for all the help peeps,
Nick.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copying data from memory to file

[Igor Tandetnik]

Berryl Hesh  wrote:
> After generating an in memory db schema, I use the attach command to
> replicate that schema to a file 

Consider using backup API: http://www.sqlite.org/c3ref/backup_finish.html
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Simon Slavin]

On 25 Nov 2010, at 4:06pm, Tito Ciuro wrote:

> Wouldn't be better to issue a SELECT statement and let SQLite come back with 
> a SQLITE_NOTADB error right away?

In one of my applications I used to do a SELECT on sqlite_master.  This 
confirms that the file exists, that it's not blank, and that it was used as an 
SQLite3 database somewhere along the way and not, say, an SQLite2 database or a 
text file.  It takes so little time you can include it in startup routines 
without people complaining.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Jim Wilcoxson]

On Thu, Nov 25, 2010 at 11:06 AM, Tito Ciuro  wrote:

> On 25 Nov 2010, at 12:51, Igor Tandetnik  wrote:
>
> > Run "PRAGMA integrity_check" right after opening.
>
> That could be a potentially slow operation if the database is valid and
> contains lots of records.
>
> Wouldn't be better to issue a SELECT statement and let SQLite come back
> with a SQLITE_NOTADB error right away?
>
>
I ran into this problem too.  What I did was to execute pragma
synchronous=full (or whatever mode you are using) after opening the
database.  This will fail if the file is not really an SQLite db or you have
the wrong key for an encrypted db.  It doesn't handle the problem of a
corrupted db, but as has been mentioned, there's not a fast way to detect
that.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Tito Ciuro]

On 25 Nov 2010, at 12:51, Igor Tandetnik  wrote:

> Run "PRAGMA integrity_check" right after opening.

That could be a potentially slow operation if the database is valid and 
contains lots of records.

Wouldn't be better to issue a SELECT statement and let SQLite come back with a 
SQLITE_NOTADB error right away?

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Igor Tandetnik]

Nick Shaw  wrote:
>> Do you really want SQLite to scan a gigabytes-large file every time it's 
>> opened?
> 
> Depends - obviously not if you need quick access to the data, however if
> the gigabytes-large file is corrupt, do you really want to wait until
> you execute the first "substantive" statement to discover the DB is
> corrupt?

You won't even necessarily know at this point. Only when one of your statements 
actually hits the corrupted part.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

[Igor Tandetnik]

Niklas Bäckman  wrote:
> Columns with special characters like ("å" "ä" "å") get too short widths when
> output.
> 
> I guess this is due to the shell not counting actual UTF8 *characters/code
> points* when calculating the widths, but instead only
> counting the plain bytes in the strings, so they will seem longer until they
> are actually printed to the console.

Note that counting codepoints, while it happens to help with your particular 
data, won't help in general. Consider combining diacritics: U+00E4 (small A 
with diaeresis) looks the same as U+0061 U+0308 (small letter A + combining 
diaeresis) when printed on the console.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Nick Shaw]

Igor Tandetnik  wrote:
>
> > Is there a reason that sqlite3_open() will happily "open" a
non-sqlite 
> > file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which

> > would seem a more obvious return value?
>
> SQLite doesn't actually touch the file until the first "substantive"
statement is executed on the connection. This allows one to set various
PRAGMAs that can only be set before the database is > created.

Ah, that makes sense, ok.

> > It will also happily open a
> > sqlite database that it will later say is corrupt when I try to
query 
> > some data, implying it doesn't do data integrity checks on the 
> > database on opening?
>
> Do you really want SQLite to scan a gigabytes-large file every time
it's opened?

Depends - obviously not if you need quick access to the data, however if
the gigabytes-large file is corrupt, do you really want to wait until
you execute the first "substantive" statement to discover the DB is
corrupt?  It makes coding for corrupt database more complex if you only
find out (possibly much) further down the code stream...

>
> > So... Assuming the above is how sqlite is meant to work, what would
be 
> > the best way to ascertain if a successfully opened file is ok to
use?
>
> Run "PRAGMA integrity_check" right after opening.

Ah, that's what I was looking for!  Thanks Igor, I'll try that.

Thanks,
Nick.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Max Vlasov]

On Thu, Nov 25, 2010 at 6:08 PM, Nick Shaw  wrote:

> Hi all,
>
>
>
> So... Assuming the above is how sqlite is meant to work, what would be
> the best way to ascertain if a successfully opened file is ok to use?
> Currently once the file has been opened by sqlite3_open(), and provided
> the file existed prior to that call, I then run the query "SELECT
> COUNT(rowid) from sqlite_master" and if it fails, I assume the DB is
> corrupt or not an sqlite file.  This seems to work, however it seems a
> bit long winded and wondered if there was a better way of checking this
> kind of thing?
>
>
When I implemented an encryption scheme with an VFS, I noticed that sqlite
doesn't read anything until specially requested, so I ended up with reading
encoding pragma for this task. At least if I provided wrong password, sqlite
just by trying reading this pragma returned an error about something like
"encrypted or bad"

Max Vlasov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

[Igor Tandetnik]

Nick Shaw  wrote:
> Is there a reason that sqlite3_open() will happily "open" a non-sqlite
> file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which
> would seem a more obvious return value?

SQLite doesn't actually touch the file until the first "substantive" statement 
is executed on the connection. This allows one to set various PRAGMAs that can 
only be set before the database is created.

> It will also happily open a
> sqlite database that it will later say is corrupt when I try to query
> some data, implying it doesn't do data integrity checks on the database
> on opening?

Do you really want SQLite to scan a gigabytes-large file every time it's opened?

> So... Assuming the above is how sqlite is meant to work, what would be
> the best way to ascertain if a successfully opened file is ok to use?

Run "PRAGMA integrity_check" right after opening.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_open on non-DB files / corrupt DBs

[Nick Shaw]

Hi all,

 

Is there a reason that sqlite3_open() will happily "open" a non-sqlite
file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which
would seem a more obvious return value?  It will also happily open a
sqlite database that it will later say is corrupt when I try to query
some data, implying it doesn't do data integrity checks on the database
on opening?

 

So... Assuming the above is how sqlite is meant to work, what would be
the best way to ascertain if a successfully opened file is ok to use?
Currently once the file has been opened by sqlite3_open(), and provided
the file existed prior to that call, I then run the query "SELECT
COUNT(rowid) from sqlite_master" and if it fails, I assume the DB is
corrupt or not an sqlite file.  This seems to work, however it seems a
bit long winded and wondered if there was a better way of checking this
kind of thing?

 

Thanks,

Nick.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file size

[Ben Harper]

> threads reading and writing to the DB non-stop, there always will be at 
> least one reader who block the checkpoint from being completed, thus

It is in essence writing, not reading, that blocks the checkpointing.
If you stopped writing for a while, the checkpointer would catch up,
and it could rewind.

> I think this kind of a solution can work of me too (I dont want to block 
> the main thread EVER, and I have a steady stream of writes to the DB).
> Can you please elaborate how did you implemented your solution?

My specific application maintains a history of tiles that are touched by
a renderer. A tile record looks like this:

struct Tile { char Signature[20]; int64 X, int64 Y; }

I simply keep a queue, in essence:
vector Queue;

The UI thread adds tiles to the queue, and a background thread
fetches them off the queue, does the writing, and the checkpointing.
By having the writing and checkpointing on the same thread you can
guarantee that the WAL will rewind.

By the sounds of it, your application may be harder to abstract, the
way I do here, with a simple queue of instructions. Aside from rolling
WAL logs, I can't think of any other way that you could achieve the
responsiveness that you require. I have no contact with the SQLite
authors, but I wouldn't place my bets on rolling logs being available
any time soon.

Ben



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Yoni
Sent: 25 November 2010 11:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] WAL file size

Hi,

 > In which case the writer would have to block on the reader,
 > which is something that the current system doesn't suffer from.
I agree when we are talking about small systems, which does not 
read/write allot. But, on a larger scale system, who have a several 
threads reading and writing to the DB non-stop, there always will be at 
least one reader who block the checkpoint from being completed, thus 
making the WAL file grow forever (unless of course, you block during the 
checkpoint - but then you make your system unresponsive).

So, if you have a large scale system, you have two choices: block during 
the checkpoint, or suffer from huge WAL file (and a crash if the file is 
too big).

 > You can never be sure when is the right time to wrap around.
 > If you wrap around too early, you run the risk of hitting the wall
 > put up by the earliest reader, who is still using the end of the
 > WAL file.
If we choose a large enough size to be free at the beginning of the file 
(can be proportional to the WAL file size of just a constant), the 
chances that this will happen a very low. and even when this will 
happen, the write wont have to block the reader, it just have to force 
running checkpoint (while they are reading a page), and wait until they 
finish read the specific page (not the whole read transaction), since 
the next page they need to read is already in the main DB file.

 > The only solution around this would be to have more than 1 WAL file.
 > To avoid hitting this same problem with multiple WAL files, you'd
 > need to support an arbitrary N number of WAL files.
I think my solution will work too. on PostgreSQL they already attend the 
problem of how many WAL files should exist. see here: 
http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html.

 > On one system of mine, where blocking is an issue, I
 > buffer up the write messages, and flush them on a background thread.
 > Of course this may not be practical for you...
I think this kind of a solution can work of me too (I dont want to block 
the main thread EVER, and I have a steady stream of writes to the DB).
Can you please elaborate how did you implemented your solution?

Yoni.


On 25/11/2010 8:42 AM, Ben Harper wrote:
> A cyclical WAL file has this problem:
>
> You can never be sure when is the right time to wrap around.
> If you wrap around too early, you run the risk of hitting the wall
> put up by the earliest reader, who is still using the end of the
> WAL file. In which case the writer would have to block on the reader,
> which is something that the current system doesn't suffer from.
> My guess is that a very high proportion of use cases would never
> suffer this issue, provided they set an appropriate wrap around size
> but this does place that burden on the implementer - of picking
> the right heuristics.
>
> It is a pathological case, but imagine an implementer never tests
> for this wraparound issue, and builds a deadlock into his app
> that arises when a writer blocks on a reader. This is the kind of
> unexpected behaviour that could really bite you.
>
> The only solution around this would be to have more than 1 WAL file.
> To avoid hitting this same problem with multiple WAL files, you'd
> need to support an arbitrary N number of WAL files. And that,
> undoubtedly, is a more complex implementation than what currently
> exists. I imagine it's quite a task for the 

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

[Dan Kennedy]

On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote:
> Hi all,
>
> I'm noticing a new failure with SQLite 3.7.3 as compared to the previous 
> version I was using, 3.6.23.1.

Are you able to share the database and the query that causes
the assert() to fail?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL, durability, and synchronous=NORMAL

[Simon Slavin]

On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote:

> In WAL mode with synchronous=NORMAL, when the user commits
> a transaction, it is written into the WAL file. No sync
> until a checkpoint happens. So if the power fails, you might
> lose all the transactions that have been written into the WAL
> file.

Ahha.  That explains it.  Thanks for that.  This makes WAL mode less attractive 
to me.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL, durability, and synchronous=NORMAL

[Dan Kennedy]

On 11/25/2010 08:49 PM, Simon Slavin wrote:
>
> On 25 Nov 2010, at 8:36am, Dan Kennedy wrote:
>
>> On 11/25/2010 03:24 PM, Twylite wrote:
>>>
>
> Argh.  Trevor, I'm going to find Stephenie Meyer and do something she doesn't 
> like.
>
>>> I am seeking technical information on the durability of transactions
>>> with journal_mode=WAL and synchronous=NORMAL.
>>>
>>> Specifically, in the event of a power failure, can the following ever
>>> happen:
>>> (1) Loss of the last transaction completed.
>>> (2) Loss of some indeterminate number of recent transactions.
>>> (3) Loss of the entire WAL file.
>>> (4) Corrupt of the database making it unopenable.
>>
>> Possible. Possible. Possible. Not supposed to be possible.
>
> Is (2) really possible ?  I thought that, unless the power failure caused 
> corruption at the hard disk level, a maximum of one transaction could be lost.
>

(2) and (3) are really the same question.

In WAL mode with synchronous=NORMAL, when the user commits
a transaction, it is written into the WAL file. No sync
until a checkpoint happens. So if the power fails, you might
lose all the transactions that have been written into the WAL
file.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL, durability, and synchronous=NORMAL

[Simon Slavin]

On 25 Nov 2010, at 8:36am, Dan Kennedy wrote:

> On 11/25/2010 03:24 PM, Twylite wrote:
>> 

Argh.  Trevor, I'm going to find Stephenie Meyer and do something she doesn't 
like.

>> I am seeking technical information on the durability of transactions
>> with journal_mode=WAL and synchronous=NORMAL.
>> 
>> Specifically, in the event of a power failure, can the following ever
>> happen:
>> (1) Loss of the last transaction completed.
>> (2) Loss of some indeterminate number of recent transactions.
>> (3) Loss of the entire WAL file.
>> (4) Corrupt of the database making it unopenable.
> 
> Possible. Possible. Possible. Not supposed to be possible.

Is (2) really possible ?  I thought that, unless the power failure caused 
corruption at the hard disk level, a maximum of one transaction could be lost.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copying data from memory to file

[Simon Davies]

On 24 November 2010 18:16, Berryl Hesh  wrote:
> Hello:
>
> After generating an in memory db schema, I use the attach command to
> replicate that schema to a file - at least that is my intention. I get
> a no such table error, so something is wrong somewhere. Can someone
> get me on the right track?

I see nothing in your code that creates the schema. You are trying to
copy data to non-existent tables.

"SELECT SQL FROM sqlite_master WHERE type='table';"

will get you the SQL to execute in the attached db to recreate the
schema tables, then you can copy the data.

You may need to handle views, triggers and indices as well as tables.

>
> Cheers,
> Berryl
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cppcheck error

[Richard Hipp]

On Wed, Nov 24, 2010 at 6:49 PM, Stephen Blessing <
stephen.bless...@datasplice.com> wrote:

> Hi SQLite folks,
>
> I ran cppcheck and got this error:
>
> [./PlugIns/SQLiteDataStore/SQLite/sqlite3.c:13028]:(error) Common realloc
> mistake: "p" nulled but not freed upon failure.
>

This should be a "warning" not an "error" (is that a bug in cppcheck?) and
in this case the warning is wrong.  The SQLite code is correct as it
stands.  sqlite3MemRealloc() has the same semantics as realloc() and so the
p pointer should definitely not be freed.


>
> /*
> ** Like realloc().  Resize an allocation previously obtained from
> ** sqlite3MemMalloc().
> **
> ** For this low-level interface, we know that pPrior!=0.  Cases where
> ** pPrior==0 while have been intercepted by higher-level routine and
> ** redirected to xMalloc.  Similarly, we know that nByte>0 becauses
> ** cases where nByte<=0 will have been intercepted by higher-level
> ** routines and redirected to xFree.
> */
> static void *sqlite3MemRealloc(void *pPrior, int nByte){
>  sqlite3_int64 *p = (sqlite3_int64*)pPrior;
>  assert( pPrior!=0 && nByte>0 );
>  nByte = ROUND8(nByte);
>  p--;
> (Line 13028)  p = realloc(p, nByte+8 );
>  if( p ){
>p[0] = nByte;
>p++;
>  }else{
>testcase( sqlite3GlobalConfig.xLog!=0 );
>sqlite3_log(SQLITE_NOMEM,
>  "failed memory resize %u to %u bytes",
>  sqlite3MemSize(pPrior), nByte);
>  }
>  return (void*)p;
> }
>
> Thanks and best regards,
>
> Stephen Blessing
> Quality Assurance
> DataSplice, LLC
> Phone:  970-232-1647
> stephen.bless...@datasplice.com
> For support issues please keep our team
> in the loop and CC: supp...@datasplice.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

[sqlite] Cppcheck error

[Stephen Blessing]

Hi SQLite folks,

I ran cppcheck and got this error:

[./PlugIns/SQLiteDataStore/SQLite/sqlite3.c:13028]:(error) Common realloc 
mistake: "p" nulled but not freed upon failure.

/*
** Like realloc().  Resize an allocation previously obtained from
** sqlite3MemMalloc().
**
** For this low-level interface, we know that pPrior!=0.  Cases where
** pPrior==0 while have been intercepted by higher-level routine and
** redirected to xMalloc.  Similarly, we know that nByte>0 becauses
** cases where nByte<=0 will have been intercepted by higher-level
** routines and redirected to xFree.
*/
static void *sqlite3MemRealloc(void *pPrior, int nByte){
  sqlite3_int64 *p = (sqlite3_int64*)pPrior;
  assert( pPrior!=0 && nByte>0 );
  nByte = ROUND8(nByte);
  p--;
(Line 13028)  p = realloc(p, nByte+8 );
  if( p ){
p[0] = nByte;
p++;
  }else{
testcase( sqlite3GlobalConfig.xLog!=0 );
sqlite3_log(SQLITE_NOMEM,
  "failed memory resize %u to %u bytes",
  sqlite3MemSize(pPrior), nByte);
  }
  return (void*)p;
}

Thanks and best regards,

Stephen Blessing
Quality Assurance
DataSplice, LLC
Phone:  970-232-1647
stephen.bless...@datasplice.com
For support issues please keep our team 
in the loop and CC: supp...@datasplice.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] copying data from memory to file

[Berryl Hesh]

Hello:

After generating an in memory db schema, I use the attach command to
replicate that schema to a file - at least that is my intention. I get
a no such table error, so something is wrong somewhere. Can someone
get me on the right track?

Cheers,
Berryl

=
Code called after in memory db is created with test data:
=

public void ExportData(SQLiteConnection conn, string dataFile)
{
if (!File.Exists(dataFile)) {
SQLiteConnection.CreateFile(dataFile);
}
_attachDatabase(conn, dataFile);
foreach (var table in _getTableNames(conn)) {
_copyTableData(conn, table,
_getAttachedTableName(table));
}
_detachDatabase(conn);
}

private static IEnumerable
_getTableNames(SQLiteConnection conn) {
var tables = SQLiteMetaDataCollectionNames.Tables;
var dt = conn.GetSchema(tables);
return dt.Rows.Cast().Select(R => (string)
R["TABLE_NAME"]);
}

private static string _getAttachedTableName(string table)
{ return string.Format("{0}.{1}", ATTACHED_DB, table); }

private static void _attachDatabase(SQLiteConnection conn,
string dataFile) {
var cmd = new SQLiteCommand(conn)
  {
  CommandText = string.Format("ATTACH '{0}' AS
{1}", dataFile, ATTACHED_DB)
  };
_log.Debug(cmd.CommandText);
cmd.ExecuteNonQuery();
}

private static void _copyTableData(SQLiteConnection conn,
string source, string destination) {
var cmd = new SQLiteCommand(conn)
{
CommandText = string.Format("INSERT INTO {0} SELECT *
FROM {1}", destination, source)
};
_log.Debug(cmd.CommandText);
cmd.ExecuteNonQuery();
}

==
error, stack trace and log output
==

TestFixture failed: System.Data.SQLite.SQLiteException : SQLite error
no such table: asdfgaqwernb.ActorRole
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at
SQLiteTesting.Helpers.SQLiteLoader._copyTableData(SQLiteConnection
conn, String source, String destination) in C:\Users\Lord & Master
\Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers
\SQLiteLoader.cs:line 71
   at SQLiteTesting.Helpers.SQLiteLoader.ExportData(SQLiteConnection
conn, String dataFile) in C:\Users\Lord & Master\Documents\Projects
\Data\NHib projects\Cookbook\SQLiteTesting\Helpers
\SQLiteLoader.cs:line 33
   at SQLiteTesting.Helpers.QueryTests.OnFixtureSetUp() in C:\Users
\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook
\SQLiteTesting\Helpers\QueryTests.cs:line 32
   at SQLiteTesting.Helpers.BaseFixture.FixtureSetUp() in C:\Users
\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook
\SQLiteTesting\Helpers\BaseFixture.cs:line 23
 2010-11-24 10:00:24,968 INFO processing cascade
NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for:
Eg.Core.Movie
 2010-11-24 10:00:24,968 INFO cascade NHibernate.Engine.CascadingAction
+SaveUpdateCascadingAction for collection: Eg.Core.Movie.Actors
 2010-11-24 10:00:24,970 INFO done cascade
NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for
collection: Eg.Core.Movie.Actors
 2010-11-24 10:00:24,970 INFO deleting orphans for collection:
Eg.Core.Movie.Actors
 2010-11-24 10:00:24,973 INFO done deleting orphans for collection:
Eg.Core.Movie.Actors
 2010-11-24 10:00:24,973 INFO done processing cascade
NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for:
Eg.Core.Movie
 2010-11-24 10:01:25,195 DEBUG ATTACH '...\SPUD.db3' AS asdfgaqwernb
 2010-11-24 10:03:22,362 DEBUG INSERT INTO asdfgaqwernb.ActorRole
SELECT * FROM ActorRole
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Paul Sanderson]

Thats interesting Max, I'll look at that.

There are two main string fields one has an average of about 15
characters per field with a max of about 100. The other has an average
length of about 150 characters with a max of about 250 (although
occasionally up to 500 or so)

It may be possible to split the table into two with the main fields in
one and all of the remainder (including the text fields) in the other.

I'll have a play and see what I come up with



On 25 November 2010 10:20, Max Vlasov  wrote:
> On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
>> I have a table with over 1 million rows and 20+ columns all of which
>> are indexed, I reasonably regularly recreate the table with new data
>> and find that the indexing process takes about 30 minutes. Are there
>> any settings/tweaks that I can use to reduce the time required to
>> create the index?
>>
>>
> Paul, thought about it recently. I think there's something you could do if
> you're ready to change the scheme a little.
>
> The index creation involves reading and writing. The size of the index in
> your case for most of the fields is smaller than the data of the main table
> so we'll forget about writing, possible it works well using the sqlite and
> system cache.
>
> But your reading for every index should read the whole table and the full
> record (if it fits on a single page) even if the index needs only one field.
> This process of full table reading can be comparatively fast (if your base
> is not fragmented), slow (if it's heavily fragmented) and amazingly fast if
> your entire table luckily fitted in the os system cache when your first
> field was indexed. And the latter is possible if your text fields contribute
> much to the record size and you're ready to move it to a different table
> accessing later with a join. I don't know your exact numbers, but 1m records
> with 20 modest integers (using much few bits than 64) should take about
> 50MB-100MB and many modern software/hardware configurations will possibly
> read it just once accessing this data from the cache when your second and
> later indexes are created.
>
> Max Vlasov
> ___
> 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] Reducing time to create indexes

[Max Vlasov]

On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I have a table with over 1 million rows and 20+ columns all of which
> are indexed, I reasonably regularly recreate the table with new data
> and find that the indexing process takes about 30 minutes. Are there
> any settings/tweaks that I can use to reduce the time required to
> create the index?
>
>
Paul, thought about it recently. I think there's something you could do if
you're ready to change the scheme a little.

The index creation involves reading and writing. The size of the index in
your case for most of the fields is smaller than the data of the main table
so we'll forget about writing, possible it works well using the sqlite and
system cache.

But your reading for every index should read the whole table and the full
record (if it fits on a single page) even if the index needs only one field.
This process of full table reading can be comparatively fast (if your base
is not fragmented), slow (if it's heavily fragmented) and amazingly fast if
your entire table luckily fitted in the os system cache when your first
field was indexed. And the latter is possible if your text fields contribute
much to the record size and you're ready to move it to a different table
accessing later with a join. I don't know your exact numbers, but 1m records
with 20 modest integers (using much few bits than 64) should take about
50MB-100MB and many modern software/hardware configurations will possibly
read it just once accessing this data from the cache when your second and
later indexes are created.

Max Vlasov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] autoindex1 test fail on linux debian

[Yoni]

That worked.
Thanks for the answer.

On 25/11/2010 11:00 AM, Dan Kennedy wrote:
> On 11/25/2010 02:43 PM, Yoni wrote:
>> Hi,
>>
>> I get the following fail when running sqlite3 tests, under linux debian,
>> with TCL 8.4.
>> Code is latest from fossil.
>> Any help appreciated.
>
> Try with 8.5 or newer.
>
>
> ___
> 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] WAL file size

[Yoni]

Hi,

 > In which case the writer would have to block on the reader,
 > which is something that the current system doesn't suffer from.
I agree when we are talking about small systems, which does not 
read/write allot. But, on a larger scale system, who have a several 
threads reading and writing to the DB non-stop, there always will be at 
least one reader who block the checkpoint from being completed, thus 
making the WAL file grow forever (unless of course, you block during the 
checkpoint - but then you make your system unresponsive).

So, if you have a large scale system, you have two choices: block during 
the checkpoint, or suffer from huge WAL file (and a crash if the file is 
too big).

 > You can never be sure when is the right time to wrap around.
 > If you wrap around too early, you run the risk of hitting the wall
 > put up by the earliest reader, who is still using the end of the
 > WAL file.
If we choose a large enough size to be free at the beginning of the file 
(can be proportional to the WAL file size of just a constant), the 
chances that this will happen a very low. and even when this will 
happen, the write wont have to block the reader, it just have to force 
running checkpoint (while they are reading a page), and wait until they 
finish read the specific page (not the whole read transaction), since 
the next page they need to read is already in the main DB file.

 > The only solution around this would be to have more than 1 WAL file.
 > To avoid hitting this same problem with multiple WAL files, you'd
 > need to support an arbitrary N number of WAL files.
I think my solution will work too. on PostgreSQL they already attend the 
problem of how many WAL files should exist. see here: 
http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html.

 > On one system of mine, where blocking is an issue, I
 > buffer up the write messages, and flush them on a background thread.
 > Of course this may not be practical for you...
I think this kind of a solution can work of me too (I dont want to block 
the main thread EVER, and I have a steady stream of writes to the DB).
Can you please elaborate how did you implemented your solution?

Yoni.


On 25/11/2010 8:42 AM, Ben Harper wrote:
> A cyclical WAL file has this problem:
>
> You can never be sure when is the right time to wrap around.
> If you wrap around too early, you run the risk of hitting the wall
> put up by the earliest reader, who is still using the end of the
> WAL file. In which case the writer would have to block on the reader,
> which is something that the current system doesn't suffer from.
> My guess is that a very high proportion of use cases would never
> suffer this issue, provided they set an appropriate wrap around size
> but this does place that burden on the implementer - of picking
> the right heuristics.
>
> It is a pathological case, but imagine an implementer never tests
> for this wraparound issue, and builds a deadlock into his app
> that arises when a writer blocks on a reader. This is the kind of
> unexpected behaviour that could really bite you.
>
> The only solution around this would be to have more than 1 WAL file.
> To avoid hitting this same problem with multiple WAL files, you'd
> need to support an arbitrary N number of WAL files. And that,
> undoubtedly, is a more complex implementation than what currently
> exists. I imagine it's quite a task for the SQLite developers to
> get 100% branch test coverage.
>
> On one system of mine, where blocking is an issue, I
> buffer up the write messages, and flush them on a background thread.
> Of course this may not be practical for you...
>
> Ben
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yoni
> Sent: 24 November 2010 04:46 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] WAL file size
>
> Hi,
>
> I am currently developing a system, which have the following requirements:
> 1. should be very fast and responsive
> 2. run forever (or at least a very long time) without restart.
> 3. have steady stream of writes to the DB.
>
> Currently I am using sqlite with one sql connection, and I run sql
> checkpoint every once in a while (e.g. after 5 minutes, or after 1
> inserts to the DB) - I don't use auto checkpoint.
> Since I run the checkpoint in the main thread, the system is
> unresponsive for a long time (can take from 400ms to 2ms!)
>
> What I would like to do, is to run the checkpoint in another thread, so
> the main thread can keep working (and using sql), and do the long
> checkpoint work in the background.
>
> The problem is that when I try to do it, the WAL file grows without limit.
> I wrote a little program that do exactly this (insert rows as fast as C
> allows, and in the background run checkpoints).
> The result (after 30 secs and 400K records) was: DB size 19MB and WAL
> size 451MB! (tried on linux Debian with sqlite 3.7.2, and on winXP with
> same 

Re: [sqlite] autoindex1 test fail on linux debian

[Dan Kennedy]

On 11/25/2010 02:43 PM, Yoni wrote:
> Hi,
>
> I get the following fail when running sqlite3 tests, under linux debian,
> with TCL 8.4.
> Code is latest from fossil.
> Any help appreciated.

Try with 8.5 or newer.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

[Philip Graham Willoughby]

Hi all,

I'm noticing a new failure with SQLite 3.7.3 as compared to the previous 
version I was using, 3.6.23.1.

Error message:
--
Assertion failed: (memIsValid([i])), function sqlite3VdbeExec, file 
.../sqlite3.c, line 64507.

Information from the time of error:
---
(gdb) print i
0
(gdb) print *u.bq.r.aMem
{
  u = {
i = 1, 
nZero = 1, 
pDef = 0x1, 
pRowSet = 0x1, 
pFrame = 0x1
  }, 
  r = 0, 
  db = 0x83a218, 
  z = 0x0, 
  n = 0, 
  flags = 4228, 
  type = 0 '\0', 
  enc = 0 '\0', 
  pScopyFrom = 0x0, 
  pFiller = 0x0, 
  xDel = 0, 
  zMalloc = 0x0
}
(gdb) print pc
31
(gdb) print u.bq
{
  pC = 0x2714e8, 
  res = 2561376, 
  r = {
pKeyInfo = 0x1fbb58, 
nField = 2, 
flags = 12, 
rowid = 343597383684, 
aMem = 0xa052d8
  }
}

Repeatability:
--
Every time. u.bq.r.aMem->flags is always 4228 (==0x1084), u.bq.r.aMem->u.i is 
always 1, u.bq.r.aMem->db varies as expected and all other fields of 
u.bq.r.aMem are zeroes.

Background:
---
I am using SQLite in an iPhone application I'm developing.  I am using the 
amalgamation from sqlite.org so that I know what I have; I am not using the 
version of SQLite supplied by Apple in their SDK. The previous version I had 
was 3.6.23.1; I updated to 3.7.3 because I wanted the very useful 
sqlite3_register_function_v2() API which is new in that release.

The same assertion fires when I run my app in the iPhone simulator or execute 
the same query on the same database from the sqlite3 shell program.

I am compiling sqlite3.c with GCC 4.2.

Obviously when I execute the same query on a version of sqlite built without 
the -DSQLITE_DEBUG it cannot fail the assertion since the assertion is not 
built. I can confirm however that it executes without any detected memory 
access violation and the result set is what I would expect.

It is important for me to understand if this is an assertion mis-fire in the 
debug case (and not a real problem) or a lucky escape in the release case (and 
therefore a real problem, unless I can guarantee my customers are all similarly 
lucky).

The failing query is explained as follows:

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 OpenEphemeral  9 3 0 keyinfo(1,-BINARY)  00   
2 Integer1 1 000   
3 Variable   1 2 0 :row   00   
4 MustBeInt  2 0 000   
5 IfPos  2 7 000   
6 Integer0 2 000   
7 Add1 2 300   
8 IfPos  1 10000   
9 Integer-13 000   
10Goto   0 118   000   
11OpenRead   0 310 0  00   
12OpenRead   1 210 3  00   
13OpenRead   2 270 3  00   
14OpenRead   10300 keyinfo(2,BINARY,BINARY)  00 
  
15OpenRead   3 130 4  00   
16OpenRead   4 8 0 2  00   
17OpenRead   5 240 2  00   
18OpenRead   6 2 0 3  00   
19OpenRead   7 250 2  00   
20OpenRead   8 2 0 3  00   
21Rewind 0 95000   
22Rowid  0 4 000   
23MustBeInt  4 94000   
24NotExists  1 94400   
25SCopy  4 5 000   
26IsNull 5 94000   
27Column 1 2 600   
28IsNull 6 94000   
29Affinity   6 1 0 d  00   
30SeekGe 10945 2  00   
31IdxGE  10945 2  01   
32IdxRowid   107 000   
33Seek   2 7 000   
34Column 2 2 800   
35MustBeInt  8 93000   
36NotExists  3 93800

Re: [sqlite] WAL, durability, and synchronous=NORMAL

[Dan Kennedy]

On 11/25/2010 03:24 PM, Twylite wrote:
> Hi,
>
> I am seeking technical information on the durability of transactions
> with journal_mode=WAL and synchronous=NORMAL.
>
> Specifically, in the event of a power failure, can the following ever
> happen:
> (1) Loss of the last transaction completed.
> (2) Loss of some indeterminate number of recent transactions.
> (3) Loss of the entire WAL file.
> (4) Corrupt of the database making it unopenable.

Possible. Possible. Possible. Not supposed to be possible.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL, durability, and synchronous=NORMAL

[Twylite]

Hi,

I am seeking technical information on the durability of transactions 
with journal_mode=WAL and synchronous=NORMAL.

Specifically, in the event of a power failure, can the following ever 
happen:
(1) Loss of the last transaction completed.
(2) Loss of some indeterminate number of recent transactions.
(3) Loss of the entire WAL file.
(4) Corrupt of the database making it unopenable.

My use case is an embedded x86 running off a CFIDE (NAND flash).  There 
is no shut down (just hard power-off), and a corrupt database will be a 
tragedy (best case: we wipe and reinit the database losing all data; 
worst case: device cannot boot and is rendered inoperable), but I can 
tolerate losing some recent transactions.  Performance tests with 
synchronous=FULL yield 41 TPS which is slightly too slow for our purposes.

I have scoured the SQLite docs (include http://www.sqlite.org/wal.html), 
the Wiki and the mailing list archives [1], and I can't find a 
definitive answer.  It seems clear that (1) and (2) can happen, and 
likely that (3) can happen, but no clarity on (4).

The following quotes would seem to indicate that the database should 
never become corrupted in WAL mode?  Can someone confirm this?

*  DRH: "If you set synchronous=NORMAL, then the checkpoint 
thread/process is the only thread/process that ever calls fsync() so you 
can do queries and updates in a GUI thread with much less risk of 
freezing the system due to a slow disk. You pay for this by giving up 
durability following a power-loss/hard-reset. Please note that SQLite 
databases with WAL will continue to feature fully automatic recovery 
from power-loss or hard-reset; applications do not have to do anything 
special to recover from a crash."

* http://www.sqlite.org/wal.html: "Further, syncing the content to the 
disk is not required, as long as the application is willing to sacrifice 
durability following a power loss or hard reboot. (Writers sync the WAL 
on every transaction commit if PRAGMA synchronous is set to FULL but 
omit this sync if PRAGMA synchronous is set to NORMAL.)"


[1] Related mailing list threads
* sqlite - WAL in SQLite, May 2010, 
http://www.pubbs.net/201005/sqlite/55971-sqlite-wal-in-sqlite.html
* [sqlite] WAL - Performance/fsync, Jul 2010 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg53555.html
* [sqlite] performance, transactions and wal checkpoints, Aug 2010, 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg54512.html
* [sqlite] Sqlite on NAND flash devices..., Aug 2010, 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg54896.html


PART 2

(A) My understanding is that the WAL is a ring-buffer that is a delta on 
the main database file.  In the context of NAND flash and wear leveling, 
are there any portions of the database file or WAL that get written more 
regularly and may impact on the longevity of the flash device? (e.g. 
with journal_mode=DELETE there are parts of the database file header 
that seem to be modified on each transaction).

(B) Will regular checkpointing cause uneven wear of the flash device?  
Specifically, does a checkpoint affect the "tail" of the WAL (causing it 
e.g. to reset to the start of the WAL file)?  Conceptually, if I 
checkpointed after every transaction, would the disk write behaviour be 
similar to journal_mode=PERSIST?

(C) If I am correct in assuming that WAL + synchronous=NORMAL can lose 
the entire WAL file but not corrupt the database, AND I don't want to 
checkpoint too regularly to prevent uneven wear of the flash device, 
what would be the best way to force an occasional fsync() of the WAL so 
that I limit losses to the last few transactions?  (Implied 
sub-question: will an fsync() make the transactions durable, or can the 
entire WAL still be lost/corrupt even if an fsync() occurs?).

Could I turn on synchronous=FULL to cause an immediate fsync()?  Turn on 
synchronous=FULL for one transaction?


Thanks in advance,
Twylite

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users