Re: [sqlite] Corruption of incremental_vacuum databases

[John Machin]

On 17/04/2009 1:39 AM, Filip Navara wrote:
> Hello,
> 
> I have expected at least some reply. Oh well, new the corruption has happened
> again (on another different machine) and I have saved the database files. One
> of the corrupted files is available at 
> http://www.emclient.com/temp/folders.zip.

U ... your first message [scroll down to read] is talking about 
*incremental* vacuuming; however the database file that you made 
available has FULL (not incremental) auto-vacumming set.

dos-prompt>sqlite3 folders.dat
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma auto_vacuum;
1 <<<=== 1 means full, incremental is 2
sqlite> pragma integrity_check;
*** in database main ***
Page 11 is never used
Page 13 is never used
Page 15 is never used
Page 20 is never used
Page 21 is never used
Page 22 is never used
Page 23 is never used
Page 24 is never used
Page 25 is never used
sqlite>

Six orphan pages at the end of the file plus another 3 orphans suggests 
that an auto-vacuum (full or incremental) may have been interrupted -- 
or perhaps later given that you are? were? using synchronous=off.

Did you get any resolution on this?

Cheers,
John

> I'd be glad for any help or at least confirmation that it could be
> related to the
> issues in the tickets listed below.
> 
> Thanks,
> Filip Navara
> 
> On Tue, Mar 31, 2009 at 11:05 AM, Filip Navara  wrote:
>> Hello,
>>
>> after seeing the recent change #6413 and ticket #3761 I finally
>> decided to write about a corruption issue we have.
>>
>> This is the environment of our application:
>> - We are using SQLite 3.6.3 (with SQLite.NET 1.0.60.0).
>> - Several database files. Each file is opened in it's own connection
>> and never shared across them.
>> - Some of these connections have another database attached to it
>> (containing mostly BLOB data).
>> - In all cases the connections are opened on program start and closed
>> on program shutdown.
>> - There's a low-priority thread that executes "pragma
>> incremental_vacuum" when the application is idle and there is enough
>> free pages. Code of the thread is listed below.
>> - "journal_mode=persist" is used on all databases in all connections
>> (to workaround a bug in the journal deletion logic on Windows, search
>> for "TortoiseSVN" in the mailing list archive for details)
>> - "synchronous=off" is used on all databases in all connections. This
>> setting is likely to change in future, but in no case of the
>> corruption a system crash was involved.
>>
>> Since we started using the incremental_vacuum mode we were getting
>> database corruption errors pretty often (sometimes as often as once a
>> day in 3 people). Most, if not all, of these corruptions happened
>> following a ROLLBACK (caused by constraint violation). "pragma
>> integrity_check;" on the already corrupted databases usually reported
>> few "Page XXX is never used" error.
>>
>> Unfortunately I don't have any of the corrupted databases at hand and
>> I have no deterministic way to create them. My question is if these
>> could be related to the just fixed problem (in ticket 3761) or if it
>> could be another issue?
>>
>> Best regards,
>> Filip Navara
>>
>> 
>>
>> WaitHandle[] handles = new WaitHandle[] { this.stopEvent, this.wakeupEvent };
>> System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
>> int timeout = -1;
>> int pagesPerIteration = 32;
>>
>> // Wait for thread shutdown and wakeup event. The shutdown event
>> // is used to stop the thread on application exit. The wakeup event is
>> // fired on startup if there are free pages in the database or if a DELETE
>> // statement was executed.
>> while (WaitHandle.WaitAny(handles, timeout, false) != 0)
>> {
>>long totalFreePages = 0, freePages;
>>lock (this.repositories)
>>{
>>stopWatch.Reset();
>>stopWatch.Start();
>>foreach (IRepositoryBase repository in this.repositories)
>>{
>>   // wrapper around "pragma freelist_count;"
>>freePages = repository.GetFreePageCount();
>>totalFreePages += freePages;
>>if (freePages > 0)
>>   // wrapper around "pragma 
>> incremental_vacuum(x)"
>>repository.Compact(pagesPerIteration);
>>}
>>stopWatch.Stop();
>>}
>>
>>// We start by freeing 32 pages per one iteration of the loop for
>>   // each database. After each iteration the number is recalculated
>>   // based on the time spent on the operation and then it's
>>   // truncated to the <24;4096> range.
>>pagesPerIteration = Math.Min(Math.Max(24, (int)(100.0 *
>> pagesPerIteration / stopWatch.ElapsedMilliseconds)), 4096);
>>
>>// If there are still free pages in the databases then schedule the
>>   // 

Re: [sqlite] 2 columns as primary key?

[John Machin]

On 30/05/2009 12:43 PM, Andrés G. Aragoneses wrote:
> I just tried to create a primary key with 2 columns and got this error:
> 
> "sqlite error" "table X has more than one primary key"
> 
> 
> Doesn't SQLite support this?? :o

It does support multi-column primary keys. It's a bit hard to tell at 
this distance what your problem is. Unfortunately, as the text of your 
CREATE TABLE statement is presumably a state secret, we'll have to play 
guessing games:

dos-prompt>sqlite3
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

Example of supporting multi-column primary keys:

sqlite> create table employment (employer_id text, employee_id text, 
start_date datetime, primary key (employer_id, employee_id));

Example of getting your error message:

sqlite> create table employment2 (employer_id text primary key, 
employee_id text primary key, start_date datetime);
SQL error: table "employment2" has more than one primary key
sqlite>

Are we getting warm?

Suggested reading:

http://www.firstsql.com/tutor6.htm#constraint
http://www.sqlite.org/syntaxdiagrams.html#table-constraint
http://www.catb.org/~esr/faqs/smart-questions.html

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

Re: [sqlite] 2 columns as primary key?

[Andrés G. Aragoneses]

Thanks! It worked. Sorry for the silly question :)

