Re: [sqlite] SQL error: database disk image is malformed

[Jay A. Kreibich]

On Tue, May 05, 2009 at 11:46:38PM +0200, Kees Nuyt scratched on the wall:
> On Tue, 5 May 2009 16:55:42 -0400, Pavel Ivanov
>  wrote:
> 
> >Is it just me or somebody else is
> >seeing too that the sql statement
> > "select blobid, fbid from sig group by peerid" 
> >is invalid and shouldn't be 
> >executed or prepared at all?
> 
> You are right, it doesn't make sense.

  It doesn't make a lot of sense, but it is still valid.

> @Joannek: When using group by, your select columns can only
> use aggregate functions and the columns you group by.

  "should only", not "can only."  SQLite will happily execute that
  statement.  The results are unlikely to be useful, however.

  When SQLite is asked to output a column that is not aggregated or
  grouped, the returned value for that column is simply the value
  of the last found row in the group.  I suspect this is the same for
  the grouped columns as well, they just happen to always be the same.

   -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] Optimizing concurrency with sql query - locks?

[Rosemary Alles]

Hullo all,

Run on a single processor, the following query is quite fast:


// Select Statement
 sprintf(sql_statements,
"select lp.%s, lp.%s, lp.%s, lp.%s, pb.%s from %s lp, %s pb "
"where lp.%s > ? and lp.%s=pb.%s "
"order by lp.%s, lp.%s, pb.%s",
ARTID_LATENT_PARENT_XPOS,
ARTID_LATENT_PARENT_YPOS,
ARTID_LATENT_PARENT_RADIUS_OF_INFLUENCE,
ARTID_LATENT_PARENT_FR_TIME_STAMP,
ARTID_LATENT_PARENT_PIXEL_BIN_NUM,
ARTID_LATENT_PARENT_TABLE,
ARTID_LATENT_PARENT_PIXEL_BIN_TABLE,
ARTID_LATENT_PARENT_FR_TIME_STAMP,
ARTID_LATENT_PARENT_SOURCE_ID,
ARTID_LATENT_PARENT_SOURCE_ID,
ARTID_LATENT_PARENT_XPOS,
ARTID_LATENT_PARENT_YPOS,
ARTID_LATENT_PARENT_PIXEL_BIN_NUM);


When concurrency is introduced (simply running the query on several  
processors against the same database - say 300 instances of it) causes  
a massive slow down with significant fluctuations in time between  
instances. Only reads are done (i.e. queries) - no writes. How does  
one optimize concurrency in sqlite3

The database is opened as follows:

//sql_rc = sqlite3_open(database_name, _db);
 sql_rc = sqlite3_open_v2(database_name,
 _db,
 SQLITE_OPEN_READONLY,
 NULL);
 if (sql_rc != SQLITE_OK) {

   fprintf(stderr, "Function:%s can't open database:%s, error:%s\n",
  FLAG_ON_LATENT, database_name,
  sqlite3_errmsg(sql_db));

   sqlite3_close(sql_db);

   return(FAIL);
 }

-rosemary.





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

Re: [sqlite] Database Corruption

[D. Richard Hipp]

On May 5, 2009, at 4:09 PM, Jacob Siegel wrote:

>
> Hi,
>
>
>I have found what I believe to be a bug in SQLite.  It is  
> reproducible
> fairly easily (though the database file is not small).
>
>   Essentially, with a non-corrupt database, and auto_vaccum enabled,  
> I can
> run a series of delete statements (through the command line tool)  
> that cause
> the database to become corrupt every time.  I'm not sure if this is  
> the
> right forum for the issue report, and I'm not certain where to put the
> database file (as it is ~200MB), however if someone here knows how  
> to report
> the issue correctly, I would much appreciate it.


Thank you for sending me the database file that is giving you problems.

This is not a bug in SQLite.  It is reporting "database disk image is  
malformed" errors because, in fact, the original database disk (the  
one you sent me) is malformed.  You can see this by running "PRAGMA  
integrity_check" on  the file to get this output:

*** in database main ***
Page 118797 is never used
Page 153960 is never used
Page 161000 is never used
Page 168034 is never used
Page 168035 is never used
Page 175070 is never used
Page 182111 is never used

