Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Joe Mistachkin]

Michael Russell wrote:
>
>  1) The break seems to have happened between 1.0.81.0 and 1.0.82.0.
>

There was a change in System.Data.SQLite disposal behavior in that
timeframe, namely using the new sqlite3_close_v2() API; however,
everything [now] conforms to the IDisposable interface semantics
and established best practices for native resource cleanup.

The root issue here is that all disposable managed objects (in this
case CriticalHandle derived classes) must be properly disposed by
whatever uses them.  Failing to do so with System.Data.SQLite used
to be a source of potential access violations, etc; now, it just
keeps the database connection handle active until everything has
been properly disposed.

>
>  2) Do Entity Framework team members read this list?
>

I don't know.

--
Joe Mistachkin

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Michael Russell]

Thanks Joe.  A couple of follow-up items:
  1) The break seems to have happened between 1.0.81.0 and 1.0.82.0.
  2) Do Entity Framework team members read this list?

--
Michael Russell
MTI Film, LLC
michael.russ...@mtifilm.com
http://www.mtifilm.com/
Providence, RI 02906 USA
+1 (401) 831-1315


On Thu, Jan 10, 2013 at 12:38 PM, Joe Mistachkin wrote:

>
> Michael Russell wrote:
> >
> >  It's hard for me to tell that, but what I did was to use your
> > "testlinq.exe" program as follows:
> >0) Did "build.bat ReleaseNativeOnly x64" to generate
> SQLite.Interop.dll
> >1) Load SQLite.NET.2010.sln into VS2010.
> >2) Set build to "Debug / Mixed Platforms"
> >3) Add Projects "System.Data.SQLite.2010" &
> > "System.Data.SQLite.Linq.2010" to "testlinq.2010" project.
> >4) Set "testlinq.2010" as StartUp Project
> >5) Add this code to "testlinq.2010's Program.cs" right at the top of
> > "OldTests()"
> >
>
> I am able to replicate your results here.
>
> The root cause in this case is that the Entity Framework indirectly creates
> a
> SQLiteCommand object and then subsequently fails to dispose it.
> Furthermore,
> it does not appear to expose these internally created commands, nor a way
> to
> explicitly dispose of them, leaving no means for an outside caller to
> cleanup.
> This seems quite strange since almost all IDbCommand implementations "in
> the
> wild" would likely require native resources of one kind or another.  Also,
> even the DbCommand base class provided by the .NET Framework itself
> implements
> IDisposable (i.e. the class used as the base class for SQLiteCommand).
>
> Perhaps somebody on the Entity Framework team could shed some extra light
> on
> this issue?
>
> --
> Joe Mistachkin
>
> ___
> 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

[sqlite] compiling for WP8

[E. Timothy Uy]

Hi, I'm working on compiling for Windows Phone. Are there any helpful
notes? In particular, I'm stalled at the missing 'rc' (resource compiler)
in the WP8 developer command prompt.

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

Re: [sqlite] special aggregate feature vs query optimization

[Igor Tandetnik]

On 1/10/2013 12:04 PM, Eduardo Morras wrote:

Select max(x), y from t will return 2 colums and n rows


Not true. The query uses an aggregate function, so it will return one 
row per group. There's no GROUP BY clause, so the whole table is one 
group. Ergo, this query will always return exactly one row. Try it, see 
for yourself.

--
Igor Tandetnik

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

Re: [sqlite] special aggregate feature vs query optimization

[Simon Slavin]

On 10 Jan 2013, at 5:39pm, Simon Davies  wrote:

> SQLite version 3.7.11 2012-03-20 11:35:50

Your test is good but the way this worked changed recently.  Can you download 
an up-to-date version of the shell tool from



and try on that version ?

Simon.

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

Re: [sqlite] special aggregate feature vs query optimization

[Simon Davies]

On 10 January 2013 17:04, Eduardo Morras  wrote:
> On Thu, 10 Jan 2013 14:53:52 +0100
> E.Pasma  wrote:
>
>> Hello,
>>
>> A query of the form: "SELECT max(x), y FROM table" returns the value
>> of y on the same row that contains the maximum x value.
>
> True
>
>> I just want to point to a construction where one would expect this to
>> work however it does not. I tried a query that returns only the value
>> of y and intuitively wrote:
>>
>> select y from (select max(x), y from t);
>>
>
> Select max(x), y from t will return 2 colums and n rows, the first column 
> with the same value, the maximum of x in table t, the second column all t.y 
> values. Something like this:
>
> max(x) |   y
> -
> 500|  5
> 500|  3
> 500|  9
> 500|  2
> 500|  31
> 500|  1
> 500|  86
> 500|  64

