Re: [sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

Hi again peeps,

thanks for all your help.

Seems there are many variables that could restrict doing this reliably.
As several of you have mentioned, I should really rethink my design 
before this simple idea becomes far more complex than it needs to be.



Cheers,

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Jean-Christophe Deschamps]

But if data was added exactly in the same way/order shouldn't the 
counters all count to the same end result if the process was repeated 
at a later time on a another machine?


Maybe, maybe not. Since the file format specifies meaningful fields 
only (my guess) it's quite possible that the slack space (meaningless 
sequences of bytes spread around the DB file) could hold random garbage 
from memory.  I doubt the team would certified that slack space will 
always contain the exact same data in all configurations, platform, etc.


So unless such formal guarantee comes from the dev team, I wouldn't bet 
my life on that, even if the exact same platform is being used for DBs 
X & Y. 


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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Nico Williams]

On Tue, Apr 3, 2012 at 8:27 PM, Webdude  wrote:
> But if data was added exactly in the same way/order shouldn't the counters
> all count to the same end result if the process was repeated at a later time
> on a another machine?

Well, why not... try it?  :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

Hi Jay,

thanks for your help,



/  >  Does anyone know if SQLite stores additional unique internal

/>/  >  information such as timestamps etc. that would affect this, and
/>/  >  if so could these "additional to the data" variable features be
/>/  >  disabled in any way?
//
/>/  SQLite files do contain some metadata like row numbers and internal page
/>/  hashes, but nothing like timestamps unless the programmer generated them
/>/  in software.
/

 While there are no timestamps, SQLite does keep a number of counters
 and other meta-data in the file header page that are not directly
 under programmer control.  This includes a "change counter" that gets
 incremeted with every write.

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

 

But if data was added exactly in the same way/order shouldn't the counters all 
count to the same end result if the process was repeated at a later time on a 
another machine?


Thanks again for your time,

David


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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Simon Slavin]

On 4 Apr 2012, at 2:15am, Webdude  wrote:

> But the same SQLite version, using the same schema, setup with the same 
> PRAGMA's, creating a db with the same data and in the same order, and despite 
> hardware / HDD / OS, should still produce the same file byte-for byte ?

And you must have done the same operations in the same order to create the data 
file.  No adding some records then deleting them again on one copy but not the 
other copy.

With all those conditions to satisfy, I suspect you're better off just writing 
a text file.

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

Hi Jean-Christophe

thanks for your help.



> Instead of trying to compare the hashes of DB files themselves, you
> appear to want a strict comparison of sets in the contents of the DBs.

No, I physically need the end resulting file to hash to the same value.
The file becomes a new identity in itself which is then treated as just 
another file
and can be included in someone else's db as a file, verifiable by it's 
hash value like any other file.




> For instance, changing the schema of the DBs in different ways will
> make schema_version differ, just as many internal values (none of which
> preclude the actual (user-visible and meaningful) content to be exactly
> the same.

But the same SQLite version, using the same schema, setup with the same 
PRAGMA's, creating a db with the same data and in the same order, and 
despite hardware / HDD / OS, should still produce the same file byte-for 
byte ?




> What would be a more robust way to compare DBs X and Y is to perform a
> (select * from X.T except select * from Y.T) union (select * from Y.T
> except select * from X.T)
> for each and every user table T in the resultset of
> select name from sqlite_master where type like 'table'
>
> (Note that I don't know off-hand what the exact distinction is between
> column name and tbl_name in this master table).
>
> You get rid of row order, order of schema creation, internal encoding,
> history of row life (insert, update, delete) and many details that will
> stop you from comparing row DB files.
>
> That may require some adjustment to work in practice, but the main idea
> is there: comparing sets and SQL is the right tool to do that.
>
> As a sidenote, understand that the query above will silently ignore
> duplicates, but it's my understanding that it shouldn't be a problem in
> your context.

Your SQL comparison technique is pretty cool though, will have to keep 
this piece of code.



Thanks again for your thoughts and ideas,

David


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

Re: [sqlite] Downgrade support for OS/2?

[Don V Nielsen]

Well.  I know some banks and some transportation systems still use OS/2.
 Do they use sqlite?  If yes, I'm sure their apps are stable, meaning their
sqlite implementation is stable.  Does sqlite need to continue for OS/2?
 Most likely not.

Death of a friend.  I always liked OS/2.  It's still better than Windows
with regard to file properties.

dvn

On Tue, Apr 3, 2012 at 12:44 PM, Michael Steiger wrote:

> On 03.04.2012 19:38 Richard Hipp said the following:
>
>  We propose to remove the VFS module for OS/2 from the SQLite amalgamation
>> in the next release, reducing the size of the amalgamation source file by
>> 1924 lines (or about 1.4%).
>>
>> If this change will cause you any serious hardship, please speak up and we
>> will reconsider.
>>
>>
> And this one day after the 25th anniversary of the announcement of OS/2.
> RIP OS/2 and RIP OS/2 support in sqlite.
>
>
> __**_
> 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] Problem compiling Tcl bindings on Win

