Re: [sqlite] SQLite DB backups on Windows

[Arun Bhalla]

I forgot to mention that we're using SQLite 3.5.9.

Arun

Arun Bhalla wrote:
> Hi there,
> 
> We are performing backups of the SQLite DB file by opening an IMMEDIATE 
> transaction and then copying the file.  (cf. 
> <http://www.mail-archive.com/sqlite-users@sqlite.org/msg19265.html>, 
> <http://www.mail-archive.com/sqlite-users@sqlite.org/msg19311.html>)
> 
> On Windows we use CopyFileA (within the same process that opened the DB 
> transaction) to perform the copy.  We've noticed that copies fail on 
> Windows for databases larger than 1 GB in size.  We suspect this is due 
> to SQLite locking bytes around the 1GB offset whenever a transaction is 
> opened on Windows.  This may be due to the fact that the locking file 
> handle gets exclusive access; other file handles opened by the same 
> process also cannot access the same locked region. (cf. 
> <http://msdn.microsoft.com/en-us/library/aa365202(VS.85).aspx>)
> 
> What we're thinking of doing is pushing the PENDING_BYTE from the first 
> byte past the 1GB boundary to somewhere deep in the 64-bit range (such 
> as perhaps the 1TB boundary).  We would have to update many lock and 
> unlock calls in os_win.c to do so, mainly adding a high-order 32-bit 
> number to the lock offset.  Is anyone aware of any issues with doing so, 
> either with SQLite or Windows?  Would we have to change anything else in 
> SQLite other than in os_win.c?  We don't use anything older than Windows 
> 2000, so older systems shouldn't be a concern for us.
> 
> Thanks,
> Arun
> ___
> 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] SQLite DB backups on Windows

[Arun Bhalla]

Hi there,

We are performing backups of the SQLite DB file by opening an IMMEDIATE 
transaction and then copying the file.  (cf. 
, 
)

On Windows we use CopyFileA (within the same process that opened the DB 
transaction) to perform the copy.  We've noticed that copies fail on 
Windows for databases larger than 1 GB in size.  We suspect this is due 
to SQLite locking bytes around the 1GB offset whenever a transaction is 
opened on Windows.  This may be due to the fact that the locking file 
handle gets exclusive access; other file handles opened by the same 
process also cannot access the same locked region. (cf. 
)

What we're thinking of doing is pushing the PENDING_BYTE from the first 
byte past the 1GB boundary to somewhere deep in the 64-bit range (such 
as perhaps the 1TB boundary).  We would have to update many lock and 
unlock calls in os_win.c to do so, mainly adding a high-order 32-bit 
number to the lock offset.  Is anyone aware of any issues with doing so, 
either with SQLite or Windows?  Would we have to change anything else in 
SQLite other than in os_win.c?  We don't use anything older than Windows 
2000, so older systems shouldn't be a concern for us.

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

[sqlite] temp_store_directory issue with SQLite 3.5.9

[Arun Bhalla]

Hello,

We came across an issue last week with SQLite 3.5.9 on Windows.  A 
program stopped working on Windows when we upgraded from SQLite 3.4.1 to 
SQLite 3.5.9.  It turned out that the cause was that SQLite 3.5.9 (and 
perhaps SQLite 3.5.x in general) was not fully honoring the 
temp_store_directory pragma.  That is, the pathname for the pager's 
statement journal (Pager::zStmtJrnl) is computed when the pager is 
opened but not necessarily recomputed when the temp_store_directory is 
changed, so the pager may try to use a different directory than 
intended.  This was aggravated by some bug (apparently in MinGW) where 
GetTempPath() is only cycling through the TMP, TEMP, and USERPROFILE 
environment variables to find a viable temporary folder when running as 
Administrator (or perhaps a user in the Administrators group).

My fix was to compute Pager::zStmtJrnl in pagerStmtBegin(), right before 
opening the statement journal.  It appears that some rearchitecture in 
SQLite 3.6.0 (http://www.sqlite.org/cvstrac/chngview?cn=5190) removes 
this bug, so I won't submit a patch, but I'll be happy to provide one 
should anyone request it.

By the way, it seems a little bit questionable that 
sqlite3_temp_directory is a global, and hence the temp_store_directory 
pragma is also global, affecting all databases opened within the same 
process.

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

Re: [sqlite] DB corruption, unit tests

[Arun Bhalla]

Arun Bhalla wrote:
2) In order to help diagnose the problem, we ran some unit tests, and we 
had some unusual results:


  c) On a Linux/x86 VM (running under Windows), some tests fail.  I 
don't have the specifics at the moment, but that would be cause for 
alarm, particularly the failures are above (a) and (b), assuming those 
are not problematic.


