Re: [sqlite] How much disk space is required to checkpoint?

2012-09-25 Thread Dan Kennedy 

On 09/26/2012 05:48 AM, Simon Slavin wrote:


On 25 Sep 2012, at 10:54pm, David Barrett
wrote:


If my database is X GB, and the WAL file is Y GB, how much total
disk space is required to:

1) Checkpoint the database


I think it's a maximum of X+Y+Y (including the existing X+Y) plus or
minus a page or two.


2) Vacuum the database


I think it's a maximum of 3*(X+Y) (including the existing X+Y) plus
or minus a page or two.  It might be 2*(X+Y).  I'm not certain how of
how checkingpointing is done.  Both of those answers are dependent on
the database not being corrupt, and SQLite being in a proper working
state.


Additionally, if there are any temporary files created, where do
they exist?  We've done some poking around and are having some
crazy theory about a temporary file being created inside /tmp, but
in a *deleted* state.


As far as I know, SQLite does not intentionally play tricks like
that.  Your OS may report the length of a temporary file as zero
because it's still open, but SQLite is just using the normal file
writing calls.


Actually it might. On unix, if you unlink a file while one or more
clients have it open, the clients can keep reading and writing the
file descriptor. No other can open the file though (as it is not
linked into the file-system). So when SQLite requires a temporary
file, it normally creates and opens a file with a randomized name
in /tmp then calls unlink() on it immediately - before it starts
using the file.

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

Re: [sqlite] database disk image is malformed - Error

2012-09-25 Thread Rittick Gupta 
Do you think a retry of the query would help resolve this issue ? Do I have to 
close & reopen the database. 

Thanks for your help.

regards,

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

Re: [sqlite] database disk image is malformed - Error

2012-09-25 Thread David Barrett 
Yes, still having that problem. We've moved to new servers with SSD's and a
ton of RAM, and that seems to have helped matters -- not sure why, though I
don't know why it was happening in the first place.  (I'm *guessing* the
issue is due to two conflicting queries happening at the same time, so if
the queries happen faster the probability of collision goes down?)

-david

On Tue, Sep 25, 2012 at 6:39 PM, Rittick Gupta  wrote:

> David,
>   Thanks. Do you still have the problem ? Did you find a workaround to
> avoid the problem - appreciate your response.
>
> regards,
>
> Rittick
>
> ___
> 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] database disk image is malformed - Error

2012-09-25 Thread Rittick Gupta 
David,
  Thanks. Do you still have the problem ? Did you find a workaround to 
avoid the problem - appreciate your response.

regards,

Rittick

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

Re: [sqlite] How much disk space is required to checkpoint?

2012-09-25 Thread Simon Slavin 

On 25 Sep 2012, at 10:54pm, David Barrett  wrote:

> If my database is X GB, and the WAL file is Y GB, how much total disk space
> is required to:
> 
> 1) Checkpoint the database

I think it's a maximum of X+Y+Y (including the existing X+Y) plus or minus a 
page or two.

> 2) Vacuum the database

I think it's a maximum of 3*(X+Y) (including the existing X+Y) plus or minus a 
page or two.  It might be 2*(X+Y).  I'm not certain how of how checkingpointing 
is done.  Both of those answers are dependent on the database not being 
corrupt, and SQLite being in a proper working state.

> Additionally, if there are any temporary files created, where do they
> exist?  We've done some poking around and are having some crazy theory
> about a temporary file being created inside /tmp, but in a *deleted* state.

As far as I know, SQLite does not intentionally play tricks like that.  Your OS 
may report the length of a temporary file as zero because it's still open, but 
SQLite is just using the normal file writing calls.

> I'm guessing we're wrong, so I figured it would be easiest just to ask.

Good strategy.

You might find something interesting from this PRAGMA:



though you should read the notes first.  But as a rule SQLite will create 
temporary files in just two places: the folder the database is in, and wherever 
your OS thinks temporary files should be (e.g. /tmp, C:\windows\temp).  There 
are some problems with OSen like Android which do not have a built-in concept 
of a temporary folder.  For those, I believe the temporary folder is set at 
compilation time.

I did once find SQLite creating temporary files on the root of my boot disk.  
This problem was traced to the fact that the disk format (not the database 
format) was corrupt !

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

[sqlite] How much disk space is required to checkpoint?

2012-09-25 Thread David Barrett 
If my database is X GB, and the WAL file is Y GB, how much total disk space
is required to:

1) Checkpoint the database

2) Vacuum the database

Additionally, if there are any temporary files created, where do they
exist?  We've done some poking around and are having some crazy theory
about a temporary file being created inside /tmp, but in a *deleted* state.
 I'm guessing we're wrong, so I figured it would be easiest just to ask.
 Thanks!

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

Re: [sqlite] database disk image is malformed - Error