The corruption here seems relatively harmless.  Unused pages are what  
they say:  pages which are never used.  Most of the time this causes  
no more trouble than taking up some extra space on disk.  You can  
always repair such corruption by running VACUUM.

But you have auto_vacuum turned on.  The way auto_vacuum works is that  
every time you delete enough information to completely free one page,  
the newly freed page is swapped with the last page in the database and  
then the database file is truncated.  The error message you are  
getting is when the file tries to shrink below 182111 pages, and thus  
tries to swap a newly freed page with unused page number 182111.   
SQLite looks at page 182111 to see what "kind" of page it is so that  
it can relink some pointer in order to swap it down to a lower page  
number.  But, because page 182111 is unused, SQLite cannot figure out  
what kind of page it is.  So SQLite does the right thing:  It reports  
database corruption.

To repeat:  Unused pages are entirely harmless as long as you do not  
have auto_vacuum enabled.  The VACUUM command uses a very different  
process and could not care less about unused pages.  In fact, the  
VACUUM command will do an excellent job of removing unused pages from  
the database file.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Large SQLite3 Database Memory Usage

[Virgilio Alexandre Fornazin]

Try to run those queries on sqlite3 program.
Then compare memory working sets between your IIS Process and sqlite3 shell.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:25
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 I am using SQLite ODBC Driver. How can I find if its leak in my provider???

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Alexandre
Fornazin
Sent: Tuesday, May 05, 2009 12:23 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Are you using ADO or plain sqlite dll ?
May this be a leak in your provider ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage


  I am running the query with CursorLocation = 2,  CursorType = 2, LockType
= 3 to handle 610MB database with 2259207 records in table.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 How much RAM is needed to handle 610MB database with 2259207 records in
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If I
run my application once the query returns results in 80 sec and  if run it
again , memory usage starts growing and reaches nearly 100% ..then I get
more hard page faults , the application responds slow, or system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still facing
problem after running the page multiple times.So I think I should try with
1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect deletes,
you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID
>= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by
ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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-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

Re: [sqlite] SQL error: database disk image is malformed

[Kees Nuyt]

On Tue, 5 May 2009 16:55:42 -0400, Pavel Ivanov
 wrote:

>Is it just me or somebody else is
>seeing too that the sql statement
> "select blobid, fbid from sig group by peerid" 
>is invalid and shouldn't be 
>executed or prepared at all?

You are right, it doesn't make sense.


@Joannek: When using group by, your select columns can only
use aggregate functions and the columns you group by.
Perhaps you meant to use ORDER BY here ?


>Pavel
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check the healthy of database and the indexesofthe tables

[Griggs, Donald]

Joanne,

I don't want to imply that you *have* to take my suggested course of
action (and probably MOST of the folks on this list know better sqlite
than I), but, nonetheless, I'm still wondering about the results of my
suggestions.   Maybe if you could either give your results for each, or
a short reason why you don't think it's a good thing to try -- or even
just say you *choose* not to try it --- by providing the list with this
information it might greatly shorten your resolution time and the number
of emails.   As it stands, I don't even know if you've read them all.

Specifically,
1-- See if the problem can be reproduced using the command-line utility.
2-- Does adding "peerid" to your second select run without error?
3-- Do the suggestions for removing the index and rebuilding it (AFTER
saving your current database) sound reasonable?   Might a VACUUM be good
as well?

A new one:
   4-- If above does not resolve the difficulty, I would try a .dump
command on each of my tables.  

Hope this is some help,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL error: database disk image is malformed

[Pavel Ivanov]

Is it just me or somebody else is seeing too that the sql statement
"select blobid, fbid from sig group by peerid" is invalid and
shouldn't be executed or prepared at all?

Pavel

On Mon, May 4, 2009 at 2:51 PM, Joanne Pham  wrote:
> Hi All,
> I ran the following sql statement:
>     select blobid, fbid from sig group by peerid;
> return about 10 rows
>         22
>    ...
>    33
> return about 10 rows and I got the error message:
>  SQL error: database disk image is malformed
>
> but when I ran the following sql statement:
>     select blobid, fbid from sig;
> I didn't see any error message. So why the first sql statement has problem 
> but not the second.
> Thanks
> JP
>
>
>
> ___
> 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] How to check the healthy of database and the indexes ofthe tables