Igor Tandetnik wrote:
> ""Andrés G. Aragoneses""
>  wrote in message
> news:gvq7b2$lp...@ger.gmane.org
>> CREATE TABLE LastSyncedRatings (
>>   DapID   TEXT PRIMARY KEY,
>>   MetadataHashTEXT PRIMARY KEY,
>>   Rating  INTEGER NOT NULL
>> )
> 
> Make it
> 
> CREATE TABLE LastSyncedRatings (
>   DapID   TEXT ,
>   MetadataHashTEXT,
>   Rating  INTEGER NOT NULL,
>   PRIMARY KEY(DapID, MetadataHash)
> );
> 
> Igor Tandetnik 
> 
> 
> 
> 
> 
> 
> 
> ___
> 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] 2 columns as primary key?

[Harold Wood]

create table tablename 
(
    colname1 coltype,
    colname2 coltype,
    colname3 coltype,
    PRIMARY KEY(colname1 asc, colname2 asc)
)

--- On Fri, 5/29/09, Pavel Ivanov  wrote:


From: Pavel Ivanov 
Subject: Re: [sqlite] 2 columns as primary key?
To: "General Discussion of SQLite Database" 
Date: Friday, May 29, 2009, 10:54 PM


What create table statement did you use?

Pavel

2009/5/29 "Andrés G. Aragoneses" :
> I just tried to create a primary key with 2 columns and got this error:
>
> "sqlite error" "table X has more than one primary key"
>
>
> Doesn't SQLite support this?? :o
>
>        Andres
>
> --
>
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 columns as primary key?

[Igor Tandetnik]

""Andrés G. Aragoneses""
 wrote in message
news:gvq7b2$lp...@ger.gmane.org
> CREATE TABLE LastSyncedRatings (
>   DapID   TEXT PRIMARY KEY,
>   MetadataHashTEXT PRIMARY KEY,
>   Rating  INTEGER NOT NULL
> )

Make it

CREATE TABLE LastSyncedRatings (
  DapID   TEXT ,
  MetadataHashTEXT,
  Rating  INTEGER NOT NULL,
  PRIMARY KEY(DapID, MetadataHash)
);

Igor Tandetnik 



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

Re: [sqlite] 2 columns as primary key?

[Andrés G. Aragoneses]

CREATE TABLE LastSyncedRatings (
  DapID   TEXT PRIMARY KEY,
  MetadataHashTEXT PRIMARY KEY,
  Rating  INTEGER NOT NULL
)