Not what I see...

>
> From this result table, you are doing select y from (result table) and 
> getting only the y values as you expected
>
>   y
> -
>   5
>   3
>   9
>   2
>   31
>   1
>   86
>   64
>
>> This however no longer returns the value of y corresponding to the
>> maximum x.
>
> For me it works, it shows all y from t. Perhaps i have misunderstood something

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t( x integer, y integer );
sqlite>
sqlite>
sqlite> insert into t values( 1, 2 );
sqlite> insert into t values( 4, 3 );
sqlite> insert into t values( 10, 5 );
sqlite> insert into t values( 2, 6 );
sqlite>
sqlite> select max(x),y from t;
10|5
sqlite> select x,y from (select max(x) x, y from t);
10|5
sqlite> select y from (select max(x) x, y from t);
6

This does not look right...

>
>>
>> It looks a consequence of query optimization. The query satisfies all
>> conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html
>>   . The max(x) column is then eliminated.
>>
>> Hope this is useful to know for who is using the feature.
>>
>> EPasma
>>
>
> ---   ---
> Eduardo Morras 

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Joe Mistachkin]

Michael Russell wrote:
>
>  It's hard for me to tell that, but what I did was to use your
> "testlinq.exe" program as follows:
>0) Did "build.bat ReleaseNativeOnly x64" to generate SQLite.Interop.dll
>1) Load SQLite.NET.2010.sln into VS2010.
>2) Set build to "Debug / Mixed Platforms"
>3) Add Projects "System.Data.SQLite.2010" &
> "System.Data.SQLite.Linq.2010" to "testlinq.2010" project.
>4) Set "testlinq.2010" as StartUp Project
>5) Add this code to "testlinq.2010's Program.cs" right at the top of
> "OldTests()"
>

I am able to replicate your results here.

The root cause in this case is that the Entity Framework indirectly creates
a
SQLiteCommand object and then subsequently fails to dispose it.
Furthermore,
it does not appear to expose these internally created commands, nor a way to
explicitly dispose of them, leaving no means for an outside caller to
cleanup.
This seems quite strange since almost all IDbCommand implementations "in the
wild" would likely require native resources of one kind or another.  Also,
even the DbCommand base class provided by the .NET Framework itself
implements
IDisposable (i.e. the class used as the base class for SQLiteCommand).

Perhaps somebody on the Entity Framework team could shed some extra light on
this issue?

--
Joe Mistachkin

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

Re: [sqlite] special aggregate feature vs query optimization

[Eduardo Morras]

On Thu, 10 Jan 2013 14:53:52 +0100
E.Pasma  wrote:

> Hello,
> 
> this mail is about the aggregate feature that was unveiled in the  
> release log of version 3.17.11, http://www.sqlite.org/releaselog/3_7_11.html 
>   :
> 
> A query of the form: "SELECT max(x), y FROM table" returns the value  
> of y on the same row that contains the maximum x value.

True

> I just want to point to a construction where one would expect this to  
> work however it does not. I tried a query that returns only the value  
> of y and intuitively wrote:
> 
> select y from (select max(x), y from t);
> 

Select max(x), y from t will return 2 colums and n rows, the first column with 
the same value, the maximum of x in table t, the second column all t.y values. 
Something like this:

max(x) |   y 
-
500|  5
500|  3
500|  9
500|  2
500|  31
500|  1
500|  86
500|  64


>From this result table, you are doing select y from (result table) and getting 
>only the y values as you expected

  y
-
  5
  3
  9
  2
  31
  1
  86
  64

> This however no longer returns the value of y corresponding to the  
> maximum x.

For me it works, it shows all y from t. Perhaps i have misunderstood something

> 
> It looks a consequence of query optimization. The query satisfies all  
> conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html 
>   . The max(x) column is then eliminated.
> 
> Hope this is useful to know for who is using the feature.
> 
> EPasma
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)

[Dan Kennedy]

On 01/10/2013 10:13 PM, Michael Schlenker wrote:

Am 10.01.2013 15:31, schrieb Dan Kennedy:

On 01/10/2013 07:11 PM, Michael Schlenker wrote:

Hi everyone,


[snip]

I'm pretty sure there was no DELETE for the missing object, but want to
verify what happend by comparing the WAL files.

