Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Rowan Worth]

On Fri, 13 Mar 2020 at 08:15, Jens Alfke  wrote:

> In a messaging system, the user interface is critically important.


Agree absolutely!

I don't think it matters much whether the SQLite forum can render a page in
> "about 0.003s" as it says in the footer.


But I take issue with this -- I find latency is a critical part of a user
interface, and the vast majority of websites and mobile apps I encounter
just get worse and worse in this area. Granted most of that is probably not
rendering time, but I find it incredibly frustrating having to wait for
billions of cycles for the UI to get ready to accept my input.

What's important is usability — following discussions, finding new content,
> reading it, and composing messages.



The forum, from my brief experience today, is really awkward.
>

I can't figure out what you're actually objecting to, because the
information currently on the forum seems be arranged sensibly and fit the
criteria of being easy to read and follow.

Finding content is impossible to assess at present, although the search
functionality seems broken -- which I posted about and the composition
process was painless.

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

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Rowan Worth]

On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:

> Updated to 3.31.1 but my application started spitting out an error when
> opening the database, so I tested some earlier sqlite versions to figure
> out when the problem starts.
> I don't get the message in versions <= 3.30.1.
>
> (from the applications log)
> SQLite Version: 3.31.0
> INFO: Database opened: /tmp/database.sqlite
> WARNING: SQLITE error code: 14 cannot open file at line 36982 of
> [3bfa9cc97d]
> WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -
>

errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

   ELOOP  Too many symbolic links were encountered in resolving
 pathname,  or  O_NOFOLLOW  was
  specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f

Probably a bug? The changelog for sqlite 3.31.0 include this which is
likely related:

- * Add the SQLITE_OPEN_NOFOLLOW
 option to
sqlite3_open_v2()  that prevents
SQLite from opening symbolic links.

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

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

[Rowan Worth]

This means you're missing a dependency - in this case zlib. It's hard to
believe you don't have zlib on your system at all; probably this is
happening because your system is amd64 but the sqlite binary you've
downloaded is x86. I'm not a debian user but this should get you going:

apt-get install zlib1g:i368

(alternately, you can download the sqlite source and compile your own
binary which will be amd64 compatible)
-Rowan

On Tue, 3 Mar 2020 at 16:42, suanzi  wrote:

> My OS is Debian10 amd64,desktop is xfce4
>
> ___
> 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] Is randomblob(16) a good guid generation across multiple computers?

[Rowan Worth]

On Fri, 21 Feb 2020 at 03:59, Jens Alfke  wrote:

> > On Feb 20, 2020, at 10:48 AM, Richard Hipp  wrote:
> >
> > That assumption is not correct for SQLite, which does you a
> > cryptographically strong PRNG.  And the SQLite PRNG is seeded from
> > /dev/random on unix.
>
> Not quite; I'm looking at the function unixRandomness() in SQLite 3.28.
> It's seeded from /dev/urandom, which on Linux "will produce lower quality
> output if the entropy pool drains, while /dev/random will prefer to block
> and wait for additional entropy to be collected." (I'm quoting the macOS
> man page, which goes on to say that on macOS it always returns high-quality
> randomness.)
>

There are a lot of myths in this area, but from what I gather /dev/urandom
is totally fine for cryptographic purposes in modern linux, and any
advantages of /dev/random are highly overstated.

https://www.2uo.de/myths-about-urandom/
 -Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Rowan Worth]

On Mon, 10 Feb 2020 at 12:53, Simon Slavin  wrote:

> On 10 Feb 2020, at 4:26am, Rowan Worth  wrote:
>
> > See also PRAGMA data_version when it comes to polling the DB, the return
> value of which changes when another process modifies the DB. IIRC the
> implementation of this depends on a value in the DB header page, so it may
> be sufficient to only monitor the main DB file for changes.
>
> Theoretically, one monitors the database file for its "last change"
> timestamp (you might know this as the "touch timestamp").


If, as previously suggested, you're using inotify or FSEvents (or
ReadDirectoryChangesExW) you can avoid polling this metadata, although I
realised that in WAL mode the if the updated DB header page might only be
stored in the write ahead log for some period of time, in which case you
definitely need to watch both.

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

Re: [sqlite] How to group this?

[Rowan Worth]

On Mon, 10 Feb 2020 at 11:12, Richard Damon 
wrote:

> On 2/9/20 7:24 PM, Bart Smissaert wrote:
> > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
> > 
> > 1308 15/Mar/2013 Systolic 127 701559
> > 1308 15/Mar/2013 Diastolic 81 701568
> > 1308 27/Jun/2013 Systolic 132 701562
> > 1308 27/Jun/2013 Systolic 141 701563
> > 1308 27/Jun/2013 Systolic 143 701564
> > 1308 27/Jun/2013 Diastolic 82 701571
> > 1308 27/Jun/2013 Diastolic 85 701572
> > 1308 27/Jun/2013 Diastolic 94 701573
> > 278975701 08/Mar/2018 Systolic 136 1583551
> > 278975701 08/Mar/2018 Diastolic 99 1583591
> > 278975701 04/Apr/2018 Systolic 119 1583552
> > 278975701 04/Apr/2018 Systolic 124 1583553
> > 278975701 04/Apr/2018 Systolic 130 1583554
> > 278975701 04/Apr/2018 Diastolic 74 1583592
> > 278975701 04/Apr/2018 Diastolic 75 1583593
> > 278975701 04/Apr/2018 Diastolic 85 1583594
> >
> > These are systolic and diastolic blood pressures for 2 people with the
> ID's
> > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
> > Systolic and diastolic values are a pair and should be grouped in one
> row.
> > This is no problem if there is only one pair for one date, but sometimes
> > there multiple pairs per date.
> > The pairing should be based on the rowed if there are multiple pairs by
> > date, so for ID 1308
> > I should get:
> >
> > 127/81
> > 132/82
> > 141/85
> > 143/94
> >
> > What should be the SQL to group like this?
> >
> > RBS
>
> To be honest, I think the problem is fundamentally badly designed. You
> say pair the two readings by ROWID, but they of course don't have the
> same ROWID, but you seem to be saying to pair them sorted by ROWID (1st
> to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't
> the same number of each? You may say that you know that there will
> always be the same number, but there is no constraint that forces this,
> so any general program is going to have to deal with the possibility
> (and at least throw out an error when it sees that).
>

Yeah, it would have been easier to group the readings at write time - eg.
via another column storing the time of day or the "nth reading of the day".
You could still add the latter, post-hoc.

Note that ROWID is not persistent -- see quirk #6 here:
https://www.sqlite.org/rowidtable.html

I would expect that VACUUM's renumbering happens to maintain the row order,
but I doubt it's guaranteed. If you do have an INTEGER PRIMARY KEY it's
better to refer to that directly.

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

Re: [sqlite] notify all processes of database modification

[Rowan Worth]

On Sat, 8 Feb 2020 at 04:02, Jens Alfke  wrote:

> > On Feb 7, 2020, at 6:23 AM, Kees Nuyt  wrote:
> >
> > Anyway, SQLite doesn't have such a mechanism by itself.
> > Maybe inotify is useful to you :
> >
> > https://en.wikipedia.org/wiki/Inotify <
> https://en.wikipedia.org/wiki/Inotify>
> > http://man7.org/linux/man-pages/man7/inotify.7.html <
> http://man7.org/linux/man-pages/man7/inotify.7.html>
>
> Or on Apple platforms, FSEvents.
>
> On any platform, you'd need to monitor both the main database and the .wal
> file.
>
> And the notification would trigger soon after a transaction began making
> changes, although the changes wouldn't be visible to you until the commit,
> so you'd probably need to start polling until you see the changes, with
> some heuristic about timing out if nothing happens for a while (e.g. if the
> transaction is aborted.)
>

See also PRAGMA data_version when it comes to polling the DB, the return
value of which changes when another process modifies the DB. IIRC the
implementation of this depends on a value in the DB header page, so it may
be sufficient to only monitor the main DB file for changes.

https://www.sqlite.org/pragma.html#pragma_data_version
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries

[Rowan Worth]

On Fri, 7 Feb 2020 at 16:25, Clemens Ladisch  wrote:

> Jürgen Baier wrote:
> >   CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) );
> >   CREATE TABLE staging ( ATT1 INT, ATT2 INT );
> >
> > Then I execute
> >
> >   DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 =
> staging.att1 AND main.att2 = staging.att2)
> >
> > which takes a very long time.
>
> DELETE FROM main WHERE (att1, att2) IN (SELECT att1, att2 FROM staging);
>

Note using row-values requires sqlite 3.15.0 or later -- which is three
years old at this point, but every version I have on hand still says
'Error: near ",": syntax error' so I thought I'd track down the details :)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Rowan Worth]

On Tue, 28 Jan 2020 at 06:19, Richard Hipp  wrote:

> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>

I think embedded does capture SQLite well though. For a lot of devs the
target API is the important thing, and whether there are threads behind the
scenes is something of an implementation detail. But it is certainly a nice
feature of SQLite's implementation, perhaps "embedded, threadless" would
work to clarify that (although it's not an objectively true description
once WORKER_THREADS enter the equation).

"in-thread" also has a certain appeal - it's not a term I've seen used
before but it makes sense as a stronger version of "in-process."

I can't find any general terms for a library which spawns threads vs. one
which doesn't.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Causal profiling

[Rowan Worth]

On Fri, 27 Dec 2019 at 06:11, Jens Alfke  wrote:

>
> > On Dec 25, 2019, at 2:53 PM, Doug  wrote:
> >
> > I wrote an application in Qt which uses SQLite. Therefore, I invoke
> SQLite functions with some wrapper. For a 9% performance improvement in
> SQLite using the direct call versus indirect call (as discussed in the
> talk), cannot the wrapper functions be changed so my application doesn't
> know the difference?
>
> This change would break the API that lets you set concurrency levels per
> connection; instead, the concurrency would be hardcoded at compile time.
> _You_ may not be using this API, but there are definitely developers who do.
>

Note that API is already inherently unreliable though, as compiling with
-DSQLITE_THREADSAFE=0 implies -DSQLITE_MUTEX_OMIT which eliminates the
mutex calls entirely. Attempting to change the concurrency level at runtime
via sqlite3_config() against such a binary will have no effect.

(this is explained in the documentation for sqlite3_threadsafe())

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

Re: [sqlite] Persisting Database Lock Issue

[Rowan Worth]

On Fri, 13 Dec 2019 at 23:50, 고예찬  wrote:

> Hello, I am experiencing `database is locked` error. I wonder if anyone has
> gone through or resolved similar issue.
>
> To illustrate, I have a .db file with below settings:
> ```
> PRAGMA journal_mode=WAL;
> PRAGMA wal_autocheckpoint=128;
> PRAGMA journal_size_limit=0;
> ```
> and I run two programs in python - one writing the data 1 row per second,
> one read / upload / then delete the data every 30 seconds.
>
> Normally, it works fine but occasionally after power failure and reboot(the
> programs run on an IoT device), the reader program throws `database is
> locked` error and never goes away after since, while the writer program
> seems to work fine. This lock doesn't go away even after program/system
> restart.


This is an odd symptom as the fcntl/POSIX file locks (sqlite's default
locking mechanism under linux) are attributed to processes, and the kernel
never writes them to disk. Is there some kind of network file system
involved or does the IoT device have its own SSD?


> One interesting fact is that if I do `cp data.db data1.db && rm data.db &&
> mv data1.db data.db`. The error always goes away completely. Though it
> happens again after several hard reboot.
>

This series of commands gets around the locks because it creates two copies
of the database contents stored in different inodes. The kernel's locking
table is based on inodes rather than file paths, so you end up with
existing processes accessing the deleted inode and newer processes
accessing the new inode, even though they're both referring to the same
path ./data.db (sidenote: this is also a great way to corrupt databases).


> It would be very helpful if anyone can give any advice.
>

There's not really any operating system/file system mechanism which
adequately explains the symptoms you're seeing, so I'd be looking into your
software components. Figure out what is holding the lock and why. Look for
sqlite_stmts which are never disposed - these will retain their locks (the
sqlite3_next_stmt function can be useful for finding these).

Also, look for code which interacts with data.db directly using regular
file syscalls rather than going via sqlite. Because this:


> If I leave the problem as it is(writer keeps writing, and reader
> keeps retrying upon error), the error thrown changes to `database disk
> image is malformed`.
>

Strongly suggests there is a process somewhere which is touching data.db
without obeying sqlite's expected locking protocol (which prevents
corruption). Even something as innocent as an open()/close() on the file
can cause problems, because of the semantics of POSIX locks (section 2.2 of
https://www.sqlite.org/howtocorrupt.html)

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

Re: [sqlite] database disk image is malformed

[Rowan Worth]

On Fri, 15 Nov 2019 at 16:10, Graham Holden  wrote:

> I've been having problems with my email system... I don't think
> earlier attempts at sending have made it to the list, but if they
> did, apologies for any duplication...
>
> Monday, November 11, 2019, 5:46:05 PM, Jukka Marin 
> wrote:
>
> >> On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:
> >>
> >> > The main process first opens the databases and checks that their
> >> > version matches that of the software and if not, the databases are
> >> > closed and initialized by running a script.
> >> >
> >> > After closing the databases, main process forks the children and
> >> > all processes (including main process) open the databases and use
> >> > their own connections.
> >> >
> >> > What I was trying to ask was this:  If any of the children dies
> >> > (a bug in the code), main process will restart the child.  At
> >> > this point, the main process has the databases open, so the new
> >> > child receives the connections as well.  What should I do now?
> >>
>
> This isn't from personal experience, but (possibly misremembered)
> snippets from this list and (possibly incorrect) deductions from
> them...
>
> The problem (or, perhaps, "a" problem) with passing SQLite connections
> across fork() is, I think, how Linux?/POSIX? deals with file-locks on
> the underlying file-handle. IIRC, if both parent/child process share a
> file-handle, and one of them closes that file, then ALL file-level
> locks (which is what SQLite uses) on that handle are released: not
> just the ones created by the terminating process.
>

The mechanism you're thinking of exists, but only between threads of the
same process. It's also described in the "how to corrupt an sqlite database
file" writeup, section 2.2:

https://sqlite.org/howtocorrupt.html#_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_

fork creates separate processes, so it shouldn't be impacted by that
mechanism. But there's an easily identified problem: posix locks are not
inherited by a fork()ed child, and sqlite keeps track (in-memory) of what
locks it has acquired. So after a fork() the child will inherit sqlite's
internal idea of what locks are held, but none of the actual locks, making
it instantly out of sync with reality if any locks were held.

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

Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"

[Rowan Worth]

On Sat, 26 Oct 2019 at 00:07, Brannon King  wrote:

> This is a request for a small change to the handling of multiple
> connections. I think it would significantly enhance the usefulness there
> via allowing multiple "views" of the data.
>
> Consider that I have two simultaneous connections to one file, named Con1
> and Con2. They could be in one process or one thread -- that's irrelevant.
> Either one may write to the DB; we don't know yet. For starters, assume
> that their journal mode is MEMORY.
>
> Both connections begin with "begin transaction". Already I'm dead in the
> water; one of those will fail presently with "database is locked".


This is not true, unless you're using BEGIN IMMEDIATE or BEGIN EXCLUSIVE
whose express purpose is to obtain a lock. BEGIN TRANSACTION's default mode
is DEFERRED, which does not obtain any locks until the DB is actually
queried (causing it to obtain a read-lock) or modified (causing it to
obtain a write-lock).

Read-locks and write-locks can coexist, except during the window when the
DB file is actually being modified. This period is protected by an
exclusive lock, and is generally brief unless you have a transaction which
modifies lots of pages and spills sqlite's memory cache before COMMIT is
reached.

The only time you get "database is locked" is (1) if a connection requests
the write-lock (ie. tries to modify the DB) when another connection already
owns it, or (2) if a connection requests a read-lock while a writing
connection is updating/ready to update the DB _and_ said update takes
longer than the busy timeout configured for the connection.


> But it
> doesn't need to be that way! Each connection can have its own journal file,
> especially if it's in memory. Once one connection commits, the other
> connection will no longer be allowed to commit. It will be forced to
> rollback (or perhaps rebase if there are no conflicts).
>

If the other connection isn't allowed to commit, how is this materially
different from the semantics currently provided? Why would sqlite wait
until a client tries to COMMIT before raising an error when it already
knows that another write transaction is in progress?

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

Re: [sqlite] Theoretical write performance for low-throughput devices

[Rowan Worth]

On Mon, 21 Oct 2019 at 23:28, Jonathan Brandmeyer 
wrote:

> Or, how many times is each page written by SQLite for an insert-heavy
> test?  The answer appears to be "4", but I can only account for two of
> those four.
>
> I'm working on an embedded system that uses a log-structured
> filesystem on raw NAND flash.  This is not your typical workstation's
> managed flash (SATA/NVMe), or portable managed flash (SD/USB).  It's a
> bare-nekkid ONFI-speaking chip.  All reads and writes are one 2kB page
> at a time.  There is no readahead, and no write buffering by the
> driver or filesystem for page-sized writes.
>
> We got the following performance numbers out of the flash storage:
>
> Streaming reads through the filesystem: 7.5 MB/s.
> Streaming writes through the filesystem: 5.4 MB/s.
> Single insert performance through SQLite: 0.2 MB/s.
> Bulk insert performance through SQLIte: 1.3 MB/s, asymptotic for very
> large transactions.
>

There's perhaps a measurement missing here: random-access/seeking writes
through the filesystem. Which doesn't sound like it should be a factor
based on the technology involved, but it's more reflective of sqlite's
workload when updating the DB.

The smallest possible insert here modifies at least one DB page for the
table and one for the index, so that's 8kb written to the journal and 8kb
to the main DB. But bulk inserts should greatly reduce the impact of index
writes, as you said.

Hm, how does deletion/truncation perform on the target filesystem?

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

Re: [sqlite] Roadmap?

[Rowan Worth]

On Sun, 20 Oct 2019 at 17:04, Simon Slavin  wrote:

> Another common request is full support for Unicode (searching, sorting,
> length()).  But even just the tables required to identify character
> boundaries are huge.
>

Nitpick: there are no tables required to identify character boundaries. For
utf-8 you know if there's another byte to come which is part of the current
codepoint based on whether the current byte's high bit is set, and
furthermore you know how many bytes to expect based on the initial byte.

I'm less familiar with utf-16 which SQLite has some support for, but a
quick read suggests there are exactly two reserved bit patterns you need to
care about to identify surrogate pairs and thus codepoint boundaries.

Tables relating to collation order, character case, and similar codepoint
data can of course get huge, so your point stands.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opposite of SQLite

[Rowan Worth]

SQLdark is free to use for any purpose except those which benefit Anish
Kapoor or an affiliate of Anish Kapoor.
-Rowan

On Fri, 11 Oct 2019 at 03:37,  wrote:

> etiLQS or SQLead or SQLdark
> Haha
>
> On Thu, Oct 10, 2019, 3:07 PM David Raymond 
> wrote:
>
> > SQLephantine
> >
> >
> > -Original Message-
> > From: sqlite-users  On
> > Behalf Of Ned Fleming
> > Sent: Thursday, October 10, 2019 2:55 PM
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] Opposite of SQLite
> >
> >
> > > Someone asked:
> > >
> > >>> What the opposite of "Lite”?
> > >
> >
> > SQLessLite
> >
> > --
> > Ned
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online backup of in memory database