Pavel Ivanov wrote:
> What create table statement did you use?
> 
> Pavel
> 
> 2009/5/29 "Andrés G. Aragoneses" :
>> I just tried to create a primary key with 2 columns and got this error:
>>
>> "sqlite error" "table X has more than one primary key"
>>
>>
>> Doesn't SQLite support this?? :o
>>
>>Andres
>>
>> --
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 columns as primary key?

[Pavel Ivanov]

What create table statement did you use?

Pavel

2009/5/29 "Andrés G. Aragoneses" :
> I just tried to create a primary key with 2 columns and got this error:
>
> "sqlite error" "table X has more than one primary key"
>
>
> Doesn't SQLite support this?? :o
>
>        Andres
>
> --
>
> ___
> 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] 2 columns as primary key?

[Andrés G. Aragoneses]

I just tried to create a primary key with 2 columns and got this error:

"sqlite error" "table X has more than one primary key"


Doesn't SQLite support this?? :o

Andres

-- 

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

Re: [sqlite] Sqlite on a Virtual Machine of Microsoft's Hyper-V ?

[Marcus Grimm]

okay, thanks to all for the info. I'm relieved..

best

Marcus


Sam Carleton wrote:
> Well, Microsoft's Hyper-V is just like VMWare.  It allows multiple OS's 
> to be installed and running on one machine at one time.  Since sqlite is 
> simply reads/writes to a file, not track/sectors, it should be fine.  
> SQLite won't even know it is running on a virtual.
> 
> Marcus Grimm wrote:
>> Hello List,
>>
>> I've been asked if my server application will run
>> in a virtual machine: Microsoft's Hyper-V
>> I have no idea what this is, nor if it affects sqlite.
>>
>> My main concern would be if the (essential) file locking
>> and sync/commit commands will still do the intended job
>> within a virtual machine.
>>
>> Any experience with this ?
>>
>> Thank you in advance.
>>
>> Marcus Grimm
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Global cache size restriction

[Pavel Ivanov]

Hi, all!

Is there a way to set global restriction on cache size in SQLite?
I like the default cache_size value for each database. But if I open a
lot of different databases then total cache size for all of them could
be too big for me. So I'd want to set some global_cache_size to limit
it. Is it possible to do so? Or I have to implement my own page cache
to do it?

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

Re: [sqlite] TOCTOU pager bug in 3.6.14.2

[Dave Toll]

Could this be the same issue as this bug I reported a while ago?

http://www.sqlite.org/cvstrac/tktview?tn=3833

Cheers,
Dave.


-Original Message-
From: Brian Dantes [mailto:bdan...@coverity.com] 
Sent: 28 May 2009 16:01
To: Discussion of SQLite Database
Subject: [sqlite] TOCTOU pager bug in 3.6.14.2

See Ticket 3883




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

Re: [sqlite] Coulden't access my db, because of "Out of memory" error

[Simon Slavin]

On 29 May 2009, at 10:42am, elmarr...@systemcompetence.de wrote:

> My question would be, how I can resize the db file or how to check  
> if it
> is corrupted. Or what I can do to access my data.

Use the command-line tool to open it, and check it can access records  
using a few 'SELECT' statements.  If those work, you can use the  
'PRAGMA integrity_check;' statement to check for corruption.

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

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

> Or is it possible to configure sqlight to not load the hole database  
> in
> the memory? Or something like that.

Unless you are specifically messing with your configuration, sqlite  
would not try to load a 2GB database into memory.

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

Re: [sqlite] Sqlite on a Virtual Machine of Microsoft's Hyper-V ?

[Sam Carleton]

Well, Microsoft's Hyper-V is just like VMWare.  It allows multiple OS's 
to be installed and running on one machine at one time.  Since sqlite is 
simply reads/writes to a file, not track/sectors, it should be fine.  
SQLite won't even know it is running on a virtual.


Marcus Grimm wrote:

Hello List,

I've been asked if my server application will run
in a virtual machine: Microsoft's Hyper-V
I have no idea what this is, nor if it affects sqlite.

My main concern would be if the (essential) file locking
and sync/commit commands will still do the intended job
within a virtual machine.

Any experience with this ?

Thank you in advance.

Marcus Grimm

___
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] Sqlite on a Virtual Machine of Microsoft's Hyper-V ?

[Cory Nelson]

