[sqlite] Bug: Successfully committed transaction rolled back after power failure
Hi Stephen, On 1 February 2016 at 21:45, Stephen Chrzanowski wrote: > > SQLite is nothing more than part of a program run by the OS. It completely > relies on whatever the OS tells it. If the OS tells it that things are OK, > then that is all that can be done. SQLite can't take on the responsibility > of making sure that my taxes are done right, it won't ever wash my car (I'd > be afraid the T would scratch the paint anyways) and it absolutely cannot > defend against the lies an OS gives it. Of course! SQLite is a remarkably reliable bit of software and I hold it in high regard, but as you say software has its limits. I'm not one to expect more of SQLite then it can reasonably provide. Going back to the OS, I've seen instance, in both Windows and Linux based > platform (RH and Debian) where file sizes do not change even though streams > of data are being pumped into that particular file. Stands to reason on linux if the directory hasn't been synced to disk yet. The file's dirent is stored in a completely independent extent. > I've seen situations > where files have been deleted in Linux (Not allowed in Windows) and data > still gets dumped into the now deleted file. ? This is not an error, this is standard POSIX file semantics - if you have a file descriptor referring to a file which gets deleted from disk, the file descriptor is guaranteed to remain valid until it is closed. NFS even goes to some lengths to make this work. Is it the programs fault that > the file is physically deleted but the program isn't aware? Is it supposed > to check every single time it writes a byte to see if the file exists, if > the partition is healthy, and the root file system passes a FSCK at every > commit? > :) > SQLite doesn't know or care what sector the bytes > live at on the drive physically. It doesn't know about fragmentation, it > doesn't know what size the clusters are, or anything that is important for > file storage. But SQLite *does* know how to interface with the file system. It's all very well to say the OS/hardware sometimes lies, but in this specific instance SQLite hasn't even asked the question. As I indicated in the last paragraph of my mail, I'm not in favour of fsync-directory-on-commit in the general case. But that's because I worry about the performance impact, not because SQLite is doing its best and at the mercy of the OS. I can't remember who in this thread suggested it but fsync-directory-on-close seemed like a great compromise. -Rowan
[sqlite] Bug: Successfully committed transaction rolled back after power failure
On 1 February 2016 at 18:58, Simon Slavin wrote: > > On 1 Feb 2016, at 9:23am, bm.email01 at gmail.com wrote: > > > --- > > No, SQLite does not. On COMMIT it fsyncs the database file and unlinks > the > > journal[1], but does not fsync the directory. > > --- > > > > Since that can cause the last transaction to be lost, despite Sqlite > returning a 'Commit successful' code to the application, doesn't that mean > that Sqlite is _not_ truly 'Durable' (per ACID)? > > 1) The fault here is the file system not SQLite. This one particular file > system has this strange requirement that you fsync the directory. SQLite > is ACID. It's the file system that messes it up. SQLite cannot defend > against untrustworthy middleware -- sooner or later a programmer has to > trust their hardware is doing what it says it's doing. > I take your point, but as Florian pointed out it's not just one file system; its a somewhat well known quirk of POSIX fsync. http://blog.httrack.com/blog/2013/11/15/everything-you-always-wanted-to-know-about-fsync/ It's a bit disingenuous to say "the file system requires a particular dance to ensure an unlink persists on disk, but the dance is excessively complex so we're going to skip it and blame any problems on the file system." The fact remains that sqlite returned SQLITE_OK for a transaction that never succeeded, because it didn't take the steps required for the i/o to hit disk. However... > 3) For both the above problems SQLite still neither loses data nor > corrupts the database file. If the program crashes the file is > automatically closed. When the SQLite API is used to reopen the file the > unexpected close will be discovered and corrected for. > I agree with this 100%. SQLite absolutely provides consistency here, which I would argue is the most important. In an actual power-loss scenario, what is an application going to do between receiving SQLITE_OK and before losing power that possibly matters? If the disk cache hasn't been flushed by the time the power drops, then nothing else you've done in the meantime will have hit disk either. Note that the OP's situation is very unusual in that a controlled shutdown is initiated after sqlite returns SQLITE_OK, but that shutdown *does not sync the filesystem*! That is the only reason for the 30 second window. In usual circumstances, my feeling is that the window between "power failing in the middle of sqlite_exec" and "power failing after sqlite_exec has returned but before the unlinked journal is synced to disk" is so small that sqlite is within its rights to blur the two. -Rowan
[sqlite] Customizing the location of the .sqlite_history
sqlite3() {
confs='.sqlite_history .sqliterc'
for c in $confs; do ln -s ~/.config/$c ~/$c; done
(sleep 10; cd; rm $confs)&
command sqlite3 "$@"
}
Alternately, simply create a new user with home directory matching your
~/.config, and su to that user to run sqlite3.
(no, neither of these suggestions are serious :P)
-Rowan
On 1 February 2016 at 13:46, Jes Slow wrote:
> Hi - this is kind of a trivial request, but .sqlite_history is one of the
> relatively few files that I can't move into ~/.config folder (or wherever
> it fits).
>
> We know that globals can become messy as you scale, and up until the XDG
> standard applications treated the home folder as their global place to drop
> configuration files. When you have dozens of programs, that starts to get
> messy.
>
> Many applications do this by allowing the user to set an environment
> variable to customize the location, altho personally I would prefer another
> way since environment variables are also global. Hope you consider it.
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Bug: Successfully committed transaction rolled back after power failure
On 31 January 2016 at 03:56, James K. Lowden wrote: > Surely SQLite does both -- fsync on file and directory -- as part of a > commit. That's not in doubt, is it? > No, SQLite does not. On COMMIT it fsyncs the database file and unlinks the journal[1], but does not fsync the directory. This is demonstrated by the logs in Stefan's initial post, and indeed the purpose of his patch was to introduce an fsync on the directory after unlinking the journal. [1] with the default PRAGMA journal_mode=delete, obviously -Rowan
[sqlite] Find out how many times does SQLite hit the disk?
On 31 January 2016 at 15:09, Yannick Duch?ne wrote: > If it's memory mapped, it's less an efficiency issue, > Hm, can you elaborate on this assertion? I don't think I agree. Lets say sqlite wants to access a page in the DB/journal. In the case of normal file access this is a call to pread/ReadFile, and in the memory-mapped case a call to memcpy. Now, the data in question may or may not alreaby be in the OS's disk cache. If it is pread/ReadFile/memcpy proceeds without delay. If the data is not in the cache, pread/ReadFile blocks until the i/o is complete. Similarly, memcpy will encounter a page fault and the process will block until the OS completes the i/o required to fill the page in memory. I'll grant there's an extra syscall per i/o in the normal file access mode, but this cost is _vanishingly_ small compared to the time required to load data from disk. Have I misunderstood the mechanism behind memory-mapped file access? -Rowan
[sqlite] Bug: Successfully committed transaction rolled back after power failure
On 25 January 2016 at 18:26, Meinlschmidt Stefan < Stefan.Meinlschmidt at esolutions.de> wrote: > > In your case it sounds like a controlled shutdown - is there a reason you > > don't do a full disk sync before that? > > Yes, it is a controlled shutdown, so in my case the /* post-commit logic > */ basically pulls the plug. > > Trouble is that I only control the database, not the shutdown procedure > (this is a commercial product with several hundred people working on > different aspects of the system). So while I can try to ask the shutdown > crew to sync like any real computer would do, I ultimately have no > saying in that but still need to ensure survival of data. > > Without the patch (which I do have applied locally, of course), me and > everyone with a similar usecase get into trouble for relying on the > quite excellent reputation of SQLite. Actually I first fingerpointed to > the flash hardware or its driver, because ?SQLite is well-tested and > doesn't have this kind of bugs? :-) > But if the shutdown procedure doesn't actually sync the disk, surely its not just sqlite users that will suffer? _Anything_ which has done a write() in the last ~10 seconds without sync()ing is going to lose data, and unlike sqlite the vast majority of standard tools _never_ call sync(). Obviously I don't know much about your project/environment, so perhaps this is what you want. It just strikes me as a problem easier solved at the system level is all :) -Rowan
[sqlite] Find SQLITE_BUSY reason?
On 22 January 2016 at 14:24, Simon Slavin wrote: > > On 22 Jan 2016, at 4:01am, Rowan Worth wrote: > > > To a point I agree, but in reality there's a fixed amount of work > involved > > with each write transaction. I recently profiled an operation involving > > ~75,000 rows that took ~8.5 minutes to complete, and found that 80% of > the > > time was spent waiting for COMMIT to complete. Rewriting the code so that > > all the work happened in a single transaction immediately dropped the > > overall time down to ~1.66 minutes. > > Nice stats. > I failed to include the number of transactions invoked by the original code, mainly because I hadn't measured it. I've done so now - there was 1,511 transactions to begin with however some of them would have been reads. It will take too long to measure precisely, but I'd estimate there was 1,132 write transactions. 80% of ~8.5 minutes is 410 seconds, so that's 362ms of I/O per transaction vs. 5 seconds of I/O in the single transaction approach. > This is, of course, all about waiting for a rotating disc to be in the > right place. 4500 rotations per minute is 75 rotations per second or 13ms > per rotation. If a transaction which involves a single row being written > involves five write operations then it can take up to 65ms just waiting for > the rotating disc. Call it an average of 33ms per transaction. Do that > 75,000 times and you're waiting up to 2475 seconds == 40 minutes. > Yes, good point. Each of the transactions in my case will end up modifying 3 tables and 12 indices, which puts us up around 15 write ops. Which doesn't quite explain the observed I/O times, but does reveal that the "fixed amount of work" in my original claim is not fixed at all but highly dependent on the database schema! > No wonder solid state storage speeds things up. > It would be interesting to test on an SSD for sure, but I have other bugs to track down :) Cheers, -Rowan
[sqlite] Find SQLITE_BUSY reason?
On 22 January 2016 at 06:33, Warren Young wrote: > With SQLite?s lack of row-level locking, your usage pattern should distill > to ?get in, get done, and get out, ASAP.? Many fine-grained queries are > better than heroic multi-statement queries that change the world. > To a point I agree, but in reality there's a fixed amount of work involved with each write transaction. I recently profiled an operation involving ~75,000 rows that took ~8.5 minutes to complete, and found that 80% of the time was spent waiting for COMMIT to complete. Rewriting the code so that all the work happened in a single transaction immediately dropped the overall time down to ~1.66 minutes. This was on standard desktop hardware running CentOS, with no other connections open against the database (located on a local ext4 filesystem). The tradeoff of course is that no one will be able to write to the database for the duration of this transaction, but in this case the ~7 minute saving is certainly worth it. -Rowan
[sqlite] Bug: Successfully committed transaction rolled back after power failure
> Shutting down power right after a successfully committed
> transaction rolls back that transaction on next startup.
nitpick: This is sqlite behaving as advertised. See
https://www.sqlite.org/lockingv3.html section 5.0 step 6, and
https://www.sqlite.org/atomiccommit.html section 3.11 which explain that
the presence of a [well-formed] journal file is the mechanism via which
sqlite discriminates between a committed and in-progress transaction.
ie. according to sqlite a transaction is *not* successfully committed if
the journal file is still present, so its well within its rights to
rollback in this scenario.
That said, syncing the directory doesn't sound like a terrible idea. But
it's not clear to me that the cost of another sync every transaction is
worth a marginal reduction in the power-failure-leads-to-rollback window.
That's if it even reduces the window; it wouldn't surprise me to find that
the journal is removed from disk after the same delay both with and without
dirsync, the difference being that the dirsync prevents sqlite from
returning control to your code until its certain the commit has persisted.
There's certainly a surprising result here:
if (sqlite3_exec("COMMIT") == SQLITE_OK) {
/* post-commit logic */
}
Logically, the code in the if block can reasonably assume that the
transaction will not rollback. But as you have discovered this is not
always true with JOURNAL_MODE=DELETE unless the dirsync is performed. To be
fair I don't think there are many power-failure scenarios where the
post-commit logic would have a chance to do anything significant, so the
incorrect assumption will usually be moot.
In your case it sounds like a controlled shutdown - is there a reason you
don't do a full disk sync before that?
-Rowan
On 19 January 2016 at 21:33, Meinlschmidt Stefan <
Stefan.Meinlschmidt at esolutions.de> wrote:
> Hi everybody!
>
> TL;DR:
>
> Shutting down power right after a successfully committed
> transaction rolls back that transaction on next startup. This is a
> problem in write-on-shutdown-then-power-off scenarios and violates my
> expectation of SQLite's transactions being ACID. This can be fixed by
> setting the dirSync-flag of the sqlite3OsDelete-call within
> pager_end_transaction.
>
>
[sqlite] WAL: difference between IMMEDIATE and DEFERRED transaction
On 15 January 2016 at 22:09, Olivier Vidal wrote: > For the DEFERRED transaction: > > - BEGIN DEFERRED TRANSACTION > - SELECT > - UPDATE > - SELECT > - UPDATE > - INSERT > - SELECT > - COMMIT > > The lock is requested at the first UPDATE (and there have no TIMEOUT?). > The database cannot be changed by others threads between the FIRST UPDATE > and COMMIT. But others threads may write between BEGIN and the first > UPDATE. Other threads that read at the same time will see the State of the > database before the first UPDATE? > Actually another thread cannot *change* the database between the first SELECT and COMMIT, although as you say they can start their own write transaction anytime before the first UPDATE. To be specific, the first SELECT will obtain a SHARED lock on the database. Another thread can still acquire the RESERVED lock at this point, and once it is ready to commit can even upgrade to the PENDING lock. However it will not be able to obtain the EXCLUSIVE lock to update the database until all SHARED locks are relinquished. -Rowan
[sqlite] hard links and SQLite
On 12 January 2016 at 13:12, Felipe Gasper wrote: > Same code, just different processes. > > We?ve just noted over the years with race conditions that that ?if it can > fail, it will?, so we try to be as bulletproof as we can. > Good policy :) After you unlink the temp file, I presume the schema creating process opens a new connection against the permanent file? I can't see your algorithm causing corruption, unless your affected users are using a filesystem which doesn't propagate POSIX file region locks across hard links. -Rowan
[sqlite] hard links and SQLite
On 12 January 2016 at 13:26, Scott Hess wrote: > On Mon, Jan 11, 2016 at 9:12 PM, Felipe Gasper > wrote: > > > On 11 Jan 2016 9:06 PM, Rowan Worth wrote: > > > >> * if it returns SQLITE_OK and zero rows, the schema hasn't been created > >> yet > >> > > > > Sure; however, by the time you do the next action it?s possible that > > something else will be creating the schema. This is the condition that > I?m > > trying to avoid. > > > If something else created the schema between check and use, what's the > problem? > > You can avoid this by doing: > - query sqlite_master. > - if the query returns rows, the schema exists, exit; else > - open a transaction. > - query sqlite_master. > - if the query returns rows, the schema exists, end transaction and exit; > else > - create the schema. > - commit transaction. > > Or, just use CREATE IF NOT EXISTS style when creating the schema, and you > will successfully not create it the second time. Or just pay attention to > your error codes in the create code. > > -scott > You can get away with querying sqlite_master only once, I think? - open a transaciton - query sqlite_master - if the query returns rows, end transaction and exit - create the schema - on SQLITE_BUSY, retry transaction[1] - commit transaction [1] most likely this means another process is also creating the schema, and the next transaction attempt will exit early after noticing sqlite_master is populated This way doesn't require any EXCLUSIVE transactions either. -Rowan
[sqlite] hard links and SQLite
On 12 January 2016 at 03:00, Felipe Gasper wrote: > On 11 Jan 2016 1:45 PM, Scott Hess wrote: > >> >> As far as preventing the other process from using it before the schema >> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >> the schema does not exist. If you create the schema as a transaction, >> that >> will be atomic. >> > > But in order for that SELECT to avert TOCTTOU errors, we?d have to do > BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. > No, only the connection which is creating the schema needs BEGIN EXCLUSIVE. The other connections can determine the schema state based on a normal "SELECT count(*) FROM sqlite_master": * if it returns SQLITE_OK and at least one row, the schema has been created and it can proceed * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet * if it returns SQLITE_BUSY, the schema is in the process of being created (or there's some other EXCLUSIVE transaction in progress, or a transaction is being committed at this very moment, or an in-progress write transaction has spilled sqlite's memory cache) As Scott also hinted at, hard linking DB files is dangerous because connections against each of the links will use different -journal files. In the event that your schema creation process (or the machine its running on) crashes halfway through COMMIT, connecting to the permanent database will cause corruption to be observed (because it's not aware of the other journal file and thus can't rollback the partial transaction). This may also be possible if another process simply connects to the permanent DB at just the wrong time (ie. halfway through the schema creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained across hardlinks but I haven't checked if this is specified by the standard or file-system dependent. Do your different connections actually run different code? Or are you potentially in the situation where two threads are trying to create the same DB at the same time (each via their own .tmp file)? -Rowan
[sqlite] About vacuum
On 4 January 2016 at 16:59, Simon Slavin wrote: > The VACUUM function takes so long to run that you cannot get faster > overall. > > Depending on which operating system, file system and storage system you > use, it is possible for VACUUM to increase speed of SELECT a little, but > not much [1]. Certainly not enough to make up for the time VACUUM takes. > To find out the effect on your specific platform you must perform the test > on your specific platform. > To provide another sample point, we've observed substantial improvements in sqlite DB performance (including SELECT statements) after a VACUUM. Our setup is a bit unusual though, as we store all our DBs on a specific file server over NFS (ie. we should probably be using an actual SQL server). Some combination of network latency, optical drives in the NFS server, and a mismatch between sqlite's page size and the disks' block size cause fragmentation to play a significant role in i/o performance. Of course this also mean that VACUUM takes even longer over NFS, so it's absolutely not worth running before every query. But we've seen benefit from using VACUUM as a maintenance step while the DBs are not in use. -Rowan
[sqlite] Using colation in Java
On 29 December 2015 at 08:23, Cecil Westerhof
wrote:
> When working in Python I can use:
> con.create_collation("mycollation", collate)
>
> To change the sort order. How should I do this in Java?
>
Note there are multiple ways to use sqlite from java, so it would help to
specify which bindings you are using. eg. sqlite4java doesn't provide a
create_collation call as far as I can tell.
Based on your previous posts I assume you are accessing sqlite via JDBC; I
can be no help there.
-Rowan
[sqlite] SQLite - Support for VSS writer
On 3 December 2015 at 22:00, Richard Hipp wrote:
> On 12/3/15, Murdare, Vijaykumar S (GE Oil & Gas)
> wrote:
> > Hi,
> >
> > Also, I would like to know while taking the backup of online databases:
> >
> > 1) If read operation is in progress, then can I take backup safely
> with
> > file copy or any other third party tool?
>
> Yes.
>
It's not directly relevant to this Windows thread, but this approach
requires caution on unix platforms if the file copy is done in the same
address space as sqlite3. As noted in
https://www.sqlite.org/howtocorrupt.html (section 2.2), closing a file
descriptor in POSIX land discards all locks associated with that
descriptor's file path, regardless of whether that fd was used to acquire
the lock.
Some [terrible] code to illustrate the approach I'm talking about:
char buf[4096];
int src, dest, n;
sqlite3 *db;
sqlite3_open("db.sqlite", &db);
// obtain read lock
sqlite3_exec(db, "BEGIN; SELECT COUNT(*) FROM sqlite_master;", NULL,
NULL, NULL);
src = open("db.sqlite", O_RDONLY);
dest = open("db.sqlite.bak", O_WRONLY);
while ((n=read(src, buf, sizeof(buf))) > 0) {
write(dest, buf + i, n - i); // XXX ignores errors/interrupted
syscalls
}
close(dest);
// despite the fact that sqlite3 has a completely separate file
descriptor for
// the database, closing our FD here drops sqlite3's locks!!
close(src);
sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);
sqlite3_close(db);
Note the comment on close(src) - this approach drops sqlite's locks
*before* the transaction finishes. If you only have a read lock and don't
use the database connection after the close this is probably safe, but if
you have a write lock (either as part of the current transaction or via a
different database connection in some other thread) it is definitely not
safe.
When you start fiddling with the database behing sqlite's back, you need to
be careful!
-Rowan
[sqlite] method for thousands separator via sed post processing
Hi Bruce,
On 27 November 2015 at 10:59, Bruce Hohl wrote:
> Thanks to suggestions on this list I explored the sed post processing
> avenue and found a solution. There are quite a few sed docs at
> http://sed.sourceforge.net For my needs I adapted an example from
> http://www-rohan.sdsu.edu/doc/sed.html (These docs are like ancient
> scrolls.)
>
> # add commas to interger strings, changing "1234567" to "1,234,567"
> gsed ':a;s/\B[0-9]\{3\}\>/,&/;ta'
>
> # add commas to numbers with decimal points and minus signs
> gsed ':a;s/\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\1\2,\3/g;ta'
>
> I adjusted the 2nd as follows to remove a space for each comma added which
> preserves column layout:
> sed ':a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta'
>
This modification is the reason it doesn't match numbers in column 1. But
also it will only insert as many commas as it has spaces to eat. eg. if the
number 1234567.89012 appears four times with zero, one, two, and then three
leading spaces, we get:
1234567.89012
1234567.89012
1234,567.89012
1,234,567.89012
You can improve it by moving the leading space inside the second group and
making it optional, so that it eats spaces when it can but still inserts
commas when it can't:
sed ':a;s/\(^\| \?\([^0-9.]\)\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta';
The output becomes:
1,234,567.89012
1,234,567.89012
1,234,567.89012
1,234,567.89012
The date problem is harder to solve, especially with sed. lex makes it
feasible - stick the following into a file called thousandify.lex and
compile it with the command:
flex thousandify.lex && gcc lex.yy.c -o thousandify
Then put the resulting 'thousandify' binary in your path and you can run
sqlite3 test.db | thousandify
/* START thousandify.lex */
%option noyywrap
DATE " "*[0-9]{4}-[0-9]{2}-[0-9]{2}
INTEGER " "*[0-9]+
FRACTION \.[0-9]*([eE][+-]?[0-9]*)?
%%
{DATE}printf("%s", yytext);
{FRACTION}printf("%s", yytext);
{INTEGER}{
char *cp;
int i, r, n, len;
for (cp=yytext; *cp == ' '; ++cp); // skip leading whitespace
len = strlen(cp);
r = len % 3;
n = (len - 1) / 3; // number of commas we'll insert
for (i = 0; i < (cp - yytext) - n; i++) {
putchar(' ');
}
putchar(cp[0]);
for (i = 1; i < len; i++) {
if (--r == 0) {
putchar(',');
r = 3;
}
putchar(cp[i]);
}
}
.printf("%s", yytext);
%%
int
main(int argc, char **argv) {
yylex();
return 0;
}
/* END thousandify.lex */
-Rowan
[sqlite] attempt at output with thousands separator via extension
Hi Bruce,
I had a go at post-processing the sqlite3 shell's output to apply thousand
separators. I don't recommend looking too hard at the sed for the sake of
your sanity, but the gist is it repeatedly prepends a comma to trailing
groups of three digits, and then repeatedly removes commas which appear
after a decimal point[1].
[1] which now that I think of it will cause problems if you have lists of
floating point values separated by commas
The simplest way to use it is to pipe sqlite3's output into the sed
command. However this has a heavy impact on the prompt - we can do slightly
better with the more complicated invocation, assuming some flavour of
linux/bsd:
sqlite3 temp.sqlite 3> >(sed ': a;
s/\([0-9]\+\)\([0-9][0-9][0-9]\)\($\|[^0-9]\)/\1,\2\3/g; t a; : b;
s/\.\([0-9]\+\),\([0-9]\)/.\1\2/; t b')
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .output /dev/fd/3
This still interacts poorly with the prompt:
sqlite> select * from a;
sqlite> 1,029,587
1,287,750
Note the first row (1029587) appears after the prompt. Its more usable if
you get rid of the prompt entirely:
sqlite> .prompt ''
select * from a;
1,029,587
1,287,750
This still preserves the continuation prompt in the case that you
mistype/forget a semicolon:
select * from a
...> ;
1,029,587
1,287,750
The downside is you can't copy/paste results into a new sql query. Also
note this doesn't differentiate between numerical/text values, anything
that looks like a number will have separators inserted.
-sqweek
On 22 November 2015 at 23:34, Bruce Hohl wrote:
> Thanks for those comments, I now understand better what is being returned.
> It seems an easy work around for shell output with thousands separator may
> not be possible. Within sqlite3.c there are ~1000 lines of printf.c code
> (added in 3.8.3) so it does seem to be a development matter. printf
> support for a thousands separator would be nice ... is there a place for
> feature requests?
>
> On Sat, Nov 21, 2015 at 4:04 PM, Scott Robison
> wrote:
>
> > On Sat, Nov 21, 2015 at 1:47 PM, Bruce Hohl wrote:
> >
> > > (1) SQLite will not return output with a thousands separator as
> follows:
> > >
> > > sqlite> select printf("%15.2f",123456789.12789);
> > >123456789.13
> > >
> > > sqlite> select printf("%'15.2f",123456789.12789);
> > >
> > >
> > >
> > >
> >
>
> > > (2) C language printf("%'15.2f",x) honors ' in printf() for thousands
> > > separator:
> > >
> >
> > Some libraries support this as a non-standard extension, but it is not
> part
> > of standard C. Thus it will not work uniformly everywhere. And since
> SQLite
> > doesn't use printf directly, it won't work at all.
> >
> >
> > >
> > > vi comma1.c
> > > #include
> > > #include
> > > int main(void)
> > > {
> > >printf("%'15.2f\n", 123456789.1234);
> > >setlocale(LC_ALL, "");
> > >printf("%'15.2f\n", 123456789.1234);
> > >return 0;
> > > }
> > >
> > > $ gcc comma1.c -o comma1
> > >
> > > $ ./comma1
> > >123456789.12
> > > 123,456,789.12
> > >
> > >
> > >
> >
>
> > > (3) So I thought maybe a C extension to SQLite might honor the
> thousands
> > > separator:
> > >
> > > Using the half.c extension example from
> > > https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
> > > printf("%'12.2f",x) added:
> > >
> > > #include
> > > #include
> > > #include
> > > SQLITE_EXTENSION_INIT1
> > >
> > > // The half() SQL function returns half of its input value.
> > > static void halfFunc(
> > > sqlite3_context *context,
> > > int argc,
> > > sqlite3_value **argv
> > > ){
> > > sqlite3_result_double(context,
> > > printf("%'12.2f",0.5*sqlite3_value_double(argv[0])));
> > > }
> > >
> > > // SQLite invokes this routine once when it loads the extension.
> > > int sqlite3_extension_init(
> > > sqlite3 *db,
> > > char **pzErrMsg,
> > > const sqlite3_api_routines *pApi
> > > ){
> > > SQLITE_EXTENSION_INIT2(pApi)
> > > sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0,
> 0);
> > > return 0;
> > > }
> > >
> > >
> > > $ gcc -shared -fPIC -I. -o half.so half.c
> > > $ sqlite3
> > > sqlite> select load_extension('./half.so');
> > >
> > > sqlite> select half(75);
> > >375000.0012.0
> > > sqlite> select half(750);
> > > 375.0012.0
> > > sqlite> select half(7500);
> > > 3750.0012.0
> > > sqlite> select half(75000);
> > > 37500.0012.0
> > > sqlite> select half(75);
> > > 375000.0013.0
> > > sqlite> select half(750);
> > > 375.0014.0
> > >
> > > Result:
> > > (1) Output includes unwanted '12.0', '13.0', '14.0'.
> > > The 12, 13, 14 appear to be the number of characters printed.
> > > Not sure of the reason for the '.0'
> > >
> > > (2) No thousands separator as wanted by includin
[sqlite] Simple Math Question
On 23 October 2015 at 23:34, Rousselot, Richard A <
Richard.A.Rousselot at centurylink.com> wrote:
> Scott,
>
> I agree with everything you said but... To me if a program/CPU evaluates
> something internally, then when it reports the result it should be the
> result as it sees it. It shouldn't report something different.
>
To be pendatic, you haven't asked for the result of the internal
calculation. You've asked for that result converted to a printable string
with certain formatting.
To be fair you've asked for plenty digits of precision, and I think it
would be reasonable to say that the failure to provide them by sqlite's
printf is a bug. But the primary use case for printf is to provide output
in *readable* form -- these ludicrous precisions are not part of that use
case, and it seems the formatting algorithms include concessions which
prevent them from being printed correctly.
eg. the system's printf, (via python 2.6.6), vs sqlite's printf:
python> "%.66f" % (9.2+7.9+0+4.0+2.6+1.3)
'25.00355271367880050092935562133789062500'
sqlite> select printf("%.66f", (9.2+7.9+0+4.0+2.6+1.3));
25.00
python> "%.66f" % (1.1+2.6+3.2+0.1+0.1)
'7.09964472863211994990706443786621093750'
sqlite> select printf("%.66f", (1.1+2.6+3.2+0.1+0.1));
7.099000
I think this explains the discrepancy you're seeing. Of course this leaves
you with no straightforward way to get the actual result via the sqlite3
shell, which also applies formatting when it displays SELECT results. I
guess if you really want the exact bits you need to use the ieee754
extension.
Aha, looking at the code I see the reason for the 16-digit cutoff - 16 is
what the counter passed to et_getdigit is initialised to, unless you use
the ! flag to %f. Interestingly this doesn't give the full 66, but does
give more non-zero digits:
sqlite> select printf("%!.66f", (9.2+7.9+0+4.0+2.6+1.3));
25.003551846317
The comments claim that 16 is the default because that's how many
significant digits you have in a 64-bit float. So I'm less convinced now
that there's actually a bug here - nothing that printf does can change the
fact that floating point is inherently inexact, and historically its been
printf's job to /hide/ those innacuracies for the sake of readability which
it is doing here by saying "realistically we can only store 16 significant
digits - anything beyond that is floating point error".
It may still be worth documenting the behaviour though?
-Rowan
[sqlite] Simple Math Question
On 23 October 2015 at 16:08, Dominique Devienne wrote: > Another good good way to think of IEEE I was presented once with, and which > kind of gave me a ah-ah moment, is the fact that numbers with exact > representation fall on the nodes of grid, and there's plenty of "space" in > between the nodes for values which cannot be exactly represented. The > "spacing" of the grid varies depending on the magnitude of your number > (i.e. the exponent) and the size of the mantissa (i.e. how small your 1/2^i > fractions get). IEEE is complex... I'm not an expert, but hopefully the > above helps. --DD > Very true, and a corollary is that using a fixed tolerance for floating point comparisons (like 1e-14 suggested elsewhere in this thread) doesn't work in general. It's fine if you know the magnitude of the numbers you're comparing, but once you get to numbers above 100, abs(f1 - f2) < 1e-14 becomes the same as saying f1 == f2, because even a single bit of error at this magnitude will be larger than 1e-14. Some languages provide a way to determine the distance between a given floating point value and the next largest representable number (eg the Math.ulp function in java), but I'm not sure if SQL provides such? Or if its even in scope... -Rowan
[sqlite] Compilation fails under Linux (Ubuntu) when FTS5 is enabled
On 18 October 2015 at 04:15, wrote: > I just tried one more time with the -lm switch and this time it worked. > Hmm... > > Problem solved. Thanks. You might find that specifying -lm *after* the .c files works but -lm before the .c files doesn't. The linker processes libraries/object files in the order they appear on the command line, and tends to only take unresolved symbols from libraries. So if the linker sees libm first, it looks at the symbols exported by the library (which includes the 'log' function), but ignores them all because it isn't yet aware of any unresolved symbols. Then it looks at the object file derived from sqlite3.c, and finds it has an unresolved symbol 'log'. But the linker only works with one object file at a time and doesn't go backwards, and since there are no more objects/libraries following sqlite3.c, the linker has no opportunity to resolve 'log'. Of course if the library appears after sqlite3.c, when the linker asks "hmm, do I have any unresolved symbols which are exported by libm?" it finds 'log' and you're all good. Cheers, -Rowan
[sqlite] Problem with sqlite3_db_filename
Suspect you are running into more VBA<->C issues. The db path, journal path
and wal path are stored sequentially in memory, so if you were to skip the
NUL terminators you'd see all three paths.
But I'm not exactly sure how that might happen without resulting in a
segfault, so I could be missing something.
-Rowan
On 9 October 2015 at 16:18, Bart Smissaert wrote:
> All working fine now, but noticed that for the attached database I get not
> just the file path of the attached database, but also the journal
> file path and wal file path:
>
> C:\Test\NewDB2.db3C:\Test\NewDB2.db3-journalC:\Test\NewDB2.db3-wal
>
> There is actually no journal file or wal file that I can see in that
> folder.
>
> For main I only get the file path of that main database.
>
> Is this behaviour as expected?
> Is it documented somewhere?
>
>
> RBS
>
>
>
> On Thu, Oct 8, 2015 at 5:18 PM, Bart Smissaert
> wrote:
>
> > Ignore this as I know what the problem was.
> > I was passing a pointer to the Unicode string, but should be pointer to
> > 8bit ASCII string.
> >
> > RBS
> >
> > On Thu, Oct 8, 2015 at 9:58 AM, Bart Smissaert >
> > wrote:
> >
> >> OK, thanks, at least I know that the function works fine then in
> >> sqlite3.dll.
> >> Problem must be on my side then.
> >>
> >> This is the code in the Std_Call dll:
> >>
> >> SQLITE3_STDCALL_API const char * __stdcall
> sqlite3_stdcall_db_filename(sqlite3
> >> *pDb, const char *zDbName)
> >> {
> >> return sqlite3_db_filename(pDb, zDbName);
> >> }
> >>
> >> And this is the Declare in VBA:
> >>
> >> Public Declare Function sqlite3_stdcall_db_filename Lib
> "SQLite3_StdCall"
> >> Alias "_sqlite3_stdcall_db_filename at 8" (ByVal hDBHandle As Long, ByVal
> >> lPtrAttachedDBName As Long) As Long
> >>
> >> Anything wrong with either of these?
> >>
> >>
> >> RBS
> >>
> >>
> >>
> >>
> >> On Thu, Oct 8, 2015 at 9:40 AM, Dan Kennedy
> >> wrote:
> >>
> >>> On 10/08/2015 03:51 AM, Bart Smissaert wrote:
> >>>
> As I understand it this should produce a filepointer to the filepath
> of
> the
> attached database, given the database handle of file the other
> database
> was
> attached to and the database name of the attached database. I checked
> all
> the return values and also did a select involving tables in both
> databases and all goes fine, so I can be sure that the other database
> is
> attached OK.
> All I get from sqlite3_db_filename is zero, so no valid file pointer.
> No
> error messages though.
>
> I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am
> working
> in VBA here.
>
> Any suggestions what could be the problem?
>
> I am running 3.8.11.1
>
>
> >>> The program below works here.
> >>>
> >>> I'm seeing full paths for databases "main" and "aux", and a zero-length
> >>> nul-terminated string for "next" (the in-memory database).
> >>>
> >>> Dan
> >>>
> >>> -
> >>>
> >>>
> >>>
> >>> #include
> >>> #include
> >>> #include
> >>>
> >>> int main(int argc, char **argv){
> >>> int rc;
> >>> sqlite3 *db;
> >>>
> >>> rc = sqlite3_open("test.db", &db);
> >>> if( rc!=SQLITE_OK ){
> >>> fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
> >>> exit(1);
> >>> }
> >>>
> >>> rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
> >>> if( rc!=SQLITE_OK ){
> >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> >>> exit(1);
> >>> }
> >>>
> >>> rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
> >>> if( rc!=SQLITE_OK ){
> >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> >>> exit(1);
> >>> }
> >>>
> >>> printf("main db is: %s\n", sqlite3_db_filename(db, "main"));
> >>> printf("aux db is: %s\n", sqlite3_db_filename(db, "aux"));
> >>> printf("next db is: %s\n", sqlite3_db_filename(db, "next"));
> >>>
> >>> return 0;
> >>> }
> >>>
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users at mailinglists.sqlite.org
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >>
> >>
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause?
Hi guys, Imagine a DB storing a history of currency exchange rate info. CREATE TABLE Prices ( id INTEGER PRIMARY KEY, day INTEGER, currency TEXT, price FLOAT); Assume 'day' increases monotonically and there is at most one price recorded per currency per day - if you want to know the latest prices, is this legal? SELECT currency, price FROM Prices GROUP BY currency HAVING time = MAX(time); I've seen this approach used and my reading of https://www.sqlite.org/lang_select.html#resultset hasn't left me confident that it is correct SQL, but in practice it seems to work fine... eg: insert into Prices (day, currency, price) values (1, 'EUR', 1.86); insert into Prices (day, currency, price) values (1, 'USD', 1.20); insert into Prices (day, currency, price) values (2, 'EUR', 1.92); insert into Prices (day, currency, price) values (2, 'USD', 1.18); insert into Prices (day, currency, price) values (3, 'USD', 1.24); /* price for EUR on day 3 intentionally left blank */ sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day = MAX(day); EUR|1.92 USD|1.24 MIN also seems to work: sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day = MIN(day); EUR|1.86 USD|1.2 As an aside, the query definitely doesn't work to get prices for a specific day: sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day = 2; EUR|1.92 I think the following explains the last result: > If a HAVING clause is a non-aggregate expression, it is evaluated with > respect to an arbitrarily selected row from the group. And the arbitrary row for EUR just happened to be day=2 here. But the documentation doesn't mention non-aggregate queries with a group by clause... > If the HAVING clause is an aggregate expression, it is evaluated across all > rows in the group. I'm not sure how to interpret this in the context of the "day = MAX(day)" expression though - does "evaluated across all rows" mean that rows excluded by the clause are discarded? If not then I suspect this query is not legit... but I don't feel I've understood everything so any clarification is appreciated! Cheers, -Rowan
[sqlite] SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE
On 29 September 2015 at 03:47, Florian Weimer wrote: > Relatively rarely, while starting a transaction on a concurrently > modified SQLite database in WAL mode, I get a SQLITE_BUSY_SNAPSHOT > error for just-prepared "BEGIN IMMEDIATE" statement. > BEGIN IMMEDIATE takes a RESERVED lock straight away, which for WAL mode translates into 1) starting a read transaction and 2) upgrading to a write transaction. I've no practical experience with WAL mode so hopefully someone corrects me if I'm wrong, but the comment on sqlite3WalBeginReadTransaction indicates that the data is snapshotted at this point in time -- if another thread modifies the database while the read transaction is open, the changes will not be visible in this transaction. So even though BEGIN IMMEDIATE is logically one step, there are two WAL operations involved, and if another thread modifies the db in between those operations we are indeed left looking at an out-of-date snapshot, so SQLITE_BUSY_SNAPSHOT seems appropriate regardless of the fact that we haven't read anything yet. -Rowan
[sqlite] UPDATE silently failing
On 23 September 2015 at 12:32, Hugues Bruant wrote: > On Wed, Sep 23, 2015 at 12:00 AM, Rowan Worth wrote: > > > Has many possible explanations. > > I can't think of any that is consistent with the Java specification. > Yeah fair enough, SIndex looks watertight. It's still possible for some other code to change _i through reflection but (a) I presume you would know if you were doing something like that and (b) it wouldn't explain the null SIndex capture. in some cases the SIndex captured inside the first lambda > (UPDATE statement) appeared to be null even though it wasn't null in the > enclosing scope (setVersion_) Interesting (and disturbing) result. Is this with Oracle's java compiler? -Rowan
[sqlite] UPDATE silently failing
> SIndex sidx is just a boxed immutable integer. If it were me I'd be reviewing this assumption very carefully. The sequence of events: 1. SIndex.getInt() returns 0 2. SIndex.toString() returns "1" 3. SIndex.getInt() returns 1 Has many possible explanations. You could also try 'final int sid = sidx.getInt();' at the start of setVersion_, and refer to sid throughout instead of sidx to guarantee a consistent value. If the same problem occurs then you know something is going wrong in the jdbc/sqlite layer, otherwise if you start getting rows inserted with cv_s = 0 you know sidx is somehow changing. -Rowan
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 21 September 2015 at 16:36, Simon Slavin wrote: > > On 21 Sep 2015, at 8:29am, Rowan Worth wrote: > > > 1) Statement is prepared > > 2) Statement is stepped > > 3) Statement is reset > > 4) ... time passes ... > > 5) Statement is stepped > > 6) Statement is reset > > 7) ... time passes ... > > etc. etc. > > > > The assertion seems to be that if the return value of > sqlite3_column_count > > is cached at step 2 it will still be valid at step 5 > > Sorry, no. I was saying that if it was cached at step 2 it will still be > valid until the end of step 3. And that if cached at step 5 it will still > be valid until the end of step 6. My understanding is that the database is > locked by the first call to _step() and unlocked at the first of ... > Sorry, I meant gwenn's assertion, not yours :) Although assertion was a poor choice of words - "premise of this thread" would have fit better. It seems reasonable on the surface - I can't think of how to phrase an SQL query such that it returns a different number of columns on a subsequent execution without the schema changing. But then I'm no SQL expert. Another solution is not to use "SELECT *" but instead to specify the values > you want returned. > I like this advice, but it kind of sounds like gwenn is working on a language binding so can't control the SQL itself. -Rowan
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 21 September 2015 at 14:38, Simon Slavin wrote: > As expected, ALTER TABLE acts like INSERT: it's a change which requires an > exclusive lock. So just as the documentation says, in a normal journal > mode you can't make a change while the database is locked (which it is > during a SELECT), and in WAL mode you can make the change but the > connection doing the SELECT won't see it until it finishes its transaction. > > Which gives the grand conclusion I posted earlier: OP does not have to > worry about this issue. It can never happen. Your schema cannot be > changed on you while you're inside a transaction if you don't intentionally > defeat SQLite's locking mechanism. > This all makes sense, but is it what the OP was asking? I thought gwenn was looking at the normal cached-statement scenario: 1) Statement is prepared 2) Statement is stepped 3) Statement is reset 4) ... time passes ... 5) Statement is stepped 6) Statement is reset 7) ... time passes ... etc. etc. The assertion seems to be that if the return value of sqlite3_column_count is cached at step 2 it will still be valid at step 5 -- unless a schema change happens at step 4 affecting the number of columns that will be returned by a SELECT *. As others have indicated, sqlite3_step may return SQLITE_SCHEMA in this scenario to indicate that the previously prepared statement needs to be recompiled. But a statement prepared by sqlite3_prepare_v2 will transparently recompile itself (up to 50 times by default) instead of returning SQLITE_SCHEMA: https://www.sqlite.org/rescode.html#schema Which I believe has lead to the OP's question "how do I know when this has happened?" One solution suggested by the docs is to rebuild sqlite3 with SQLITE_MAX_SCHEMA_RETRY=0, then handle the SQLITE_SCHEMA error by clearing the cache + manually recompiling the statements. Or maybe using sqlite3_prepare instead of _v2 would do the trick, if no other _v2 features are relied upon. -Rowan
[sqlite] [OT] Handling the whole select query from an index
> Outside London, a postcode can cover a far, far wider area specially in rural or sparsely populated areas. I would imagine Australian postcodes to be similar but thats based on a guess rather than actually any knowledge. I'm not sure whether to take this claim of ignorance at face value or whether its an incredibly subtle troll aimed at Australia's lack of population density (in which case well played sir, I guess you deserve the ashes after all). Postcodes in my city (Perth) cover a *much* wider area than 20 houses - generally several suburbs as Chris Waters suggested. But we jokingly refer to Perth as "the biggest country town in the world" which is why your comparison to rural England is so amusing/cutting :P -Rowan
[sqlite] About backup example
On 12 September 2015 at 20:59, David Kazlauskas
wrote:
> Hi, I see something in the second backup example (
> http://www.sqlite.org/backup.html ) that seems like logical error to me.
>
> if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
> sqlite3_sleep (250);
> }
>
>
> Why should we sleep 250 milliseconds if sqlite result is OK? Shouldn't we
> wait only if database is busy or locked?
>
That's intentional - the point of the second example is to demonstrate how
to backup a database without starving starving other processes that might
want to update it. Without the sleep the backup process would re-enter
sqlite3_backup_step and re-obtain a lock on the DB almost immediately.
-Rowan
[sqlite] BEGINNER - Transactions in shell script
On 6 September 2015 at 18:26, Luuk wrote: > > Suppose i have 'test.sql': > .echo on > DELETE FROM test; > BEGIN; > INSERT INTO test VALUES(1,'test1'); > INSERT INTO test VALUES(3,'test3',3); > INSERT INTO test VALUES(2,'test2'); > COMMIT; > SELECT * FROM test; > > And a database 'test.sqlite' with the following table: > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE test(i int, t text); > COMMIT; > > If i do: > type test.sql | sqlite3.exe test.sqlite > > Than there is an error executing this line: > INSERT INTO test VALUES(3,'test3',3); > > But the transaction is not stopped, or rolled back. > What am i missing? > The default behaviour of the sqlite3 shell when a statement encounters an error is to continue executing the remaining statements. Try sqlite3.exe -bail and instead it will stop at the first error. In that case the COMMIT statement is never executed so the statements within the half-finished transaction don't take effect. The DELETE of course remains effective - it is done and dusted by the time the transaction starts. -Rowan
[sqlite] Performance problems on windows
What about fragmentation of the database itself? Does running VACUUM on the
database affect performance?
-Rowan
On 26 August 2015 at 16:16, Jakub Zakrzewski wrote:
> Hi,
>
> nope. The defragmentation job runs every Wednsday night and the
> fragmentation is very low.
>
> PS: I'm putting together a small program to replay the queries I got from
> sqlite trace. If that will behave the same way as the original, I'll post
> it here.
>
> --
> Gruesse,
> Jakub
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> Sent: Dienstag, 25. August 2015 18:55
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Performance problems on windows
>
> On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski" wrote:
> >
> > Hi,
> >
> > The slowdown is confirmed by one of our customers. He uses Win 2008
> Server x64. I'm testing on Win7 x64.
>
> This might be obvious in which case my apologies for bringing it up but:
> are these systems demonstrating slowness perhaps heavily fragmented?
> Either the database is fragmented, other non-SQLite files required by the
> system, or especially the master file table? I've seen truly abysmal
> performance on such systems myself, depending on IO patterns.
>
> >
> > I'm sure that you're testing each release carefully. We do too. And we
> still have weird bugs ;)
> >
> > Thanks for interest. If I can provide any useful information, just
> > tell
> me, what you need. I cannot reveal the source code but some profiling
> results or SQL statements are not a secret.
> >
> > --
> > Gruesse,
> > Jakub
> >
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp
> > Sent: Dienstag, 25. August 2015 03:19
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Performance problems on windows
> >
> > I don't have any clues.
> >
> > While most of our work happens on Linux, we do measure performance on
> Windows from one release to the next (see for example item 23-b on the
> most recent release check-list
> > https://www.sqlite.org/checklists/private/3081100/index#c23) and it
> > gets
> better from one release to the next. So I don't know why you are seeing a
> slowdown.
> >
> > How do you measure?
> >
> > On 8/24/15, Jakub Zakrzewski wrote:
> > > Hi All,
> > >
> > > I finally got a chance to upgrade SQLite for our product from
> > > ancient 3.7.16.2. Initial tests on Linux were very promising -
> > > ranging from 33% to even 300% (for one degenerated case) speed
> > > improvement. So far
> so good.
> > > Problems begun when I have tested it on Windows. Depending on test
> > > case the new version is up to 0.28x slower! In the course of
> > > investigation I have managed to improve the performance by adding
> > > few missing indexes but this has influenced both old and new
> > > versions so the relative performance with
> > > 3.8.11.1 is still like one third worse.
> > >
> > > I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0
> > > and the results are quite surprising:
> > > 3.7.16.2 -> 3.7.17.0 : ~16% faster
> > > 3.7.17.0 -> 3.8.0.0 : ~26% slower
> > > 3.7.16.2 -> 3.8.0.0 : ~15% slower
> > > 3.7.16.2 -> 3.8.11.1 : ~28% slower
> > >
> > > We use SQLite as backend ("persistent storage") to an implementation
> > > of a state machine. The queries are rather simple and each of them
> > > is too fast to measure (SQLite performance timer only has resolution
> > > of
> > > milliseconds) in the profiler it looks like the actual filesystem
> > > functions were taking more time in new versions but that tells me
> nothing really.
> > >
> > > Is there something I can tweak?
> > > The page size is set to 4K (NTFS file system), synchonous is OFF,
> > > journal_mode=truncated;
> > >
> > > With 10K objects all versions seem to perform equally: test takes
> > > ~45s. For 100K objects it's already [m]:[s]
> > > 3.7.16.2 : ~10:55
> > > 3.7.17.0 : ~09:30
> > > 3.8.0.0 : ~12:46
> > > 3.8.11.1 : ~15:08
> > >
> > > I'm out of ideas here. Can someone help me with further investigation?
> > >
> > > --
> > > Gruesse,
> > > Jakub
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-u
[sqlite] ATTACH DATABASE statement speed
On 17 August 2015 at 14:52, Paolo Bolzoni wrote: > After I attach the INPUT db and I copy the useful lines. > > The input db is about 13GB, so not really large, however the step on > this sql statement (where ? is of course binded to the db name) > > ATTACH DATABASE ? AS indb; > > requires several minutes (or more, it does not really seem to finish)! > Besides sqlite3 already created a 5GB journal file. > Are you sure it's the process trying to attach the DB that created the 5GB journal? ie. is it possible the 5GB journal is a hot journal leftover from something else, and needs to be rolled back? Not sure if that happens during ATTACH or not... -Rowan
[sqlite] Using uninitialized value nDummy when calling vdbePmaReaderInit
On 15 August 2015 at 14:35, Carlos Tangerino wrote: > > The variable nDummy is not initialized *(1)* in the function but its > pointer *(2)* is passed to *vdbePmaReaderInit* that increments *(3)* its > value. > nDummy is never used after vdbePmaReaderInit though, so it doesn't really matter that its value is undefined, right? -Rowan
[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)
On 11 August 2015 at 06:22, Simon Slavin wrote: > If you did set a timeout then SQLite wais a very short time (a millisecond > ?) before it tries again, then it waits a little longer, then a little > longer still, etc.. It gradually increases the time until the total time > reaches the time you set. It does not use the number you used as a guide > for the short times is waits. > If you're building sqlite yourself, note that this backoff behaviour is dependent on a specific preprocessor macro, HAVE_USLEEP. If not set, sqlite's busy handler sleeps in _one second_ intervals, which is not ideal for concurrent usage scenarios. (The above only applies to non-windows platforms. On windows the incremental backoff is always used.) -Rowan
[sqlite] ATTACH Problem
Chris, you have an extra pair of single quotes in your original code compared to Simon's suggestion. Also in your parameter-based attempt you have a typo when assigning the path (@DNName instead of @DBName). Hope that helps, -Rowan On 31 July 2015 at 15:09, Chris Parsonson wrote: > That's exactly what I started with, then somebody suggested using a > parameter > > On 31 July 2015 at 09:02, Simon Slavin wrote: > > > > > On 31 Jul 2015, at 5:32am, Chris Parsonson wrote: > > > > >SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD" > > > > This is not the correct syntax for the ATTACH command. The name of the > > file to be attached is not a value, it's just included in the command. I > > would expect you to have better results using something like > > > > SQLStmt = "ATTACH " & sUpdateDBPath & " AS UPD" > > > > You must, of course, make sure that the sUpdateDBPath specifies a place > > that your application does have access to. You can test this by > inserting > > a few test lines which open a database of that location. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Chris Parsonson > 083 777 9261 > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Interrupting the busy handler
Hiya, Firstly thank you for sqlite, it's proved to be a remarkably solid bit of software. I recently noticed one interesting interaction -- or lack thereof -- between the default busy handler (registered via sqlite3_busy_timeout) and sqlite3_interrupt. Specifically, sqlite3_interrupt appears to have no effect on a statement that is currently trying to acquire a lock - at least not until the acquisition succeeds/fails. A quick look at the code suggested that the default busy handler indeed isn't checking the interrupted flag. I couldn't find anything in the changelog/archives on this topic, so my first question is whether the behaviour is by design? Or has it just fallen out this way? Thanks, -Rowan