[Joanne Pham]

Thanks Donald,
Ran  "PRAGMA integrity_check;" and it turned "ok" but the select statement 
using the select return the error message that "...malformed".
I am using sqlite3.5.9.
Thanks,
JP





From: "Griggs, Donald" 
To: General Discussion of SQLite Database 
Sent: Tuesday, May 5, 2009 1:39:08 PM
Subject: Re: [sqlite] How to check the healthy of database and the indexes 
ofthe tables

Hello Joanne,

Regarding:  "Is there any command to check if the index or database in good 
condition."

That's why I listed the pragma below in my email of last night and repeat email 
earlier.

You're in luck if only the index is corrupted of course.

SAVE a copy of your current database (very important).

Try dropping the index and rebuilding it.
Even if ok at that point, you might want to then run a VACUUM.

==

Joanne,

I couldn't say, but if I were you I'd probably

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?  Does "Explain query plan" show that it 
is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

Regards,
  Donald
      

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Tuesday, May 05, 2009 4:02 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to check the healthy of database and the indexes ofthe 
tables

Hi All,
I had the database and one of the index is not good condition. Every time I use 
the index by select ... group by .. the result only return few rows and the 
message print  out that "database disk image is malformed". Is there any 
command to check if the index or database in good condition.
Thanks,
JP


      
___
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] How to check the healthy of database and the indexes of the tables

[Joanne Pham]

Thanks,
I used sqlite 3.5.9 but when I ran PRAGMA integrity_check; it returns OK but 
one of the index has problem.
Thanks,
JP

 




From: Kees Nuyt 
To: sqlite-users@sqlite.org
Sent: Tuesday, May 5, 2009 1:30:58 PM
Subject: Re: [sqlite] How to check the healthy of database and the indexes of 
the tables

On Tue, 5 May 2009 13:02:13 -0700 (PDT), Joanne Pham
 wrote:

>Hi All,
>  I had the database and one of the index is
>not good condition. Every time I use the
>index by select ... group by .. the result
>only return few rows and the message print
>out that "database disk image is malformed".
>  Is there any command to check if the index
>or database in good condition.

PRAGMA integrity_check;
http://www.sqlite.org/pragma.html#debug

>Thanks,
>JP
-- 
  (  Kees Nuyt
  )
c[_]
___
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] How to check the healthy of database and the indexes ofthe tables

[Griggs, Donald]

Hello Joanne,

Regarding:  "Is there any command to check if the index or database in good 
condition."

That's why I listed the pragma below in my email of last night and repeat email 
earlier.

You're in luck if only the index is corrupted of course.

SAVE a copy of your current database (very important).

Try dropping the index and rebuilding it.
Even if ok at that point, you might want to then run a VACUUM.

==

Joanne,

I couldn't say, but if I were you I'd probably

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?   Does "Explain query plan" show that 
it is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

Regards,
  Donald
   

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Tuesday, May 05, 2009 4:02 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to check the healthy of database and the indexes ofthe 
tables

Hi All,
I had the database and one of the index is not good condition. Every time I use 
the index by select ... group by .. the result only return few rows and the 
message print  out that "database disk image is malformed". Is there any 
command to check if the index or database in good condition.
Thanks,
JP


  
___
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] How to check the healthy of database and the indexes of the tables

[Kees Nuyt]

On Tue, 5 May 2009 13:02:13 -0700 (PDT), Joanne Pham
 wrote:

>Hi All,
>  I had the database and one of the index is
>not good condition. Every time I use the
>index by select ... group by .. the result
>only return few rows and the message print
>out that "database disk image is malformed".
>  Is there any command to check if the index
>or database in good condition.

PRAGMA integrity_check;
http://www.sqlite.org/pragma.html#debug

>Thanks,
>JP
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Corruption

[D. Richard Hipp]

On May 5, 2009, at 4:09 PM, Jacob Siegel wrote:

>
> Hi,
>
>
>I have found what I believe to be a bug in SQLite.  It is  
> reproducible
> fairly easily (though the database file is not small).
>
>   Essentially, with a non-corrupt database, and auto_vaccum enabled,  
> I can
> run a series of delete statements (through the command line tool)  
> that cause
> the database to become corrupt every time.  I'm not sure if this is  
> the
> right forum for the issue report, and I'm not certain where to put the
> database file (as it is ~200MB), however if someone here knows how  
> to report
> the issue correctly, I would much appreciate it.