On Fri, May 29, 2009 at 8:27 AM, Marcus Grimm  wrote:
> Hello List,
>
> I've been asked if my server application will run
> in a virtual machine: Microsoft's Hyper-V
> I have no idea what this is, nor if it affects sqlite.
>
> My main concern would be if the (essential) file locking
> and sync/commit commands will still do the intended job
> within a virtual machine.
>
> Any experience with this ?
>

My experience has been that VMs strongly focus on correctness and
reliability, and will obey sync orders and everything else databases
depend on.

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

[sqlite] Sqlite on a Virtual Machine of Microsoft's Hyper-V ?

[Marcus Grimm]

Hello List,

I've been asked if my server application will run
in a virtual machine: Microsoft's Hyper-V
I have no idea what this is, nor if it affects sqlite.

My main concern would be if the (essential) file locking
and sync/commit commands will still do the intended job
within a virtual machine.

Any experience with this ?

Thank you in advance.

Marcus Grimm

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

Re: [sqlite] add column creating null columns even with default?

[Damien Elmes]

I use a Python ORM called SQLAlchemy, which maps its generic boolean
type to 'boolean' on SQLite - which I subsequently plucked from the
schema and put into an alter statement. If this is going to cause
problems then I can report it to SQLAlchemy, but as John said, this
seems to be treated like a numeric type.

Oh, and just to clarify - in the second alter table statement, it
should have said: default '' - an error with my copying

Cheers,

Damien

On Fri, May 29, 2009 at 8:40 AM, John Machin  wrote:
> On 29/05/2009 2:53 AM, Simon Slavin wrote:
>> On 28 May 2009, at 9:00am, Damien Elmes wrote:
>>
>>> alter table cardModels add column allowEmptyAnswer boolean not null
>>> default 1
>>
>>> sqlite> update cardModels set allowEmptyAnswer = 0;
>>
>> You're obviously used to other implementations of SQL.  'boolean'
>> isn't a legit type name:
>>
>> http://www.sqlite.org/datatype3.html
>
> AFAICT that page says nothing about what is a "legit type name". This
> one does: http://www.sqlite.org/syntaxdiagrams.html#type-name
>
> Here are some examples of legitimate type-names:
>
> dos-prompt>sqlite3
> SQLite version 3.6.14
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table foo (c1 jabberwocky, c2 very big inteher whoops
> typo, c3 "3.14159", c4 very variable character (-123456, +666.987), c5
> boolean);
> sqlite> pragma table_info(foo);
> 0|c1|jabberwocky|0||0
> 1|c2|very big inteher whoops typo|0||0
> 2|c3|3.14159|0||0
> 3|c4|very variable character (-123456, +666.987)|0||0
> 4|c5|boolean|0||0
> sqlite>
>
> "boolean" as a type-name will cause the column to have NUMERIC affinity
> according to the rules on the page you quoted, and the OP seems to be
> being careful to restrain values to 0, 1, and NULL, so this all looks
> rather sensible to me.
>
>>
>> This may or may not be the cause of the problem you report, but fix it
>> first.
>
> How would you propose to fix it?
>
> Cheers,
>
> John
> ___
> 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] Slow Transaction Speed?

[Jim Wilcoxson]

I agree that adding this to the library, and making it accessible via
a pragma command would be very useful.  For example, pragma commitrate
1000 would test the commit rate of 1000 commits and return the results
in transactions per second as a row.

If I install my app on a client's machine, I could run this test
periodically to ensure that the system environment is going to support
"no corruption" operation, and/or send some kind of warning to my
customer that there is a risk of corruption because their system
environment has problems.

If it were only an external program bundled with sqlite, I couldn't
really make use of it, because I'd have to distribute the program and
instructions how to use it, and rely on customers to actually do it.

This is a pretty small function.  Just for my own use, I'd consider
foreign key support to be way more bloated that this.

Jim

