Re: [sqlite] What happens if an in memory database runs out of memory

[Rowan Worth]

No clue sorry. Not familiar with c++ builder or clang.
-Rowan

On 29 December 2017 at 15:05, x  wrote:

> Well spotted Rowan. I still get the same error message though. I’m using
> c++ builder (clang compiler) and in Project | Options | C++ (Shared
> Options) | Conditional defines I've entered the following
>
> SQLITE_EXTRA_INIT=core_init;-DSQLITE_ENABLE_MEMSYS5
>
> I’m unsure how c++ builder presents that on the command line though.
>
> If I compile without the minus sign it builds OK but I presume the minus
> sign is required?
>
> Does that mean anything to you? If it doesn’t I’ll make a post on the c++
> builder forum.
>
>
> From: Rowan Worth
> Sent: 29 December 2017 03:13
> To: SQLite mailing list
> Subject: Re: [sqlite] What happens if an in memory database runs out of
> memory
>
> On 23 December 2017 at 00:17, curmudgeon  wrote:
>
> > >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
> >
> > Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try
> compiling
> > with a minus sign before that directive I get a compile error "macro
> names
> > must be identifiers ". Compiles OK if I leave out the minus
> > sign.
> >
>
> Yes it's a minus sign, and also it ends in a 5 not an S. Both
> SQLITE_ENABLE_MEMSYS5 and SQLITE_ENABLE_MEMSYSS are valid indentifiers
> though so not sure about the error; best guess is that a funny character
> has somehow crept into your command line.
>
> -Rowan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[x]

Well spotted Rowan. I still get the same error message though. I’m using c++ 
builder (clang compiler) and in Project | Options | C++ (Shared Options) | 
Conditional defines I've entered the following

SQLITE_EXTRA_INIT=core_init;-DSQLITE_ENABLE_MEMSYS5

I’m unsure how c++ builder presents that on the command line though.

If I compile without the minus sign it builds OK but I presume the minus sign 
is required?

Does that mean anything to you? If it doesn’t I’ll make a post on the c++ 
builder forum.


From: Rowan Worth
Sent: 29 December 2017 03:13
To: SQLite mailing list
Subject: Re: [sqlite] What happens if an in memory database runs out of memory

On 23 December 2017 at 00:17, curmudgeon  wrote:

> >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
>
> Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
> with a minus sign before that directive I get a compile error "macro names
> must be identifiers ". Compiles OK if I leave out the minus
> sign.
>

Yes it's a minus sign, and also it ends in a 5 not an S. Both
SQLITE_ENABLE_MEMSYS5 and SQLITE_ENABLE_MEMSYSS are valid indentifiers
though so not sure about the error; best guess is that a funny character
has somehow crept into your command line.

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

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

Re: [sqlite] Btree page corruption

[Simon Slavin]

On 29 Dec 2017, at 4:10am, Rowan Worth  wrote:

> do any of your processes open the database file, for any
> reason, without going through sqlite's API?

Just to note that a major offender in this respect is anti-virus software.  So 
don’t think just of things that might want to read a SQLite database, but think 
of anything that might read all files, or changed files.

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

Re: [sqlite] Btree page corruption

[Rowan Worth]

On 28 December 2017 at 02:55, Simon Slavin  wrote:

> On 27 Dec 2017, at 6:10pm, Nikhil Deshpande  wrote:
>
> >> Can you include a "pragma integrity_check" at startup ?
> >> Can you include a "pragma integrity_check" executed at regular
> intervals ?
> > The writer process does "pragma quick_check" on every startup at init,
> > bails out on failure and spawns a separate thread to do same
> > "pragma quick_check" every 5 minutes (opens it's own separate DB handle
> > and closes it). Would changing quick_check to integrity_check be
> > helpful? (Would integrity_check catch such corruption earlier than
> > quick_check? Would it hold longer exclusive locks on the DB file
> > that could prevent reads?)
>
> Changing "quick_check" to "integrity_check" may help in this case may be
> useful in investigating your problem.  "integrity_check" is far more
> thorough and checks every aspect of data integrity from both directions.
> However, the type of corruption you’re experiencing may be one which is
> spotted just as well by "quick_check".  There’s no way to know without
> checking it.
>

I haven't checked the code but the docs say:

https://sqlite.org/pragma.html#pragma_quick_check

> The pragma is like integrity_check
 except that it does
not verify UNIQUE constraints and does
> not verify that index content matches table content. By skipping UNIQUE
and index consistency checks,
> quick_check is able to run much faster than integrity_check. Otherwise
the two pragmas are the same.

Based on my personal experience/testing, quick_check still reads the entire
DB. The only type of corruption I've ever seen integrity_check report which
quick_check didn't was an index lacking an entry for a particular ROWID (in
an otherwise well-formed DB). Coupled with Nikhil's tests I'm certain
quick_check will catch this type of corruption.