Can you compress the database file and send it to me directly, or make  
it available to me to download from someplace?  Also please send the  
sequence of commands you are using and the version number of the  
SQLite you are using.


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] Database Corruption

[Jacob Siegel]

Hi,
 

I have found what I believe to be a bug in SQLite.  It is reproducible
fairly easily (though the database file is not small).

   Essentially, with a non-corrupt database, and auto_vaccum enabled, I can
run a series of delete statements (through the command line tool) that cause
the database to become corrupt every time.  I'm not sure if this is the
right forum for the issue report, and I'm not certain where to put the
database file (as it is ~200MB), however if someone here knows how to report
the issue correctly, I would much appreciate it.

   Thanks,
 Jacob

-- 
View this message in context: 
http://www.nabble.com/Database-Corruption-tp23395003p23395003.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] How to check the healthy of database and the indexes of the tables

[Joanne Pham]

Hi All,
I had the database and one of the index is not good condition. Every time I use 
the index by select ... group by .. the result only return few rows and the 
message print  out that "database disk image is malformed". Is there any 
command to check if the index or database in good condition.
Thanks,
JP


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

Re: [sqlite] Large SQLite3 Database Memory Usage

[Kalyani Phadke]

 I am using SQLite ODBC Driver. How can I find if its leak in my provider???

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Virgilio Alexandre Fornazin
Sent: Tuesday, May 05, 2009 12:23 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Are you using ADO or plain sqlite dll ?
May this be a leak in your provider ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage


  I am running the query with CursorLocation = 2,  CursorType = 2, LockType = 3 
to handle 610MB database with 2259207 records in table.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 How much RAM is needed to handle 610MB database with 2259207 records in 
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If I run 
my application once the query returns results in 80 sec and  if run it again , 
memory usage starts growing and reaches nearly 100% ..then I get more hard page 
faults , the application responds slow, or system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still facing 
problem after running the page multiple times.So I think I should try with 1GB 
of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite still 
has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect deletes, 
you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID >= 
(select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by ID 
desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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-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] Large SQLite3 Database Memory Usage

[Virgilio Alexandre Fornazin]

Are you using ADO or plain sqlite dll ?
May this be a leak in your provider ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage


  I am running the query with CursorLocation = 2,  CursorType = 2,
LockType = 3 to handle 610MB database with 2259207 records in table.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 How much RAM is needed to handle 610MB database with 2259207 records in
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If
I run my application once the query returns results in 80 sec and  if
run it again , memory usage starts growing and reaches nearly 100%
..then I get more hard page faults , the application responds slow, or
system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still
facing problem after running the page multiple times.So I think I should
try with 1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect
deletes, you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where
Events.ID >= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order
by ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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-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] Large SQLite3 Database Memory Usage

[Kalyani Phadke]

  I am running the query with CursorLocation = 2,  CursorType = 2,
LockType = 3 to handle 610MB database with 2259207 records in table.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 How much RAM is needed to handle 610MB database with 2259207 records in
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If
I run my application once the query returns results in 80 sec and  if
run it again , memory usage starts growing and reaches nearly 100%
..then I get more hard page faults , the application responds slow, or
system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still
facing problem after running the page multiple times.So I think I should
try with 1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect
deletes, you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where
Events.ID >= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order
by ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Large SQLite3 Database Memory Usage

[Kalyani Phadke]

 How much RAM is needed to handle 610MB database with 2259207 records in
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If
I run my application once the query returns results in 80 sec and  if
run it again , memory usage starts growing and reaches nearly 100%
..then I get more hard page faults , the application responds slow, or
system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still
facing problem after running the page multiple times.So I think I should
try with 1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect
deletes, you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where
Events.ID >= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order
by ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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] one or many tables, what gives the best performance ?

[Stef Mientki]

Hello,

Sorry I'm not very familiar with databases, so I probably don't use the 
right terms.

I wonder what's the best approach in my specific case:

I have a table "A",
in this table "A", each row is connected to about 50 rows of other 
information.

