Re: [sqlite] Tracing latencies

2012-12-04 Thread Dan Kennedy 

On 12/05/2012 09:22 AM, Keith Chew wrote:

On Wed, Dec 5, 2012 at 1:14 PM, Keith Chew  wrote:

Since there is no fsync (I know this using grep on the strace ouput),
it is hard to tell what to look for in the strace. The output is very
noisy, so it makes it hard to go through them.

Does anyone know any handy flags for strace to only see all the
activities associated with sqlite file access?


I have confirmed using strace there is no fsyncs done by sqlite (using
synchronous=0 or synchronous=1), just seeks and writes. fsync is seen
only when synchronous=2.

After conducting more tests (using synchronous=0), I have some results
which is baffling me:
- Mysql average insert time, with write-cache OFF: 1-2ms
- Mysql average insert time, with write-cache ON: 0-1ms
- sqlite average insert time, with write-cache OFF: 160ms (have seen
up to 4000ms)
- sqlite average insert time, with write-cache ON: 50ms (have seen up to 150ms)

Quite a significant difference. I am stumped on how to make sqlite run faster.


If it's not fsync() then IO delays are normally caused by read().
You could try [strace -T -eread ...] to check.

Are SELECT statements fast on the same database? How large is the
database compared to the machines memory?

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
On Wed, Dec 5, 2012 at 1:14 PM, Keith Chew  wrote:
> Since there is no fsync (I know this using grep on the strace ouput),
> it is hard to tell what to look for in the strace. The output is very
> noisy, so it makes it hard to go through them.
>
> Does anyone know any handy flags for strace to only see all the
> activities associated with sqlite file access?

I have confirmed using strace there is no fsyncs done by sqlite (using
synchronous=0 or synchronous=1), just seeks and writes. fsync is seen
only when synchronous=2.

After conducting more tests (using synchronous=0), I have some results
which is baffling me:
- Mysql average insert time, with write-cache OFF: 1-2ms
- Mysql average insert time, with write-cache ON: 0-1ms
- sqlite average insert time, with write-cache OFF: 160ms (have seen
up to 4000ms)
- sqlite average insert time, with write-cache ON: 50ms (have seen up to 150ms)

Quite a significant difference. I am stumped on how to make sqlite run faster.

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
Hi Michael

On Wed, Dec 5, 2012 at 12:02 PM, Black, Michael (IS)
 wrote:
> Can you re-run your strace as "strace -tt" and look at the timings to help 
> pinpoint it?

Since there is no fsync (I know this using grep on the strace ouput),
it is hard to tell what to look for in the strace. The output is very
noisy, so it makes it hard to go through them.

Does anyone know any handy flags for strace to only see all the
activities associated with sqlite file access?

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

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

2012-12-04 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/12/12 14:43, Paul Menzel wrote:
> The following code caused the segmentation fault.

By far the most likely cause is some other library or the app itself
stomping on SQLite's memory.  SQLite is on every Android device, every iOS
device, virtually every browser (including Firefox and Chrome), used as a
component in a lot of programs etc.  All of that provides an immense
amount of real world testing.  Then add in the testing SQLite itself
undergoes which is *far* in advance of most other software short of some
safety critical systems.

  http://www.sqlite.org/testing.html

All of the above is why it requires strong evidence in order to point the
finger at SQLite.

If you do want to trace this further then I recommend you try the valgrind
program.  However it does require some understanding of how memory is used
and managed in a C based process.  Also if glib/evolution/libcamel play
silly games with memory (eg using their own allocators and pools) then it
will completely obscure what is going on from valgrind.

You'll most likely get better insight in a group devoted to evolution.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC+j8YACgkQmOOfHg372QTexgCgldd0k9006XRiaetrEOcidSJ1
UMgAnjXoiTHUDa/oTxvXTkoPCg37DUmQ
=uLKA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracing latencies

2012-12-04 Thread Black, Michael (IS) 
Can you re-run your strace as "strace -tt" and look at the timings to help 
pinpoint it?

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Keith Chew [keith.c...@gmail.com]
Sent: Tuesday, December 04, 2012 5:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 11:10 AM, Keith Chew  wrote:
> The strange thing is that I am setting sqlite it to use WAL,
> autocheckpoint off and synchronous off. Even in this setup, I still
> see > 350ms transactions times for less than 3 TPS. A bit hard to
> believe, so I am now doing a strace to find out exactly what is
> hitting the disk.

II can confirm using strace that there are no fsyncs happening from
the application (which we expect because synchronous=0). So, it must
be something else that is causing these blocks. Somekind of file
locking issue (particular to my environment)? What else should I be
looking at?

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

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

2012-12-04 Thread Larry Brasfield 

On 4 December 2012, Paul Menzel wrote:

After doing `apt-get source sqlite3` and building it myself with
`debuild -b -us -uc`, I have the source file `sqlite3.c` and I am able
to look at the code statements.

> The backtrace from the core dump file is the following.
>
> Thread 1 (Thread 0x8acf1b70 (LWP 15522)):
> #0  0xb69bafe3 in pcache1Fetch (p=0xb8effb00, iKey=35985, 
createFlag=2) at sqlite3.c:36093
> h = 1169
> nPinned = 
> pCache = 0xb8effb00
> pGroup = 0xb8effb30
> pPage = 0xbf8ab0e8

The following code caused the segmentation fault.


Paul, I offer this to help you get your problem solved sooner, not
as criticism or complaint.

Earlier in this thread, Richard Hipp wrote:

Just because SQLite appears in a stack trace does not mean that SQLite is
at fault here.  In fact, far more often than not, when SQLite appearing in
a stack trace it means that some other unrelated component of the
application has corrupted the heap and SQLite just happened to be the
unlucky subsystem to trip over that corruption.

If you can generate some evidence that SQLite is malfunctioning, we will be
happy to look into the situation for you, and perhaps open a ticket.  But,
unfortunately, a single unreproducible segfault with a stacktrace that
includes SQLite routines does not constitute evidence of an SQLite
malfunction.


What you have supplied is evidence that SQLite either has a bug that your
code has exposed or its correct operation is subject to the various forms
of the "undefined behavior" that typically result from stray memory
overwrites by other code.  There is no way, given the data you provide,
to distinguish between these cases, and it is a fool's errand to attempt
such an effort.  The SQLite developers have better uses for their time
than to try to improve the codebase in response to allegations that it
might have a bug because its code exhibits the same vulnerability to
other code that every human-created C/C++ program must have.