[Gerry Snyder]

I thought it might be nice to be able to keep my Tcl bindings for SQLite 
up to date, so I downloaded the autoconf tarball on both my linux and 
Win (Vista) machines. Not surprisingly, the compile went fine on linux.


On Windows I also downloaded and installed MSYS / MINGW.

I got into the tea directory and ran configure, which ran to completion 
with only one warning " WARNING:  'Makefile.in' seems to ignore the 
--datarootdir setting"


In running make, there were a few warnings and then:

gcc -shared -o sqlite3711.dll tclsqlite3.o  "/c/Tcl/lib/tclstub85.lib"
tclsqlite3.o:tclsqlite3.c:(.text+0x4883): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48a0): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48b9): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48d2): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48e0): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48f1): more undefined references to 
`tclStubs Ptr' follow
tclsqlite3.o:tclsqlite3.c:(.text+0x6da54): undefined reference to 
`Tcl_InitStubs

...
collect2: ld returned 1 exit status
make: *** [sqlite3711.dll] Error 1

The "/c/Tcl/lib/tclstub85.lib" points to the Active State 8.5.11 Tcl I use.

Is my problem due to omitted steps, wrong steps, or configuration?

TIA,

Gerry



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

Re: [sqlite] how to use c binding and write output in c

[Kees Nuyt]

YAN HONG YE  wrote:

> Richard Hipp  wrote:
>
>> On Sun, Apr 1, 2012 at 11:21 PM, Roy Tam  wrote:
>
>> 2012/4/2 YAN HONG YE :
>> > when I run this following code  , the html file encoding changed to
>> GB2312,not utf-8, I don't know why?
>> > system("echo ^ >mm.html");
>> >   system("sqlite3 -html -header foods.db \"select * from dzh where
>> qph15>10;\" >>mm.html");
>> >   system("echo ^ >>mm.html");
>>
>> You're windows user, right?
>> system() in windows will convert all things to System Codepage
>> (CP_ACP), so please use C bindings and write output in C but not using
>> system().
>>
>
>The open-source Fossil  version control system
>used by SQLite contains an implementation of system() that correctly deals
>with UTF8 even on windows systems.  Perhaps the OP can copy some of the
>code from that.  LInk:
>
>http://www.fossil-scm.org/fossil/artifact/70e4b10c0208b3?ln=685-707
>
> But I don't know how to write.

Well, perhaps today you don't know how to write code that uses the
example, but if you take some time to learn it, you may be able to do it
in a few weeks or months.
Or you may want to hire someone who can do it for you.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Consistency check in big databases results out of memory error.

[Richard Hipp]

On Tue, Apr 3, 2012 at 1:52 PM, Kari Hoijarvi wrote:

> Thanks a lot, I'll keep my eye on it.
>

The patch is in.  http://www.sqlite.org/src/info/fa3a498dfe