[Rowan Worth]

On Sun, 6 Oct 2019 at 23:27, Kadirk  wrote:

> How to do online backup of an in memory database (to disk)?
>
> Planning to use in memory database with 10 gb+ data, there are queries
> continuously so stopping application is not an option. Looks like for on
> disk databases it is possible with a non-blocking fashion but I couldn't
> find a way to do it for in memory database. Whenever an update comes in,
> backup process starts over so it won't finish. Any idea how to solve this?
>

Huh, the documentation explicitly points this out:

If another thread or process writes to the source database while this
> function is sleeping, then SQLite detects this and usually restarts the
> backup process when sqlite3_backup_step() is next called. There is one
> exception to this rule: If the source database is not an in-memory
> database, and the write is performed from within the same process as the
> backup operation and uses the same database handle (pDb), then the
> destination database (the one opened using connection pFile) is
> automatically updated along with the source.
>

https://www.sqlite.org/backup.html

Seems like a strange exception, I wonder why it's there?

You could still complete the backup by specifying nPage=-1 to
sqlite3_backup_step -- this requires a read lock for the duration, but the
lock methods appear to be a no-op for in-memory DBs. Presumably holding the
DB's mutex will still prevent other threads from accessing it though.

Another option if you're on linux is to put the DB file in /dev/shm, at
which point it is physically in memory but from sqlite's perspective is a
regular disk file. You'd have to check the performance characteristics
again of course.

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

Re: [sqlite] disable file locking mechanism over the network

[Rowan Worth]

On Sat, 28 Sep 2019 at 06:59, Roman Fleysher 
wrote:

> ( somewhat related to Re: [sqlite] Safe to use SQLite over a sketchy
> network?)
>
> Dear SQLiters,
>
> I am using SQLite over GPFS distributed file system. I was told it
> honestly implements file locking. I never experienced corruption. But it is
> slow in the sense that when many jobs from many compute nodes try to access
> the same database things slow down considerably.
>
> I suspect, from the point of view of file system, there is lots of
> pressure to develop fast grabbing of a lock and slow release. I think this
> is because the key to fast network file system in general is making it as
> independent as possible, thus distributed. Avoid bottlenecks. But locking
> is by definition a bottleneck.
>


> From experience, it seems that because SQLite still requests file locks,
> the performance increase is not that big. I wonder if there is a way to
> disable SQLite's internal file locking mechanism.


In my experience with SQLite over network file systems, the biggest
bottleneck has nothing to do with locking and everything to do with
synchronous I/O, journalling, and the single-writer model.

Disabling locking in your scenario is _guaranteed_ to break your jobs.
SQLite on the compute nodes will at some point read a half-committed change
to the database and return SQLITE_CORRUPT (best case), or silently compute
a garbage result (worst case). Unless, that is, the database in question is
read-only and never updated. But if that was the case there would be no
scaling issue with the number of compute nodes as read-locks do not
conflict with each other.

The best thing you can do to improve concurrency for SQLite over a
networked file system is to carefully manage your transaction lifetimes.
There are several patterns to avoid:

1. Lots of small write transaction
2. Transactions which are open for a long time
3. Write transactions which do a lot of work before taking the RESERVED lock

All of which apply to SQLite on a local filesystem, but the network latency
magnifies the effects. To elaborate quickly, synchronous I/O and data being
written twice¹ impose a significant constant-time cost per transaction,
which is why small writes are not efficient. Avoiding long-running
transactions applies to both read and write transactions, because during a
DB update there is a period where the writer needs exclusive access to the
DB. If there is a long-running read transaction active at this point, the
writer must wait for it to finish and the effect is _every_ node wanting to
access the DB has to wait for this one read transaction.

¹once to the journal, once to the main DB

Somewhat related is a transaction which reads a bunch of data before doing
any DB updates - the problem here is that another node may take the
RESERVED lock during the read phase. SQLite only supports a single writer
at a time, so when the transaction tries to proceed to its write phase it
will not be able to proceed; you end up having to abort it and redo the
read phase. This one is avoided by phrasing the transaction using "BEGIN
IMMEDIATE", which will cause SQLite to take the RESERVED lock at the start
of the transaction.


I think WAL journal mode can improve concurrency but of course it doesn't
work in a network context. Anyway, trying to shortcut SQLite's mechanisms
is almost certainly the wrong question to be asking. If you don't need
locking then you don't need consistency and you should consider whether a
DB is the right tool or whether regular files would suffice.

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

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Rowan Worth]

On Thu, 26 Sep 2019 at 13:01, Jens Alfke  wrote:

>
> > On Sep 24, 2019, at 3:48 PM, Keith Medcalf  wrote:
> >
> > There are not, to my knowledge, any client/server database systems that
> will work properly if the database resides on a network filesystem (meaning
> remote multi-access).  The "client" is remote from the "server" because the
> "client" and "server" use some sort of IPC mechanism (of which a network is
> an example) so that the "client" can send commands to and receive responses
> from the "server".
>
> Well, obviously. “Client/server” means databases like MySQL or Oracle. No
> one would run those with the server using a networked file system.
>

Haha, you'd be surprised!

https://blogs.msdn.microsoft.com/varund/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive/

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

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Rowan Worth]

On Wed, 25 Sep 2019 at 12:58, Simon Slavin  wrote:

> When I first learned the SQLite had problems with Network File Systems I
> read a ton of stuff to learn why there doesn't seem to be a Network File
> Systems that implements locking properly. 
>
> Still, I wonder why someone working on a Linux network file system, or
> APFS, or ZFS, hasn't done it.
>

I'm not sure what your definition of "locking properly" is or when your
research was done, but POSIX advisory locks¹ work just fine on linux over
nfs (since at least v3) and lustre.

¹ That's the F_SETLK/F_GETLK/F_SETLKW commands via the fcntl() syscall,
which is also sqlite's default locking mechanism under UNIX.

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

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Rowan Worth]

On Wed, 25 Sep 2019 at 05:14, Randall Smith  wrote:

> I have an application where remote users will be connecting to a SQLite DB
> over a network connection that seems to be somewhat sketchy (I can't
> characterize it well; I'm hearing this second-hand).
>
> My question is: Do the commit-or-rollback semantics of SQLite transactions
> work if the connection to the file system is weird?  For example, do I get
> a rollback if the connection is lost mid-transaction or something?


SQLite's transaction protocol provides durability in the face of a crash or
severed connection - a partially committed transaction will be rolled back
as you suspected. Whether that comes into play also depends on the
behaviour on the filesystem though; eg. following a "lost connection" some
file servers will still consider the file locked by the disconnected client
until they hear back from said client or a timeout expires. I've seen some
configurations where this seems to be retained indefinitely, locking all
clients out of the DB until the server is restarted.

Also SQLite relies on synchronous i/o operations to provide consistency and
durability, and if the filesystem skimps on these (to try and provide
better performance or whatever) you will likely end up with a corrupt DB.
These synchronous ops tend to become the limiting factor in DB performance
- a write transaction in this environment is quite expensive and you won't
get anywhere close to the write throughput that you would from a
traditional RDMS.

  Or, is the underlying assumption with transactions that the connection
> between SQLite code and file system is 100% reliable?
>

As long as the filesystem provides a consistent view to each client of
events surrounding locks and cache invalidation despite the sketchy network
then you ought to get consistent data coming from sqlite. As others have
said sqlite is very much at the mercy of the filesystem's locking
semantics, and if that is not robust in the face of a sketchy network then
it probably will not be a usable solution.

Source: hundreds of production sqlite DBs on NFS over many years. We have
had a few instances of DB corruption as well as some phantom lock scenarios
so you do have to plan for those, but by and large things work well. That
said we have a reliable network layer, and the write-concurrency caveat is
a real concern.

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

Re: [sqlite] The best way to check if a file is a sqlite3 DB file?

[Rowan Worth]

On Thu, 19 Sep 2019 at 16:03, Dominique Devienne 
wrote:

> On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch 
> wrote:
>
> > Peng Yu wrote:
> > > Is there a better way to just return an exit status of 0 for
> > > a sqlite3 DB file and 1 otherwise?
> >
> > Extract the magic header string from a known DB file:
> >
> >   dd bs=16 count=1 < some.db > sqlite3-signature
> >
> > Then you can compare it against the beginning of the file:
> >
> >   cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
> >   [ $? = 0 ] && echo SQLite DB
>
>
> I'm actually surprised sqlite3[.exe] itself doesn't have a more to do that.
> I tried using it to open a non-DB file, and it opens in interactive mode,
> with
> no error or warning, wether I use -bail or not. I was expecting a hard
> error.
>

As usual, sqlite doesn't touch the DB file until it is asked to. Try
"sqlite3 FILENAME 'pragma schema_version'" on some random file and you'll
get "Error: file is encrypted or is not a database". But note that trying
the same on a non-existent file will succeed, and additionally create an
empty file.

Technically from sqlite's perspective a non-existent or empty file is a
perfectly well-formed database, just one which happens to contain no data.


> You'd think sqlite3[.exe] is the best suited to figure out if a file is a
> valie SQLite database or not,
>

It still is: sqlite3 FILENAME 'pragma integrity_check'

Parsing the header doesn't tell you whether the DB is valid, but if that's
all you want to do I suggest the ubiquitous file(1) command which reports
"SQLite 3.x database" for a [non-empty] sqlite db file.

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

Re: [sqlite] Query for Many to Many

[Rowan Worth]

On Tue, 3 Sep 2019 at 22:17, Keith Medcalf  wrote:

> And the "," in the list of tables may be replaced by the word JOIN.  It is
> merely an alternate spelling.
>

I was surprised when this behaved differently in other SQL engines. eg. in
SQLite you can write:

SELECT col1, col2 FROM table1, table2 USING (commonId)

But in eg. postgres it must be written using "table1 JOIN table2" rather
than the comma, because postgres treats "table1, table2" as "table1 JOIN
tabel2 ON TRUE" resulting in a conflict with the USING clause.

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

Re: [sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[Rowan Worth]

On Fri, 30 Aug 2019 at 04:18, test user 
wrote:

> B. Is there any method for determining lock transitions for connections?
> - Is there an API?
> - Would it be possible to use dtrace to instrument SQLite to detect
> lock transitions?
> - Where should I be looking?
>

 On unix sqlite uses fcntl() with cmd=F_SETLK on specific byte locations to
acquire locks -- I'm not familiar with dtrace, but I've used strace + sed
to watch sqlite lock activity before. eg:

#!/bin/sh

PID=$1

replace() {
 echo "s#F_SETLK, {type=F_$1, whence=SEEK_SET, start=$2, len=$3}#$4#"
}

strace -Ttt -ff -e trace=fcntl -p $PID 2>&1 |
sed \
-e "$(replace RDLCK 1073741824 1 acquireR{PENDING})" \
-e "$(replace RDLCK 1073741825 1 acquireR{RESERVED})" \
-e "$(replace RDLCK 1073741826 510 acquire{SHARED})" \
-e "$(replace WRLCK 1073741824 1 acquireW{PENDING})" \
-e "$(replace WRLCK 1073741825 1 acquireW{RESERVED})" \
-e "$(replace WRLCK 1073741826 510 acquire{EXCLUSIVE})" \
-e "$(replace UNLCK 1073741824 2 release{PENDING+RESERVED})" \
-e "$(replace UNLCK 1073741824 1 release{PENDING})" \
-e "$(replace UNLCK 1073741825 1 release{RESERVED})" \
-e "$(replace UNLCK 1073741826 510 release{SHARED/EXCLUSIVE})" \
-e "$(replace UNLCK 0 0 release{ALL})"

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

Re: [sqlite] database like file archive

[Rowan Worth]

On Tue, 27 Aug 2019 at 21:57, Peng Yu  wrote:

> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>

Note that you wouldn't actually reclaim any disk space by deleting a file
in-place from an archive backed by sqlite -- unless you issue a VACUUM,
which rewrites the whole DB. Without a VACUUM, the free pages within the DB
would only be taken advantage of by future additions to the archive.

That may be an acceptable compromise for the
delete-small-file-from-large-archive use case you're presenting, but the
fact that archive formats are generally designed to minimise storage
requirements goes a long way towards explaining why the scenario is not
well-catered for. It also raises the question of whether an archive is the
correct tool for the job!

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

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

[Rowan Worth]

On Mon, 12 Aug 2019 at 16:55, Kira Backes  wrote:

> > When you do not use explicit transactions, SQLite will automatically
> create implicit transactions.
>
> But the documentation only says that an implicit transaction is
> created for data-changing queries like INSERT:
>
> https://www.sqlite.org/lang_transaction.html
>
> > Any command that changes the database (basically, any SQL command other
> than SELECT) will automatically start a transaction if one is not already
> in effect
>

Yeah I see what you mean... That sentence should not be taken in isolation,
but I agree it's misleading! It's clarified a few paragraphs down (after
noting that a "deferred" transaction is the default mode of operation):

Thus with a deferred transaction, the BEGIN statement itself does nothing
> to the filesystem. Locks are not acquired until the first read or write
> operation. The first read operation against a database creates a SHARED
>  lock and the first
> write operation creates a RESERVED
>  lock.
>


I think the initial statement should read:

"Any command that changes or reads the database will automatically start a
transaction if one is not already in effect"

Because the actual SQL command is irrelevant -- you can still run
INSERT/CREATE or other queries which represent write operations on a
database which is EXCLUSIVELY locked by another process, as long as the
query only involves temporary tables (which is kind of a cop-out because
such queries don't have to touch the database, but it just further
highlights the fact that DB access/modification is the crucial component
and not the SQL command).

Btw your original comment said "as far as I can tell this is not documented
anywhere," but the behaviour is unsurprising after understanding sqlite's
locking model, which is documented here:

https://www.sqlite.org/lockingv3.html

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

Re: [sqlite] round function inconsistent

[Rowan Worth]

On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera 
wrote:

> Ok, I think it happens even before the casting.  This should be,
> 3.2598, and yet, it's 3.26.
>
> sqlite> SELECT 0.005 + 3.2548;
> 3.26
>

Note that no arithmetic is required to see these symptoms:

sqlite> SELECT 3.2598;
3.26

But also note that when floating point numbers are displayed, they are
_almost always_ simplified for ease of reading. Try this C code:

#include 

int
main(int argc, char** argv)
{
double d = 3.2598;
printf("%f\n", d);
return 0;
}

It prints 3.26. There is also a kind of rounding that happens at the
display level, which can make it tricky to appreciate what is going on
behind the scenes. You can adjust that behaviour in C; eg. changing %f to
%.72f gives you "more precision":
3.2597868371792719699442386627197265625000

Also note that in practice 3.2598 _is_ 3.26. You can check this
in sqlite:

sqlite> SELECT 3.2598 = 3.26;
1

64-bit floating point just doesn't have the accuracy to represent the
difference. Here's what changing the lowest bit looks like around 3.26:

0x400a147ae147ae13 =~ 3.2593
0x400a147ae147ae14 =~ 3.26
0x400a147ae147ae15 =~ 3.2602

One way to think of floating point is that each 64-bit value represents a
"bin" of closely related numbers - the decimal value I've written on the
right hand side here represents the (approximate) bin centre.
3.2598 is closer to 3.26 than 3.2593 so it gets
lumped into 0x400a147ae147ae14

This is all very subtle which is why some languages/software offer actual
decimal arithmetic. sqlite does not, but there's also nothing to stop you
from storing eg. strings in the DB and converting to/from decimal
representations in your application.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readfile() enhancement request

[Rowan Worth]

On Sat, 18 May 2019 at 00:34, Tony Papadimitriou  wrote:

> It’s quite often (for me, at least) the case I need to do something like
> this from the command line:
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
> copied from some other app’)
>
> The problem is the multi-line text cannot be copy-pasted directly into the
> command line as the first newline will terminate the command.  So, I’ve
> been using readline() like so:
>

I haven't tested on windows, but FWIW the interactive sqlite3 shell has no
such limitation - statements are free to span multiple lines. ie. you can
type:

   sqlite> INSERT INTO T VALUES('simple field', '«paste-
  ...> multi-line-
  ...> content»')
  ...> ;

I guess you'd also prefer to not have to type out the full INSERT statement
each time, but this kind of seems like a shell problem more than an sqlite
problem!
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Location of error in SQL statements ?

[Rowan Worth]

On Tue, 7 May 2019 at 16:00, Eric Grange  wrote:

> Is there are way to get more information about an SQL syntax error message
> ?
> For example on a largish SQL query I got the following error message
>
> near "on": syntax error
>
> but as the query is basically a long list of joins, this is not too helpful
> ;)
>

It's not clear from the docs whether this is guaranteed, but a quick look
at the code suggests that the pzTail argument (if provided to
sqlite3_prepare_v2) is also updated when there's a parse error. You might
be able to rely on that to infer how far through the statement the problem
lies.

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

Re: [sqlite] Go & SQLite asserts

[Rowan Worth]

On Fri, 3 May 2019 at 16:03, Dominique Devienne  wrote:

> On Mon, Apr 29, 2019 at 9:49 PM Russ Cox  wrote:
>
> > On Mon, Apr 29, 2019 at 3:28 PM Richard Hipp  wrote:
> > For what it's worth, it was not clear to me until just now that the
> article
> > existed to push back on a general "asserts considered harmful" notion. I
> > was reading it as primarily documenting SQLite coding conventions. The
> > reference to Go makes more sense to me now.
> >
>
> Very interesting discussion between two of my very favorite programmers.
> Thanks.
>

Seconded!


> But I also regret the lack of invariant/always/never (I dare not say
> assert...) in Go. I wish Robert/Rob/Ken/Russ instead of shunning
> "assertions" would
> promote "proper use" of invariant/always/never by making them a built-in
> part of the language, and have the compiler perform the kind of static
> analysis
> Richard mentions in this thread. My $0.02. --DD
>

This is getting off-topic, but the immediate question arising from this
proposal is what would you _do_ in response to a violation? For those
unfamiliar with go, the language doesn't feature exceptions. The only
stack-unwinding mechanism is to call panic(), which will terminate the
entire process (unless captured by a call to recover() before unwinding the
entire stack).

Go is also a very modular language, designed to be very easy to drop other
people's code/libraries into your project. But there's a very clear
convention set out surrounding the use of panic() - it should never form
part of a module's public API. It's not an error reporting mechanism and
callers should never be expected to invoke recover() just to use your
module.

I can't see another way to implement invariant/always/never other than
creating some "blessed" form of panic(), and if you do that then the
cross-module convention shifts from a very clear "DO NOT" to a more subtle
"UNLESS THINGS HAVE GONE REALLY SIDEWAYS." Now there's a judgement call
involved in whether it's acceptable for a given API call to bring the whole
process down when provided nonsense state, and since judgement varies
between individuals the effect that has on the entire go ecosystem could be
huge.

And I guess this is the core of the disagreement - when used "correctly"
assertions are informative and helpful but there's no way to enforce
"correct" usage. Admittedly you could say this about a lot of programming
constructs but I don't think we can do away with arrays just yet!

But it also highlights one of the great things about go, which is that you
don't have to agree with all its design decisions to reap their benefits.
There's real advantages to sticking to your guns when it comes to
conceptual integrity, even if it doesn't suit absolutely everybody. It's
why we like the "lite" in sqlite, no?

-Rowan (aka sqweek -- hi Russ ^_^)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state

[Rowan Worth]

On Wed, 1 May 2019 at 19:30, Frank Kemmer  wrote:

>
> https://github.com/xerial/sqlite-jdbc/blob/14839bae0ceedff805f9cda35f5e52db8c4eea88/src/main/java/org/sqlite/core/CoreResultSet.java#L86
>
> Here we see, that colsMeta == null results in throwing the seen exception.
>
> But how can colsMeta be null in a valid resultSet?
>
> Does anybody have a deeper understanding how this can happen in the sqlite3
> code?
>

This has nothing to do with the sqlite3 code itself but judging by the
comments in the jdbc binding, colsMeta being null implies that the
ResultSet has been closed. But I'm not sure where colsMeta is initialised,
it appears some other class is responsible for that.

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

Re: [sqlite] Regarding sqlite3 reliability in using in service oriented architecture

[Rowan Worth]

On Mon, 29 Apr 2019 at 01:22, Lullaby Dayal 
wrote:

>
> Considering all this, I have written a test application running on Linux
> with sqlite3 library in serialized mode. My test application has 200
> parallel threads in which 100 threads are executing SELECT * operation from
> a table and 100 are executing update table (alternate fields in alternate
> run) command in auto-commit mode (while(1)). I haven't verified the data
> correctly written in database as I only rely on return code and I was
> stress testing. I expect at some point it should produce SQLITE_BUSY
> command at some point of time. But it didn't.
>
> Only thing I got is:- while the test application is running, in a separate
> SQLite command prompt I open the same database and executed .tables
> command.  This time, I got a database locked error in my test application.
>
> So my questions are:-
>
> 1. In auto-commit mode in serialized threading mode, how command queueing
> works?
>
2. Multiple simultaneous calls to sqlite_exec() performing Multiple write
> commands or read commands while write is in progress - will this be handled
> by sqlite_exec() itself? Or does the application need to do some kind of
> locking to avoid such situation as mentioned in the FAQ? In serialized
> mode, sqlite3 implements its own locking, right? Do application need to do
> a high level locking beyond this?
>

In serialized threading mode using sqlite3_exec, I don't believe you'll get
any DB concurrency between threads -- rather each thread will take turns to
run sqlite3_exec (which holds a connection-level mutex while it executes).
This is why you never see SQLITE_BUSY.

To allow different threads to access the DB concurrently, they need to use
separate connections. However given that a lot of what sqlite does is i/o
bound you won't necessarily find any performance benefits from
multi-threading. If your DB is small enough and no other processes are
updating the DB, you could think about upping the cache_size PRAGMA and
using shared-cache mode for the connections to minimise i/o.

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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Rowan Worth]

Richard Hipp wrote (quoting from several emails):

> The problem is that Git now thinks that 9b888fcc is the HEAD of master
> and that the true continuation of master (check-in 4f35b3b7 and
> beyond) are disconnected check-ins
>

Because from the git perspective it _is_ still the HEAD -- there's been no
further changes made on top of that commit. The "true" changes are in a
separate branch hanging off some historic fork point.

I don't understand this part.  From the Fossil perspective, moving a
> check-in from one branch to another is just adding a new tag to that
> check-in.  No history is changed.  The DAG of check-ins (the block-chain)
> is unmodified.


Hm. Initially, the commits on the primary branch looked like this:

1. HISTORY - FORK - MISTAKE

Then you changed it to this:

2. HISTORY - FORK - FIXED - BEYOND

How can you justify the claim that history was unchanged on trunk between
time (1) and time (2)? You haven't just added a new check-in to the branch
in this situation (which git is more than happy to do via cherry-pick),
you've also erased the MISTAKE check-in.

What happens to fossil users who updated trunk while MISTAKE was the head?
Does the next update somehow pathfind to the new BEYOND head, backtracking
via FORK?

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

Re: [sqlite] importing a large TSV file

[Rowan Worth]

On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte 
wrote:

> Hello Gert !
>
> I normally do this (be aware that if there is a power outage the
> database is screwed):
>
> ===
>
> PRAGMA synchronous = OFF;
> begin;
>
> --processing here
>
> commit;
> PRAGMA synchronous = ON;
>

You can probably leave the pragma alone without overly affecting import
time tbh. The main thing is putting all the work into one transaction, and
at that point you're down to 2 or 3 sync() calls. I guess there's still
value in not having to wait for the journal to hit disk though. Maybe even
PRAGMA journal_mode = OFF would be appropriate.

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

Re: [sqlite] Handling ROLLBACK

[Rowan Worth]

On Sun, 3 Mar 2019 at 20:53, Keith Medcalf  wrote:

> My observation (on the current tip version 3.28.0) of Schrodingers
> Transactions is that if there is (for example) a transaction in progress
> and that is COMMIT or ROLLBACK, then the changes are either committed or
> rolled back and the explicit transaction is ended (that is, autocommit
> becomes True).
>

You kind of covered this in a previous email where you talked about "COMMIT
or ROLLBACK command completing successfully", but sqlite has a special case
around COMMIT which I think is worth mentioning in detail:

If COMMIT fails with SQLITE_BUSY, it means the EXCLUSIVE lock could not be
obtained within the configured timeout, because of other concurrent
activity on the DB. In this case, the transaction's changes are not
committed or rolled back -- it _remains open_. It is then up to the
programmer to decide whether to ROLLBACK and give up, or try to COMMIT
again at a later date.


> Statements which were in progress that were permitted to proceed (ie,
> where the next step did not return an abort error) continue with a read
> lock in place (ie, as if they were part of an implicit transaction on the
> connection) and once all those statements are completed, the read locks are
> released.  You can BEGIN another transaction on the same connection (or
> another connection) and the locks will be escalated as you requested in the
> same fashion as would normally be expected for an in-progress implicit
> transaction.
>

Wait what? If I've understood correctly you're describing a situation where
statements outlive their transaction context? Something like:


sqlite3 *db; // initialised elsewhere

sqlite3_stmt *stmt;
int rc;

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
stmt = sqlite3_prepare_v2(db, "SELECT * FROM some_table", -1, , 0);
rc = sqlite3_step(stmt); // advance to first row
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

rc = sqlite3_step(stmt); // advance to second row?
...
sqlite3_finalize(stmt);

And the sqlite3_step() following the transaction acquires a new read-lock?
Or it prevents the COMMIT from dropping the read-lock?

It seems bizarre that this is even possible, so I may have misunderstood!
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deserialize a WAL database file

[Rowan Worth]

On Fri, 1 Mar 2019 at 18:26, Lloyd  wrote:

> I have two database files. One in Rollback mode and the other in WAL mode.
> I am able to serialize, deserialize and prepare a SQL query against the
> rollback database. When I do the same against the WAL database file, the
> 'prepare' statement fails with code '1'. Is it not possible to do this on
> WAL based database file? A sample code fragment is given below-
>
> sqlite3 *dbHandle=nullptr;
> if (sqlite3_open_v2("db_filename", , SQLITE_OPEN_READONLY, NULL)
> != SQLITE_OK){//error}
>
> sqlite3_int64 sz=0;
> unsigned char* mem=sqlite3_serialize(dbHandle,"main",,0);
>
> if(sqlite3_deserialize(dbHandle, "main", mem, sz,
> sz,SQLITE_DESERIALIZE_READONLY) != SQLITE_OK){//error}
>
> char* Query = "select * from test";
> sqlite3_stmt *statement = nullptr;
> int res=sqlite3_prepare_v2(dbHandle, Query, strlen(Query), , 0);
> //res is 1 for WAL
>

I can't see any obvious reason for this. The WAL database definitely has a
table called test? Is sqlite3_serialize returning non-NULL in the WAL case?
Check what sqlite3_errmsg(dbHandle) has to say.

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

Re: [sqlite] Disk I/O errors

[Rowan Worth]

On Sun, 24 Feb 2019 at 01:55, Tim Streater  wrote:

> (sorry for the duplicate - vibrating finger).
>
> I have a hosted web site using the SQLite functions from PHP. The page
> where PHP is used was failing, and on investigation this is because an
> SQLite function called from within PHP is now returning:
>
> Code: 10 (SQLITE_IOERR)
> Msg:  disk I/O error
>
> I will be working with my hosting provider but, is there a way to get more
> specific information about this?
>

I don't know if it's tricky to arrange from php, but you can call
sqlite3_config(SQLITE_CONFIG_LOG, callback_function) to register a logging
function. sqlite will call it when I/O errors are encountered¹, and the
message it provides includes the underlying OS error code/message.

¹ and also at other times; you probably want to filter out SQLITE_BUSY and
SQLITE_SCHEMA events at least

The downside of this is that this is a global config change, and the
message doesn't always identify the affected database (but for I/O errors
the filename is generally there).

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

Re: [sqlite] Index on expression optimization

[Rowan Worth]

On Fri, 15 Feb 2019 at 16:13, Wout Mertens  wrote:

> sqlite> create index b on t(b) where b is not null;
> sqlite> explain query plan select b from t where b is not null;
> QUERY PLAN
> `--SCAN TABLE t USING COVERING INDEX b
> sqlite> explain query plan select b from t where (b is not null)=1;
> QUERY PLAN
> `--SCAN TABLE t
>
> So basically, match the where of the index as part of an expression.
>
> I'm guessing the answer is no, but I thought I'd ask anyway
>

Hm, interesting. The docs have something to say about this -- from
https://www.sqlite.org/expridx.html section 1:

The SQLite query planner will consider using an index on an expression when
> the expression that is indexed appears in the WHERE clause or in the ORDER
> BY clause of a query, *exactly* as it is written in the CREATE INDEX
> statement. The query planner does not do algebra. In order to match WHERE
> clause constraints and ORDER BY terms to indexes, SQLite requires that the
> expressions be the same, except for minor syntactic differences such as
> white-space changes.
>

"The query planner does not do algebra" seems damning, but OTOH your query
_does_ have the exact index expression embedded in the WHERE clause...

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

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

[Rowan Worth]

On Tue, 12 Feb 2019 at 15:07, Rowan Worth  wrote:

> Huh, fascinating stuff. I'm not an sqlite developer but I can shed light
> on some of your questions.
>
> On Tue, 12 Feb 2019 at 09:54, Edwin Török  wrote:
>
>> A very conservative interpretation of various fsync bugs in various OS
>> kernels [2][5] would suggest that:
>>
>> #1. the list of known OS issues [3] should be updated with an entry
>> similar to: "Linux kernels <4.13 do not guarantee to report errors
>> encountered during writeback on next fsync, therefore data corruption
>> can occur without SQLite knowing about it.", see [4]:
>>
>
> I haven't read all the links yet so forgive me if this is answered there,
> but when you refer to versions <4.13 is that referring to eg. the ext3/ext4
> implementation in those versions, or is it a wider problem which affects
> _all_ filesystems? (I'm particularly interested in whether lustre is
> affected)
>

OK having now watched the talk and read up a bit, I don't know that sqlite
needs any attention. The main problem, it seems, is that postgres relied on
certain semantics from fsync. I've extracted the expectations from the
presentation:

> Expectation #1
>
> If there's an error during the fsync, the next fsync call will try to
flush the data from page cache again.
>
>
> Expectation #2
>
> There may be multiple file descriptors per file, possibly from multiple
processes. If the fsync fails in one process, the failure will be reported
in other processes too.

Dr H and/or Dan Kennedy may want to correct me, but I'm pretty sure sqlite
doesn't hold either of these expectations. For one it's architecture is
distributed rather than client/server, which means it doesn't really have
the luxury of leaving the page cache dirty for extended periods of time.
I'm only familiar with rollback journal (not WAL), but in this mode
sqlite's write cycle goes:

1. Changes are made in RAM, and the original contents of any modified pages
are written to the rollback journal
2. Once the transaction is ready to COMMIT, the rollback journal is fsync()d
3. An EXCLUSIVE lock is acquired on the database (which blocks waiting for
active readers complete)
4. The changes held in RAM are written to the main database (via write())
5. The main database is fsync()d
6. The rollback journal is deleted
7. The EXCLUSIVE lock is relinquished

Further details are here: https://www.sqlite.org/atomiccommit.html

An error from fsync() at at step (2) or step (5) will, I think, fail and
roll back the transaction. I don't know what happens if another error is
encountered during rollback though! It would make sense if
rollback-on-error was implemented using the same hot-journal recovery
mechanism used to recover from power loss, in which case it might be a
separate process which encounters the rollback error and the hot-journal
would remain there until someone finally succeeds in rolling it back. But I
don't know if that's what actually happens.

sqlite's expectations are also spelled out in section 2 of the atomic
commit doc. In particular:

> SQLite assumes that the operating system will buffer writes and that a
write request will return before data has actually been stored in the mass
storage device. SQLite further assumes that write operations will be
reordered by the operating system. For this reason, SQLite does a "flush"
or "fsync" operation at key points. SQLite assumes that the flush or fsync
will not return until all pending write operations for the file that is
being flushed have completed.

It goes on to say that "some fsync primitives are broken" which could cause
"database corruption following power loss." It doesn't currently
acknowledge the possibility of silent corruption, ie. there's an unspoken
expectation here from sqlite that flush/fsync _will_ return an error if the
data did not hit disk.

I haven't quite nailed down the circumstances in which linux <4.13 doesn't
report this. The lwn article (https://lwn.net/Articles/752613/) mentions
one scenario, which I understand as:

1. write(3, ...)  // update page cache for fd 3
2. // ... time passes. the writeback for step (1) fails with an error
3. // due to memory pressure, kernel structures for fd 3 are evicted
(swapped-out?) from RAM
4. fsync(3) // the kernel structures for fd 3 are paged back in, but the
error info is lost, and fsync() succeeds

If this is what we're looking at it seems pretty unlikely to happen in
sqlite's usage. The window for eviction small because fsync() comes
immediately after the write()s. ie. we're likely blocked in fsync() while
the write-backs are in progress, which seems an ideal scenario for the
kernel to pass on errors.

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

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

[Rowan Worth]

Huh, fascinating stuff. I'm not an sqlite developer but I can shed light on
some of your questions.

On Tue, 12 Feb 2019 at 09:54, Edwin Török  wrote:

> A very conservative interpretation of various fsync bugs in various OS
> kernels [2][5] would suggest that:
>
> #1. the list of known OS issues [3] should be updated with an entry
> similar to: "Linux kernels <4.13 do not guarantee to report errors
> encountered during writeback on next fsync, therefore data corruption
> can occur without SQLite knowing about it.", see [4]:
>

I haven't read all the links yet so forgive me if this is answered there,
but when you refer to versions <4.13 is that referring to eg. the ext3/ext4
implementation in those versions, or is it a wider problem which affects
_all_ filesystems? (I'm particularly interested in whether lustre is
affected)


> #2. errors not reported on fsync, but on close
>
> According to [5] "A writeback error may not actually be reported by
> fsync(), however; other calls, such as close(), could return it. "
> AFAICT sqlite3 only logs errors from close, and doesn't surface them to
> the caller.
> It is unclear the exact kernel versions that are affected by this, IIUC
> from [2] then >= 4.16 would not be affect Postgresql beacuse it always
> does an fsync before close.
> Does SQLite follow WWPD here, or is it possible that SQLite3 in one
> process calls close without fsync, gets the writeback error reported
> there (which it only logs), and another process calls fsync and gets
> success, wrongly concluding that the data has safely reached the disk?
> (because you only get an error from an inode reported at most once)
>

You're correct that sqlite doesn't relay errors from close() back to the
application. In normal circumstances however, it will always call fsync()
before close(), because an fsync() is involved at the end of each
transaction in sqlite.

The exception is if you've played with the SYNCHRONOUS pragma, in which
case you've explicitly asked sqlite to skip the fsync() and in this
configuration an application can end up writing a corrupt DB without
getting an error from sqlite (I have seen this happen in practice). But I'm
hard pressed to fault sqlite for this behaviour, and the app can workaround
it by resetting the SYNCHRONOUS pragma to the default and committing one
last transaction to trigger fsync() before closing the DB. At least in
theory -- as I said I haven't been through the links so maybe the bugs mean
this workaround isn't reliable :)


> #3 how does this affect multiple processes accessing same sqlite
> database?
> If inode errors are reported at most once, could it be that a writeback
> error from changes made by process A actually get reported to process
> B, and process A never learns about it?
> If process A would always call fsync and close before relinquishing the
> lock on the journal/WAL I think this wouldn't happen, but does SQLite
> guarantee that?
>

sqlite guarantees that only one writer is active at any time. Ie. if
process A is updating the DB via write() calls, the locking protocol
ensures that no other process will call write() or fsync(), and in fact not
even read() (because the DB might be in a partially-updated inconsistent
state) before process A has finalised the transaction and called fsync().

sqlite does not close() the main DB's file descriptor while the application
holds the connection open, only after sqlite3_close() is called.

Thanks for the info,
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Committing changes to the database without releasing a writer lock

[Rowan Worth]

On Fri, 8 Feb 2019 at 20:03, Theodore Dubois  wrote:

> I'd like to essentially commit changes to disk in the middle of the
> transaction, resulting in a transaction that is atomic with respect to
> other database connections but is two atomic transactions with respect to
> the filesystem.
>

"atomic transaction with respect to the filesystem" doesn't really make
sense to me - from a filesystem perspective all you have is a bunch of
changes to various data blocks, and a sync(). Why does it matter to you
that changes are on-disk mid-transaction? What are the actual semantics
you're hoping to implement?

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

Re: [sqlite] Min/Max and skip-scan optimizations

[Rowan Worth]

On Tue, 5 Feb 2019 at 22:46, Simon Slavin  wrote:

> On 5 Feb 2019, at 8:59am, Rowan Worth  wrote:
>
> > SELECT source1, source2, ts, value
> > FROM rolling
> > WHERE source1 = 'aaa'
> >  AND ts > 1 AND ts < 1
> > ORDER BY source1, source2, ts;
> >
> > And this index:
> >
> > CREATE INDEX `sources` ON `rolling` (
> >`source1`,
> >`source2`,
> >`ts`
> > );
> >
> > What is stopping sqlite's query planner from taking advantage of the
> index, which it has chosen to use for the query, to also satisfy the ORDER
> BY?
>
> I suspect that, given the data in the table, the index supplied is not
> optimal for selecting the correct rows from the table.  SQLite may have
> decided that it needs to select on the contents of ts first, then source1.
>

This seems like a reasonable hypothesis, and explains one of Gerlando's
observations (sqlite _did_ decide to use an index on `ts` in a different
version of the DB). However, the EXPLAIN QUERY PLAN output demonstrates
that it _is_ using the `sources` index when that's the only one available:

QUERY PLAN
|--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND ANY(source2)
AND ts>? AND tshttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min/Max and skip-scan optimizations

[Rowan Worth]

On Tue, 5 Feb 2019 at 16:06, Simon Slavin  wrote:

> On 5 Feb 2019, at 8:00am, Gerlando Falauto 
> wrote:
>
> > Thank you for your explanations guys. All this makes perfect sense.
> > I still can't find a solution to my problem though -- write a query that
> is guaranteed to return sorted results, in some optimal way.
>
> Please state your table definition, and desired query including ORDER BY
> clause.  Please also tell us whether the amount of space taken up by your
> database file is important.  Then we will tell you how to make SQLite use
> an efficient way to arrive at your desired result.
>

The table definition was literally the first thing in Gerlando's initial
email, and the desired query has also been clarified. But I assume you
didn't actually read the thread before commenting; if you had you would
have also noticed that Gerlando was the first person to note that it isn't
reliable to depend on the order of results coming out of a SELECT which
doesn't have an ORDER BY clause.

IMO it would be great if we could all move on from that well established
fact and focus on the issue Gerlando is trying to raise. We have this query:

SELECT source1, source2, ts, value
FROM rolling
WHERE source1 = 'aaa'
  AND ts > 1 AND ts < 1
ORDER BY source1, source2, ts;

And this index:

CREATE INDEX `sources` ON `rolling` (
`source1`,
`source2`,
`ts`
);

What is stopping sqlite's query planner from taking advantage of the index,
which it has chosen to use for the query, to also satisfy the ORDER BY?
Instead adds an extra TEMP B-TREE step to sort the results, which slows
things down. Intuitively it seems there's a potential for optimisation
here. Which doesn't mean it's feasible, but it would be a pretty good win
to be able to provide ORDER BY for free in more circumstances so it's worth
considering.

Gerlando, what version of sqlite are you using?

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

Re: [sqlite] Feedback on article about SQLITE_BUSY

[Rowan Worth]

On Fri, 25 Jan 2019 at 13:21, Rahul Jayaraman 
wrote:

> > 1. "In the above case, since all transactions are started with
> IMMEDIATE,” -- the diagram is actually using EXCLUSIVE transactions not
> IMMEDIATE
> > 2. "they behave as writers, and concurrent transactions are blocked" —
> this implies to me that all transactions are blocked, and conflicts with
> the first sentence which says (correctly) that a write lock allows
> concurrent readers
>
> In the context of enforcing serial execution, it doesn’t matter if all
> transactions are started with `IMMEDIATE` or if all transactions are
> started with `EXCLUSIVE`. When using these behaviours, locks are acquired
> at the beginning of a transaction, and are retained till commit or abort.
> In `BEGIN IMMEDIATE` mode, all transactions try to acquire `RESERVED` locks
> at the beginning, meaning there are no readers. If one transaction
> succeeds, other concurrent transactions get blocked.
>

All true. But why start with this scenario? Having no readers and every
connection explicitly opting in to IMMEDIATE/EXCLUSIVE transactions is an
unusual set of circumstances but the article doesn't clarify this and as it
is the first example it's likely that a new reader will misinterpret this
as describing the default mode of operation.

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

Re: [sqlite] Feedback on article about SQLITE_BUSY

[Rowan Worth]

On Tue, 22 Jan 2019 at 17:24, Rahul Jayaraman 
wrote:

> From an operational perspective, which describes algorithms used and
> implementation details. I think it’s useful to understand algorithms
> because different algorithms give rise to different `busy` scenarios, and
> having a better mental model of algorithm helps reason about some of these
> scenarios. I’m not sure if the ’single writer’, ‘multiple reader’
> abstraction gives insight into this perspective. A weaker model might end
> up confusing the user, if he ends up in a scenario which he doesn’t
> understand.
>

Of course - no four words are going to be able to communicate the subtlety
of sqlite's locking protocol and the details of how it interacts with the
different journal modes. But I maintain that "single writer multiple
readers" is the simplest abstraction available to provide a solid
foundation for unfamiliar readers; YMMV.

I was also confused by this section of the article, the second sentence of
which feels inconsistent and misleading:

> NOTE: IMMEDIATE behaviour acquires a write lock which allows concurrent
> readers, but blocks other concurrent writers (discussed further in Rollback
> journal section). In the above case, since all transactions are started
> with IMMEDIATE, they behave as writers, and concurrent transactions are
> blocked thus enforcing serial execution. EXCLUSIVE behaviour acquires a
> lock which blocks concurrent readers and writers.
>
1. "In the above case, since all transactions are started with IMMEDIATE,"
-- the diagram is actually using EXCLUSIVE transactions not IMMEDIATE
2. "they behave as writers, and concurrent transactions are blocked" --
this implies to me that all transactions are blocked, and conflicts with
the first sentence which says (correctly) that a write lock allows
concurrent readers

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

Re: [sqlite] Feedback on article about SQLITE_BUSY

[Rowan Worth]

On Tue, 22 Jan 2019 at 12:51, Rahul Jayaraman 
wrote:

> > I think "single writer, multiple readers" is the simplest way to describe
> sqlite's approach to isolation
>
> I’m not sure if this summarization paints enough of a picture about how
> SQLite restricts interleaving of read & write operations between concurrent
> transactions, to guarantee isolation. For eg, in Rollback journal, a
> writing transaction looking to commit blocks other readers (as you
> mentioned), but WAL does not.
>

True, it's not the full story. But both "single writer" and "multiple
readers" remain true regardless of the journal mode in use. And the same
isolation semantics are provided either way, no? Transactions cannot see
any updates which occur after they have begun, and writers are serialised.

As you say, the details of how readers and writers interact differ. In
rollback mode, a writer trying to COMMIT prevents new readers from
starting. And in WAL mode, a long-running reader prevents a CHECKPOINT from
proceeding.

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

Re: [sqlite] Database locking problems

[Rowan Worth]

On Mon, 21 Jan 2019 at 07:21, Keith Medcalf  wrote:

> In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction
> in progress blocks a WRITE transaction and a WRITE transaction in progress
> blocks all other attempts to commence a transaction of any type on any
> other connection.
>

Nitpick: an active READ transaction blocks the COMMIT stage of a WRITE
transaction. An active WRITE transaction blocks all attempts to start
another WRITE transaction, and once it is ready to COMMIT blocks attempts
to start READ transactions.

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

Re: [sqlite] Database locking problems

[Rowan Worth]

On Mon, 21 Jan 2019 at 15:46,  wrote:

> For the moment, the solution that is working for me is to disable syncing
> with PRAGMA synchronous = OFF.  This is acceptable in this particular
> application because a power failure or OS crash will necessitate restarting
> the data gathering process anyway.
>

If the data gathering process is creating a database for later consumption
by other applications, and you potentially want to use this on a network
file system, note that you want to turn synchronous back ON and run one
final transaction to update the database on each connection you've opened.

This is because sqlite ignores errors from close() - which doesn't matter
in normal circumstances because any i/o errors should have been collected
by fdatasync(). But when you're not syncing it opens the possibility of a
silently corrupt data set, because even though the file server reported the
error sqlite suppressed it.


> In looking at the logs, I'm seeing several unlucky locking patterns.
> Let's just pick one.  Process A gets starved by process B since B does many
> write transactions in a row, each time successfully getting an EXCLUSIVE
> lock.  When B calls fdatasync() (actually fsync()), it blocks while holding
> EXCLUSIVE.  A wakes up, but A can't immediately get a SHARED lock because B
> holds EXCLUSIVE.  A goes to sleep while holding no locks, then B wakes up
> when the fsync() completes.  B then releases locks but grabs EXCLUSIVE
> again to complete its next transaction, and the cycle repeats.  A still
> can't get its SHARED lock, so it goes to sleep again, and then B continues
> to monopolize the lock.  This goes on long enough to exhaust A's patience,
> at which point SQLITE_BUSY propagates to the application.
>

Yeah, I've seen a tight loop of write transactions starve readers before -
since they've most likely backed off to sleep for 100ms at a time they have
to get really lucky to wake up while the writer is idle. It doesn't strike
me as a  common workload though? Like if you need that kind of constant
throughput without disrupting readers it may be time to consider an RDMS.


> I saw another pattern that I'm having a harder time explaining.  It looked
> almost like the process holding EXCLUSIVE got stuck waiting for the process
> trying to get EXCLUSIVE.  The latter would eventually timeout, at which
> point the former would proceed.  Shrug!  I should investigate this further,
> but look at the time.
>

I don't understand this one, and it doesn't really add up because:

1. A process holding EXCLUSIVE has the highest lock possible; it's not
waiting for anyone
2. Why is any other process blocked trying to acquire EXCLUSIVE at this
point? It shouldn't even be able to get a SHARED/RESERVED lock.

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

Re: [sqlite] SQLite error (5): database is locked

[Rowan Worth]

On Tue, 15 Jan 2019 at 02:54, Simon Slavin  wrote:

> The "just-in-time" idea mentioned in your question doesn't work in real
> life, since constantly checking mutex status keeps one core completely
> busy, using lots of power and generating lots of heat.
>

Technically "just-in-time" could be implemented fine; the normal file
locking primitives used on both windows and unix can operate in a blocking
mode, where the kernel wakes the process up once the lock has been
relinquished. sqlite just doesn't use that API.

It would be a pretty significant change for sqlite to invoke said API, and
may affect the ability to support other existing locking modes which don't
provide the same semantics and where polling _is_ required (eg. dotfile).

Also I'm not 100% sure whether it would be safe to drop in, or whether
blocking the process for lock acquisition within sqlite's locking protocol
would introduce deadlock scenarios.

FYI sqlite's backoff reaches a maximum sleep time of 100ms. Unless you're
on unix and compile without -DHAVE_USLEEP=1 in which case there's no
backoff and every sleep is 1000ms long.

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

Re: [sqlite] Feedback on article about SQLITE_BUSY

[Rowan Worth]

There seems to be a few misconceptions in the article regarding the
difference between DEFERRED/IMMEDIATE/EXCLUSIVE, and the fine details of
how different lock states interact.

Specifically, your diagrams suggest that once a writer obtains a RESERVED
lock (as happens when an IMMEDIATE transaction begins), no readers will be
able to obtain a SHARED lock. But this is not the case - while a process
holds the RESERVED lock it's only other attempts to _write_ the database
which will be met with SQLITE_BUSY.

It's only once the writer upgrades to a PENDING lock that readers will be
rejected via SQLITE_BUSY. Historical note: this wasn't part of sqlite's
original locking protocol, but was added later to solve a writer starvation
problem.

Anyway, the RESERVED -> PENDING transition only happens when either (a) the
writer is ready to commit the transaction, or (b) there's a cache spill,
ie. the transaction has modified more database page then will fit in the
configured cache (see pragmas cache_size and cache_spill for more info).


It looks like you go into more detail on the locks later in the article - I
only got to the "Shared cache mode" section. The content before that felt
misleading in terms of reader/writer locking interaction.

I think "single writer, multiple readers" is the simplest way to describe
sqlite's approach to isolation, but I'm also pretty biased because I have a
_lot_ more experience with sqlite compared to other DBs and I'm only just
getting my head around the idea of non-isolated transactions or multiple
concurrent writers!

-Rowan

On Wed, 9 Jan 2019 at 21:48, Rahul Jayaraman 
wrote:

> I wrote an article about my high-level understanding of `SQLITE_BUSY`
> errors, hoping it might help others understand concurrency in SQLite
> better. It covers scenarios under which the error shows up, while SQLite
> tries to respect its isolation guarantee.
>
> https://www.activesphere.com/blog/2018/12/24/understanding-sqlite-busy
>
> I’d appreciate feedback on the article. Apologies if this is the wrong
> place to post such content.
>
> Thanks,
> Rahul
> ___
> 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] Question about floating point

[Rowan Worth]

On Sat, 15 Dec 2018 at 15:10, Frank Millman  wrote:

> On Dec 15, 2018, at 08.58, Jay Kreibich wrote:
>
> > > On Dec 15, 2018, at 12:49 AM, Frank Millman 
> wrote:
> > >
> > > I know that floating point is not precise and not suitable for
> financial uses. Even so, I am curious about the following -
> > >
> [...]
> > >
> > > With the same version of sqlite3 and the same select statement, why
> does python return a different result from sqlite3.exe?
> >
> > Because the shell is altering the output to make it easier to read.
> Consider:
> >
> > sqlite> select 211496.252;
> > 211496.26
>

I just wanted to point out that python does the same thing (as does
basically every floating point display routine):

$ python
Python 2.7.5 (default, Aug  4 2017, 00:39:18)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux2
>>> a=211496.252
>>> a
211496.252
>>> print a
211496.26
>>> repr(a)
'211496.252'
>>> str(a)
'211496.26'

It's just that the python interpreter outputs the "representation" by
default.

(Interesting thread; wow at the uₙ = 111 - 1130/uₙ₋₁ + 3000/(uₙ₋₁·uₙ₋₂)
sequence!)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] Non-EXCLUSIVE modes in dot-file locking strategy