To provide the most useful evidence of a SQLite bug, you need to create
some SQL that will crash the SQLite shell, or which will crash a C
program that is so simple that its correctness is apparent from some
reasonably small effort.

In all likelihood, you will be unable to create such a reproduction
scenario, and that should encourage you to start using a debug heap
or sophisticated tools such as Purify to discover where your code
has a stray memory write occurring.

Best regards,
--
Larry Brasfield

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
On Wed, Dec 5, 2012 at 11:10 AM, Keith Chew  wrote:
> The strange thing is that I am setting sqlite it to use WAL,
> autocheckpoint off and synchronous off. Even in this setup, I still
> see > 350ms transactions times for less than 3 TPS. A bit hard to
> believe, so I am now doing a strace to find out exactly what is
> hitting the disk.

II can confirm using strace that there are no fsyncs happening from
the application (which we expect because synchronous=0). So, it must
be something else that is causing these blocks. Somekind of file
locking issue (particular to my environment)? What else should I be
looking at?

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

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

2012-12-04 Thread Paul Menzel 
Am Sonntag, den 02.12.2012, 22:49 +0100 schrieb Paul Menzel:

> using Debian Sid/unstable with self-built Evolution 3.4.4 and
> libsqlite3-0 3.7.14.1-1, Evolution crashed with a segmentation fault.
> 
> pool[15522]: segfault at 5 ip b69bafe3 sp 8acf0850 error 6 in 
> libsqlite3.so.0.8.6[b69a4000+ac000]

After doing `apt-get source sqlite3` and building it myself with
`debuild -b -us -uc`, I have the source file `sqlite3.c` and I am able
to look at the code statements.

> The backtrace from the core dump file is the following.
> 
> Thread 1 (Thread 0x8acf1b70 (LWP 15522)):
> #0  0xb69bafe3 in pcache1Fetch (p=0xb8effb00, iKey=35985, 
> createFlag=2) at sqlite3.c:36093
> h = 1169
> nPinned = 
> pCache = 0xb8effb00
> pGroup = 0xb8effb30
> pPage = 0xbf8ab0e8

The following code caused the segmentation fault.

36093   *(void **)pPage->page.pExtra = 0;
(gdb) l
36088   pPage->iKey = iKey;
36089   pPage->pNext = pCache->apHash[h];
36090   pPage->pCache = pCache;
36091   pPage->pLruPrev = 0;
36092   pPage->pLruNext = 0;
36093   *(void **)pPage->page.pExtra = 0;
36094   pCache->apHash[h] = pPage;
36095 }
36096   
36097   fetch_out:
(gdb) p pPage
$1 = (PgHdr1 *) 0xbf8ab0e8
(gdb) p pPage->page.pExtra
$2 = (void *) 0x5
(gdb) info register
eax0x5  5
ecx0xb8effb30   -1192232144
edx0x4911169
ebx0xb6a51d3c   -1230693060
esp0x8acf0850   0x8acf0850
ebp0xb8effb00   0xb8effb00
esi0xbf8ab0e8   -1081429784
edi0xb8effb00   -1192232192
eip0xb69bafe3   0xb69bafe3 
eflags 0x10212  [ AF IF RF ]
cs 0x73 115
ss 0x7b 123
ds 0x7b 123
es 0x7b 123
fs 0x0  0
gs 0x33 51

As

*(void **)pPage->page.pExtra = 0;

is above my basic C knowledge, maybe somebody sees if there is a reason
for the segfault here. Otherwise Richard is probably right, that the
heap corruption is caused by some other program.


Thanks,

Paul


signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
Hi Michael

On Wed, Dec 5, 2012 at 10:57 AM, Black, Michael (IS)
 wrote:
> Could it be waitiing on the prior transaction though?
>
> Since disk I/O lies it might be syncing the last transaction causing the new 
> one to wait longer.

Yes, that is possible. In the application, it opens 5 connections and
put them in a pool. I had a look, and at the most 3 connections are
used at a time. So, I am looking at 3 TPS max.

The strange thing is that I am setting sqlite it to use WAL,
autocheckpoint off and synchronous off. Even in this setup, I still
see > 350ms transactions times for less than 3 TPS. A bit hard to
believe, so I am now doing a strace to find out exactly what is
hitting the disk.

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
On Wed, Dec 5, 2012 at 10:56 AM, Richard Hipp  wrote:
>> It is not an indexing issue. For one of the UPDATE SQLs, it is
>> updating a table with only 1 record in it. And this takes > 350ms...
>>
>
> Can you provide specifics:  The schema and the UPDATE statement?

Schema and SQL are below:

CREATE TABLE `k_user` (
  `user_id` integer primary key autoincrement,
  `user_name` varchar(50) default NULL,
  `created_at` datetime default NULL,
  `user_group_id` bigint(20) default NULL,
  `x` double default NULL,
  `y` double default NULL,
  `heading` double default NULL,
  `altitude` double default NULL,
  `speed` double default NULL,
  `state_id` bigint(20) default NULL,
  `status` varchar(15) default NULL,
  `last_updated_at` datetime default NULL
);

update k_user set x = ? , y = ? , heading = ? , last_updated_at = ?
where user_id=?

It cannot get simpler than this. For the inserts, it is just as
simple. A table with 15 columns, and a straight insert.

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Black, Michael (IS) 
Could it be waitiing on the prior transaction though?

Since disk I/O lies it might be syncing the last transaction causing the new 
one to wait longer.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Keith Chew [keith.c...@gmail.com]
Sent: Tuesday, December 04, 2012 3:45 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 10:28 AM, Keith Chew  wrote:
>
> I wonder what could be causing sqlite to hang so long? Will try to
> remove all indexes to see if that narrows things down.

It is not an indexing issue. For one of the UPDATE SQLs, it is
updating a table with only 1 record in it. And this takes > 350ms...
All the other tables have only 1 or 2 indexes, so should not be
impacting the inserts/updates.

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Richard Hipp 
On Tue, Dec 4, 2012 at 4:45 PM, Keith Chew  wrote:

> On Wed, Dec 5, 2012 at 10:28 AM, Keith Chew  wrote:
> >
> > I wonder what could be causing sqlite to hang so long? Will try to
> > remove all indexes to see if that narrows things down.
>
> It is not an indexing issue. For one of the UPDATE SQLs, it is
> updating a table with only 1 record in it. And this takes > 350ms...
>

Can you provide specifics:  The schema and the UPDATE statement?


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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
On Wed, Dec 5, 2012 at 10:28 AM, Keith Chew  wrote:
>
> I wonder what could be causing sqlite to hang so long? Will try to
> remove all indexes to see if that narrows things down.