Here are the test failures.  It looks like to me that the NFS 
implementation is the problem and not SQLite, but if someone can confirm 
one way or the other or provide hints, that would be great.


From SQLite 3.4.1:

avtrans-9.2.1-1024...
Expected: [1024 67941cd790716d4a63548cd51aa51707]
 Got: [404 624f6573af0850effc03dad438a7bb22]
avtrans-9.2.2-1024...
Expected: [1024 67941cd790716d4a63548cd51aa51707]
 Got: [400 32e7ee7933bb9daedb9beebd4ee3b6e9]
avtrans-9.2.3-1024... Ok
avtrans-9.2.4-1024... Ok
avtrans-9.2.5-1024... Ok
avtrans-9.3.1-434...
Expected: [434 61b0d88626a9cfb71469d42bbce87c2d]
 Got: [415 5453c0f719486ccee158ec7690caa467]
avtrans-9.3.2-434...
Expected: [434 61b0d88626a9cfb71469d42bbce87c2d]
 Got: [418 9727721910a7c205200e90b7c1407f15]
avtrans-9.3.3-434... Ok
avtrans-9.3.4-434... Ok
avtrans-9.3.5-434... Ok
avtrans-9.4.1-449...
Error: database disk image is malformed
avtrans-9.4.2-449...
Error: cannot start a transaction within a transaction
avtrans-9.4.3-449...
Error: database disk image is malformed
avtrans-9.4.4-449... Ok
avtrans-9.4.5-449... Ok
/nfs/t/sqlite-3.4.1/.libs/lt-testfixture: database disk image
is malformed
while executing
"db eval {SELECT count(*), md5sum(x) FROM t3}"
(procedure "signature" line 2)
invoked from within
"signature"
("for" body line 2)
invoked from within
"for {set i 2} {$i<=$limit} {incr i} {
  set ::sig [signature]
  set cnt [lindex $::sig 0]
  if {$i%2==0} {
execsql {PRAGMA fullfsync=ON}
  } else ..."
(file "./test/avtrans.test" line 863)
invoked from within
"source $testfile"
("foreach" body line 5)
invoked from within
"foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
set tail [file tail $testfile]
if {[lsearch -exact $EXCLUDE $tail]>=0} continue
..."
("for" body line 7)
invoked from within
"for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
  if {$Counter%2} {
set ::SETUP_SQL {PRAGMA default_synchronous=off;}
  } else ..."
(file "./test/all.test" line 85)
make: *** [fulltest] Error 1

From SQLite 3.5.2:

avtrans-9.1...
Error: database disk image is malformed
/nfs/t/src/sqlite-3.5.2/.libs/lt-testfixture: database disk
image is malformed
while executing
"db eval {SELECT count(*), md5sum(x) FROM t3}"
(procedure "signature" line 2)
invoked from within
"signature"
("for" body line 2)
invoked from within
"for {set i 2} {$i<=$limit} {incr i} {
  set ::sig [signature]
  set cnt [lindex $::sig 0]
  if {$i%2==0} {
execsql {PRAGMA fullfsync=ON}
  } else ..."
(file "./test/avtrans.test" line 865)
invoked from within
"source $testfile"
("foreach" body line 5)
invoked from within
"foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
set tail [file tail $testfile]
if {[lsearch -exact $EXCLUDE $tail]>=0} continue
..."
("for" body line 7)
invoked from within
"for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
  if {$Counter%2} {
set ::SETUP_SQL {PRAGMA default_synchronous=off;}
  } else ..."
(file "./test/all.test" line 83)
make: *** [fulltest] Error 1



Thanks,
Arun

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] DB corruption, unit tests

[Arun Bhalla]

Hello there,

I have a couple related questions:

1) Is anyone aware of any recent cases of SQLite DB corruption?  We 
upgraded from SQLite 3.3.6 to SQLite 3.4.1 a few months ago.  In the 
last couple days, we've encountered a couple cases of SQLite reporting 
that a DB file had become corrupted ("database disk image is 
malformed").  I believe that both cases involved writing to NFS. 
However, we use our own locking mechanism to handle some faulty NFS 
implementations regarding locking and fcntl().


In one case, only a single (unthreaded) process was accessing the 
database over NFS.  However, that process was running in a Linux VMware 
virtual machine.  Less is known about the other case, but it was also 
running in Linux (not running in a VM) while writing to NFS.  Both cases 
involve NetApp boxes (but not the same one).


Aside from NFS locking issues, I'm not aware of any issues involving 
SQLite and NFS.  Since one case didn't involve concurrency, perhaps 
there's another problem.


2) In order to help diagnose the problem, we ran some unit tests, and we 
had some unusual results:


  a) On a native Linux/x86 system, the lock4-1.3 test for SQLite 3.4.1 