Now i checked the wal with the tool/showwal.c program from the source
distribution and see differences in the frames 50-91 of 288 total
frames, so basically in the middle of the file.

Now my questions:

Is it a sign of file corruption that some frames in the middle of the
WAL file are differing between those two snapshots?


Possibly. But also possibly not.

Normally, new frames are appended to the WAL file. However, after a
checkpoint occurs (copying the contents of the WAL into the database
file), SQLite wraps around and starts writing at the start of the
WAL file again. So you can get into a situation where you have a large
WAL file on disk with only a small number of valid frames at the
start of it.

If a crash occurs and SQLite needs to read the WAL file (database
recovery), it can tell the difference between the valid frames at
the start of the WAL and the trailing garbage using the running
checksum embedded in each frame.

However, it looks like showwal.c assumes the entire WAL file consists
of valid frames (it does not verify the checksums). So, it is possible
that on day 1 your WAL file may have contained just 49 valid frames -
not the 288 indicated by showwal. And that between day 1 and day 2
42 extra frames were appended to the WAL.


Thanks Dan,

No, i checked this case.

The checksums would explain my observations, e.g. some commits
vanishing, as the end of the WAL is skipped/ignored due to checksum
mismatch.

The checksums are correct for the day 1 log, a SELECT finds pages that
are later in the WAL than the last difference (frame 92 in my case).
Some trivial grepping/python find() on the strings confirms they are at
an offset beyond the last difference. In the day 2 log the checksums
start to differ on frame 50 and are the same again on frame 92, which
leads to those frames>= 92 being ignored.

The file header info (salt and checksums) are identical for both files.
If i read the code correct, this means no walRestartLog() has happend,
as that seems to reset salt1 to a new value. In addition, i would not
expect to see 49 identical frames after a walRestartLog(), before new
ones are added.

So this points to pWal->hdr.mxFrame not being setup correctly on
entering sqlite3WalFrames(), which would cause an overwrite of
the already written frames without a walRestartLog().

Which points at walIndexTryHdr() reading the maximum sane index from
shared memory. And thats probably not working correctly on NFS/Network.


That seems a pretty reasonable analysis. You could confirm it by
adding an sqlite3_log() hook and then opening the day 2 backup.
If the sqlite3_log() hook spits out a message like "recovered 50
frames from wal", and you can confirm that the wal file header is
the same as the day 1 header, then the theory is quite likely correct.

Contrary to what I said earlier, the "PRAGMA locking_mode=exclusive"
trick might help you here - as that will cause SQLite to use heap
memory for the contents of the *-shm file.

Dan.



So this is a sure way to corrupt the database if the shm code gets an
outdated but not corrupted copy of the -shm file from the OS/filesystem
and additional writes are done.

Ok, so it seems i should implement some really strict asserts/checks so
customers do not even try to put those files up on NFS, and just have
some pretty error blow up in their face if they try on startup.

Thanks.

Michael



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

Re: [sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)

[Simon Slavin]

On 10 Jan 2013, at 3:13pm, Michael Schlenker  wrote:

> So this is a sure way to corrupt the database if the shm code gets an
> outdated but not corrupted copy of the -shm file from the OS/filesystem
> and additional writes are done.
> 
> Ok, so it seems i should implement some really strict asserts/checks so
> customers do not even try to put those files up on NFS, and just have
> some pretty error blow up in their face if they try on startup.

I've run into this problem when a customer moved a system which had been 
working perfectly from a local hard disk to a Windows share using SMB or CIFS 
or something like it.  They already knew they shouldn't do this because my 
company charged a lot more for a network-compatible version of the system than 
a single-user version.  But until they tried it they thought we were simply 
trying to get more money out of them and didn't expect their data to be 
corrupted through the complicated problems to do with networking.  So 
fortunately I was able to tell them that they'd violated our license agreement 
and were suffering the consequences.

However, after the incident I looked into ways of checking that the file was 
being accessed locally rather than on a network share and totally failed.  
There didn't seem to be any way of doing this under Windows which worked all 
the time.  I ended up putting a check in to see that the full path of the 
database file started with "C:", as being the only way I could get even close 
to a proper check, and it's possible to fool even that.

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

Re: [sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)

[Michael Schlenker]