[Rowan Worth]

On Sun, 16 Dec 2018 at 05:00, Pierre Tempel  wrote:

> > “... programs which rely on [the O_CREAT and O_EXCL flags of
> > open(2) to work on filesystems accessed via NFS version 2] for
> > performing locking tasks will contain a race condition. The solution
> > for performing atomic file locking using a lockfile is to create a
> > unique file on the same filesystem (e.g., incorporating hostname and
> > pid), use link(2) to make a link to the lockfile and use stat(2) on
> > the unique file to check if its link count has increased to 2. Do not
> > use the return value of the link(2) call.”
> >
>
> The question is if this applies to current NFS versions (3, 4).
> However, If I read this correctly, the link count strategy can be used
> to encode more info. However, this again depends on OS-specific
> extended attributes, which is contrary to the general file-system based
> locking I was looking to create.
>
> As an aside, I don't quite understand why and how a reader/writer lock
> requires/uses a counter. I'd appreciate a link to any documentation
> about that.
>

For a reader/writer lock, the usual semantics allow a single writer but
arbitrarily many readers. However we're in a situation where we don't have
actual locking primitives. ie. each reader will need its own lockfile -
they can't share one - and this the problem a counter addresses (by
providing unique files).

Of course it also creates another problem, as now how do you atomically
check whether anyone has a read lock? Or prevent two processes
simultaneously creating a read-lockfile and a write-lockfile?

Encoding information into the attributes of a common lockfile seems to
suffer similar race conditions, but lets say there's a way to do it. Now
one process comes and starts a transaction; obtains a read lock. While it's
busy a second process comes and also acquires a read lock. The second query
finishes really quick and goes to relinquish its read lock. However it does
this, it must leave the overall database in a read-locked state, because
the first process is still busy! How would you achieve that without a
counter or equivalent mechanism?

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

Re: [sqlite] Parallel reading can be slow on APFS

[Rowan Worth]

"The problem will affect you only if you have multiple reads/writes
happening at the same time."

ie. The problem will only manifest if the user is doing anything at all
with their computer? :P

Interesting analysis - thanks for sharing.
-Rowan



On Tue, 30 Oct 2018 at 10:13, Simon Slavin  wrote:

> This post is about a problem with Apple's new APFS file system.  The
> problem will affect you only if you have multiple reads/writes happening at
> the same time.  The problem involves merely slowing of performance, not
> corruption of databases or incorrect answers being returned by SQLite.
>
> Gregory Szorc, expert on Mercurial (revision control and tracking
> software) investigated a report that Mercurial was performing slowly on new
> Macs, and deduced that the cause was that APFS uses a kernel-level global
> lock, not only on writes (expected) but also on reads when no writing was
> happening (unexpected).  Technical details can be found here:
>
> 
>
> The more parallel operations are trying to access the storage device, the
> slower it gets.
>
> This post is intended to urge users to avoid premature optimization by
> parallel processing, and to consider that slow performance may not be
> SQLite's fault.  This is not a "Macs suck" post.  Please consider the
> following text from the article (which comes with relevant links):
>
> "While this post is about APFS, this issue of global kernel locks during
> common I/O operations is not unique to APFS. I already referenced similar
> issues in AUFS. And I've encountered similar behaviors with Btrfs (although
> I can't recall exactly which operations). And NTFS has its own bag of
> problems."
>
> Simon.
> ___
> 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] Regarding CoC

[Rowan Worth]

On Fri, 19 Oct 2018 at 19:52, Mantas Gridinas  wrote:

> I found code of conduct in documentation and I was wondering if it were
> true. Checking the version history it appears to have been added on
> 2018-02-22.
>

> 23. Do not nurse a grudge.

::sigh::
DROP TABLE grudges;

I was amassing such a good collection :(
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Rowan Worth]

On Wed, 10 Oct 2018 at 05:18, Warren Young  wrote:

> That event was the immediate spur to start this Fossil forum project, but
> if you search the archives, there are multiple threads.  Here’s one from
> about a year ago:
>
>
> http://sqlite.1065341.n5.nabble.com/Many-ML-emails-going-to-GMail-s-SPAM-td98685.html
>
> If you think that thread is about a Gmail-specific problem, you’ve
> misunderstood it.  The problem is happening because spam gets reflected off
> this list, so people click “This is Spam” in Gmail, which causes Gmail’s
> spam filters to treat all messages on the list as more spammish.  The more
> that happens, the less likely a given SQLite ML message is to get to a
> Gmail user.
>

In my experience with gmail and this list, 99% of the mails dumped in the
spam folder are from yahoo senders with gmail citing something along the
lines of "the sender claims to be from yahoo.com but gmail could not verify
this." I'm guessing this is related to DMARC/SPF or similar anti-spam
mechanisms - a quick search suggests this is a common issue and nothing to
do with mailing lists in particular.

The remaining 1% feature senders from other domains, but otherwise look
similar. I've never seen an actual spam sent via the mailing list, though
about a year ago I did get a few spams in response to mails I sent to the
list.

Anyway my point is I'm not seeing evidence to support the assertion that
gmail treats messages to the list in general as spammish. I'll keep a
closer eye on what's ending up in spam, to double-check whether my
"sender-identity" theory holds, or whether I'm picking up on a pattern
which isn't really there.

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Rowan Worth]

On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen  wrote:

> I type info into some labeledits and by pressing "Append to DB" button
> this code is executed (fdqGoser2 is a FDQuery)
>
>   fdqGloser2.Open;
>   fdqGloser2.Append;
>   fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
> ...
>   fdqGloser2.FieldByName('Graesk_ren').AsString:=
> Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
>   fdqGloser2.Post; [ here the error is triggered]
>
> I wonder if some kind of property i fdqGloser2 is set wrong. I think the
> only thing I have set i SQL ('SELECT * FROM Gloser2').
>
> Any ideas?
>

Not sure if this got resolved off-list, but based on the code you've posted
I wonder if there is an "fdqGloser" as well as an "fdqGloser2", and if so
what state it is in.

It reads like you're trying to update the database with fdqGloser2 -
another reason this would fail with "database is locked" is if you're
holding a separate read transaction open.

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Rowan Worth]

On Sat, 13 Oct 2018 at 00:21, Chris Locke  wrote:

> > Database is locked
>
> Close your application.  Is there a xxx-journal file in the same directory
> as the database? (where xxx is the name of the database)
> Try deleting this file.
>

For the record, "delete the journal file" is terrible advice and a great
way to corrupt a database. In the case where a program crashes
mid-transaction, the journal contains information which is crucial for
recovering to a correct database state. And in non-crash scenarios, the
journal should be cleaned up¹. So when you can see a journal file it's
likely that either:

1. some program is currently using the DB, or
2. there was a crash mid-transaction

Either way, deleting the journal is a wrong move.

¹ unless the DB is configured with PRAGMA journal_mode set to TRUNCATE or
PERSIST, in which case you've asked for the rollback journal to linger
around.

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

Re: [sqlite] [EXTERNAL] shell csv import

[Rowan Worth]

You can also filter out specific messages at the shell level:

sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the
rest with NULL' >&2)

But note that the >() syntax is not a POSIX sh feature, and will not work
in a script using a shebang of #!/bin/sh. You need to change it to
#!/bin/bash or whatever shell you have on hand. For more info see the
"Process Substition" section of the bash man page.

If you have the ability to modify the generated SQL, presumably you could
avoid the error by generating a NULL yourself for the missing column?
-Rowan

On 18 September 2018 at 14:28, D Burgess  wrote:

> Thanks hick. But that's not the problem.
> The import will always write to stderr, it's not an error
> Lots of other stuff in the script and I want to be able to catch any errors
> in the other parts of the script.
>
> On Tue, Sep 18, 2018 at 4:20 PM, Hick Gunter  wrote:
>
> > When running a script from the shell, you can redirect stderr tot he null
> > device using 2>/dev/null or to the same destination as stdout using 2>&1.
> > The latter is also very useful in crontab entries, as neglecting to
> handle
> > stderr will result in an email tot he user that contains anything written
> > there
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von D Burgess
> > Gesendet: Dienstag, 18. September 2018 08:15
> > An: sqlite-users@mailinglists.sqlite.org
> > Betreff: [EXTERNAL] [sqlite] shell csv import
> >
> > I have a script that loads csv into an existing table.
> >
> > I get this message on stderr for each row imported:
> >
> > "... expected 7 columns but found 6 - filling the rest with NULL"
> >
> >
> > We have the means to send stdout to /dev/null using the .once or .output
> >
> > Is there a way to send suppress stderr messages for a dot command?
> >
> > If not, can we have one?
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> >  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 |
> (O)
> > +43 1 80100 - 0
> >
> > May be privileged. May be confidential. Please delete if not the
> addressee.
> > ___
> > 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] Database occasionally very slow for trivial query

[Rowan Worth]

On 10 September 2018 at 22:28, Joshua Watt  wrote:

>   BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT;
> Normally, this query takes no more than 1-3 seconds to complete,
> however, on rare occasion this will take an order of magnitude more
> (20-30 seconds).
>


> pragma synchronous = normal; pragma journal_mode = WAL; pragma
> wal_autocheckpoint = 100;
>  I use the small wal_autocheckpoint because the database is read-
> mostly, and we would rather have fast readers at the expense of
> occasional slow writes.
>

I don't have much experience with WAL mode, but these two statements jump
out at me. Is it simply the auto-checkpoint which ends up taking a long
time when the machine is already under heavy i/o load?

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

Re: [sqlite] Unsigned

[Rowan Worth]

What version is that? I have 3.8.1 handy (ancient I know), which doesn't
support hex literals but:

sqlite> select cast(9223372036854775808 as integer);
-9223372036854775808

Which is different to your result...
-Rowan

On 21 August 2018 at 17:19, D Burgess  wrote:

> My problem is getting handling unsigned integers that have the high
> bit set (i.e. negative)
> (assume 64bit)
> if I insert 0x8000 (i.e. 9223372036854775808), I would
> like to be able to select and get the same unsigned decimal number
> back.
>
> select 0x8000,cast(9223372036854775808 as
> integer),printf('%lu %ld 0x%0X 0x%0X',
> 0x8000,0x8000,0x8000,
> 9223372036854775808);
>
> -9223372036854775808|9223372036854775807|9223372036854775808
> -9223372036854775808 0x8000 0x7FFF
>
> The above select shows the issues.
>
>
> On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth  wrote:
> > sqlite is pretty loose about types. The column definitions don't
> constrain
> > what is stored in the rows at all:
> >
> > sqlite> CREATE TABLE a(c INTEGER);
> > sqlite> INSERT INTO a VALUES ("fourty-two");
> > sqlite> SELECT * FROM a;
> > fourty-two
> >
> > So "UNSIGNED" seems kind of pointless as it's implies a further
> constraint
> > which is not going to be honoured. Note that sqlite does support actual
> > constraints via the CHECK clause:
> >
> > sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
> > sqlite> INSERT INTO b VALUES (-15);
> > Error: constraint failed
> > sqlite> INSERT INTO b VALUES (15);
> >
> > Although this is still allowed:
> >
> > sqlite> INSERT INTO b VALUES ("twenty");
> > sqlite> SELECT * FROM b;
> > 15
> > twenty
> >
> > You can disallow it if you get even more specific:
> >
> > sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
> > 0));
> > sqlite> INSERT INTO b2 VALUES ("twenty");
> > Error: constraint failed
> > sqlite> INSERT INTO b2 VALUES (0);
> > sqlite> INSERT INTO b2 VALUES (-1);
> > Error: constraint failed
> > sqlite> INSERT INTO b2 VALUES (1);
> > sqlite> SELECT * FROM b2;
> > 0
> > 1
> >
> > Note that the type in the column definition does have an effect - it
> > defines the column's "affinity" and may change the way data is stored.
> For
> > example:
> >
> > sqlite> INSERT INTO b2 VALUES ("2");
> > Error: constraint failed
> >
> > The TYPEOF check rejects this, but without that constraint:
> >
> > sqlite> INSERT INTO b VALUES ("2");
> > sqlite> SELECT c, TYPEOF(c) FROM b;
> > 15|integer
> > twenty|text
> > 2|integer
> >
> > ie. the text data we tried to insert was converted to an integer for
> > storage.
> >
> > Further reading: https://www.sqlite.org/datatype3.html
> >
> > -Rowan
> >
> >
> > On 21 August 2018 at 14:46, D Burgess  wrote:
> >
> >> Is there a historical reason why sqlite does not have a UNSIGNED type
> >> to go with INTEGER?
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned

[Rowan Worth]

sqlite is pretty loose about types. The column definitions don't constrain
what is stored in the rows at all:

sqlite> CREATE TABLE a(c INTEGER);
sqlite> INSERT INTO a VALUES ("fourty-two");
sqlite> SELECT * FROM a;
fourty-two

So "UNSIGNED" seems kind of pointless as it's implies a further constraint
which is not going to be honoured. Note that sqlite does support actual
constraints via the CHECK clause:

sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
sqlite> INSERT INTO b VALUES (-15);
Error: constraint failed
sqlite> INSERT INTO b VALUES (15);

Although this is still allowed:

sqlite> INSERT INTO b VALUES ("twenty");
sqlite> SELECT * FROM b;
15
twenty

You can disallow it if you get even more specific:

sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
0));
sqlite> INSERT INTO b2 VALUES ("twenty");
Error: constraint failed
sqlite> INSERT INTO b2 VALUES (0);
sqlite> INSERT INTO b2 VALUES (-1);
Error: constraint failed
sqlite> INSERT INTO b2 VALUES (1);
sqlite> SELECT * FROM b2;
0
1

Note that the type in the column definition does have an effect - it
defines the column's "affinity" and may change the way data is stored. For
example:

sqlite> INSERT INTO b2 VALUES ("2");
Error: constraint failed

The TYPEOF check rejects this, but without that constraint:

sqlite> INSERT INTO b VALUES ("2");
sqlite> SELECT c, TYPEOF(c) FROM b;
15|integer
twenty|text
2|integer

ie. the text data we tried to insert was converted to an integer for
storage.

Further reading: https://www.sqlite.org/datatype3.html

-Rowan


On 21 August 2018 at 14:46, D Burgess  wrote:

> Is there a historical reason why sqlite does not have a UNSIGNED type
> to go with INTEGER?
> ___
> 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] Safe sqlite over remote filesystem?

[Rowan Worth]

On 15 August 2018 at 14:12, Wout Mertens  wrote:

> On Tue, Aug 14, 2018 at 6:28 PM Rowan Worth  wrote:
>
> > FWIW in the building I work in we have 20-30 users hitting around a dozen
> > SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs.
>
>
> Multiple writers? I presume you use WAL mode?
>

Yes, all clients read and write the DBs. No we don't use WAL mode, as the
clients all run on different machines (WAL only works for multiple clients
on the same machine).

> We plan to migrate to an actual SQL server
> > for that reason, but please don't take it as a criticism of SQLite - I
> > think it does a marvelous job in a scenario it definitely wasn't designed
> > for.
> >
>
> Before you do that, did you see bedrockdb? http://bedrockdb.com/
>  (Although it looks like the github repo is being a little bit ignored by
> Expensify)
>

I've given a reasonable amount of thought towards some kind of
"server-fied" SQLite backend, but the main problem I'm facing is that our
application's DB layer is very coupled to SQLite and its library bindings.
The changes/abstractions required for the application to talk to such a
backend are the same amount of work to implement as having it talk to an
SQL server.

Actually replication might allow that work to be bypassed, but there's a
lot of unknowns there as to failure modes and how to manage the machines
involved in replication as users drop in and out of different DBs in an
ad-hoc fashion. Also on the infrastructure side we have a push towards
net-booted diskless nodes...

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

Re: [sqlite] Safe sqlite over remote filesystem?

[Rowan Worth]

On 15 August 2018 at 13:57, Wout Mertens  wrote:

> For the interested:
>
> In NixOS (http://nixos.org), a very interesting Linux distribution, the
> entire OS (libraries, binaries, shared files, up to and including
> configuration files) is composed out of "build products" that are addressed
> by "input hash".
>
> The input hash is calculated on everything that is used to generate the
> build product from scratch. For a text file, this is simply the content
> hash of the text. For a binary, it is the input hash of the compiler,
> libraries, build scripts, and all the build flags, plus the content hash of
> the source, all hashed together.
>
> A build product (whether file or directory) is stored in /nix/store/ hash>-human-readable-name.
>

It's not clear whether you're involved in NixOS development or just a user,
but you might be interested in ipfs:

https://ipfs.io/

It's marketed as an http competitor but if I understand correctly it's
basically a distributed hash-addressed data store. They provide fairly
regular file-system semantics on top of that I believe, but probably NixOS
would be happy with a simpler VFS which exposes the hashes themselves. See
also venti, because how can you talk about hash-addressed storage without a
reference to plan 9 ;)

-Rowan

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

Re: [sqlite] Safe sqlite over remote filesystem?

[Rowan Worth]

FWIW in the building I work in we have 20-30 users hitting around a dozen
SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs. The
number of corruptions I've seen in the last 5 years which nfs *might* be
responsible for is *very low*. The number of corruptions where nfs was
*definitely* responsible is... zero I think? Definitely single digits. And
off the top of my head I can recall four incidents where corruption was
found in a copy of the database during our backup procedure, while the main
DB was intact.

The thing is, we don't use SQLite's backup api in this procedure -- we lock
the DB and make a copy using OS syscalls. Ironically this was the cause of
the vast majority of our corruptions, because our initial implementation
did not account for POSIX fcntl locking semantics and when we closed our
file descriptor used in the copy we inadvertently dropped SQLite's locks.
Anyway, the incidents I'm talking about occurred long after that bug was
fixed. Somehow a regular copy of a file, involving a single nfs client,
wound up corrupt.

I've looked very closely at these and the corruption affected a single 4k
region of the DB. It's been observed on DBs using both 4k and 1k page
sizes. It could be a broken nfs cache but the weird part is that the first
512 bytes of the region are absolutely fine. Exactly the same as the
non-corrupt DB. Then the next 3584 bytes are random garbage.

This is not a solved mystery, and probably it never will be as it's been
many months since we've seen these particular symptoms. Maybe the problem
stemmed from nfs, but it could also be a faulty disk controller, or strange
kernel interaction with multiple fds, or a memory corruption in our
application itself (which is kind of what I lean towards given the
corruption is 512 bytes misaligned from the page boundary).


Erm, I got a bit carried away. My point is, it's not all doom and gloom.
I'm sure nfs implementations used to be much worse, and I'm not saying
they're perfect these days -- we get semi-regular reports from windows
users regarding DB corruption, and there's one particular customer we have
who's NAS server likes to keep hold of file locks on behalf of some unknown
client, long after all client machines have been rebooted (resulting in
SQLite being unable to access the DBs). And nfs on OSX seems to be a
non-starter; not sure what's going on with that client implementation but
the brief experimentation I've done with it suggested that corruption was
guaranteed.


But if your nfs solution is configured not to lie, to honour lock and sync
requests, things work well. Corruption has almost been a non-issue for us
since we fixed our application's bug. The bigger problem we face is
concurrency, due to the single writer model and the writer-starvation
solution locking new readers out of the DB until all current readers are
done (so the writer can finish). We plan to migrate to an actual SQL server
for that reason, but please don't take it as a criticism of SQLite - I
think it does a marvelous job in a scenario it definitely wasn't designed
for.

-Rowan

On 14 August 2018 at 21:07, Wout Mertens  wrote:

> Idle musing again, I'm pretty bad at dropping thoughts that are not
> immediately applicable to me, sorry.
>
> I know that multi-writer sqlite and NFS don't play well with each other.
>
> However, I wonder if some constraints could be added that would make this
> situation safe.
>
> My problem space is that of a shared NixOS package store between VMs, which
> holds metadata about the available packages:
>
>- many writers need access to the same db
>- their only communication channel is the POSIX filesystem that holds
>the db
>- they only write "seldomly", every few seconds at the fastest
>- they do read all the time
>- it is ok if read data is a little bit stale (10s is acceptable)
>- it is ok if write transactions fail and can be retried
>- it is ok if writes are slow
>- it is never ok for data to be corrupt
>
> Is there a way to use safely sqlite in this situation, perhaps by using
> extra lock files or some other additional mechanism?
>
> One solution I can think of involves sending all writes through a single
> master, via files describing changes and lots of polling, but that seems
> really outlandish.
>
> Wout.
> ___
> 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] No unicode characters in sqlite3 console prg

[Rowan Worth]

On 12 August 2018 at 00:51, Lars Frederiksen  wrote:

> Is this an error of my windows 10 cmd prompt or is it a general problem
> that
> the sqlite3 console is not able to show unicode in the cmd-window.??
>
> No problem writing to the database and show (greek) unicode characters in
> the cmd prompt as I key them, but when I want to show the data with the SQL
> SELECT * FROM Gloser then the greek word is shown with most of its letters
> as questionmarks.
>

What version of sqlite are you using? There was significant efforts to
improve windows console unicode handling awhile back, which I think made it
in for 3.12/3.13.

If you're still seeing issues with a recent sqlite can you provide a short
example which reproduces the issue?
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locks

[Rowan Worth]

On 7 August 2018 at 21:25, David Raymond  wrote:

> Correct.
>
> In rollback journal mode when one connection says "I'm ready to write now"
> it blocks any new transactions from being made, but it can't do anything
> about existing read transactions. It has to wait for them to finish their
> reads and end their transactions before it can do any actual writing.
>

To clarify, the "I'm ready to write now" phase only happens at the end of a
transaction. Each SQL command implies different lock requests:

BEGIN -> is a no-op, it just opens a transaction
BEGIN IMMEDIATE -> says "I want to write at some point in the future" (aka
RESERVED lock)
SELECT -> says "I want to read now" (aka SHARED lock)
INSERT/UPDATE/DELETE -> says "I want to write at some point in the future"
(aka RESERVED lock)
COMMIT (with a RESERVED lock) -> says "I'm ready to write now" (aka PENDING
lock)

Obtaining a RESERVED lock will fail if any other process already has the
RESERVED lock - ie. there can only be one writer. But RESERVED does not
block SHARED; other processes can still read.

It's only when the writer is ready to commit and obtains the PENDING lock
that attempts to obtain a SHARED lock will fail. This is to prevent writer
starvation eg. in the case of constant read queries. Once all pending read
transactions are finished, the writer obtains the EXCLUSIVE lock and
actually updates the main database file. Then all locks are relinquished
and everything is fair game again.


So in general the writer doesn't need to block readers for very long. The
exception is a large write transaction, which can blow sqlite's memory
cache and cause it to obtain PENDING+EXCLUSIVE before COMMIT happens (see
the cache_size pragma).

More problematic is that a long read transaction ends up blocking other
readers if a writer wants to COMMIT in the same period. I *think* WAL mode
can help with that, but I don't have much experience with it.

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

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[Rowan Worth]

On 6 August 2018 at 22:20, R Smith  wrote:

> Think of paragraphs in English as large records delimited by 2 or more
> Line-break characters (#10+#13 or perhaps only #10 if on a *nix platform)
> between texts.
>
> Each paragraph record could be comprised of one or more sentences (in
> English) as records delimited by a full-stop+Space or full-stop+linebreak,
> or even simply the paragraph end.


This is perfect because English has such strict rules and is so consistent;
how fortunate that the symbol used for delimiting sentences is never reused
for other purposes eg. to indicate an abbreviation or something crazy like
that.

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

Re: [sqlite] Reducing index size

[Rowan Worth]

On 30 July 2018 at 18:10, Eric Grange  wrote:

> @Rowan Worth
> > Doesn't that problem already exist with the current index? Except worse
> > because it's storing the cryptographic hash *and* the rowid.
>
> No, because SQLite is using a B-Tree (and with cryptographic hashes, it
> should even take less effort to balance)
>

Of course. My suggestion is to retain the same B-Tree structure, but only
store rowids in there. The B-Tree would still be ordered according to the
hash data - it would just be an indirect lookup to get at that data.

I'm not super familiar with B-Trees or eg. how many nodes need to be
touched when rebalancing, so it could well be a completely infeasible
approach performance-wise :) I feel like it just depends on whether you can
keep enough pages in cache to avoid constant I/O.

I wonder how FTS implements prefix search...
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing index size

[Rowan Worth]

On 30 July 2018 at 17:53, Eric Grange  wrote:

> @Rowan Worth
> > What if you could create a "lite" index, which stores just the rowids in
> a particular order and
> > refers back to the table for the rest of the column data?
>
> As I have millions of rows, and data could get inserted anywhere in that
> index (given the values are
> essentially random), maintaining such an index would not be light work.
>

Doesn't that problem already exist with the current index? Except worse
because it's storing the cryptographic hash *and* the rowid.

I wasn't suggesting that you manage such an index yourself fwiw, but as a
potential future feature for sqlite - a simple mechanism to control space
used by an index.

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

Re: [sqlite] Reducing index size

[Rowan Worth]

On 30 July 2018 at 17:25, Dominique Devienne  wrote:

> On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin 
> wrote:
>
> > On 30 Jul 2018, at 9:32am, Eric Grange  wrote:
> >
> > > As these are cryptographic GUIDs, the first few bytes of a values are
> in
> > > practice unique, so in theory I can index just the first few bytes
> (using
> > > substr()),
> > > this indeed reduces in a much smaller index, but this also requires
> > > adapting all queries that search by value.
> >
> > Don;t index using substr().  That would be slow because it has to keep
> > working out substr().  Instead create another column in the table called
> > "hash" which contains the first few bbytes, and index that column instead
> > of the full-length one.  If you define it
> >
> > hash BLOB UNIQUE
> >
> > then SQLite will make up and maintain its own index on the column, which
> > means you don't have to.  And it will check for uniqueness in case your
> > assumption is wrong.
> >
> > How you set that new column's value ... it could be done by modifying the
> > INSERT.  Or with a TRIGGER.
>
>
> But that's the rub IMHO. You're still storing that substring info, twice,
> once in the table, another in the index.
> SQLite supports function-based indexes, but unfortunately if does not
> support "function-based columns".
> (alas called virtual columns in Oracle, or Computed columns in SQL server I
> believe. Not (yet) in Postgres).
>

What if you could create a "lite" index, which stores just the rowids in a
particular order and refers back to the table for the rest of the column
data? The ordering it provides would allows you to binary search as usual.
But the worst case might be too expensive (ie. if pages are thrown out of
cache before being re-used), especially as it would have to be paid every
time you lookup the index...

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

Re: [sqlite] Backup and integrity check questions

[Rowan Worth]

On 28 July 2018 at 05:41, Rune Torgersen  wrote:

> > Rowan Worth Thu, 26 Jul 2018 22:02:50 -0700
> >
> > On 26 July 2018 at 05:56, Rune Torgersen  wrote:
> >
> > > The databases have been opened with two connections (one for reads, one
> > > for writes), and use the following options:
> > > sqlite3_busy_timeout(mDbConn, 500);
> > > sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0);
> > >
> >
> > Surely this effectively reduces your number of connections to one?
>
> No, both connections are from within the same application, and have seemed
> to work just fine for about 10 years now...
>

If the write connection is held open I honestly can't see how that's
possible, unless it is never actually used to write.

I tried a quick test with two connections on different threads configured
like this, and as soon as the writer obtains an exclusive lock for the
first time it holds onto it forever (as described in the PRAGMA docs). From
that point on the reader cannot even obtain a SHARED lock to read the
database, and spends the rest of its life in the busy handler.