These 50 rows of other information have an identical structure for all 
rows in table "A".

The  probability that one of these 50 rows is identical to another row 
in other sets of 50 rows is approximately zero.

So I have 2 choices:
1. create 1 table with all the sets of 50 rows, by adding a reference in 
each row to the corresponding line in table "A"
2. create a separate table for each set of 50 rows
(3. I could flatten the data, so I only will have a huge table "A", but 
that doesn't sound very efficient to me)

Maybe there's one other issue, that might influence the choice,
and that's how I use the data:
when I extract data from this database,
- I search for a row in table "A"
- I read all the 50 extra rows of other information

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

Re: [sqlite] Large SQLite3 Database Memory Usage

[Kalyani Phadke]

 Thanks for your response. The query runs faster than mine but still
facing problem after running the page multiple times.So I think I should
try with 1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect
deletes, you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where
Events.ID >= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order
by ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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] Large SQLite3 Database Memory Usage

[Kalyani Phadke]

 If I access other pages in my application ,it works fine. Its only this page 
with query given that's causing problem.I also tried running the query from 
sqlite studio query editor. It beahves the same . So I think its Sqlite and not 
the webserver.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ken
Sent: Tuesday, May 05, 2009 10:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage


Upgrade your ram.

Are you sure its sqlite using all the memory and not the WebServer?

Do you have an index on Events.NotificationTime ?





--- On Tue, 5/5/09, Kalyani Phadke  wrote:

> From: Kalyani Phadke 
> Subject: [sqlite] Large SQLite3 Database Memory Usage
> To: sqlite-users@sqlite.org
> Date: Tuesday, May 5, 2009, 11:36 AM
> I have 2259207 records in table using
> SQLite3 database. I am running the
> select query to retrive records from DB
>  
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>  
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or 
> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on 
> ID and notificationtime (datatype timestamp) 
> ___
> 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] Large SQLite3 Database Memory Usage

[Ken]

Upgrade your ram.

Are you sure its sqlite using all the memory and not the WebServer?

Do you have an index on Events.NotificationTime ?





--- On Tue, 5/5/09, Kalyani Phadke  wrote:

> From: Kalyani Phadke 
> Subject: [sqlite] Large SQLite3 Database Memory Usage
> To: sqlite-users@sqlite.org
> Date: Tuesday, May 5, 2009, 11:36 AM
> I have 2259207 records in table using
> SQLite3 database. I am running the
> select query to retrive records from DB
>  
> SELECT ID, EventClassName, EventClassRef, TransitionTime,
> Message,
> MonitoredRef, EventRef,ToState,Priority,Acked from
> Events  WHERE
> Events.NotificationTime >= {ts '2009-05-04
> 14:44:10'}  Order By ID DESC
> LIMIT 100
>  
> If I run my application once the query returns results in
> 80 sec and if
> run it again , memory usage starts growing and reaches
> nearly 100%
> ..then I get more hard page faults , the application
> responds slow, or
> system just hangs. I am running my application on Windows
> web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have
> indexes on
> ID and notificationtime (datatype timestamp)
> ___
> 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 and GHS Integrity Issues

[Christopher Taylor]

I cannot seem to get anywhere with this.  The heap usage for the file system
address space in my embedded app continues to rise until the file system
crashes due to no more available RAM.  In February 2008 someone mentioned
issues with a mismatch on number of locks versus unlocks.  Was this ever
resolved?  Any help would be appreciated.  Thanks.

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

Re: [sqlite] Large SQLite3 Database Memory Usage

[BareFeet]

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running  
> the
> select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID  
> DESC
> LIMIT 100
>
> If I run my application once the query returns results in 80 sec and  
> if
> run it again , memory usage starts growing and reaches nearly 100%
> ..then I get more hard page faults , the application responds slow, or
> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on
> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100
since SQLite still has to build and sort the entire result table  
before limiting it. You are better off to filter using the search (ie  
where).

For instance, if ID is the integer primary key and you don't expect  
deletes, you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,  
MonitoredRef, EventRef, ToState, Priority, Acked
from Events
where Events.ID >= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'}
order by ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

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

[sqlite] Large SQLite3 Database Memory Usage

[Kalyani Phadke]

I have 2259207 records in table using SQLite3 database. I am running the
select query to retrive records from DB
 
SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE
Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID DESC
LIMIT 100
 
If I run my application once the query returns results in 80 sec and if
run it again , memory usage starts growing and reaches nearly 100%
..then I get more hard page faults , the application responds slow, or
system just hangs. I am running my application on Windows web server
2008 with 512 MB RAM. How can I optimize the query ? I have indexes on
ID and notificationtime (datatype timestamp)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max Value In Group By Scenario

[epankoke]

Thanks. 



- Original Message - 
From: "Igor Tandetnik"  
To: sqlite-users@sqlite.org 
Sent: Tuesday, May 5, 2009 8:05:42 AM GMT -05:00 US/Canada Eastern 
Subject: Re: [sqlite] Max Value In Group By Scenario 

"Eric Pankoke"  wrote 
in message news:6a90e70a6f404948991a0380e6793...@gamesmachine 
The following query: 
> 
> SELECT MfgProductID, ProductID, SUM(Weight) As MaxWeight FROM 
> dominantmaterials GROUP BY MfgProductID, ProductID 
> 
> Gets me most of the way to what I need. What I'm really after, 
> however, is the ProductID for each MfgProductID that has the greatest 
> combined weight from all MfgProductID / ProductID rows. 

select m, p, 
(select sum(Weight) from dominantmaterials 
where MfgProductId=m and ProductID=p) w 
from 
(select m, 
(select ProductID from dominantmaterials 
where MfgProductId = m 
group by ProductID 
order by sum(Weight) desc limit 1) p 
from 
(select distinct MfgProductId m from dominantmaterials)); 

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] [newbie] How to upgrade sqlite3 in Ubuntu?