Am 10.01.2013 15:31, schrieb Dan Kennedy:
> On 01/10/2013 07:11 PM, Michael Schlenker wrote:
>> Hi everyone,
>>
[snip]
>> I'm pretty sure there was no DELETE for the missing object, but want to
>> verify what happend by comparing the WAL files.
>>
>> Now i checked the wal with the tool/showwal.c program from the source
>> distribution and see differences in the frames 50-91 of 288 total
>> frames, so basically in the middle of the file.
>>
>> Now my questions:
>>
>> Is it a sign of file corruption that some frames in the middle of the
>> WAL file are differing between those two snapshots?
> 
> Possibly. But also possibly not.
> 
> Normally, new frames are appended to the WAL file. However, after a
> checkpoint occurs (copying the contents of the WAL into the database
> file), SQLite wraps around and starts writing at the start of the
> WAL file again. So you can get into a situation where you have a large
> WAL file on disk with only a small number of valid frames at the
> start of it.
> 
> If a crash occurs and SQLite needs to read the WAL file (database
> recovery), it can tell the difference between the valid frames at
> the start of the WAL and the trailing garbage using the running
> checksum embedded in each frame.
> 
> However, it looks like showwal.c assumes the entire WAL file consists
> of valid frames (it does not verify the checksums). So, it is possible
> that on day 1 your WAL file may have contained just 49 valid frames -
> not the 288 indicated by showwal. And that between day 1 and day 2
> 42 extra frames were appended to the WAL.

Thanks Dan,

No, i checked this case.

The checksums would explain my observations, e.g. some commits
vanishing, as the end of the WAL is skipped/ignored due to checksum
mismatch.

The checksums are correct for the day 1 log, a SELECT finds pages that
are later in the WAL than the last difference (frame 92 in my case).
Some trivial grepping/python find() on the strings confirms they are at
an offset beyond the last difference. In the day 2 log the checksums
start to differ on frame 50 and are the same again on frame 92, which
leads to those frames >= 92 being ignored.

The file header info (salt and checksums) are identical for both files.
If i read the code correct, this means no walRestartLog() has happend,
as that seems to reset salt1 to a new value. In addition, i would not
expect to see 49 identical frames after a walRestartLog(), before new
ones are added.

So this points to pWal->hdr.mxFrame not being setup correctly on
entering sqlite3WalFrames(), which would cause an overwrite of
the already written frames without a walRestartLog().

Which points at walIndexTryHdr() reading the maximum sane index from
shared memory. And thats probably not working correctly on NFS/Network.

So this is a sure way to corrupt the database if the shm code gets an
outdated but not corrupted copy of the -shm file from the OS/filesystem
and additional writes are done.

Ok, so it seems i should implement some really strict asserts/checks so
customers do not even try to put those files up on NFS, and just have
some pretty error blow up in their face if they try on startup.

Thanks.

Michael

-- 
Michael Schlenker
Software Architect

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)

[Dan Kennedy]

On 01/10/2013 08:44 PM, Michael Schlenker wrote:

Am 10.01.2013 14:23, schrieb Clemens Ladisch:

Michael Schlenker wrote:

I'm trying to track down some lost commits [...]
The sqlite db file and WAL/SHM files are on some automounted linux SAN
filesystem (probably NFS beneath it)


  says:
| WAL does not work over a network filesystem.

Also read.


I know it is a bad/questionable idea to run things like this. But as no
locking or concurrency is involved (single server process with exactly 1
connection to the DB), it should work (and does most of the time).


It's probably not the cause of your missing transaction problem, but
in this scenario it is a good idea to use
"PRAGMA locking_mode=exclusive". Execute the command before reading
any data from the database.

As well as preventing any other connections from accessing your db
(which you don't want to happen if you are using WAL mode on a remote
file-system), this causes SQLite to use heap memory for the wal-index
instead of creating the *-shm file and using mmap() to map it. Which
is probably better than memory-mapping a file located on a remote
file-system for a few reasons.

Dan.

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

Re: [sqlite] How to decrease IO usage

[Simon Slavin]

On 9 Jan 2013, at 10:59pm, Bebel  wrote:

> Now my problem is that my limitation for increase my latency is on the
> amount of IO ops perform by the databases. In fact, sqlite make a lot of
> random IO on my disks (many hundred) and this increase my disk latency.
> 
> I made a test on a empty database, on which I insert a value and then read
> it. This two sqlite operations made more than 40 IO operation.

Are you including in those 40 operations the opening and closing of the 
database files ?  Opening the database file is complicated and takes many 
operations.  By having many tiny databases instead of fewer big databases you 
are going to spend a lot of time just changing which database you have open.

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Michael Russell]

Hello Joe,
  It's hard for me to tell that, but what I did was to use your