So the only scenario left to worry about is an index corruption (ie. one
only detected by integrity_check) happening first, which somehow leads to a
DB page corruption AND masks the original index corruption. Doesn't seem
likely, but switching to integrity_check would rule out any chance.

It does take longer than quick_check, but how much longer will depend on
how big your indices are. On a 2GB DB I've measured 200 seconds for
quick_check vs 300 seconds for integrity_check, but that was over NFS.

I don't think either check takes an EXCLUSIVE lock on the DB? But a
competing writer might, which ends up locking out readers until the check
finishes (and the write completes).


I agree with Simon you're not doing anything obviously wrong... This is
listed in the how to corrupt page, but I'll ask anyway because it's a
subtle one: do any of your processes open the database file, for any
reason, without going through sqlite's API?

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Rowan Worth]

On 23 December 2017 at 00:17, curmudgeon  wrote:

> >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
>
> Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
> with a minus sign before that directive I get a compile error "macro names
> must be identifiers ". Compiles OK if I leave out the minus
> sign.
>

Yes it's a minus sign, and also it ends in a 5 not an S. Both
SQLITE_ENABLE_MEMSYS5 and SQLITE_ENABLE_MEMSYSS are valid indentifiers
though so not sure about the error; best guess is that a funny character
has somehow crept into your command line.

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

Re: [sqlite] Minor bug reports during build.

[Rowan Worth]

On 22 December 2017 at 23:57, Michael Tiernan 
wrote:

>
> >  It just doesn’t install to a directory it can’t write to, because you
> > told it to install system-level things.
>
> Not going to hash it out here but I didn't tell it to install system-level
> things, I told it to compile and install everything locally. Just like I do
> with lots of other source packages especially when I'm not very familiar
> with the software and wish to make sure of what I'm doing before committing
> it to the system.
>

How is the build system supposed to differentiate "local" vs "system-level"?

It's obvious from your perspective, but --prefix does not clearly
disambiguate between the two; it's also used for system-level builds. Eg.
--prefix=/opt or --prefix=/usr (for package maintainers).

AFAIK there's no standard way to tell configure to avoid touching any
system-level dirs. If you want to know what an unfamiliar package is going
to do before the fact your best bet is `make -n install`, or `make
DESTDIR=/tmp/foo install` if the package supports staged installs (sqlite
does, probably most autoconf projects do).