>
> Kari
>
>
>
> On 4/3/2012 12:25 PM, Richard Hipp wrote:
>
>> On Tue, Apr 3, 2012 at 1:05 PM, Kari 
>> Hoijarvi
>> >wrote:
>>
>>  Hello,
>>>
>>> running PRAGMA integrity_check from command line works for a 300 GB
>>> database, but I seem to have hit some kind of limit with 832 GB sqlite
>>> DB.
>>>
>>> I running on Windows server 2008 R2 standard (64bit of course). Is this a
>>> Windows or sqlite problem?
>>>
>>> Any good ideas for workarounds?
>>>
>>>  As part of PRAGMA integrity_check, SQLite allocates counters for every
>> page
>> in the database file, and counts the number of references to each page.
>> There should be either zero or one references to each page, depending on
>> the role that page is serving. The integrity_check pragma prints error
>> messages if this is not the case.
>>
>> Unfortunately, when you get really big database files (in your case on the
>> order of 800 million pages worth) and you allocate a 32-bit integer for
>> each page, that can blow out available memory.
>>
>> We are working on a patch now in which we only allocate a single bit per
>> page, which is all we really need to determine that each page is
>> referenced
>> zero or one times.  That should reduce the memory requirements 32-fold.
>> You'll still need a 120 MB allocation to check a 800 GB database, but most
>> system can handle that these days.
>>
>> Watch the 
>> timeline>
>>  for availability of
>> this patch.
>>
>>
>>  Kari
>>>
>>> ___
>>> 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
>



-- 
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] Consistency check in big databases results out of memory error.

[Kari Hoijarvi]

Thanks a lot, I'll keep my eye on it.

Kari


On 4/3/2012 12:25 PM, Richard Hipp wrote:

On Tue, Apr 3, 2012 at 1:05 PM, Kari Hoijarviwrote:


Hello,

running PRAGMA integrity_check from command line works for a 300 GB
database, but I seem to have hit some kind of limit with 832 GB sqlite DB.

I running on Windows server 2008 R2 standard (64bit of course). Is this a
Windows or sqlite problem?

Any good ideas for workarounds?


As part of PRAGMA integrity_check, SQLite allocates counters for every page
in the database file, and counts the number of references to each page.
There should be either zero or one references to each page, depending on
the role that page is serving. The integrity_check pragma prints error
messages if this is not the case.

Unfortunately, when you get really big database files (in your case on the
order of 800 million pages worth) and you allocate a 32-bit integer for
each page, that can blow out available memory.

We are working on a patch now in which we only allocate a single bit per
page, which is all we really need to determine that each page is referenced
zero or one times.  That should reduce the memory requirements 32-fold.
You'll still need a 120 MB allocation to check a 800 GB database, but most
system can handle that these days.

Watch the timeline  for availability of
this patch.



Kari

__**_
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] Downgrade support for OS/2?

[Michael Steiger]

On 03.04.2012 19:38 Richard Hipp said the following:

We propose to remove the VFS module for OS/2 from the SQLite amalgamation
in the next release, reducing the size of the amalgamation source file by
1924 lines (or about 1.4%).

If this change will cause you any serious hardship, please speak up and we
will reconsider.



And this one day after the 25th anniversary of the announcement of OS/2.
RIP OS/2 and RIP OS/2 support in sqlite.

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

[sqlite] Downgrade support for OS/2?

[Richard Hipp]

We propose to remove the VFS module for OS/2 from the SQLite amalgamation
in the next release, reducing the size of the amalgamation source file by
1924 lines (or about 1.4%).

If this change will cause you any serious hardship, please speak up and we
will reconsider.

-- 
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] Consistency check in big databases results out of memory error.

[Richard Hipp]

On Tue, Apr 3, 2012 at 1:05 PM, Kari Hoijarvi wrote:

> Hello,
>
> running PRAGMA integrity_check from command line works for a 300 GB
> database, but I seem to have hit some kind of limit with 832 GB sqlite DB.
>
> I running on Windows server 2008 R2 standard (64bit of course). Is this a
> Windows or sqlite problem?
>
> Any good ideas for workarounds?
>

As part of PRAGMA integrity_check, SQLite allocates counters for every page
in the database file, and counts the number of references to each page.
There should be either zero or one references to each page, depending on
the role that page is serving. The integrity_check pragma prints error
messages if this is not the case.

Unfortunately, when you get really big database files (in your case on the
order of 800 million pages worth) and you allocate a 32-bit integer for
each page, that can blow out available memory.

We are working on a patch now in which we only allocate a single bit per
page, which is all we really need to determine that each page is referenced
zero or one times.  That should reduce the memory requirements 32-fold.
You'll still need a 120 MB allocation to check a 800 GB database, but most
system can handle that these days.

Watch the timeline  for availability of
this patch.


>
> Kari
>
> __**_
> 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] Consistency check in big databases results out of memory error.

[Kari Hoijarvi]

Hello,