On 5/29/09, Marcus Grimm  wrote:
>> On Thu, May 28, 2009 at 03:12:55PM -0700, Allen Fowler scratched on the
>> wall:
>>>
>>> > just for anybody who is interested:
>>>
>>> >
>>> > I translated Jim's function into window code and added
>>> > a page of 1024 that will be written, instead of a single byte.
>>> > On my Win-XP system I got 55 TPS, much faster than sqlite
>>> > seems to write a page but that might be related to the
>>> > additional overhead sqlite needs to do.
>
> just to add: I traced a little what sqlite does when an
> simple UPDATE is done within a transaction: It does
> two syncs on the journal file and one final sync on the
> db itselve, so achieving something like 15 TPS is reasonable.
>
>
>>> >
>>> > This brings me to a nice to have feature request:
>>> > How about adding similar test function in the sqlite API ?
>>> > This might use the vfs to write pages and gives some feedback
>>> > on the performance of the system where sqlite runs on.
>>> > It might also detect problems with the commit function...
>>> > Just an idea...
>>> >
>>>
>>> Interesting idea.
>>
>>   It would make a lot more sense to make this an external utility
>>   or an extension of the sqlite3 shell.  Adding it to the core library
>>   is a definite case of code bloat.
>
> Adding it into the API would allow my application to
> easily make the test for example the first time it runs
> on a system. But maybe a problem for the users that
> apply a sqlite wrapper.
> However, having it in sqlite3 shell would be very useful as
> well.
>
> Marcus
>
>>
>>   Actually, a whole suite of performance related tests might be
>>   interesting.
>>
>>-j
>>
>> --
>> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>>
>> "Our opponent is an alien starship packed with atomic bombs.  We have
>>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>>  and a piece of string."  --from Anathem by Neal Stephenson
>> ___
>> 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
>


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

Re: [sqlite] SQLite3 question

[Oza, Hiral_Dineshbhai]

Hi Richard,

Thank you for quick reply.

Actually I am reading from that file only, but I didn't get the concept
of 'Cell' that is mentioned as...
" The payload for an entry and the preceding pointer are combined to
form a "Cell".

Can you please help me to clarify this.

Actually I got some bug in sqlite-3.5.9 for NFS pages (disk image
malformed) so I was debugging the same, and came across this.
The bug is as follow, which I posted in 'sqlite-...@sqlite.org',
awaiting reply...
=== sqlite-3.5.9 bug ===
I using sqlite-3.5.9 and observing a 'disk image malformed' error after
executing several sql statements.
After debugging found that the in sqlite3BtreeInitPage() it returns with
SQLITE_CORRUPT_BKPT based on some condition (see below)...

SQLITE_PRIVATE int sqlite3BtreeInitPage( 
 MemPage *pPage,/* The page to be initialized */
 MemPage *pParent   /* The parent.  Might be NULL */
){
 ...
 if( pPage->nCell==0 && pParent!=0 && pParent->pgno!=1 ){
 /* All pages must have at least one cell, except for root pages */
 return SQLITE_CORRUPT_BKPT;
 }
 ...
}

So I some doubts, is this condition required? if required what does it
checks?
Also if you have any workaround, please let me know.
Please let me know from which version of sqlite this bug has been
resolved.
=== sqlite-3.5.9 bug ===

I appreciate you help to understand this bug.


Thank you.
-Hiral 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Thursday, May 28, 2009 6:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite3 question


On May 28, 2009, at 8:43 AM, Oza, Hiral_Dineshbhai wrote:
>
> Can you please let me know meaning of 'Cell' in Btrees used in  
> sqlite3.

See line 43 of the btreeInt.h source file for the definition.  You  
will want to read the previous 42 lines of that same file for context.

D. Richard Hipp
d...@hwaci.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] Coulden't access my db, because of "Out of memory" error

[ElmarRath]

Hello,

I'm new to sqlite and have now the problem, that i couldn't access my 
database. When I try I get the following line.

sqlite.c:210 Unable to open Database=/usr/local/testing/db/test.db. 
ERR=out of memory

The database has nearly a size of 2GB. My guess would be that I've run out 
of addresses of the memory, or has it sumthing to do with the :memory:

The database is running on a Novel SLES10 with an IBM I6 cpu.

My question would be, how I can resize the db file or how to check if it 
is corrupted. Or what I can do to access my data. 
Or is it possible to configure sqlight to not load the hole database in 
the memory? Or something like that.

If someone could give my some hints or advices, I'm yery thankfull.

Sorry if that are a lot of stupid questions, but I'm only starting with 
sqlight and Linux. 