It is not an indexing issue. For one of the UPDATE SQLs, it is
updating a table with only 1 record in it. And this takes > 350ms...
All the other tables have only 1 or 2 indexes, so should not be
impacting the inserts/updates.

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

Re: [sqlite] Async IO in SQLite 3.7.15

2012-12-04 Thread Ralf Junker 
On 04.12.2012 22:25, Richard Hipp wrote:

>> My question is if existing applications which Async IO should continue
>> to work with SQLite 3.7.15? Or has something in the SQLite core changed
>> so that you'd expect Async IO failures in 3.7.15, or later versions?
> 
> They should continue to work, as far as we are aware.  Nothing in the core
> has changed to break them.

Thanks!

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
On Wed, Dec 5, 2012 at 9:33 AM, Keith Chew  wrote:
> Any other ideas for me to trace this at a lower level would be greatly
> appreciated. I think I will need to get deeper into linux's block
> layer stuff, so perhaps this is not the correct place to ask the
> question, but I thought I'd start here.

I have done a bit more comparison on the IO pattern when running MySQL
and sqlite, using vmstat (results below). I can observe that sqlite
causes a bit more bytes to be written to the disk, but not by much.
Wait times are also reasonable.

I wonder what could be causing sqlite to hang so long? Will try to
remove all indexes to see if that narrows things down.

Sqlite:
 2  0  0 184148  51564 44622400 028  290  602  0  0 86 14  0
 1  0  0 184148  51572 44623200 028  293 1041 26  7 51 16  0
 0  0  0 184148  51580 44623200 052  300  926  0  0 84 16  0
 1  0  0 184024  51588 44623200 028  293 1071 24  9 53 14  0
 1  0  0 184024  51596 44622800 036  287  802  0  1 83 16  0
 2  0  0 183588  51604 44623600 028  291 1004 10  4 45 41  0
 0  0  0 184024  51612 44623600 056  293 1003 16  3 65 17  0
 0  0  0 184024  51620 44624000 028  293  854  0  0 87 13  0
 0  0  0 184024  51628 44624400 040  292 1060 28  6 49 18  0
 0  0  0 184024  51636 44624000 040  293  598  0  1 82 17  0
 1  0  0 184024  51644 44624400 036  290  899 24  7 54 15  0
 0  0  0 184024  51652 44624800 036  292  591  0  1 90  9  0
 1  0  0 183580  51660 44624800 040  290  758 16  3 67 14  0
 0  0  0 183900  51668 44624800 036  291  730 11  3 71 16  0
 1  0  0 183900  51676 44624800 028  292  601  0  1 59 40  0
 0  0  0 183900  51684 44625200 036  292  894 25  8 67  0  0
 1  0  0 183900  51692 44625200 064  295  590  0  0 91  9  0
 0  0  0 183900  51700 44625600 028  289  860 25  7 55 13  0


Mysql:
 2  0  0 192408  48120 44830800 048  260 1076 30  5 62  3  0
 0  0  0 195300  48128 45031600 032  263 1027 15  4 71 10  0
 2  1  0 194708  48128 45027600 056  257  768 15  4 81  0  0
 2  0  0 199004  48136 44640000 052  263  722 10  2 85  3  0
 1  0  0 197268  48144 44842800 040  258  636  7  0 81 12  0
 0  0  0 196896  48152 44845600 036  260  916 22  9 59 10  0
 1  0  0 198880  48152 44640800 0 0  253  496  0
0 100  0  0
 1  0  0 199012  48160 44626400 024  258  765 28  9 63  0  0
 1  0  0 199012  48168 44626400 032  260  382  1  0 85 14  0
 2  0  0 198868  48168 44626400 0 0  254  701 23  8 69  0  0
 0  0  0 199012  48176 44629200 032  259  434  6  0 82 12  0
 3  0  0 198832  48184 44628000 032  259  445  0  2 86 12  0
 1  0  0 199012  48184 44626400 0 0  253  656 22  8 70  0  0
 1  0  0 199012  48192 44626400 032  259  412  6  1 79 14  0
 1  0  0 19  48200 44625600 056  261  702 24  6 53 17  0
 0  0  0 19  48200 44626400 056  266  376  0  1 96  3  0
 1  0  0 198864  48208 44626400 024  257  757 31  5 53 11  0
 0  0  0 19  48216 44626400 032  259  382  1  1 85 13  0
 2  0  0 19  48216 44626400 0 0  253  368  0
0 100  0  0
 0  0  0 19  48224 44626800 036  258  430  6  1 73 20  0
 1  0  0 197568  48232 44468800 024  267  687  8  3 80  9  0

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

Re: [sqlite] Async IO in SQLite 3.7.15

2012-12-04 Thread Richard Hipp 
On Tue, Dec 4, 2012 at 4:20 PM, Ralf Junker  wrote:

> On 04.12.2012 22:14, Richard Hipp wrote:
>
> >> Does this also mean that it will no longer be working with SQLite
> >> 3.7.15? Is it just deprecated for new development? Or has something else
> >> changed that I should take care of?
> >
> > That means that we are not willing to devote large amounts of time to it
> > anymore.  If you have patches that you want us to put in, we'll be glad
> to
> > do that.
>
> Thanks, this is well understood and clear from the README note.
>
> My question is if existing applications which Async IO should continue
> to work with SQLite 3.7.15? Or has something in the SQLite core changed
> so that you'd expect Async IO failures in 3.7.15, or later versions?
>

They should continue to work, as far as we are aware.  Nothing in the core
has changed to break them.


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



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

Re: [sqlite] Async IO in SQLite 3.7.15

2012-12-04 Thread Ralf Junker 
On 04.12.2012 22:14, Richard Hipp wrote:

>> Does this also mean that it will no longer be working with SQLite
>> 3.7.15? Is it just deprecated for new development? Or has something else
>> changed that I should take care of?
>
> That means that we are not willing to devote large amounts of time to it
> anymore.  If you have patches that you want us to put in, we'll be glad to
> do that.

Thanks, this is well understood and clear from the README note.

My question is if existing applications which Async IO should continue
to work with SQLite 3.7.15? Or has something in the SQLite core changed
so that you'd expect Async IO failures in 3.7.15, or later versions?

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

Re: [sqlite] Async IO in SQLite 3.7.15

2012-12-04 Thread Richard Hipp 
On Tue, Dec 4, 2012 at 4:11 PM, Ralf Junker  wrote:

> I experience problems with Async IO testing the latest 3.7.15 trunk.
> Simple statements like CREATE TABLE fail with SQLITE_IOERR.
>
> I read the note in the Async IO README.txt that Async IO is now
> superceded by WAL mode and no longer maintained.
>
> Does this also mean that it will no longer be working with SQLite
> 3.7.15? Is it just deprecated for new development? Or has something else
> changed that I should take care of?
>

That means that we are not willing to devote large amounts of time to it
anymore.  If you have patches that you want us to put in, we'll be glad to
do that.



>
> Ralf
>
> On 03.12.2012 21:25, Richard Hipp wrote:
>
> > We want 3.7.15 to be a good release, so please do have a look at the
> > amalgamation snapshot described in the previous email (and copied below)
> if
> > you have not done so already, and let us know if you encounter any
> > problems.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Async IO in SQLite 3.7.15

2012-12-04 Thread Ralf Junker 
I experience problems with Async IO testing the latest 3.7.15 trunk.
Simple statements like CREATE TABLE fail with SQLITE_IOERR.

I read the note in the Async IO README.txt that Async IO is now
superceded by WAL mode and no longer maintained.

Does this also mean that it will no longer be working with SQLite
3.7.15? Is it just deprecated for new development? Or has something else
changed that I should take care of?

Ralf

On 03.12.2012 21:25, Richard Hipp wrote:

> We want 3.7.15 to be a good release, so please do have a look at the
> amalgamation snapshot described in the previous email (and copied below) if
> you have not done so already, and let us know if you encounter any
> problems.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump ... [was: Please test the latest SQLite snapshot]

2012-12-04 Thread Larry Brasfield 

On 3 Dec 2012, Richard Hipp wrote:

... You can get a tarball or ZIP archive of the latest raw
sources from Fossil at http://www.sqlite.org/src/info/trunk


I notice that in this (3.7.15 pre-release snapshot) version of
the shell the .help out for the .dump command reads:
  .dump ?TABLE? ...  Dump the database in an SQL text format
 If TABLE specified, only dump tables matching
 LIKE pattern TABLE.