running PRAGMA integrity_check from command line works for a 300 GB 
database, but I seem to have hit some kind of limit with 832 GB sqlite DB.


I running on Windows server 2008 R2 standard (64bit of course). Is this 
a Windows or sqlite problem?


Any good ideas for workarounds?

Kari

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

Re: [sqlite] Foreign Key Problems

[Simon Slavin]

On 3 Apr 2012, at 5:38pm, Dan Kennedy  wrote:

> On 04/03/2012 11:18 PM, Pete wrote:
>> I am running OS X 10.6.8 and sqlite3 comes with the OS.  Does anyone know
>> where I can get a version of sqlite3 for OS X that does support foreign
>> keys?  It would have to be a compiled binary since I don't access to
>> compilers (or the skills to use them).
> 
> From here:
> 
>  http://www.sqlite.org/download.html
> 
> First artifact listed under "Precompiled Binaries For Mac OS X (x86)".

Do /not/ replace any version of sqlite3 that comes with the OS.  You're 
probably using /usr/bin/sqlite3.  But just drop this one into one of your own 
folders and use it from there.  So if you're used to doing

prompt$ sqlite3 myDatabase.s3

then put the executable in the same folder and do

prompt$ ./sqlite3 myDatabase.s3

instead.

I don't know of specific problems that would be caused by updating Apple's own 
version, but it's generally considered to be a risky thing to do because 
Apple's software might depend on behaviour of its current version.

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

Re: [sqlite] Foreign Key Problems

[Dan Kennedy]

On 04/03/2012 11:18 PM, Pete wrote:

Thanks you SImon.  I see this is because the version of sqlite3 I have does
not support foreign keys.

I am running OS X 10.6.8 and sqlite3 comes with the OS.  Does anyone know
where I can get a version of sqlite3 for OS X that does support foreign
keys?  It would have to be a compiled binary since I don't access to
compilers (or the skills to use them).


From here:

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

First artifact listed under "Precompiled Binaries For Mac OS X (x86)".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key Problems

[Pete]

Thanks you SImon.  I see this is because the version of sqlite3 I have does
not support foreign keys.

I am running OS X 10.6.8 and sqlite3 comes with the OS.  Does anyone know
where I can get a version of sqlite3 for OS X that does support foreign
keys?  It would have to be a compiled binary since I don't access to
compilers (or the skills to use them).

Thanks,
Pete

On Tue, Apr 3, 2012 at 9:00 AM,  wrote:

> Message: 5
> Date: Mon, 2 Apr 2012 17:58:28 +0100
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Foreign Key Problems
> Message-ID: <922a3407-7604-4f64-87bc-07221c066...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 2 Apr 2012, at 5:56pm, Pete  wrote:
>
> > Enabling foreign keys in my application works fine and INSERTs thast
> > violate a fkey constraint fail.  Is this a know problem with sqlite3?
>  I'm
> > using version 3.6.12 on a Mac.
>
> 
>
> "This document describes the support for SQL foreign key constraints
> introduced in SQLite version 3.6.19."
>
> Simon.
>



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Jay A. Kreibich]

On Tue, Apr 03, 2012 at 01:22:02AM +0100, Simon Slavin scratched on the wall:
> On 3 Apr 2012, at 12:27am, Webdude  wrote:

> > Does anyone know if SQLite stores additional unique internal 
> > information such as timestamps etc. that would affect this, and
> > if so could these "additional to the data" variable features be
> > disabled in any way?
> 
> SQLite files do contain some metadata like row numbers and internal page
> hashes, but nothing like timestamps unless the programmer generated them
> in software.

  While there are no timestamps, SQLite does keep a number of counters
  and other meta-data in the file header page that are not directly
  under programmer control.  This includes a "change counter" that gets
  incremeted with every write.

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

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with 3.7.11 cmdline shell

[Richard Hipp]

On Tue, Apr 3, 2012 at 6:36 AM, Han Rougoor  wrote:

> problem: reserved words not quoted in dump output
> version: 3.7.11
> file: shell.c
> line: 1308
> example:
>

Already fixed at http://www.sqlite.org/src/info/638b711502 but thanks for
the report all the same.


>
> create table "group" as select 1;
> .dump
>
> outputs:
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE "group"("1");
> INSERT INTO group VALUES(1);
> COMMIT;
> ___
> 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] problem with 3.7.11 cmdline shell