The tests rely on tcl, but they run here without installing the tcl
extension so not sure what's going wrong in your situation. They run on a
DB in the current directory, so maybe related to you running under a
dropbox vfs (which I don't know if implements properly locking?).


Dunno about OSX either; do you have tcl installed there?
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF()

[Richard Hipp]

On 12/25/17, Cezary H. Noweta  wrote:
>
> Could you consider an exponentiation by squaring (in the main release)
> instead of current n-multiplication of exponents?

Please test the latest trunk version (or any version after check-in
https://www.sqlite.org/src/timeline?c=fd2e0e7a) and confirm that the
modifications work for you.  Thanks for the suggested algorithm
improvement.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Committing and memory usage

[Nelson, Erik - 2]

Does committing reduce memory usage on a memory database with pragma 
journal_mode=off?

I see advice to do periodic commits in order to reduce memory usage, but I'm 
wondering if that also applies to memory databases with journaling off.

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is WAL mode more robust against corruption?

[Simon Slavin]

On 28 Dec 2017, at 8:10pm, Chris Brody  wrote:

> I am considering whether or not to recommend the WAL mode for users in the
> PhoneGap sqlite plugin that I maintain. The negative I see is the delays
> that may result at certain points from the need for database checkpoints.
> But I wondered if WAL may be more robust against possible sqlite
> corruption, in theory or in practice. Any comments?

WAL mode makes SQLite neither more nor less liable to corruption.  You should 
not be seeing corruption in SQLite no matter what mode it’s in.  If you want a 
PRAGMA to strengthen against corruption, try "PRAGMA synchronous = FULL":



If you are seeing corruption, you might want to see this checklist.



Given that you’re running on a phone/tablet, emphasis is on handling suspension 
(backgrounding) and termination of the app properly.  There are people here 
experienced with using SQLite on iPhone and Android who can criticise your 
approach.

If nothing obvious occurs to you, please post some details: which platform, 
which circumstances, are you doing multi-thread or multi-process, do you check 
result codes for your API calls, are you getting error results ?

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

[sqlite] Is WAL mode more robust against corruption?

[Chris Brody]

Hello,

I am considering whether or not to recommend the WAL mode for users in the
PhoneGap sqlite plugin that I maintain. The negative I see is the delays
that may result at certain points from the need for database checkpoints.
But I wondered if WAL may be more robust against possible sqlite
corruption, in theory or in practice. Any comments?

Thanks and happy 2018!

Chris

https://www.linkedin.com/in/chrisbrody/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] advice about schema versioning and WAL

[Dan Kennedy]

On 12/29/2017 01:28 AM, Gwendal Roué wrote:

Hello,

Season's greetings to all SQLite fellows!


I'm developping a library that would like to keep a "cache" of some information 
about the database schema. Such information are the columns of a table, its primary key, 
or its indexes. The purpose of this cache is not really speed, even if it may help, but 
mainly to avoid cluttering the sqlite3_trace/sqlite3_trace_v2 hooks with noisy pragmas 
whenever the library needs to infer some implicit information from the actual database 
schema.

This cache has to be invalidated whenever the schema changes. The Compile-Time 
Authorization Callback [1] is the perfect tool for the job, since it allows to 
identify statements that create, drop, alter tables and indexes.

Everything is fine and easy when a single connection is used in a 
single-threaded way: statements are executed one after the other, and the 
management of the schema cache is trivial.


It's much less trivial with the WAL mode. I focus on a setup which uses a 
unique writer connection, and several reader connections. All connections are 
used sequentially in their own thread, but readers and writer can run 
concurrently in order to take advantage from the WAL mode.

When a read-only connection uses a deferred transaction to enter snapshot 
isolation, it doesn't see the changes performed by other transactions. For 
example, if a reader acquires snapshot isolation before a table is altered by 
the writer, it won't see the alteration until it commits its deferred 
transaction. I wish my schema cache would behave the same.

To be precise, I only have two important needs:

1. A connection's schema cache is correct, which means that it never contains 
information that does not match SQLite's genuine view of the database schema. 
Being invalidated/empty is correct, if not efficient (the missing information 
is then loaded from SQLite).
2. Synchronization points between readers and writers are avoided (non-blocking 
access is the whole point of WAL, and I want to avoid locks as much as possible)

I was hoping that a connection would have a "schema version": an automatically 
incremented value that SQLite bumps whenever the schema is changed. That would have been 
enough for my use case. Unfortunately, PRAGMA schema_version reads the database header, 
and I thus guess that it does not play well with WAL (I'm not sure).



"PRAGMA schema_version" should work the same way in WAL mode. The pragma 
will read the "database header" from the newest version of page 1 in the 
wal file if required.


Dan.

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

[sqlite] advice about schema versioning and WAL

[Gwendal Roué]

Hello,

Season's greetings to all SQLite fellows!


I'm developping a library that would like to keep a "cache" of some information 
about the database schema. Such information are the columns of a table, its 
primary key, or its indexes. The purpose of this cache is not really speed, 
even if it may help, but mainly to avoid cluttering the 
sqlite3_trace/sqlite3_trace_v2 hooks with noisy pragmas whenever the library 
needs to infer some implicit information from the actual database schema.

This cache has to be invalidated whenever the schema changes. The Compile-Time 
Authorization Callback [1] is the perfect tool for the job, since it allows to 
identify statements that create, drop, alter tables and indexes.

Everything is fine and easy when a single connection is used in a 
single-threaded way: statements are executed one after the other, and the 
management of the schema cache is trivial.


It's much less trivial with the WAL mode. I focus on a setup which uses a 
unique writer connection, and several reader connections. All connections are 
used sequentially in their own thread, but readers and writer can run 
concurrently in order to take advantage from the WAL mode.

When a read-only connection uses a deferred transaction to enter snapshot 
isolation, it doesn't see the changes performed by other transactions. For 
example, if a reader acquires snapshot isolation before a table is altered by 
the writer, it won't see the alteration until it commits its deferred 
transaction. I wish my schema cache would behave the same.

To be precise, I only have two important needs:

1. A connection's schema cache is correct, which means that it never contains 
information that does not match SQLite's genuine view of the database schema. 
Being invalidated/empty is correct, if not efficient (the missing information 
is then loaded from SQLite).
2. Synchronization points between readers and writers are avoided (non-blocking 
access is the whole point of WAL, and I want to avoid locks as much as possible)

I was hoping that a connection would have a "schema version": an automatically 
incremented value that SQLite bumps whenever the schema is changed. That would 
have been enough for my use case. Unfortunately, PRAGMA schema_version reads 
the database header, and I thus guess that it does not play well with WAL (I'm 
not sure). Furthermore, PRAGMA schema_version clutters the tracing hook.

The most simple solution I have is to invalidate a reader's schema cache each 
time it is used. This would unfortunately invalidate the readers' caches too 
often, since most real-life uses only alter the schema at application start-up, 
which means that the schema is, practically speaking, stable after this 
initialisation phase.

Do any of you have any better idea?

Thanks in advance, regards,
Gwendal Roué

[1] https://sqlite.org/c3ref/set_authorizer.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users