[Eric Bohlman]

Derrell Lipman wrote:
> The amalgamation probably installed into some directory not in your path.
> You should look at where it installed (re-run ../configure and look at its
> output, which should tell you where it will install to. For Ubuntu, you
> almost certainly want it to install into /usr/local with the executable
> going into /usr/local/bin. If it chose some path other than /usr/local, you
> probably want to remove it from wherever it installed to.
> 
> Next, remove the Ubuntu-provided version of sqlite3 since you won't need it
> any longer:
> 
>   sudo apt-get remove sqlite3

The amalgamation builds by default into /usr/local, but the apt package 
goes into /usr. Since some other packages have sqlite3 as a dependency, 
it's best not to remove the original install, but rather to overwrite 
it; otherwise installing new packages may end up "restoring" the old 
version. Simply configure with --prefix=/usr.

Note that the main problem isn't the search path for executables; it's 
the search path for dynamic libraries.

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

Re: [sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?

[Ken]

Dan... Yes the varint!

--- On Mon, 5/4/09, Dan  wrote:

> From: Dan 
> Subject: Re: [sqlite] OT: how best to convert sqlite3_int64 to and from 
> string in a cross platform fashion?
> To: "General Discussion of SQLite Database" 
> Date: Monday, May 4, 2009, 11:52 PM
> 
> On May 4, 2009, at 10:14 PM, Ken wrote:
> 
> >
> > You could just pass the sqlite3_int64 value. It is
> portable between  
> > systems. Search through the sqlite3 code and there are
> routines that  
> > do the conversions from the sqlite3_int64 to a native
> int64 type.  
> > They basically perform bit shifting and will be much
> faster than the  
> > string conversions.
> 
> 
> You're thinking of the varint format used in the
> file-format:
> 
>    http://www.sqlite.org/fileformat.html#varint_format
> 
> sqlite3_int64 is just a typdef of int64_t on most
> platforms.
> 
> Dan.
> 
> 
> 
> 
> >
> >
> > HTH,
> > Ken
> >
> > --- On Sun, 5/3/09, Sam Carleton 
> wrote:
> >
> >> From: Sam Carleton 
> >> Subject: [sqlite] OT: how best to convert
> sqlite3_int64 to and from  
> >> string in a cross platform fashion?
> >> To: "General Discussion of SQLite Database" 
> >> Date: Sunday, May 3, 2009, 4:21 PM
> >> I am current developing a system only
> >> on Windows, but I do plan to port it
> >> to OSX someday.  I am passing ID's as strings
> to keep
> >> maximum flexibility
> >> between databases and the existing system. 
> So how do
> >> I convert a
> >> sqlite3_int64 to a string and a string to a
> sqlite3_int64
> >> in a cross
> >> platform fashion?
> >>
> >> Sam
> >> ___
> >> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL error: database disk image is malformed

[Griggs, Donald]

Hi Joanne,

It's possible you'll have to resort to your most recent backup.   But before 
doing that, I wasn't sure from your reply that you saw the other points I 
listed. 

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?  Does "Explain query plan" show that it 
is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

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

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

Thanks for the reply. SQLITE_MISUSE would make sense in the scenario you 
describe. But it is difficult for me to see how I could be calling prepare with 
an unopened or closed connection. I am basically in the process of executing a 
series of statements against an open database that is exclusive to the thread. 
Everything succeeds until a random point. Is the database being closed somehow, 
and I am not realizing it? Or are my threads interacting in a way I've not 
considered?


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, May 04, 2009 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Beau Wilkinson  wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle
to sqlite3_prepare.

Igor Tandetnik



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat of empty strings with separator bug?

[Steve Bauer]

Many thanks for:

1) the very fast response
2) the very active development
3) the pointer to the bug report


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of D. Richard Hipp
Sent: Tuesday, May 05, 2009 9:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] group_concat of empty strings with separator bug?