2012-09-25 Thread David Barrett 
You might be interested in this thread:

http://www.theusenetarchive.com/usenet-message-sqlite-does-disabling-synchronous-and-shared-cache-cause-%22error-database-disk-image-is-malformed%22-20780199.htm

No conclusion was ever obtained, but the discussion was good.

-david

On Mon, Sep 24, 2012 at 4:55 PM, Rittick Gupta  wrote:

> I have a mult-threaded application. Each thread uses its own database
> handle. One thread got a return value of 11 (disk image malformed) - with
> the sqlite3_step statement (select statement). When we restarted the
> application after the failure the database recovered and did not give any
> error.
>
> Why did the "sqlite3_step" statement returned a "disk malformed" error
> when the disk is not corrupt ?
>
> Any help will be appreciated.
>
>
> ___
> 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] [Fwd: [FEATURE REQUEST] support of %y in strftime()]

2012-09-25 Thread Clemens Ladisch 
LacaK wrote:
> But why not add %y, when it is only 5 lines of source code,

Plus documentation, test cases, etc.

> take into account that %y is supported also by C strftime()
> or PHP etc.

Those languages are not called Clite or PHPlite.

> and it is really useful.

You still haven't mentioned a single example.

The only possible use that I could imagine would be for formatting the
date for display to the user -- but this is *not* what strftime() is
intended for in SQLite.  All strftime() substitutions are zero-padded to
a fixed length so that they can be used easily for sorting and grouping.


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

[sqlite] [Fwd: [FEATURE REQUEST] support of %y in strftime()]

2012-09-25 Thread LacaK 
Yes I know, that there is workaround using: 
 substr(strftime('%Y',d),3)


But why not add %y, when it is only 5 lines of source code, take into account 
that %y is supported also by
C strftime() or PHP etc. and it is really useful.

-Laco.


Seriously, what your patch is actually about is to extract the last two
digits out of a four-digit year.  Apart from all the obvious Y2K
problems, there already is a function for that: it's called substr().





Please can any developer do any comment on this request?
Would it be possible to add such support?
Thanks
-Laco.

- Pôvodná správa -
Predmet:[FEATURE REQUEST] support of %y in strftime()
Dátum:  Fri, 21 Sep 2012 14:15:45 +0200
Od: LacaK 
Pre:sqlite-users@sqlite.org



Hi *,
some month ago I wrote question about possibility to add support of %y 
(2 digit year) to strftime() function.
Patch is very simple (only few lines of code) and I hope, that will be 
useful for many users.
Patch by Alexey is here: 
http://sqlite.mobigroup.ru/wiki?name=2-digit+year+patch
(although I think, that it can be done in a simpler way ... see my 
comment later)
Can you consider please adding such support ? Is this proper place for 
post such requests ? Can I do something more to add such support ?

TIA
-Laco.

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
   case 'W':
+   case 'y':
 n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}


  

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

Re: [sqlite] [Fwd: [FEATURE REQUEST] support of %y in strftime()]

2012-09-25 Thread Clemens Ladisch 
LacaK wrote:
> some month ago I wrote question about possibility to add support of %y
> (2 digit year) to strftime() function.
> Patch is very simple (only few lines of code) and I hope, that will be
> useful for many users.

Two-digit years happend about two thousand years ago, and at that time,
nobody used the Gregorian calendar.

Seriously, what your patch is actually about is to extract the last two
digits out of a four-digit year.  Apart from all the obvious Y2K
problems, there already is a function for that: it's called substr().

Please note that none of the current strftime() substitutions throw away
data; they all generate output long enough for the largest possible
value.


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

[sqlite] [Fwd: [FEATURE REQUEST] support of %y in strftime()]

2012-09-25 Thread LacaK 

Please can any developer do any comment on this request?
Would it be possible to add such support?
Thanks
-Laco.

- Pôvodná správa -
Predmet:[FEATURE REQUEST] support of %y in strftime()
Dátum:  Fri, 21 Sep 2012 14:15:45 +0200
Od: LacaK 
Pre:sqlite-users@sqlite.org



Hi *,
some month ago I wrote question about possibility to add support of %y 
(2 digit year) to strftime() function.
Patch is very simple (only few lines of code) and I hope, that will be 
useful for many users.
Patch by Alexey is here: 
http://sqlite.mobigroup.ru/wiki?name=2-digit+year+patch
(although I think, that it can be done in a simpler way ... see my 
comment later)
Can you consider please adding such support ? Is this proper place for 
post such requests ? Can I do something more to add such support ?

TIA
-Laco.

Looking at patch I do not understand, why we add

n += 4;
in case of 'y' ?

I would do:
  case 'W':
+   case 'y':
n++; 
...


+case 'y': {
+  sqlite3_snprintf(3,[j],"%02d",x.Y % 100); j+=2;
+  break;
+}



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