[Han Rougoor]

problem: reserved words not quoted in dump output
version: 3.7.11
file: shell.c
line: 1308
example:

create table "group" as select 1;
.dump

outputs:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "group"("1");
INSERT INTO group VALUES(1);
COMMIT;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling warnings in SQLite v3.7.11

[Boštjan Eržen]

Hi,

I would like to report two compiler warnings when compiling v3.7.11 with 
Embedded Visaul C++ 4.0 for Windows Mobile devices:
- sqlite3.c, line 68356, code "u.bb.r.flags = (u16)(UNPACKED_INCRKEY * 
(1 & (u.bb.oc - OP_SeekLt)));" causes compiler warning "warning C4244: 
'=' : conversion from 'unsigned short ' to 'unsigned char ', possible 
loss of data". As I see loss of data is not possible, but still I 
suggest  code replacement with "u.bb.r.flags = (u8)(UNPACKED_INCRKEY * 
(1 & (u.bb.oc - OP_SeekLt)));". As I checked it's more correct.
- sqlite3.c, line 21434, code "result = 1e308*1e308*s;  /* Infinity */" 
causes compiler warning "warning C4056: overflow in floating-point 
constant arithmetic". As I see Visual Studio 2010 doesn't give warning 
for this case, so I guess there should be some kind of define for 
maksimum value, instead of hard coded value (DBL_MAX maybe?). Different 
systems can have different maxsimum values.



--
Lep pozdrav / Best regards
Bostjan Erzen

ERPO SISTEMI d.o.o.
Britof 94, 4000 Kranj, Slovenia
WWW: http://www.erpo-sistemi.si
Tel. : +386 41 386 680

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

[sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Gregory Petrosyan]

Hello,

I am experiancing a weird problem: sometimes (1 time in a 10-100) when
2 processes try to open the same database file (and execute something
like 'create table foo if not exists'), one of them fails with
SQLITE_BUSY — despite 1 second (or bigger) timeout.

Processes themselves produce almost no DB activity; they merely start
and initialize the database roughly at the same time.
sqlite3_open_v2() is immediately followed by sqlite3_busy_timeout().

Unfortunately I can't write a small program that reproduces this
reliably: my test program never crashes this way (except when timeout
is really small — say, 10ms). Yet, this behaviour is rare but
reproducible, both from Python (apsw) on Windows, Python (apsw) on OS
X and native C API on OS X (in entirely different program).

Can you please point me at what can be the cause of this?

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Black, Michael (IS)]

Database files are purportedly platform independent.  So why don't you 
distribute the database file instead of building it?

Then your checksum would be fine.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

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

Re: [sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Gregory Petrosyan]

On Tue, Apr 3, 2012 at 3:53 PM, Simon Slavin  wrote:
>
> On 3 Apr 2012, at 9:53am, Dan Kennedy  wrote:
>
>> As Jay says, deadlock is not possible for implicit transactions.
>> SQLite will keep retrying until either your busy-handler returns
>> zero (if you configured a busy-handler) or the timeout is reached
>> (if you configured a timeout). It sounds like the latter in this
>> case.
>
> You seem to have a setup where your failures can be produced on demand, 
> albeit at random.  So you can test whether this is a timeout-related problem 
> but varying your timeout.  Run it, and log how many failures you get.  Then 
> multiply (or divide) your timeout setting by ten and run it again.  If you 
> get failures at the same interval, it's not a timeout-related problem.

I've managed to make *something* reproducible. Take a look at the
following Python code, I hope it is self-explanatory:

--

import os
import sys
import apsw
import multiprocessing as mp


BUSYTIMEOUT = 5000
NPROCS = 4


def rmdb():
try:
os.remove('file.db')
os.remove('file.db-wal')
os.remove('file.db-shm')
except:
pass


def child():
try:
db = apsw.Connection('file.db')
db.setbusytimeout(BUSYTIMEOUT)
try:
db.cursor().execute('PRAGMA JOURNAL_MODE=WAL')
except:
print >>sys.stderr,  "Error with PRAGMA:", sys.exc_info()[0]
db.close()
except:
print >>sys.stderr,  "Unexpected error:", sys.exc_info()[0]


if __name__ == '__main__':
while True:
rmdb()