Note that while the pragma talks about excluding other "processes" from
accessing the DB, "process" and "thread" are interchangeable as far as
sqlite is concerned. That is documented here:
https://www.sqlite.org/lockingv3.html

Last paragraph of section 2.0:

>
> The pager module effectively controls access for separate threads, or
> separate processes, or both. Throughout this document whenever the word
> "process" is written you may substitute the word "thread" without changing
> the truth of the statement.
>


Ah, unless you're in shared cache mode (which PRAGMA read_uncommitted would
suggest), which I don't really know anything about...

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

Re: [sqlite] Backup and integrity check questions

[Rowan Worth]

On 26 July 2018 at 05:56, Rune Torgersen  wrote:

> The databases have been opened with two connections (one for reads, one
> for writes), and use the following options:
> sqlite3_busy_timeout(mDbConn, 500);
> sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0);
>

Surely this effectively reduces your number of connections to one? After
the write connection performs its first update, the read connection will be
locked out forever. Unless the write connection is specifically opened to
perform the update and then closed, in which case the PRAGMA is superfluous?

PRAGMA quick_check is faster, but not as exhaustive as integrity_check.

There's more efficient ways to copy a DB than the backup api, but they
involve either downtime as Simon said, or some risk in correctly managing
locks.

It's not clear whether you have another process calling sqlite3_backup_* or
that is done by the application itself. The latter can be more efficient as
sqlite will not have to restart the backup if the application updates the
DB (any writes to a DB are automatically propagated to in-progress backups
within the same process).

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

Re: [sqlite] two threads block eachother opening db (WAL)

[Rowan Worth]

On 2 July 2018 at 23:32, Charles Samuels  wrote:

> I have found that when my process has a lot of threads each of which opens
> a
> DIFFERENT database, they each block on eachother while opening each
> database.
>


> This is at least on conflict with the documentation, as the documentation
> suggests that a thread is a process for the purposes of sqlite's
> concurrency,


Actually I believe the mutex acquisition here is to provide those
semantics, because unix file locks are necessarily bound to a process and
not a thread. So sqlite must manage some of the per-thread details itself
to correctly handle the case where multiple threads are using the same DB,
possibly with different locking levels, and the mutex protects those shared
structures. Intuitively it seems like a mutex held on the sqlite3_file
structure rather than a global mutex could be used for this purpose which
would prevent unrelated databases from contesting each other, however...


> I also feel like it's a bug in
> general, as opening two unrelated databases should not block eachother.
>

... unix is something of a complex beast and the question of whether two
databases are unrelated is not as simple as it may seem. You might have two
databases /tmp/db and /var/tmp/db, but what if /var/tmp is a symlink to
/tmp? Or what if the two db files are hardlinks of the same inode?

Identifying multiple opens of the same inode is the other problem the mutex
is helping to solve here. And it's a detail sqlite _must_ be aware of
because of the bizarre POSIX locking semantics where closing a file
descriptor relinquishes all locks associated with its inode, across the
entire process. So if sqlite isn't careful about this, corruption can
easily result.

I'm not saying there isn't room for improvement here, but it's not a simple
problem!

Each thread blocks at this point:
>
> #0  __lll_lock_wait () at ../sysdeps/unix/sysv/linux/x86
> _64/lowlevellock.S:135
> #1  0x774e4b95 in __GI___pthread_mutex_lock (mutex=0x77dd8148
> )
> at ../nptl/pthread_mutex_lock.c:80
> #2  0x77b26419 in unixEnterMutex () at sqlite3.c:31952
> #3  unixLock (id=0x74a25180, eFileLock=1) at sqlite3.c:32894
> #4  0x77b1f2fa in sqlite3OsLock (lockType=1, id=)
> at
> sqlite3.c:21299
> #5  pagerLockDb (pPager=pPager@entry=0x74a25008, eLock=eLock@entry=1)
> at
> sqlite3.c:50293
> #6  0x77b1f34b in pagerLockDb (eLock=1, pPager=0x74a25008) at
> sqlite3.c:53054
> #7  pager_wait_on_lock (pPager=pPager@entry=0x74a25008,
> locktype=locktype@entry=1) at sqlite3.c:53051
> #8  0x77b55d6c in sqlite3PagerSharedLock (pPager=0x74a25008)
> at
> sqlite3.c:54293
> #9  0x77b56835 in lockBtree (pBt=0x74a151e8) at sqlite3.c:64591
> #10 sqlite3BtreeBeginTrans (p=0x74a1b508, wrflag=wrflag@entry=0) at
> sqlite3.c:64956
> #11 0x77b82997 in sqlite3InitOne (db=0x74a12008, iDb=iDb@entry=0,
>
> pzErrMsg=pzErrMsg@entry=0x751fe778) at sqlite3.c:119558
> #12 0x77b82aca in sqlite3Init (db=0x74a12008,
> pzErrMsg=pzErrMsg@entry=0x751fe778)
> at sqlite3.c:119740
> #13 0x77b82b00 in sqlite3ReadSchema
> (pParse=pParse@entry=0x751fe770) at sqlite3.c:119765
> #14 0x77b8d8e4 in sqlite3Pragma (pParse=0x751fe770,
> pId1=pId1@entry=0x751fddd0,
> pId2=pId2@entry=0x751fdde8, pValue=pValue@entry=0x751fde18,
> minusFlag=minusFlag@entry=0)
> at sqlite3.c:117300
>

Hm, this backtrace doesn't seem to have anything to do with WAL mode, so
I'm not sure why using the rollback journal makes for a 5 time speedup.
Perhaps because the same mutex is used to protect shared-memory accesses?
sqlite3OSLock really doesn't do much with the mutex held; is there another
thread holding things up via a different codepath?

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

Re: [sqlite] Strange Corruption Issue

[Rowan Worth]

Between updates, automatic maintenance, registry churn, event logs, and
background "optimisations" I reckon windows could give 400G/day a run for
its money :P

-Rowan

On 19 June 2018 at 12:37, Keith Medcalf  wrote:

>
> The new "consumer" SSDs from Samsung carry a 1200 TBW/8 year warranty on a
> 4 TB device.  That is a lot of writing for a "consumer desktop" computer
> ... that is about 400 GB written per DAY every day for 8 years!
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Scott Doctor
> >Sent: Monday, 18 June, 2018 22:27
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: Re: [sqlite] Strange Corruption Issue
> >
> >SSD's have a limited number of write cycles. You may have a
> >failing SSD. Those are still, IMO, another 5-10 years before
> >they solve the write lifetime reliabilty issue.
> >
> >-
> >Scott Doctor
> >sc...@scottdoctor.com
> >-
> >
> >On 6/18/2018 20:15, Patrick Herbst wrote:
> >> I'm using sqlite in an embedded application, running on SSD.
> >>
> >> journal_mode=persist
> >> so that it is more resilient to loss of power.
> >>
> >> I'm seeing corruption.  I'm using sqlite to log events on the
> >system,
> >> and the corruption is well in the middle of a power session; not at
> >> the tail end of log when a power loss might occur.
> >>
> >> What i'm seeing is just a few pages corrupted with random bits
> >being
> >> flipped.  looking in a hex editor I can see the corrupted data, and
> >> where I can tell what values it SHOULD be, I see that they're
> >wrong,
> >> but only by a single bit flip in random bytes here and there.
> >for
> >> example a "A" is "a", or a "E" is "A".  These are all changes of a
> >> single bit.  there are far more examples... but in pretty much
> >every
> >> case (even when RowID's are wrong) its just off by a bit.
> >>
> >> I'm using sqlite 3.7 (i know, old, but this this system is old).
> >Has
> >> anyone else seen random bit flips?  Any idea what could be causing
> >it?
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger behaviour on UPDATE after release 3.24.0

[Rowan Worth]

On 6 June 2018 at 07:14, Richard Hipp  wrote:

> On 6/5/18, Stéphane Aulery  wrote:
> > Hello,
> >
> > The changelog of the last release [1] say at the point 13 :
> >
> > UPDATE avoids writing database pages that do not actually change. For
> > example, "UPDATE t1 SET x=25 WHERE y=?" becomes a no-op if the value in
> > column x is already 25. Similarly, when doing UPDATE on records that
> > span multiple pages, only write the subset of pages that contain the
> > changed value(s).
> >
> > -
> >
> > I can't find in [2] and [3] if this change implies that a trigger will
> > be fired or not on a row where x is already 25. A trigger could act at a
> > physical or logical level and this change is a physical enhancement.
> >
> > Can someone inform me please?
>
> The enhancement is a low-level b-tree thing where it does a memcmp()
> and only writes the page back to disk if there are actual byte
> changes.
>
> Triggers continue to fire as they always have.
>
> There is no change in behavior.  This is a performance optimization only.


Does this affect the locking behaviour? ie. is upgrading to a RESERVED lock
a side effect of the b-tree writing out pages?

Hmmm I presume not, since UPDATE currently upgrades to a RESERVED lock
regardless of whether any rows are modified.

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

Re: [sqlite] Feature suggestion / requesst

[Rowan Worth]

On 3 June 2018 at 07:28, Scott Robison  wrote:

> I've encountered a feature that I think would be awesome:
> https://www.postgresql.org/docs/9.3/static/dml-returning.html
>
> Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING
> id;
>


> my thoughts are just that this could greatly simplify a lot of sql
> code that currently has to prepare and execute at least two statements
> to accomplish what is conceptually an atomic task.
>

For most use cases you only need a single query:

if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES (x,
y, z)") == SQLITE_OK) {
long id = sqlite3_last_insert_rowid(db);
...
}

Of course this relies on the table's primary key being a rowid alias, where
the RETURNING syntax is presumably flexible enough to support multi-column
keys and such. Although I'm not sure how you'd generate sane defaults for
such columns...

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

Re: [sqlite] This is driving me nuts

[Rowan Worth]

On 28 May 2018 at 17:29, x  wrote:

> I’ve just discovered the thread in the original app decreases the
> available memory by around 4 GB. Are they really that expensive?


A thread itself is not expensive in terms of memory.


> It has very little data of its own


Either this statement is wrong, or you've misattributed the 4 GB of memory.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Rowan Worth]

On 14 May 2018 at 01:08, Richard Damon <rich...@damon-family.org> wrote:

> On 5/13/18 12:55 PM, Rowan Worth wrote:
> > On 9 May 2018 at 08:56, Richard Hipp <d...@sqlite.org> wrote:
> >
> >>   But with
> >> SQLite, there is no round-trip latency.  A "round-trip" to and
> >> database is just a function call, and is very very cheap.
> >>
> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes
> the
> > latency of _communication_ between the app and database in this
> statement,
> > and excludes any processing time required by the database.
> >
> > If you were to interpret "round-trip" from an app-centric perspective (as
> > in "the time taken to retrieve/commit data") then the statement becomes
> > misleading because handling the data involves i/o, possibly even
> > synchronous i/o, which is not "very very cheap" by any standard I'm aware
> > of :)
> >
> > -Rowan
>
Yes, if the request requires I/O, then that costs time, but then the
> operation would likely use similar I/O in whatever way the application
> needed to get that information, so that I/O shouldn't really be charged
> to the use of a database, but to the information requested. One thing to
> remember is SQLite is often compared as a better way to get information
> then using simple disk i/o, so the 'cost' of using SQLite (compared to
> the alternative) shouldn't include the base time to read the file, but
> only any extra i/o above that.
>

That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
cheap" either - it doubles writes (once to the rollback journal, once to
the DB), forces syncs, and likely results in a more seek heavy i/o pattern
(this depends a bit on schema design and whether the app requires/fully
takes advantage of relational operations).

To be clear, this is not a criticism of sqlite. These costs are paid for a
reason (eg. durability) and I think sqlite does its job very efficiently.
You're also right that an app implementing similar features without sqlite
will have to pay similar costs.

My point is simply that it's unwise to think of any DB query as having "no
latency" even when dealing with an sqlite DB.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Rowan Worth]

On 9 May 2018 at 08:56, Richard Hipp  wrote:

>   But with
> SQLite, there is no round-trip latency.  A "round-trip" to and
> database is just a function call, and is very very cheap.
>

I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the
latency of _communication_ between the app and database in this statement,
and excludes any processing time required by the database.

If you were to interpret "round-trip" from an app-centric perspective (as
in "the time taken to retrieve/commit data") then the statement becomes
misleading because handling the data involves i/o, possibly even
synchronous i/o, which is not "very very cheap" by any standard I'm aware
of :)

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

Re: [sqlite] Database locking with online backup API

[Rowan Worth]

On 8 May 2018 at 17:22, R Smith  wrote:

> On 2018/05/08 9:37 AM, Donald Shepherd wrote:
>
>> I've long assumed that when using the online backup API on a SQLite
>> database, other processes will not be able to write to the source database
>> for the duration of the sqlite3_backup_step call.  However under some
>> testing I've been performing, I've found that this doesn't appear to be
>> the
>> case.  Instead writes are prevented for a very small subset of that time,
>> if at all.
>>
>> I'm testing a WAL database if that affects it.
>>
>
> Expected and documented indeed. The basic rule that backup abides by is
> this:
>
> "Copy the database in a completely wholesome state to the destination."
> "If the data changes (and it can) then restart the backup process from
> start on the new data state."
>
> This works well for 90% of cases, but care is to be exercised for a really
> big + busy database (where writes are likely within the period of backup),
> the backup can infinitely restart. 
>
> It would actually be real nice if the backup API had a parameter or flag
> like "sqlite3_lockduringbackup".
>

Not quite right. sqlite3_backup_step accepts a number of pages to be copied
by the current invocation. If you specify a negative number of pages,
sqlite3_backup_step will obtain a read lock and copy the entire database
before returning. So the "lock during backup" mode already exists - the
only way you get the backup restarting behaviour is if you ask
sqlite3_backup_step to copy a subset of the database.

The behaviour I describe is documented here:
https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep

> Every call to sqlite3_backup_step() obtains a shared lock
 on the source database
that lasts for the duration of the sqlite3_backup_step() call.


I'm not familiar with WAL mode, but since it's designed to allow readers
and writers to operate concurrently, it's perhaps no surprise that the
backup (purely a read operation) doesn't appear to block writes to the
source DB.


Fun fact: changes made to the source DB from an sqlite connection in the
same address space as the backup's sqlite connection are automatically
propagated to the destination DB, without having to restart the backup.

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

Re: [sqlite] Always call a value-quoting routine

[Rowan Worth]

On 7 May 2018 at 15:13, Scott Robison <sc...@casaderobison.com> wrote:

> On Sun, May 6, 2018 at 11:34 PM, Rowan Worth <row...@dug.com> wrote:
> > Its omission is interesting though. Does it indicate an incompetent
> > attacker, or is companieshouse.gov.uk using some bespoke approach like
> > "delete all single quotes" instead of actually quoting strings?
>
> It could just indicate someone with a sense of humor who crafted a
> name that looks like an injection attack for their company.
>

True, or crafted a name that makes it look like the registrar is using
unusual sanitation approaches ;)
Although a search for "it's"¹ reveals they do allow single quotes in
company names, so they're off the hook.

¹ or "its" - it seems single quotes are ignored for search purposes,
although other punctuation like ; and , are not.

LP and LLP are apparently also acceptable suffixes:

https://beta.companieshouse.gov.uk/company/LP004358
https://beta.companieshouse.gov.uk/company/OC387006

Not sure about these next ones -- did they just fall through the cracks?

https://beta.companieshouse.gov.uk/company/SL003914
https://beta.companieshouse.gov.uk/company/SC096234

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

Re: [sqlite] Always call a value-quoting routine

[Rowan Worth]

Amusing -- but without the leading single-quote it would take intentional
effort for a programmer to detonate this payload.

Its omission is interesting though. Does it indicate an incompetent
attacker, or is companieshouse.gov.uk using some bespoke approach like
"delete all single quotes" instead of actually quoting strings?

-Rowan

On 6 May 2018 at 06:57, Simon Slavin  wrote:

> This is a genuine company registered under the UK Companies Act:
>
> 
>
> The name of company is
>
> ; DROP TABLE "COMPANIES";-- LTD
>
> (Note: For legal reasons a UK company name must end in 'LTD' or 'plc',
> depending on the type of company it is.)
>
> Simon.
> ___
> 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] Index on expression goes corrupt from valid SQL

[Rowan Worth]