This seems to indicate that more than one TABLE argument could be
supplied.  I happen to have a good use for that feature, and when
it did not work, I went to see why and found this code
if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){
guarding/selecting the .dump implementation.  By commenting out the
last && term, it happily does what I wanted, doing a combined .dump
output for just the tables I wish to recreate later.

Is multiple table dumping suspected to not work for some reason?
If so, the .help output should lose the '...'.  If not, perhaps
the shell.c source should lose that "&& nArg<3" clause.

Thanks,
--
Larry Brasfield

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
Hi Richard

On Wed, Dec 5, 2012 at 9:05 AM, Richard Hipp  wrote:
> The $dir will contain initial database contents and scripts of SQL
> statements that were run.  You can rerun those SQL statement using the
> command-line shell to find slow ones, then do things like EXPLAIN QUERY
> PLAN to figure out why they are slow and perhaps fix them by adding indices.

Thank you very much for your comprehensive reply. I actually know the
queries, because the application logs both the SQL and times. They are
simple SQL statements, eg "insert into table_a (...) values (...)" or
"update table_a set aaa='...' where bbb='...'".

I just tried enabling the write-cache of the drive, and the warn logs
disappeared. So, I know that somehow the OS/filesystem/disk setup is
blocking the inserts/updates quite severely for sqlite (but not for
MySQL). Is there anything else I can do to improve the situation?
Ideally I would like to understand what is happening under the hood of
sqlite, to improve my understanding of the difference between the 2
DBs.

Any other ideas for me to trace this at a lower level would be greatly
appreciated. I think I will need to get deeper into linux's block
layer stuff, so perhaps this is not the correct place to ask the
question, but I thought I'd start here.

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

Re: [sqlite] Tracing latencies

2012-12-04 Thread Richard Hipp 
On Tue, Dec 4, 2012 at 2:30 PM, Keith Chew  wrote:

> Hi
>
> I have an existing application, which I am experimenting with
> replacing MySQL with sqlite. I have 2 boxes, one running MySQL, the
> other sqlite.
>
> Execution times for every insert/update/delete SQLs is measured, and a
> warn log is printed if it takes > 250ms to perform the SQL.
>
> OS configuration:
> - kernel 2.6.39
> - ext3, data=ordered, commit=1, barrier=1
>
> On the MySQL box, I do not see any warn logs, so everything is going
> well. On the sqlite box, I see warn logs come up quite often, ranging
> from 350ms up to 4000ms to run simple insert/update/delete SQL.
>
> I have tried many sqlite configurations including:
> - WAL, synchronous=0, wal_autocheckpoint=0
>
> which should make sqlite run the fastest!
>
> My question is how do I go about tracing the cause of these latencies?
>

Here is one approach:

(1) Get the latest SQLite sources from the Fossil repository
(2) Do "./configure; make sqlite3.c" to build the amalgamation
(3) Do "cat src/test_sqllog.c >>sqlite3.c" to append the "test_sqllog.c"
file to the end of the amalgamation from step (2).
(4) Edit the "sqlite3.c" file to insert a line near the top:

   #define SQLITE_ENABLE_SQLLOG

Or, you can compile with -DSQLITE_ENABLE_SQLLOG, whichever is easiest for
you.
(5) Set the environment variable SQLITE_SQLLOG_DIR=$dir where $dir is an
empty directory someplace where you want logging information to be written.
(6) Run your application

The $dir will contain initial database contents and scripts of SQL
statements that were run.  You can rerun those SQL statement using the
command-line shell to find slow ones, then do things like EXPLAIN QUERY
PLAN to figure out why they are slow and perhaps fix them by adding indices.

Further information in the header comment of test_sqllog.c.


http://www.sqlite.org/src/artifact/7813b47021a6d4e39bb7b1b328a8893dc59885cb

The SQLLOG facility is new.  So you'll have to use the latest sources out
of Fossil for it to work.



> I am starting to look at tools like blktrace, is that the right path?
> Some guidance on how to use some low level tools would be appreciated,
>
> Regards
> Keith
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Tracing latencies

2012-12-04 Thread Keith Chew 
Hi

I have an existing application, which I am experimenting with
replacing MySQL with sqlite. I have 2 boxes, one running MySQL, the
other sqlite.

Execution times for every insert/update/delete SQLs is measured, and a
warn log is printed if it takes > 250ms to perform the SQL.

OS configuration:
- kernel 2.6.39
- ext3, data=ordered, commit=1, barrier=1

On the MySQL box, I do not see any warn logs, so everything is going
well. On the sqlite box, I see warn logs come up quite often, ranging
from 350ms up to 4000ms to run simple insert/update/delete SQL.

I have tried many sqlite configurations including:
- WAL, synchronous=0, wal_autocheckpoint=0

which should make sqlite run the fastest!

My question is how do I go about tracing the cause of these latencies?
I am starting to look at tools like blktrace, is that the right path?
Some guidance on how to use some low level tools would be appreciated,

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

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

2012-12-04 Thread Richard Hipp 
On Tue, Dec 4, 2012 at 9:35 AM, Paul Menzel <
paulepan...@users.sourceforge.net> wrote:

>
> Could this please be added to the bug tracker? Unfortunately this
> backtrace is all I have got.
>

Just because SQLite appears in a stack trace does not mean that SQLite is
at fault here.  In fact, far more often than not, when SQLite appearing in
a stack trace it means that some other unrelated component of the
application has corrupted the heap and SQLite just happened to be the
unlucky subsystem to trip over that corruption.

If you can generate some evidence that SQLite is malfunctioning, we will be
happy to look into the situation for you, and perhaps open a ticket.  But,
unfortunately, a single unreproducible segfault with a stacktrace that
includes SQLite routines does not constitute evidence of an SQLite
malfunction.


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

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

2012-12-04 Thread Paul Menzel 
Dear SQLite folks,


Am Sonntag, den 02.12.2012, 22:49 +0100 schrieb Paul Menzel:

> using Debian Sid/unstable with self-built Evolution 3.4.4 and
> libsqlite3-0 3.7.14.1-1, Evolution crashed with a segmentation fault.
> 
> pool[15522]: segfault at 5 ip b69bafe3 sp 8acf0850 error 6 in 
> libsqlite3.so.0.8.6[b69a4000+ac000]
> 
> The backtrace from the core dump file is the following.
> 
> Thread 1 (Thread 0x8acf1b70 (LWP 15522)):
> #0  0xb69bafe3 in pcache1Fetch (p=0xb8effb00, iKey=35985, 
> createFlag=2) at sqlite3.c:36093
> h = 1169
> nPinned = 
> pCache = 0xb8effb00
> pGroup = 0xb8effb30
> pPage = 0xbf8ab0e8
> #1  0xb69b4a25 in sqlite3PcacheFetch (pCache=0xb8eeeb60, 
> pgno=pgno@entry=35985, createFlag=createFlag@entry=1, ppPage=ppPage@entry=
> 0x8acf092c) at sqlite3.c:34905
> pPage = 0x0
> pPgHdr = 0x0
> eCreate = 2
> #2  0xb69e6980 in sqlite3PagerAcquire (pPager=0xb8eeeaa0, 
> pgno=pgno@entry=35985, ppPage=ppPage@entry=0x8acf092c, 
> noContent=noContent@entry=0) at sqlite3.c:41989
> rc = 
> pPg = 
> #3  0xb69e6b3a in btreeGetPage (pBt=0xb8edef00, pgno=35985, 
> ppPage=ppPage@entry=0x8acf097c, noContent=noContent@entry=0) at 
> sqlite3.c:49488
> rc = 
> pDbPage = 0x140
> #4  0xb69f37f4 in getAndInitPage (pBt=, 
> pgno=, ppPage=0x8acf097c) at sqlite3.c:49541
> rc = 
> #5  0xb69f3866 in moveToChild (pCur=0x92140d50, newPgno= out>) at sqlite3.c:52106
> rc = 
> i = 1
> pNewPage = 
> pBt = 
> #6  0xb69f39ad in moveToLeftmost (pCur=0x92140d50) at sqlite3.c:52284
> rc = 
> pPage = 
> #7  0xb6a2dc8d in sqlite3VdbeExec (p=p@entry=0x921512b0) at 
> sqlite3.c:68025
> pc = 
> aOp = 0x92361a08
> pOp = 0x92361b98
> rc = 
> db = 
> resetSchemaOnFault = 
> encoding = 1 '\001'
> checkProgress = 0
> nProgressOps = 0
> aMem = 0x9235b050
> pIn1 = 
> pIn2 = 
> pIn3 = 
> pOut = 0x0
> iCompare = 
> aPermute = 
> lastRowid = 
> u = {aa = {pcDest = -1844179936}, ab = {cnt = -1844179936}, 
> ac = {pVar = 0x92140c20}, ad = {zMalloc = 0x92140c20 "P\r\024\222", 
> n = 0, p1 = -1966142271, p2 = 10}, ae = {pMem = 
> 0x92140c20, i = 0}, af = {nByte = 2450787360}, ag = {flags = -1844179936, 
> iA = -8444516753228169216, iB = 107374182410, rA = 
> -1.8091740329328587e-113, rB = -1.3866037848944667e-221}, ah = {
> i = -1844179936, pArg = 0x0, ctx = {pFunc = 0x8acf0cc1, 
> pVdbeFunc = 0xa, s = {db = 0x19, z = 0x92140c20 "P\r\024\222", 
> r = -1.3868233835305826e-221, u = {i = 
> -7920691651615126400, nZero = -1844179840, pDef = 0x92140c80, pRowSet = 
> 0x92140c80, 
>   pFrame = 0x92140c80}, n = 26, flags = 31, type = 0 
> '\000', enc = 0 '\000', xDel = 0xb69b7f84 , 
> zMalloc = 0x0}, pMem = 0x1, pColl = 0x1, isError = 
> -1230693060, skipFlag = 10}, apVal = 0xb6a51d3c, n = -1230809131}, 
>   ai = {iA = 2450787360, uA = 45278497985, iB = 
> -7920692513059373031, op = 206 '\316'}, aj = {res = -1844179936, 
> affinity = 0 '\000', flags1 = 0, flags3 = 3265}, ak = {n 
> = -1844179936, i = 0, p1 = -1966142271, p2 = 10, pKeyInfo = 0x19, 
> idx = -1844179936, pColl = 0xa88646ce, bRev = 
> -1844179632}, al = {v1 = -1844179936, v2 = 0}, am = {c = -1844179936}, an = {
> payloadSize = 2450787360, payloadSize64 = 
> -8444516753228169216, p1 = 10, p2 = 25, pC = 0x92140c20, zRec = 
> 0xa88646ce 
> "\037\031\002\001\001\001\001\001\001\001\001\002\004\004\201!A3", pCrsr = 
> 0x92140d50, aType = 0x92140c80, aOffset = 
> 0x92140ce8, nField = 26, len = 31, i = -1231323260, zData = 0x0, 
> pDest = 0x1, sMem = {db = 0x1, z = 0xb6a51d3c ",\334\n", 
>   r = -1.8492057199714905e-45, u = {i = 
> -5288166510061987883, nZero = -1230809131, pDef = 0xb6a357d5, pRowSet = 
> 0xb6a357d5, 
> pFrame = 0xb6a357d5}, n = 0, flags = 0, type = 0 
> '\000', enc = 0 '\000', xDel = 0xa, zMalloc = 0x0}, zIdx = 
> 0xa88646eb "!!297611\001\020", zEndHdr = 0xa88646ed 
> "297611\001\020", offset = 1302605444, szField = 0, szHdr = -1228328569, 
> avail = 378, t = 3066643705, pReg = 0xb6cf37a3}, ao = 
> {zAffinity = 0x92140c20 "P\r\024\222", cAff = 0 '\000'}, ap = {
>

[sqlite] Test failures on Ubuntu

2012-12-04 Thread brijesh_philips 
Hi,

I am getting failures in the below tests while doing the regression test on
Ubuntu 
with ,
SHA1 Hash: ba8d08b67021a32fda069c18b7eb93523e6f0d1f 
Date: 2012-11-27 21:56:28

Most of the failures are, 

Expected: [1 1]
 Got: [0 {}]

Are these errors serious, How to fix these ?

Thank You
Brijesh

142 errors out of 2083931 tests
Failures on these tests: 
altermalloc-1.transient.19 
attachmalloc-1.transient.19 
attachmalloc-1.transient.63 
attachmalloc-1.transient.361 
attachmalloc-1.transient.580 
attachmalloc-2.transient.237 
malloc-1.transient.19 
malloc-10.transient.19 
malloc-13.transient.21 
malloc-14.transient.19 
malloc-17.transient.21 
malloc-20.transient.19 
malloc-20.transient.57 
malloc-26.transient.19 
malloc6-1.transient.19 
mallocD-4.transient.21 
mallocG-1.transient.19 
shared_err-5.transient.21 
shared_malloc-8.64.cleanup.1 
shared_malloc-8.65.cleanup.1 
shared_malloc-8.66.cleanup.1 
shared_malloc-8.67.cleanup.1 
shared_malloc-8.68.cleanup.1 
shared_malloc-8.64.cleanup.1 
shared_malloc-8.65.cleanup.1 
shared_malloc-8.66.cleanup.1 
shared_malloc-8.67.cleanup.1 
shared_malloc-8.68.cleanup.1 
no_optimization.where-14.1 
no_optimization.where-14.2 
no_optimization.where-14.3 
no_optimization.where-14.4 
no_optimization.where-14.7.2 
memsubsys1.altermalloc-1.transient.18 
memsubsys1.attachmalloc-1.transient.18 
memsubsys1.attachmalloc-1.transient.61 
memsubsys1.attachmalloc-1.transient.354 
memsubsys1.attachmalloc-1.transient.569 
memsubsys1.attachmalloc-2.transient.233 
memsubsys1.malloc-1.transient.18 
memsubsys1.malloc-10.transient.18 
memsubsys1.malloc-13.transient.20 
memsubsys1.malloc-14.transient.18 
memsubsys1.malloc-17.transient.20 
memsubsys1.malloc-20.transient.18 
memsubsys1.malloc-20.transient.55 
memsubsys1.malloc-26.transient.18 
memsubsys1.malloc6-1.transient.18 
memsubsys1.mallocD-4.transient.20 
memsubsys1.mallocG-1.transient.18 
memsubsys2.altermalloc-1.transient.19 
memsubsys2.attachmalloc-1.transient.19
memsubsys2.attachmalloc-1.transient.63 
memsubsys2.attachmalloc-1.transient.361 
memsubsys2.attachmalloc-1.transient.580 
memsubsys2.attachmalloc-2.transient.237 
memsubsys2.malloc-1.transient.19 
memsubsys2.malloc-10.transient.19 
memsubsys2.malloc-13.transient.21 
memsubsys2.malloc-14.transient.19 
memsubsys2.malloc-17.transient.21 
memsubsys2.malloc-20.transient.19 
memsubsys2.malloc-20.transient.57 
memsubsys2.malloc-26.transient.19 
memsubsys2.malloc6-1.transient.19 
memsubsys2.mallocD-4.transient.21 
memsubsys2.mallocG-1.transient.19 
exclusive.malloc-1.transient.19 
exclusive.malloc-10.transient.22 
exclusive.malloc-13.transient.15 
exclusive.malloc-14.transient.19 
exclusive.malloc-17.transient.21 
exclusive.malloc-20.transient.19 
exclusive.malloc-20.transient.62 
exclusive.malloc-26.transient.22 
persistent_journal_error.malloc-1.transient.19 
persistent_journal_error.malloc-10.transient.19
 persistent_journal_error.malloc-13.transient.15 
 persistent_journal_error.malloc-14.transient.19 
 persistent_journal_error.malloc-17.transient.21 
 persistent_journal_error.malloc-20.transient.19 
 persistent_journal_error.malloc-20.transient.113 
 persistent_journal_error.malloc-26.transient.19 
 no_journal_error.malloc-1.transient.19 
 no_journal_error.malloc-10.transient.19 
 no_journal_error.malloc-13.transient.15 
 no_journal_error.malloc-14.transient.19 
 no_journal_error.malloc-17.transient.21 
 no_journal_error.malloc-20.transient.19 
 no_journal_error.malloc-20.transient.113 
 no_journal_error.malloc-26.transient.19 
 no_mutex_try.altermalloc-1.transient.19 
 no_mutex_try.attachmalloc-1.transient.19 
 no_mutex_try.attachmalloc-1.transient.63 
 no_mutex_try.attachmalloc-1.transient.361 
 no_mutex_try.attachmalloc-1.transient.580 
 no_mutex_try.attachmalloc-2.transient.237 
 no_mutex_try.malloc-1.transient.19 
 no_mutex_try.malloc-10.transient.19 
 no_mutex_try.malloc-13.transient.21 
 no_mutex_try.malloc-14.transient.19 
 no_mutex_try.malloc-17.transient.21 
 no_mutex_try.malloc-20.transient.19 
 no_mutex_try.malloc-20.transient.57 
 no_mutex_try.malloc-26.transient.19 
 no_mutex_try.malloc6-1.transient.19 
 no_mutex_try.mallocD-4.transient.21 
 no_mutex_try.mallocG-1.transient.19 
 journaltest.altermalloc-1.transient.22 
 journaltest.attachmalloc-1.transient.22 
 journaltest.attachmalloc-1.transient.69 
 journaltest.attachmalloc-1.transient.407 
 journaltest.attachmalloc-1.transient.661 
 journaltest.attachmalloc-2.transient.272 
 journaltest.malloc-1.transient.19 
 journaltest.malloc-10.transient.19 
 journaltest.malloc-13.transient.21 
 journaltest.malloc-14.transient.19 
 journaltest.malloc-17.transient.21 
 journaltest.malloc-20.transient.19 
 journaltest.malloc-20.transient.57 
 journaltest.malloc-26.transient.19 
 journaltest.malloc6-1.transient.19 
 journaltest.mallocD-4.transient.21 
 journaltest.mallocG-1.transient.19 
 inmemory_journal.altermalloc-1.transient.19 
 inmemory_journal.attachmalloc-1.transient.19

[sqlite] FW: Invalid Code gemerated for Virtual Table Join with OR clause

2012-12-04 Thread Hick Gunter 



Von: Hick Gunter
Gesendet: Montag, 03. Dezember 2012 15:28
An: 'Richard Hipp'
Betreff: AW: [sqlite] Invalid Code gemerated for Virtual Table Join with OR 
clause

Thank you for your reply.

As indicated in the second message, I have located the code where the error 
manifests itself in where.c and in the meantime also found a workaround.

The sqlite3OpenTable function generates nothing for a virtual table, so no 
attempt to modify a P4 value should be made.

I think that only one VOpen per virtual table is required.


#ifndef SQLITE_OMIT_VIRTUALTABLE

if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){

  const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);

  int iCur = pTabItem->iCursor;

  sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);

}else