ps = [mp.Process(target=child) for i in xrange(NPROCS)]
for p in ps:
p.daemon = True
for p in ps:
p.start()
for p in ps:
p.join()

--

It reliably results in the "deadlock" scenario described above; hence
SQLite returns _BUSY for "PRAGMA"  despite the big timeout (timeout
value seems to have no effect).

Can you please describe why SQLite does not retries the implicit
"PRAGMA" transaction here?

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

Re: [sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Simon Slavin]

On 3 Apr 2012, at 9:53am, Dan Kennedy  wrote:

> As Jay says, deadlock is not possible for implicit transactions.
> SQLite will keep retrying until either your busy-handler returns
> zero (if you configured a busy-handler) or the timeout is reached
> (if you configured a timeout). It sounds like the latter in this
> case.

You seem to have a setup where your failures can be produced on demand, albeit 
at random.  So you can test whether this is a timeout-related problem but 
varying your timeout.  Run it, and log how many failures you get.  Then 
multiply (or divide) your timeout setting by ten and run it again.  If you get 
failures at the same interval, it's not a timeout-related problem.

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

[sqlite] We have a snake in paradise

[Arbol One]

We have a snake in paradise. Pls Administrator, remove the OP of this
message. We cannot afford this kind of distractions from SQLite or
distortion of what SQLite is.

TIA

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Darko Filipovic
Sent: Monday, April 02, 2012 9:28 AM
To: goran6...@hotmail.com; sqlite-users-requ...@sqlite.org;
s.tij...@gmail.com; blackwhit...@hotmail.com; darko_...@yahoo.com;
balkanskispijun...@yahoo.com; sqlite-users@sqlite.org
Subject: [sqlite] (no subject)

http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html;>
http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html
___
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] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Dan Kennedy]

On 04/03/2012 04:20 PM, Gregory Petrosyan wrote:

On Tue, Apr 3, 2012 at 12:53 PM, Dan Kennedy  wrote:

As a side note: why does not SQLite automatically retry implicit
transactions after invoking busy handler?


It's a race. That's what SQLITE_BUSY means.

As Jay says, deadlock is not possible for implicit transactions.
SQLite will keep retrying until either your busy-handler returns
zero (if you configured a busy-handler) or the timeout is reached
(if you configured a timeout). It sounds like the latter in this
case.


I don't follow the logic here: if deadlock is not possible with
implicit transactions, SQLite should retry them after specified
timeout, but not return _BUSY, forcing me into implementing the same
retry logic, no?


It continues attempting the operation until the length of time
since the first attempt exceeds the configured timeout. At which
point it returns SQLITE_BUSY.


If you are using WAL mode, check that connections are being closed
properly. If they are not, a *-wal file may be left on the disk even
after all active clients have disconnected. In this case, when the
next client connects it has to read and process the entire *-wal
file. This is called "recovery", and the client holds an exclusive
lock on the db file while it is underway. Any other clients
that attempt to read or write the db see an SQLITE_BUSY. Any
busy-handler (or timeout) will apply here, but if recovery takes a
while then a short timeout could be exceeded.


Am I correct that if e.g. I have 2 processes, A and B, using the same
WAL-enabled database, then following:
1. A creates the database and starts to write data (connection is open
all the time)
2. B tries to connect to the database
can easily result in SQLITE_BUSY returned to either A or B?


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

Re: [sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Gregory Petrosyan]

On Tue, Apr 3, 2012 at 12:53 PM, Dan Kennedy  wrote:
>> As a side note: why does not SQLite automatically retry implicit
>> transactions after invoking busy handler?
>
> It's a race. That's what SQLITE_BUSY means.
>
> As Jay says, deadlock is not possible for implicit transactions.
> SQLite will keep retrying until either your busy-handler returns
> zero (if you configured a busy-handler) or the timeout is reached
> (if you configured a timeout). It sounds like the latter in this
> case.

I don't follow the logic here: if deadlock is not possible with
implicit transactions, SQLite should retry them after specified
timeout, but not return _BUSY, forcing me into implementing the same
retry logic, no?

> If you are using WAL mode, check that connections are being closed
> properly. If they are not, a *-wal file may be left on the disk even
> after all active clients have disconnected. In this case, when the
> next client connects it has to read and process the entire *-wal
> file. This is called "recovery", and the client holds an exclusive
> lock on the db file while it is underway. Any other clients
> that attempt to read or write the db see an SQLITE_BUSY. Any
> busy-handler (or timeout) will apply here, but if recovery takes a
> while then a short timeout could be exceeded.