Best regards 

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

Re: [sqlite] FTS3

[Martin Pfeifle]

One further question:

In fts3.c, a comment is found which describes the file format dependent on the 
different compiler settings.
* Result formats differ with the setting of DL_DEFAULTS.  Examples:
**
** DL_DOCIDS: [1] [3] [7]
** DL_POSITIONS: [1 0[0 4] 1[17]] [3 1[5]]
** DL_POSITIONS_OFFSETS: [1 0[0,0,3 4,23,26] 1[17,102,105]] [3 1[5,20,23]]
 
I also found one functional limitation if we use only  DL_DOCIDS, in order to 
reduce the overall size.
 
/*
** By default, only positions and not offsets are stored in the doclists.
** To change this so that offsets are stored too, compile with
**
**  -DDL_DEFAULT=DL_POSITIONS_OFFSETS
**
** If DL_DEFAULT is set to DL_DOCIDS, your table can only be inserted
** into (no deletes or updates).
*/

Are there any other functional drawbacks if we go for DOCIDS only, e.g. search 
for "term1 term2" in a document?

Best Martin




Von: D. Richard Hipp 
An: General Discussion of SQLite Database 
Gesendet: Dienstag, den 26. Mai 2009, 12:27:59 Uhr
Betreff: Re: [sqlite] FTS3


On May 26, 2009, at 5:03 AM, Martin Pfeifle wrote:

> Dear all,
> we need full and fuzzy text search for addresses.
> Currently we are looking into Lucene and SQLite's FTS extension.
> For us it is crucial to understand the file structures and the  
> concepts behind the libraries.
> Is there a self-contained, comprehensive document for FTS3 (besides  
> the comments in fts3.c) ?

There is no information on FTS3 apart from the code comments and the  
README files in the source tree.

The file formats for FTS3 and lucene are completely different at the  
byte level.  But if you dig deeper, you will find that they both use  
the same underlying concepts and ideas and really are two different  
implementations of the same algorithm.  During development, we were  
constantly testing the performance and index size of FTS3 against  
CLucene using the Enron email corpus.  Our goal was for FTS3 to run  
significantly faster than CLucene and to generate an index that was no  
larger in size.  That goal was easily met at the time, though we have  
not tested FTS3 against CLucene lately to see if anything has changed.

One of the issues with CLucene that FTS3 sought to address was that  
when inserting new elements into the index, the insertion time was  
unpredictable.  Usually the insertions would be very fast.  But lucene  
will occasionally take a very long time for a single insertion in  
order to merge multiple smaller indices into larger indices.  This was  
seen as undesirable.  FTS3 strives to give much better worst-case  
insertion times by doing index merges incrementally and spreading the  
cost of index merges across many inserts.

D. Richard Hipp
d...@hwaci.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow Transaction Speed?

[Marcus Grimm]

> On Thu, May 28, 2009 at 03:12:55PM -0700, Allen Fowler scratched on the
> wall:
>>
>> > just for anybody who is interested:
>>
>> >
>> > I translated Jim's function into window code and added
>> > a page of 1024 that will be written, instead of a single byte.
>> > On my Win-XP system I got 55 TPS, much faster than sqlite
>> > seems to write a page but that might be related to the
>> > additional overhead sqlite needs to do.

just to add: I traced a little what sqlite does when an
simple UPDATE is done within a transaction: It does
two syncs on the journal file and one final sync on the
db itselve, so achieving something like 15 TPS is reasonable.


>> >
>> > This brings me to a nice to have feature request:
>> > How about adding similar test function in the sqlite API ?
>> > This might use the vfs to write pages and gives some feedback
>> > on the performance of the system where sqlite runs on.
>> > It might also detect problems with the commit function...
>> > Just an idea...
>> >
>>
>> Interesting idea.
>
>   It would make a lot more sense to make this an external utility
>   or an extension of the sqlite3 shell.  Adding it to the core library
>   is a definite case of code bloat.

Adding it into the API would allow my application to
easily make the test for example the first time it runs
on a system. But maybe a problem for the users that
apply a sqlite wrapper.
However, having it in sqlite3 shell would be very useful as
well.

Marcus

>
>   Actually, a whole suite of performance related tests might be
>   interesting.
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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