#endif

if( (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0

 && (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 ){

  int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;

  sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);

  testcase( pTab->nCol==BMS-1 );

  testcase( pTab->nCol==BMS );

  if( !pWInfo->okOnePass && pTab->nColcolUsed;

int n = 0;

for(; b; b=b>>1, n++){}

sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,

SQLITE_INT_TO_PTR(n), P4_INT32);

assert( n<=pTab->nCol );

  }

}else{

  sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

}



Von: Richard Hipp [mailto:d...@sqlite.org]
Gesendet: Montag, 03. Dezember 2012 14:35
An: General Discussion of SQLite Database
Cc: Hick Gunter
Betreff: Re: [sqlite] Invalid Code gemerated for Virtual Table Join with OR 
clause


On Mon, Dec 3, 2012 at 3:11 AM, Hick Gunter 
> wrote:
The following test case produces correct code for native tables, but 
substituting virtual tables seems to clobber the P4 Keyinfo to be propagated to 
OpenEphemeral at #59 by the spurious P4 to Goto at #8

Thank you for the bug report.

Nevertheless, please recognize that what you have provided us is not a "test 
case", for at least two reasons:

(1) You only provided code that works, not code that fails
(2) You have not given us any data or expected results

Hence, we have no easy way to validate your claim that SQLite is 
malfunctioning.  We will look into this, but because our investigation will 
involve a lot of reverse engineering trying to reconstruct the failure scenario 
from your hints, the investigation will take place at a lower priority than it 
would otherwise.