Am I correct that if e.g. I have 2 processes, A and B, using the same
WAL-enabled database, then following:
1. A creates the database and starts to write data (connection is open
all the time)
2. B tries to connect to the database
can easily result in SQLITE_BUSY returned to either A or B?

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

Re: [sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Dan Kennedy]

On 04/03/2012 12:48 PM, Gregory Petrosyan wrote:

On Mon, Apr 2, 2012 at 7:51 PM, Jay A. Kreibich  wrote:

On Mon, Apr 02, 2012 at 07:40:58PM +0400, Gregory Petrosyan scratched on the 
wall:

Hello,

I am experiancing a weird problem: sometimes (1 time in a 10-100) when
2 processes try to open the same database file (and execute something
like 'create table foo if not exists'), one of them fails with
SQLITE_BUSY ??? despite 1 second (or bigger) timeout.

Processes themselves produce almost no DB activity; they merely start
and initialize the database roughly at the same time.
sqlite3_open_v2() is immediately followed by sqlite3_busy_timeout().

Unfortunately I can't write a small program that reproduces this
reliably: my test program never crashes this way (except when timeout
is really small ??? say, 10ms). Yet, this behaviour is rare but
reproducible, both from Python (apsw) on Windows, Python (apsw) on OS
X and native C API on OS X (in entirely different program).

Can you please point me at what can be the cause of this?


  You may be seeing a deadlock situation.  This is most commonly
  associated with explicit transactions (that are open for a longer
  period of time), but it is possible with implicit transactions.

  If two connections attempt to write to the DB at the same time, it is
  possible for them to deadlock on the file locking.  SQLite recognizes
  this and has one of the connections back off with an SQLITE_BUSY error.
  If this happens in an explicit transaction, the program needs to
  ROLLBACK the current transaction and start over.  In the case of an
  implicit transaction around a statement, you can safely re-run
  the statement.


Thanks a lot for the reply.

Shouldn't it be extremely unlikely for this situation to happen, though?

Can it be diagnosed with more certaincy? I feel uncomfortable guessing
(hoping) that the bug exists due to this race/deadlock.

As a side note: why does not SQLite automatically retry implicit
transactions after invoking busy handler?


It's a race. That's what SQLITE_BUSY means.

As Jay says, deadlock is not possible for implicit transactions.
SQLite will keep retrying until either your busy-handler returns
zero (if you configured a busy-handler) or the timeout is reached
(if you configured a timeout). It sounds like the latter in this
case.

If you are using WAL mode, check that connections are being closed
properly. If they are not, a *-wal file may be left on the disk even
after all active clients have disconnected. In this case, when the
next client connects it has to read and process the entire *-wal
file. This is called "recovery", and the client holds an exclusive
lock on the db file while it is underway. Any other clients
that attempt to read or write the db see an SQLITE_BUSY. Any
busy-handler (or timeout) will apply here, but if recovery takes a
while then a short timeout could be exceeded.

Other than that, you probably just want to increase the busy-timeout
some.











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

[sqlite] how to use c binding and write output in c

[YAN HONG YE]

Date: Mon, 2 Apr 2012 00:37:18 -0400
From: Richard Hipp  wrote:

On Sun, Apr 1, 2012 at 11:21 PM, Roy Tam  wrote:

> 2012/4/2 YAN HONG YE :
> > when I run this following code  , the html file encoding changed to
> GB2312,not utf-8, I don't know why?
> > system("echo ^ >mm.html");
> >   system("sqlite3 -html -header foods.db \"select * from dzh where
> qph15>10;\" >>mm.html");
> >   system("echo ^ >>mm.html");
>
> You're windows user, right?
> system() in windows will convert all things to System Codepage
> (CP_ACP), so please use C bindings and write output in C but not using
> system().
>

The open-source Fossil  version control system
used by SQLite contains an implementation of system() that correctly deals
with UTF8 even on windows systems.  Perhaps the OP can copy some of the
code from that.  LInk:

http://www.fossil-scm.org/fossil/artifact/70e4b10c0208b3?ln=685-707


--
But I don't know how to write  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users