On May 5, 2009, at 9:26 AM, Steve Bauer wrote:

> The behavior of group_concat for empty strings isn't what I expected.

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

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

Re: [sqlite] group_concat of empty strings with separator bug?

[D. Richard Hipp]

On May 5, 2009, at 9:26 AM, Steve Bauer wrote:

> The behavior of group_concat for empty strings isn't what I expected.

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

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] group_concat of empty strings with separator bug?

[Steve Bauer]

The behavior of group_concat for empty strings isn't what I expected.  This 
looks to me like a bug.  Would the sqlite developers consider it to be one? 

sqlite> .nullvalue VISIBLENULL
sqlite> CREATE table example (id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT);
sqlite> INSERT INTO example(a) VALUES ("");
sqlite> INSERT INTO example(a) VALUES ("");
sqlite> INSERT INTO example(a) VALUES ("");
sqlite> select * from example;
1|
2|
3|
sqlite> select group_concat(a, "|") from example;
VISIBLENULL
sqlite> INSERT INTO example(a) VALUES ("a");
sqlite> INSERT INTO example(a) VALUES ("b");
sqlite> INSERT INTO example(a) VALUES ("c");
sqlite> select group_concat(a, "|") from example;
a|b|c
sqlite> select * from example;
1|
2|
3|
4|a
5|b
6|c

I expected "||" in the first case and "|||a|b|c" in the second.

In python the equivalent group concatenation of lists results in what I expect:

In [2]: "|".join(["","",""])
Out[2]: '||'

In [3]: "|".join(["","","","a","b","c"])
Out[3]: '|||a|b|c'




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

Re: [sqlite] Max Value In Group By Scenario

[Igor Tandetnik]

"Eric Pankoke"  wrote
in message news:6a90e70a6f404948991a0380e6793...@gamesmachine
The following query:
>
> SELECT MfgProductID, ProductID, SUM(Weight) As MaxWeight FROM
> dominantmaterials GROUP BY MfgProductID, ProductID
>
> Gets me most of the way to what I need.  What I'm really after,
> however, is the ProductID for each MfgProductID that has the greatest
> combined weight from all MfgProductID / ProductID rows.

select m, p,
  (select sum(Weight) from dominantmaterials
   where MfgProductId=m and ProductID=p) w
from
  (select m,
 (select ProductID from dominantmaterials
  where MfgProductId = m
  group by ProductID
  order by sum(Weight) desc limit 1) p
   from
 (select distinct MfgProductId m from dominantmaterials));

Igor Tandetnik 



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

Re: [sqlite] SQLite and GHS Integrity Issues

[Christopher Taylor]

After running a night of tests I see that the VFS address space has run out
of memory.  Searching on this I have found a post from February which said
there was an issue with the number of locks/unlocks not matching.  I did not
actually see a resolution in that thread.  I am going to try and investigate
further.

Chris


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