"testlinq.exe" program as follows:
0) Did "build.bat ReleaseNativeOnly x64" to generate SQLite.Interop.dll
1) Load SQLite.NET.2010.sln into VS2010.
2) Set build to "Debug / Mixed Platforms"
3) Add Projects "System.Data.SQLite.2010" &
"System.Data.SQLite.Linq.2010" to "testlinq.2010" project.
4) Set "testlinq.2010" as StartUp Project
5) Add this code to "testlinq.2010's Program.cs" right at the top of
"OldTests()"

var tmpDb = "northwindEF-tmp.db";
File.Copy("northwindEF.db", tmpDb, true);

var connectionString =
string.Format(@"metadata=res://*/NorthwindModel2010.csdl|res://*/NorthwindModel2010.ssdl|res://*/NorthwindModel2010.msl;provider=System.Data.SQLite;provider
connection string='data source=.\{0}'", tmpDb);

using (northwindEFEntities db = new
northwindEFEntities(connectionString))
{
string scity = "London";
Customers c = db.Customers.FirstOrDefault(cd =>
cd.City == scity);
Console.WriteLine(c.CompanyName);
}

File.Delete(tmpDb);

6) Set "testlinq.2010" as StartUp Project
7) Build "testlinq.2010"
8) Copy SQLite.Interop.DLL to bin\2010\Debug\bin
9) Run "testlinq.2010" - get Exception at "File.Delete()" line.

I hope this helps.


--
Michael Russell
MTI Film, LLC
michael.russ...@mtifilm.com
http://www.mtifilm.com/
Providence, RI 02906 USA
+1 (401) 831-1315


On Wed, Jan 9, 2013 at 8:13 PM, Joe Mistachkin wrote:

>
> Michael Russell wrote:
> >
> > I did do a check-out after you made the above suggestion to me.  I did
> the
> > fossil checkout / build, but I still have the same problem.
> >
>
> Is there any way you can determine which System.Data.SQLite objects are
> being
> held open by the Entity Framework?
>
> --
> Joe Mistachkin
>
> ___
> 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] showwal.c to find WAL file differences? (trying to analyze some lost commits)

[Dan Kennedy]

On 01/10/2013 07:11 PM, Michael Schlenker wrote:

Hi everyone,

I'm trying to track down some lost commits for SQLite 3.7.7.1 via the
APSW Python wrapper. Pagesize etc. are all left at defaults for the APSW
wrapper.

The following probablematic scenario:

The sqlite db file and WAL/SHM files are on some automounted linux SAN
filesystem (probably NFS beneath it), pretty old SLES 9 system with
kernel 2.6.5-7.244-smp and pretty normal mount options for the filesystem.

I now have SAN snapshots of two sets of sqlite.db and sqlite.db-wal/shm
files from two consecutive days. The only change is in the WAL file, the
sqlite.db file is totally unchanged (checked via md5sum and fs timestamps).

In the snapshot form day 1 i see a commit, when executing a query.
In the snapshot from day 2 the same entry is gone.

I'm pretty sure there was no DELETE for the missing object, but want to
verify what happend by comparing the WAL files.

Now i checked the wal with the tool/showwal.c program from the source
distribution and see differences in the frames 50-91 of 288 total
frames, so basically in the middle of the file.

Now my questions:

Is it a sign of file corruption that some frames in the middle of the
WAL file are differing between those two snapshots?


Possibly. But also possibly not.

Normally, new frames are appended to the WAL file. However, after a
checkpoint occurs (copying the contents of the WAL into the database
file), SQLite wraps around and starts writing at the start of the
WAL file again. So you can get into a situation where you have a large
WAL file on disk with only a small number of valid frames at the
start of it.

If a crash occurs and SQLite needs to read the WAL file (database
recovery), it can tell the difference between the valid frames at
the start of the WAL and the trailing garbage using the running
checksum embedded in each frame.

However, it looks like showwal.c assumes the entire WAL file consists
of valid frames (it does not verify the checksums). So, it is possible
that on day 1 your WAL file may have contained just 49 valid frames -
not the 288 indicated by showwal. And that between day 1 and day 2
42 extra frames were appended to the WAL.

Dan.

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

[sqlite] special aggregate feature vs query optimization

[E . Pasma]

Hello,

this mail is about the aggregate feature that was unveiled in the  
release log of version 3.17.11, http://www.sqlite.org/releaselog/3_7_11.html 
 :