If you want, you can help us to expedite the investigation and resolution of 
your issue by providing us with a true "test case" - that is to say, providing 
us with some SQL that generates an incorrect result.  Thanks.



create temp table a (f1 integer, f2 integer);
create temp table b (f1 integer, f2 integer);
create unique index ai on a(f1,f2);
create unique index bi on b(f1,f2);
select
cast (f1 as integer) f1,
cast (f2 as integer) f2
from (
select f1, f2
from   a
where  (f1 =  7 and f2 = 11) or
   (f1 = 15 and f2 = 11)
union all
select f1, f2
from   b
where  (f1 =  7 and f2 = 11) or
   (f1 = 15 and f2 = 11)
)
order by f1, f2;

generated code (difference best seen in a side by side comparison):

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Goto   0 137   000  NULL
2 Noop   0 0 000  Begin coroutine for 
left SELECT
3 OpenEphemeral  3 4 0 keyinfo(2,BINARY,BINARY)  00  NULL
4 Integer7 7 000  NULL
5 Integer118 000  NULL
6 Integer159 000  NULL
7 Integer1110000  NULL
8 Goto   0 147   000  NULL

9 OpenRead   2 2 1 2  00  a
10Null   0 12000  NULL
11Integer3211000  NULL
12OpenRead   4 4 1 keyinfo(2,BINARY,BINARY)  00  ai
13SCopy  7 14000  NULL
14SCopy  8 15000  NULL
15SeekGe 4 22142  00  NULL
16IdxGE  4 22142  01  NULL
17IdxRowid   4 16000  NULL
18Seek   2 16000  NULL

19RowSetTest 1221160  00  NULL
20Gosub  1133000  NULL
21Next   4 16000  NULL

Re: [sqlite] Another example of windows users needing help. Fwd: sqlite.dll

2012-12-04 Thread Stephen Chrzanowski 
@Richard;

What kind of support are you supposed to be giving users?  sqlite3.dll is a
DLL, not an application, and without looking at the source code, I know for
certain the DLL itself isn't asking someone to download something else,
ASSUMING of course, its a precompiled version from sqlite.org and not some
rogue viral POS (Piece of Software).  Applications rely on this DLL for
database activities.  An application is installed via means outside SQLite,
so, if a program is screaming that it can't find that DLL, the application
would need a reinstall, or a darned good scrubbing with AV software, and
I'm kinda 'smelling' with this and previous emails, the latter seems to be
the case.

... and this kind of thing is a reminder that I am so thankful I don't have
to deal with end user application support for applications I didn't write
anymore


On Tue, Dec 4, 2012 at 5:44 AM, Richard Hipp  wrote:

> Here is another example of the kind of email I get on a regular basis.
>
> Note that I also get phone calls about this.  Sometimes at odd hours.
>
> -- Forwarded message --
> From: Åke Halvarson 
> Date: Tue, Dec 4, 2012 at 5:22 AM
> Subject: sqlite.dll
> To: d...@hwaci.com
>
>
> **
> Dear Sirs
>
> I am asked to download a file/program sqlite.dll to my computer to make it
> run better.
>
> How do I do that? I tried to do it from your download side but I got a
> file sqlite3.dll  is that the right one?
>
> Sincerely yours
>
> Åke Halvarson
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another example of windows users needing help. Fwd: sqlite.dll

2012-12-04 Thread e-mail mgbg25171 
It's not easy being an incon 

On 4 December 2012 10:44, Richard Hipp  wrote:

> Here is another example of the kind of email I get on a regular basis.
>
> Note that I also get phone calls about this.  Sometimes at odd hours.
>
> -- Forwarded message --
> From: Åke Halvarson 
> Date: Tue, Dec 4, 2012 at 5:22 AM
> Subject: sqlite.dll
> To: d...@hwaci.com
>
>
> **
> Dear Sirs
>
> I am asked to download a file/program sqlite.dll to my computer to make it
> run better.
>
> How do I do that? I tried to do it from your download side but I got a
> file sqlite3.dll  is that the right one?
>
> Sincerely yours
>
> Åke Halvarson
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result of multi-command _exec() that errors ?

2012-12-04 Thread Simon Slavin 

On 4 Dec 2012, at 11:13am, Simon Davies  wrote:

> 3rd paragraph:
> "If an error occurs while evaluating the SQL statements passed into
> sqlite3_exec(), then execution of the current statement stops and
> subsequent statements are skipped."

Dammit.  Missed it.  Thanks.

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

Re: [sqlite] Result of multi-command _exec() that errors ?

2012-12-04 Thread Simon Davies 
On 4 December 2012 11:02, Simon Slavin  wrote:
> (Yes, I know I should be preparing and binding for security reasons, but that 
> doesn't work here.)
>
> What is the result when using _exec() on multiple statements when one of the 
> statements errors ?  I read this page:
>
> 
>
> and I can't figure out whether execution continues to following statements 
> after one of them errors or not.  Assume, for the sake of argument, that I'm 
> not using any callbacks or hooks.

3rd paragraph:
"If an error occurs while evaluating the SQL statements passed into
sqlite3_exec(), then execution of the current statement stops and
subsequent statements are skipped."

>
> Simon.


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

[sqlite] Result of multi-command _exec() that errors ?

2012-12-04 Thread Simon Slavin 
(Yes, I know I should be preparing and binding for security reasons, but that 
doesn't work here.)

What is the result when using _exec() on multiple statements when one of the 
statements errors ?  I read this page:



and I can't figure out whether execution continues to following statements 
after one of them errors or not.  Assume, for the sake of argument, that I'm 
not using any callbacks or hooks.

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

Re: [sqlite] "SQLite not working on windows". Fwd: download

2012-12-04 Thread Simon Slavin 

On 4 Dec 2012, at 10:42am, Richard Hipp  wrote:

> I get a lot of inquiries like this from windows users.  They get some kind
> of dialog box about an SQLite3.dll not being found or being out-of-date.
> It might be good to create a webpage of some sort to try to help them, but
> I don't know what to put on that webpage.  Does anybody have any
> suggestions?

The easiest way to start diagnosing this problem is to know exactly what the 
dialog/message looks like.  Although all the messages are reported to you (and 
I'm sure to many others) in an annoyingly vague form, the precise look and 
phrasing of the message sometimes tells us that it's the one generated by 
64-bit iTunes, or some other specific app with a specific cure.  So a page 
which looks like this:

If your message looks like this:The cure is this:

Screenshot 1Culprit app 1.  Cure procedure 1.
Screenshot 2Culprit app 2.  Cure procedure 2.
Screenshot 3Culprit app 3.  Cure procedure 3.
... ...

might be helpful.  Of course, persuading them to send you screenshots is tough. 
 Perhaps readers of this list could help.

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

[sqlite] Another example of windows users needing help. Fwd: sqlite.dll

2012-12-04 Thread Richard Hipp 
Here is another example of the kind of email I get on a regular basis.

Note that I also get phone calls about this.  Sometimes at odd hours.

-- Forwarded message --
From: Åke Halvarson 
Date: Tue, Dec 4, 2012 at 5:22 AM
Subject: sqlite.dll
To: d...@hwaci.com


**
Dear Sirs

I am asked to download a file/program sqlite.dll to my computer to make it
run better.

How do I do that? I tried to do it from your download side but I got a
file sqlite3.dll  is that the right one?

Sincerely yours

Åke Halvarson



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

[sqlite] "SQLite not working on windows". Fwd: download

2012-12-04 Thread Richard Hipp 
I get a lot of inquiries like this from windows users.  They get some kind
of dialog box about an SQLite3.dll not being found or being out-of-date.
It might be good to create a webpage of some sort to try to help them, but
I don't know what to put on that webpage.  Does anybody have any
suggestions?

-- Forwarded message --
From: Lisa Jalmander 
Date: Tue, Dec 4, 2012 at 2:01 AM
Subject: download
To: d...@hwaci.com


**
Dear Sirs,

My SQLite does not work anymore and a get the message from Windows
to download a newer version of your program, but when I go to you webpage
I do not know what to download as there are so many links and versions!

Can you send a link so that I can download the latest version. We have
Microsoft Office on our computer.

Best regards,

Lisa Jalmander



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

Re: [sqlite] what diffrent with this two sql command?

2012-12-04 Thread Dave McKee 
... SELECT b FROM mytable WHERE b = c will give you each row of the
database which has identical b and c values.
... SELECT b FROM mytable AS m2 WHERE m2.b = m1.c will join the two views
of the database together, so if mytable contains

b,c
1,2
2,3

the combined table for this subquery looks like:
m1.b, m1.c, m2.b, m2.c
1,2,1,2
1,2,2,3
2,3,1,2
2,3,2,3

and it compares the middle two columns.

Hope this helps,
Dave.

(PS: I'm not particularly knowledgeable about SQLite, so this might well be
factually incorrect in important minor ways.)




On Tue, Dec 4, 2012 at 1:26 AM, YAN HONG YE  wrote:

>   SELECT c FROM mytable AS m1 WHERE NOT EXISTS (SELECT b FROM mytable AS
> m2 WHERE m2.b = m1.c);
>
>   SELECT c FROM mytable WHERE NOT EXISTS (SELECT b FROM mytable  WHERE b =
> c);
>
>
>
>
> Sincerely,
> Cordialement / Best Regards / Mit freundlichen Grüßen / Cordiali saluti !
> ___
> ---
> YE YANHONG/???- DASI/DRDS/ITVC/APFS/AVPM
> PSA PEUGEOT CITROEN(CHINA) AUTOMOTIVE TRADE CO,. LTD SHANGHAI BRANCH
> Tel: (+86) 21-2419 5488  Mobile:(+86) 13816808338  Fax: (+86) 21-2419 5004
> PSA internal call: 49 5488
> Email: yanhong...@mpsa.com
> Address: 6th Floor, Building 1, No. 1528, Gumei Road,
> Xuhui District, Shanghai
> Post Code:200233
> ??: ?1528?A1?6?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users