On 23 March 2018 at 08:54, Deon Brewis  wrote:

> Most of the time when the database gets corrupted, we don't crash, it
> corrupts midway through valid SQL (no pattern to it - completely unrelated
> SQL). I was thinking if the expression functions have bugs in them it could
> cause corruption, but can't really imagine how. We use sqlite3_result_*
> functions to report results, and even if we read invalid memory or pass
> invalid memory to SQLITE, it should crash - not corrupt.
>

Do you have an sqlite logging callback installed
(sqlite3_config(SQLITE_CONFIG_LOG, ...))?
The messages on that channel include more details (eg. the line number of
the sqlite3 source where the corruption was first noticed) for some
instances of corruption. Might help to pin down the issue.

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

Re: [sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

[Rowan Worth]

On 20 March 2018 at 22:33, Deon Brewis  wrote:

> How do you actually get a SQLITE_CANTOPEN_ISDIR error?
>
> In order to get an extended result code, we need to pass a sqlite3*
> connection, but you don't have that if the file can't be opened in the
> first place.
>

I understand why you'd think that, but check the docs:

https://www.sqlite.org/capi3ref.html#sqlite3_open

> A database connection handle is usually returned in *ppDb, __even if an
error occurs__.
> The only exception is that if SQLite is unable to allocate memory to hold
the sqlite3 object,
> a NULL will be written into *ppDb instead of a pointer to the sqlite3
object.

So we're not looking at an "either an error or sqlite3* is returned" - you
can get both. The other thing to takeaway:

> Whether or not an error occurs when it is opened, resources associated
with the
> database connection handle should be released by passing it to
sqlite3_close()
> when it is no longer required.

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

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

[Rowan Worth]

On 23 March 2018 at 05:24, Jonathan Moules 
wrote:

> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
> (preview)) despite looking through hundreds of thousands of records in each
> table, and it returns 86 records in all. This is great!
>
> But when I stick an "ORDER BY" on the end (either ASC or DESC), the
> processing time shoots up to 0.15s. The EXPLAIN between the two is
> considerably different so it seems the ORDER BY is getting it to use a
> sub-optimal query plan.
> If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
> plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
> are also here (they start changing at item 36).
>
> Any suggestions for what's going on here and how to coerce the planner to
> stick to the fast-path and then do a simple order by on those 86 (or
> however many - it'll always be a low number) results?
> (ANALYZE has been run)
>

Does it help if you move the ORDER BY to an outer select? ie:

SELECT id, u, err FROM (
SELECT u.url_id, u.url, l.error_code ...
) ORDER BY id;

If the query planner flattens the subquery this probably won't make a
difference though...
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Rowan Worth]

5/10
1/11
5/10

Always in conjunction with INTEGER PRIMARY KEY fwiw.


Also the following command is perhaps more portable:

sqlite3 yourfile.db .schema | grep -ic autoincrement

The sqlite3 shell on my system is too old to understand .schema --indent
and doesn't output anything so there's always zero lines to count :)

-Rowan


On 16 March 2018 at 23:37, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Rowan Worth]

On 16 March 2018 at 21:44, Paul  wrote:

> A few years back I've been asking the same question. To be honest, there's
> no more
> efficient alternative, than the one that can be implemented within library
> itself.
> Both performance-wise and productivity-wise.
>
> Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with
> problems:
>  * Which strategy to choose, INSERT + UPDATE or the reverse? No way to
> make this generic without hurting performance
>  * No matter the strategy, we end up with two queries which leads to extra
> code that has to be maintained and kept in sync plus a penalty from
> preparing two statements
>  * Existence of two statements leaves us vulnerable to race conditions,
> which adds two extra statements to BEGIN and COMMIT a transaction
>

I agree with your overall sentiment, but BEGIN/COMMIT actually eliminates
two statements because in the standard mode of operation (ie. autocommit)
you're essentially doing:

(implicit) BEGIN
INSERT ...
(implicit) COMMIT
(implicit) BEGIN
UPDATE ...
(implicit) COMMIT

By making the BEGIN/COMMIT explicit you reduce the overall work when two
statements are required.

It does seem like sqlite could avoid an extra btree lookup if it
implemented UPSERT itself, but since the required pages are practically
guaranteed to be in cache for the second query I wonder how many rows you'd
need in a table for it to make a significant difference. As you say the
main benefit would be to avoid synthesising two statements in user code.

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

Re: [sqlite] UPSERT

[Rowan Worth]

On 16 March 2018 at 18:24, Robert M. Münch 
wrote:

> Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since
> it doesn’t has an UPSERT?
>
> So, if I have a table with 30 columns and my code updates sub-sets out of
> these columns, I don’t want to write queries that manually retrieve the old
> values one by one.
>
> insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
> (select ID from Book where Name = "SearchName"),
>"SearchName",
> 5,
> 6,
> (select Seen from Book where Name = "SearchName"));
>
> So, for every column I don’t want to change I need to add a sub-select
> statement. If I need to build this statement dynamically, IMO it would be
> better to handle this code directly in code:
>
> if(record-exists?){
> UPDATE …
> } else {
> INSERT …
> }
>

Doing it with an if means you always run two queries - the first to
determine whether a row exists:

SELECT EXISTS (SELECT ID from Book where Name = "SearchName")

There's two approaches which reduce the best case to a single query:

1. Immediately try to INSERT using the ON ABORT conflict strategy (ie.the
default). If the query fails with SQLITE_CONSTRAINT you know the row is
already present, so run the UPDATE.
2. Immediately try to UPDATE the existing row. Then call sqlite3_changes to
determine how many rows were updated - if zero then you know the row didn't
exist, so run the INSERT.

Whether this makes a significant difference in practice I don't know :)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] High performance and concurrency

[Rowan Worth]

On 2 March 2018 at 03:43, Shevek  wrote:

> We use HikariCP, so a connection is in use by one thread at a time with
> JMM-safe handoff, and they all share the mmap region.
>

Shevek also wrote:

> What I think is happening is that either a pthread mutex or a database
lock is serializing the accesses, so each thread blocks the others.

I'm not familiar with HikariCP but if it's handing the connection around to
a single thread at a time, sounds like database accesses are serialised
long before sqlite becomes a factor.

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

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

[Rowan Worth]

What is your expected answer for:

select length(printf ('%4s', 'です'))

-Rowan

On 18 February 2018 at 01:39, Ralf Junker  wrote:

> Example SQL:
>
> select
>   length(printf ('%4s', 'abc')),
>   length(printf ('%4s', 'äöü')),
>   length(printf ('%-4s', 'abc')),
>   length(printf ('%-4s', 'äöü'))
>
> Output is 4, 3, 4, 3. Padding seems to take into account UTF-8 bytes
> instead of UTF-8 code points.
>
> Should padding not work on code points and output 4 in all cases as
> requested?
>
> Ralf
> ___
> 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] Recommended tool to read SQLITE btree?

[Rowan Worth]

On 17 February 2018 at 08:34, Deon Brewis  wrote:

> Anybody have a recommendation for a tool that can read/show/interpret a
> SQLITE file at the BTREE level?
>
> Want to be able to decode the links between pages, figure out what all the
> data mean etc. And should be able to work on a corrupted file.
>

If you download the full sqlite source tree you'll find tool/showdb.c,
which can show you the btree structure (at least the intact part) and the
raw data from any DB page.

I can't remember if it can interperet index/table data off the top of my
head; you might need to refer to https://www.sqlite.org/fileformat.html to
decode the payload.

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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Rowan Worth]

https://www.sqlite.org/withoutrowid.html

"NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
table."

It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
KEY columns of _every_ table according to the SQL standard, but an early
version of sqlite included a bug which allowed NULLs and as a result sqlite
does not enforce this for ROWID tables.

-Rowan

On 19 January 2018 at 14:32, Shane Dev  wrote:

> Hello,
>
> The following SQL works as I expect -
>
> sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> child));
> sqlite> insert into edges select null, 1;
> sqlite> select * from edges;
> parent  child
> 1
> sqlite>
>
> but if I remove the superfluous rowid column from the table definition -
>
> sqlite> drop table edges;
> sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> child)) without rowid;
> sqlite> insert into edges select null, 1;
> Error: NOT NULL constraint failed: edges.parent
> sqlite>
>
> Why do I get this error?
> ___
> 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] os_unix.c:36136: (2)

[Rowan Worth]

On 12 January 2018 at 07:21, wei1.z  wrote:

> What is the meaning of this line?
>
> 01-11 14:40:59.733 10011  2864  2877 E SQLiteLog: (14) os_unix.c:36136: (2)
> open() -
>
> db file cannot be found, or permission issue ?
>

To decipher this in future, the first number in parens is the sqlite error
code. (14) is SQLITE_CANTOPEN. The second number in parens is the unix
errno; (2) is ENOENT. If you check the man page for the open syscall,
you'll see that ENOENT is returned if the requested file doesn't exist.

For a permission issue you would see (14) os_unix.c:36136: (13) open() -  errno 13 is EACCES.

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

Re: [sqlite] Search % using sqlite

[Rowan Worth]

Firstly this code is extremely dangerous. What would happen if
acInputString contained this string?

';DROP TABLE AUDIO; SELECT '

It's best practice to use bound parameters to prevent this kind of problem,
ie.

sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, ,
NULL);
sprintf(acGlobString, "%%%s%%", acInputString);
sqlite3_bind_string(stmt, 1, acGlobString);
sqlite3_step(stmt);

Which is still dangerous if acInputString is larger than can fit in
acGlobString -- snprintf is advised to avoid buffer overflow. And all
sqlite3 return codes should be checked to see if an error occurred, of
course.


Ok now to the actual problem -- you can modify your query to read:

SELECT NAME FROM AUDIO WHERE NAME LIKE ? ESCAPE '!'

The ESCAPE clause defines a character which can be used to match a literal
% instead of % being treated as a wildcard. I've chosen ! as the escape
character, which means you'll have to prefix all !, %, and _ characters
with an ! to get a literal match.


For this simple search it's easier to replace "NAME LIKE ?" with
"instr(NAME, ?) > 0", unless you need case insensitive matching.

-Rowan


On 5 January 2018 at 15:49, Hegde, Deepakakumar (D.) <
deep...@allgosystems.com> wrote:

> Hi All,
>
>
> We are implementing a wild card search feature.  our query is as below:
>
>
> sprintf (acQstring,
>
> "SELECT NAME FROM AUDIO WHERE NAME LIKE '%%%s%%'", acInputString);
>
>
> In the input string is '%' then we are getting t all the entry in the
> column.
>
>
> ex: name column have following:
>
> %a
>
> a
>
> a%a
>
> aa%
>
>
> we are expecting entry 2 which don't have % in it should not get as
> output. But it seems not the case, it is giving all the 4 entry as output.
>
> Please can we know is there any way of searching this? Thanks.
>
>
> Thanks and Regards
>
> Deepak
> ___
> 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

[Rowan Worth]

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

On 29 December 2017 at 15:05, x <tam118...@hotmail.com> 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<mailto:row...@dug.com>
> Sent: 29 December 2017 03:13
> To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] What happens if an in memory database runs out of
> memory
>
> On 23 December 2017 at 00:17, curmudgeon <tam118...@hotmail.com> 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] 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] Btree page corruption

[Rowan Worth]

Does either process take backups of the DB? If so, how is that implemented?
-Rowan

On 22 December 2017 at 05:47, Nikhil Deshpande 
wrote:

> Hi,
>
> We have an application that in a Linux VM that's running into
> SQLite DB corruption (after weeks and months of running,
> 4 such instances yet in different VMs).
>
> We would appreciate some help in debugging this further to identify
> source of corruption!
>
> Symptom is btree page corruption, e.g.
>
> > $ sqlite3 stats.sqlite "pragma integrity_check;"
> > *** in database main ***
> > Page 3818: btreeInitPage() returns error code 11
> > Page 46: btreeInitPage() returns error code 11
> > Error: database disk image is malformed
> (Same error is raised for SELECT queries too.)
>
> There were no power-off or reboots in near time vicinity when the
> corruption was detected. We have poured over this document
> https://sqlite.org/howtocorrupt.html
> many times to check if any of the conditions could apply,
> but so far no leads yet.
>
> We have also been unable to reproduce the corruption by stressing
> application's SQLite DB read/write code paths for a week.
>
> I'm attaching showdb output for the DB header and 2 corrupt pages
> if it's of any hint.
>
> ---
>
> A bit more application setup context/information:
>
> - Linux kernel 4.4.41
> - glibc 2.22
> - Ext4 file system, mounted as (rw,relatime,data=ordered).
>
> - Writer C++ process: sqlite-3.17
>   - Creates a set of "time series" tables, each table has 2 numeric
> columns (timestamp, int) during initialization.
>   - Every 1 minute, 2 threads will do total 15 writes. (using "INSERT OR
> REPLACE ... (timestamp, int)" SQL into 15 tables).
>   - SQLite DB opened with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
> flags, initialized with "PRAGMA journal_mode=wal;", threading mode
> is Serialized for the libsqlite build, uses default VFS ("unix").
> All other config params are default (e.g. autovacuum is disabled
> etc.).
>   - A separate thread runs "PRAGMA quick_check;" periodically every 5
> minutes, in its own separate DB connection.
> - Reader process: sqlite-3.11 + Python 2.7.11
>   - Periodically reads time series tables for a given timestamp range
> (usually latest 5 minutes) using SELECT queries (no INSERT/UPDATE/
> DELETE from this process).
>   - Uses same same "PRAGMA journal_mode=wal", uses the sqlite3 DBAPI
> module from Python standard library.
> Apart from above 2, no other processes are accessing the SQLite DB file.
>
> We have updated both the reader and writer to use latest SQLite 3.21,
> but without understanding the cause of corruption, we are unable to
> say if this update to latest 3.21 would indeed prevent further
> occurrences.
>
> Thanks,
>  Nikhil
>
> ___
> 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] Minor bug reports during build.

[Rowan Worth]

Seems to be working as advertised. Unless you specify --disable-tcl, the
configure script defaults to building an sqlite extension for TCL.

The extension can't go in $PREFIX, since tcl wouldn't be able to find it.
So your options for a non-root install are:

1) --disable-tcl
2) set the environment variable TCLLIBDIR to somewhere you have write
access (presumably you'll also need to configure tcl to look here for
extensions)
3) install your own tcl to $PREFIX and make sure its tclsh is the first one
in your $PATH

-Rowan

On 22 December 2017 at 03:41, Michael Tiernan 
wrote:

> Sorry for the top post. Sadly the android client forces it.
>
> In short, I'm building two copies of sqlite3, one of which works fine, the
> attempt to build it on the Linux host (using the prefix flag of course)
> causes the build to begin but it to fail when it runs into the attempt at
> modifying the (non-existent) file /usr/share/tcl8.5/sqlite3
>
> All the other warnings and considerations are secondary to the point that
> the makefile is attempting to change the permissions on an external (to the
> user) tool which it neither built or should be able to modify.
>
> Thanks for everyone's time!
> --
> << MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan
>
> Non Impediti Ratione Cogatationis
> Women and cats will do as they please, and
> men and dogs should relax and get used to the idea. -Robert A. Heinlein
>
> On Dec 21, 2017 2:26 PM, "Warren Young"  wrote:
>
> On Dec 21, 2017, at 11:37 AM, Michael Tiernan 
> wrote:
> >
> > I'm trying to build two copes of sqlite3 in a shared dropbox folder.
>
> Do you intend to use SQLite inside the Dropbox folder once you’ve got it
> working?  That’s only safe if only one person is using the database at a
> time, and you wait for the sync to finish before trying to use the DB on
> another machine.
>
> If you need a networked DBMS, SQLite is generally not what you want, at
> least not as-stock.  There are add-ons and alternatives that work far
> better for this.  Google “SQLite Dropbox”.  It’s come up many times before.
>
> > On a "Scientific Linux 6.7" (RHEL 6.7) system I did a built then build
> > install *as a user* and not as root. Looking to create a localized copy
> > specifically.
>
> Try this:
>
> $ ./configure --prefix="$HOME/sqlite3"
>
> That will allow the “make install” to work without root privileges.  The
> sqlite3 binary would land in $HOME/sqlite3/bin, with that configuration
> option.
>
> You can set the prefix to somewhere under your Dropbox folder if you’re
> willing to take the risks to data safety that that entails.
> ___
> 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