A query of the form: "SELECT max(x), y FROM table" returns the value  
of y on the same row that contains the maximum x value.


I just want to point to a construction where one would expect this to  
work however it does not. I tried a query that returns only the value  
of y and intuitively wrote:


select y from (select max(x), y from t);

This however no longer returns the value of y corresponding to the  
maximum x.


It looks a consequence of query optimization. The query satisfies all  
conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html 
 . The max(x) column is then eliminated.


Hope this is useful to know for who is using the feature.

EPasma

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

Re: [sqlite] How to decrease IO usage

[Igor Tandetnik]

On 1/9/2013 5:59 PM, Bebel wrote:

I'm working with a lot of sqlite base (many million), but they are very
small, 2000 entries for the bigger. In this base, I create 6 tables, but I
mostly work on just one of them.

My structure are very simple, I stored varchar and blob.


Looks like you are using SQLite as a key-value store. Perhaps you might 
be happier with a real key-value store, like Berkeley DB or LevelDB, 
which would be optimized specifically for such use case.


http://en.wikipedia.org/wiki/NoSQL#Key.E2.80.93value_stores_on_solid_state_or_rotating_disk

--
Igor Tandetnik

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

Re: [sqlite] How to decrease IO usage

[Michael Black]

Increase your cache size?  Default is 2000*page_size
http://www.sqlite.org/pragma.html#pragma_cache_size



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bebel
Sent: Wednesday, January 09, 2013 5:00 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to decrease IO usage

Hi there,

Let me explain you my problem.

I'm working with a lot of sqlite base (many million), but they are very
small, 2000 entries for the bigger. In this base, I create 6 tables, but I
mostly work on just one of them.

My structure are very simple, I stored varchar and blob.

My amount of insert is not important regarding to my amount of select
action.

Now my problem is that my limitation for increase my latency is on the
amount of IO ops perform by the databases. In fact, sqlite make a lot of
random IO on my disks (many hundred) and this increase my disk latency.

I made a test on a empty database, on which I insert a value and then read
it. This two sqlite operations made more than 40 IO operation.

Then, do you know a way to optimize my usage ?

NB : Data integrity is very important on my environment.

Thanks for help, and happy new year !



--
View this message in context:
http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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

[sqlite] How to decrease IO usage

[Bebel]

Hi there,

Let me explain you my problem.

I'm working with a lot of sqlite base (many million), but they are very
small, 2000 entries for the bigger. In this base, I create 6 tables, but I
mostly work on just one of them.

My structure are very simple, I stored varchar and blob.

My amount of insert is not important regarding to my amount of select
action.

Now my problem is that my limitation for increase my latency is on the
amount of IO ops perform by the databases. In fact, sqlite make a lot of
random IO on my disks (many hundred) and this increase my disk latency.

I made a test on a empty database, on which I insert a value and then read
it. This two sqlite operations made more than 40 IO operation.

Then, do you know a way to optimize my usage ?

NB : Data integrity is very important on my environment.

Thanks for help, and happy new year !



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)

[Michael Schlenker]

Hi everyone,

I'm trying to track down some lost commits for SQLite 3.7.7.1 via the
APSW Python wrapper. Pagesize etc. are all left at defaults for the APSW
wrapper.

The following probablematic scenario:

The sqlite db file and WAL/SHM files are on some automounted linux SAN
filesystem (probably NFS beneath it), pretty old SLES 9 system with
kernel 2.6.5-7.244-smp and pretty normal mount options for the filesystem.

I now have SAN snapshots of two sets of sqlite.db and sqlite.db-wal/shm
files from two consecutive days. The only change is in the WAL file, the
sqlite.db file is totally unchanged (checked via md5sum and fs timestamps).

In the snapshot form day 1 i see a commit, when executing a query.
In the snapshot from day 2 the same entry is gone.

I'm pretty sure there was no DELETE for the missing object, but want to
verify what happend by comparing the WAL files.

Now i checked the wal with the tool/showwal.c program from the source
distribution and see differences in the frames 50-91 of 288 total
frames, so basically in the middle of the file.

Now my questions:

Is it a sign of file corruption that some frames in the middle of the
WAL file are differing between those two snapshots?

>From the description how the WAL file works, i would assume that only
new frames are appended, but old frames are never modified.
My current assumption is, that some WAL journal writes were lost or
messed up by the filesystem/SAN mount somehow, but i need to verify my
assumptions about how WAL works first.

Michael

-- 
Michael Schlenker
Software Architect

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users