will fail intermittently, both on NFS and local disk.  Is the test not 
very robust?  Perhaps there's a race condition?


  b) On a native Linux/AMD64 system, the io-4.2.2 test for SQLite 3.5.2 
will fail regularly on both local disk and NFS.  Is this cause for concern?


  c) On a Linux/x86 VM (running under Windows), some tests fail.  I 
don't have the specifics at the moment, but that would be cause for 
alarm, particularly the failures are above (a) and (b), assuming those 
are not problematic.


Thanks,
Arun

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] NaN

[Arun Bhalla]

Last night I upgraded from SQLite 3.3.6 to SQLite 3.4.0.  This morning a 
transaction in my application failed because SQLite detected that the 
application was trying to insert a row with a NULL in a REAL NOT NULL 
column.  Actually, it turns out that it was a NaN, which SQLite 3.4.0 
apparently converts to NULL.


I don't necessarily mind this change, although I would consider NaN and 
NULL to be different (although they have similar semantics).  In fact, 
I'm rather glad that SQLite failed in this case, otherwise it surely 
would have been much longer until I noticed a problem in my application. 
 However, I have existing databases that have NaN values in some 
fields.  I noticed that when I dump tables containing NaN using the 
SQLite 3.4.0 shell, the value is now represented as (NaN) rather than 
the previous (0.0) which was clearly incorrect.  However, the problem 
now is that a table can't be dumped and restored using the SQLite shell 
because (NaN) is neither a column nor a special value (such as NULL). 
For example:


sqlite> .dump foo
BEGIN TRANSACTION;
CREATE TABLE foo (bar REAL);
INSERT INTO "foo" VALUES(NaN);
COMMIT;

sqlite> INSERT INTO "foo" VALUES(NaN);
SQL error: no such column: NaN

This is not necessarily a problem for me, but it's something I noticed 
through some experimentation that may have been an unexpected 
consequence of the NaN fix.  I've also noticed that I can perform an 
INSERT-SELECT statement using rows that contain NaN, and the NOT NULL 
constraint is not enforced then, although it is with UPDATE operations 
when the result would be NaN.


cheers,
Arun

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] disk I/O error

[Arun Bhalla]

Hello,

This message may be off-topic in that I don't think the following issue
 corresponds to a bug with SQLite, but it's something I've discovered
while using SQLite.  Perhaps someone on the list has had a similar
experience or could make a suggestion.

A Linux 2.6/x86_64 system reports a "disk I/O error" (SQLITE_IOERR)
while generating a specific report from a SQLite database (SQLite
3.3.6).  The database and temporary files are accessed through an NFS
mount.  After running the program again with SQLite tracing enabled
(plus a bit more I added), I see that SQLITE_IOERR is returned by
unixRead() because read() unexpectedly returned 0!

Here's some relevant strace output:

open("/nfs/tmp/sqlite_dBjTG5bZdsqFVPb", O_RDWR|O_CREAT|O_EXCL, 0644) = 8

[...]

lseek(8, 193536, SEEK_SET)= 193536
write(8, "\n\0\0\0\30\0\222\0\0\266\0\332\0\376\1\"\1F\1l\1\222\1"...,
1024) = 1024

[...]

lseek(8, 226304, SEEK_SET)= 226304
write(8, "\n\0\0\0\30\0\240\0\0\240\0\304\0\350\1\f\0010\1T\1x\1"...,
1024) = 1024
lseek(8, 193536, SEEK_SET)= 193536
read(8, "", 1024) = 0
fstat(8, {st_mode=S_IFREG|0644, st_size=227328, ...}) = 0

The read() call shouldn't fail -- the same page was written to at the
beginning of the transaction!  At least by the time fstat() is called,
the file is 227328 bytes long, so a read at an offset of 193536 should
not fail.

I'm suspecting that the NFS server in question is buggy or
misconfigured.  Unfortunately I don't have access to either the NFS
server or the host running the program, so mainly all I can access is
some strace and SQLite tracing output.

Thanks for any suggestions,
Arun


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?

[Arun Bhalla]

Dennis Cote wrote:

Erik Jensen wrote:




very interesting. Do you have the chance to run the same test with
version 3.2.1? Like i wrote in an earlier message, i noticed a similar
performance drop when i upgraded my application from sqlite 3.2.1 to
3.3.5.



Eric,

I reran these test using 3.2.1 dll downloaded from the sqlite website. 
It is very similar to the results from 3.3.5. It is slightly slower for 
all except the memory insert without transaction case. So it doesn't 
look like the big change happened after 3.2.1.




On a whim, I tested 3.0.8 and 3.3.4:


SQLite  DB  TX  records inserts/sec
===
3.3.5   :memory:no  1M   2778
3.3.5   :memory:yes 1M  22727
3.3.5   fileno  1K 10
3.3.5   fileyes 1M  24390
3.2.1   :memory:no  1M   2857
3.2.1   :memory:yes 1M  21739
3.2.1   fileno  1K  9
3.2.1   fileyes 1M  22727
2.8.17  :memory:no  1M  62500
2.8.17  :memory:yes 1M  58824
2.8.17  fileno  1K 13
2.8.17  fileyes 1M  23256


  3.3.4   :memory:yes 1M  25866
  3.3.4   fileyes 1M  28587

  3.0.8   :memory:yes 1M  20573
  3.0.8   fileyes 1M  22903


It seems there were some significant performance improvements in between 
the two versions!


Arun

Re: [sqlite] a string indexing experiment

[Arun Bhalla]

I forgot to mention -- I was using SQLite 3.3.4.

Arun Bhalla wrote:

Hi,

I performed a quick benchmark of three different string indexing schemes 
for SQLite3.


 * Scheme 0 = indexing on the string field
 * Scheme 1 = indexing on the MD5 sum (as text in hexadecimal 
representation) of the string
 * Scheme 2 = indexing on the high 64 bits of the MD5 sum (as int) of 
the string


I varied string size and number of strings and evaluated the schemes on 
database size and a couple insertion and retrieval tests each.  In 
general, scheme 2 was quite effective for most cases.  Scheme 0 was the 
best all-around for short strings (16 bytes or less), but in most cases, 
scheme 2 was not far behind.  When working with larger strings, scheme 2 
would dominate, with scheme 1 generally having similar performance. 
SQLite's indexing mechanism (scheme 0) did not scale well in size or 
performance for large strings.


Strangely, scheme 1 always outperformed scheme 2 in the pure retrieval 
test, but even when scheme 1 was 50-200% faster than scheme 2, scheme 0 
was an order of magnitude slower.  Scheme 2 was faster for unique 
insertion, though, so either scheme 1 or scheme 2 could be useful 
depending upon the usage model.


Is there a good reason why retrieval with scheme 1 would be faster than 
retrieval with scheme 2?  Scheme 1 involves an index on 32 bytes while 
scheme 2 involves an index on 8 bytes.  I would think that scheme 2 
would always be faster than scheme 1 because fewer bytes are involved. 
Does SQLite index INT fields differently than TEXT fields?


Some background information, if necessary: the benchmark program was 
written in C/C++ and run on a P4 (x86) Linux box.  Inserts were 
performed in transactions of 1000 each; pure retrievals were not grouped 
together in transactions.  String sizes ranged from 16-1048576 bytes; 
number of rows in a table ranged from 10K to 10M.  Synchronous writes 
were disabled.


Thanks,
Arun

[sqlite] a string indexing experiment

[Arun Bhalla]

Hi,

I performed a quick benchmark of three different string indexing schemes 
for SQLite3.


 * Scheme 0 = indexing on the string field
 * Scheme 1 = indexing on the MD5 sum (as text in hexadecimal 
representation) of the string
 * Scheme 2 = indexing on the high 64 bits of the MD5 sum (as int) of 
the string


I varied string size and number of strings and evaluated the schemes on 
database size and a couple insertion and retrieval tests each.  In 
general, scheme 2 was quite effective for most cases.  Scheme 0 was the 
best all-around for short strings (16 bytes or less), but in most cases, 
scheme 2 was not far behind.  When working with larger strings, scheme 2 
would dominate, with scheme 1 generally having similar performance. 
SQLite's indexing mechanism (scheme 0) did not scale well in size or 
performance for large strings.


Strangely, scheme 1 always outperformed scheme 2 in the pure retrieval 
test, but even when scheme 1 was 50-200% faster than scheme 2, scheme 0 
was an order of magnitude slower.  Scheme 2 was faster for unique 
insertion, though, so either scheme 1 or scheme 2 could be useful 
depending upon the usage model.


Is there a good reason why retrieval with scheme 1 would be faster than 
retrieval with scheme 2?  Scheme 1 involves an index on 32 bytes while 
scheme 2 involves an index on 8 bytes.  I would think that scheme 2 
would always be faster than scheme 1 because fewer bytes are involved. 
Does SQLite index INT fields differently than TEXT fields?


Some background information, if necessary: the benchmark program was 
written in C/C++ and run on a P4 (x86) Linux box.  Inserts were 
performed in transactions of 1000 each; pure retrievals were not grouped 
together in transactions.  String sizes ranged from 16-1048576 bytes; 
number of rows in a table ranged from 10K to 10M.  Synchronous writes 
were disabled.


Thanks,
Arun