Re: [sqlite] Virtual Memory Size used looks too large

2014-02-19 Thread Woody Wu 
On 20 February 2014 04:35, Clemens Ladisch  wrote:
> Woody Wu wrote:
>> On 19 February 2014 23:09, Clemens Ladisch  wrote:
>>> Woody Wu wrote:
 sqlite is using mmap to map the database file?
>>
>> How do I confirm that this is true on Linux platform?
>
> PRAGMA mmap_size
>
 If this is the reason, how can I reduce the VSZ used?
>>>
>>> You could disable mmap.
>>
>> How do disable it? And what are possible disadvantages if I do it?
>
> see 

Surprised after read the doc.  What I used is sqlite 3.7.5, but the
doc says that memory-mapped io only started from 3.7.17.  I also
checked 3.7.5 source code and found it really does not have concept
such as "pragma mmap_size", sqlite_config_mmap_size" and so on.

So, what are used for the huge VSZ in my sqlite application?  Whenn
monitor the program, I fund the VSZ of my application can changes from
70M to 200+M.

>
>>> But why would you want to do this?  The size of the virtual address
>>> space is 4 GB, so I doubt you are running out of that.
>>
>> Hum, I understand you are right.  But I just wan to ensure ...
>
> Ensure what?  VSZ is just a number.

The OS is an embed Linux 3.1.x.  I am afraid, when the VSZ of a
process goes too large, it could trigger some kind of system bug.  By
far, I've been always bothered with a sqlite "disk image malformed"
issue after my application run a long time.  I still have not yet
found the root cause of it.  So, every suspicious place I want to
check.




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



-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trying to use in-memory database

2014-02-19 Thread Richard Hipp 
On Wed, Feb 19, 2014 at 5:25 PM, Jeff Archer <
jsarc...@nanotronicsimaging.com> wrote:

> Long time SQLite user but I don't think I have ever tried to do an
> in-memory database before.
> Just upgraded to 3.8.3.1 but I am not having any other failures with
> existing code so I don't think that is any part of the problem.
> I am trying to open the database with sqlite3_open16() using a filename of
> L":MEMORY:".  I am getting a result code of 14 which is SQLITE_CANTOPEN.
> It does open if I use a real filename.
>

The magic name is case-sensitive and lower-case.  Use L":memory:" and it
should work for you.


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



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

[sqlite] Trying to use in-memory database

2014-02-19 Thread Jeff Archer 
Long time SQLite user but I don't think I have ever tried to do an
in-memory database before.
Just upgraded to 3.8.3.1 but I am not having any other failures with
existing code so I don't think that is any part of the problem.
I am trying to open the database with sqlite3_open16() using a filename of
L":MEMORY:".  I am getting a result code of 14 which is SQLITE_CANTOPEN.
It does open if I use a real filename.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Memory Size used looks too large

2014-02-19 Thread Clemens Ladisch 
Woody Wu wrote:
> On 19 February 2014 23:09, Clemens Ladisch  wrote:
>> Woody Wu wrote:
>>> sqlite is using mmap to map the database file?
>
> How do I confirm that this is true on Linux platform?

PRAGMA mmap_size

>>> If this is the reason, how can I reduce the VSZ used?
>>
>> You could disable mmap.
>
> How do disable it? And what are possible disadvantages if I do it?

see 

>> But why would you want to do this?  The size of the virtual address
>> space is 4 GB, so I doubt you are running out of that.
>
> Hum, I understand you are right.  But I just wan to ensure ...

Ensure what?  VSZ is just a number.


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

Re: [sqlite] select/count/group by question

2014-02-19 Thread Patrick Proniewski 
> Message: 10
> Date: Wed, 19 Feb 2014 09:11:27 -0500
> From: Igor Tandetnik 
> 
> select strftime(...) as year, sum(ISO=100)
> FROM exifdata GROUP BY year ORDER BY year;
> 
> --
> 
> Message: 11
> Date: Wed, 19 Feb 2014 09:16:56 -0500
> From: Richard Hipp 

> Maybe this:
> 
> SELECT strftime('%Y', CreateDate, 'unixepoch') AS year,
> sum(iso==100)
> FROM exifdata WHERE year NOT NULL
> GROUP BY year
> ORDER BY year;
> 
> The main trick is to use "sum(iso==100)" instead of "count(iso)".  Note
> also that SQL strings use a single quote, not a double-quote.  And the
> datetime() function call is redundant as strftime can do the unixepoch
> conversion for you.  And you don't need to GROUP BY iso, since you are only
> interested in a single iso value.


Thank you Igor and Richard for your fast replies.
It solved my problem.

I thought about using sum() or total() but the ISO==100 part is something I 
would never have thought about.

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

Re: [sqlite] Virtual Memory Size used looks too large

2014-02-19 Thread Woody Wu 
On 19 February 2014 23:09, Clemens Ladisch  wrote:
> Woody Wu wrote:
>> I found my sqlite application presents a huge VSZ usage, 100+M in an
>> ARM system, which has only 128M physical memory.  Does this because
>> sqlite is using mmap to map the database file?
>
> This is likely.

Glad to hear that :)  How do I confirm that this is true on Linux platform?

>
>> If this is the reason, how can I reduce the VSZ used?
>
> You could disable mmap.

How do disable it? And what are possible disadvantages if I do it?


>
> But why would you want to do this?  The size of the virtual address
> space is 4 GB, so I doubt you are running out of that.

Hum, I understand you are right.  But I just wan to ensure ...  Many
thanks Clemens ~

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



-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Memory Size used looks too large

2014-02-19 Thread Clemens Ladisch 
Woody Wu wrote:
> I found my sqlite application presents a huge VSZ usage, 100+M in an
> ARM system, which has only 128M physical memory.  Does this because
> sqlite is using mmap to map the database file?

This is likely.

> If this is the reason, how can I reduce the VSZ used?

You could disable mmap.

But why would you want to do this?  The size of the virtual address
space is 4 GB, so I doubt you are running out of that.


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

[sqlite] Virtual Memory Size used looks too large

2014-02-19 Thread Woody Wu 
Hi, List

I found my sqlite application presents a huge VSZ usage, 100+M in an
ARM system, which has only 128M physical memory.  Does this because
sqlite is using mmap to map the database file?  If this is the reason,
how can I reduce the VSZ used?

I checked my application with Valgrind, I think there is no memory
leak in the app.

-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select/count/group by question

2014-02-19 Thread Richard Hipp 
On Wed, Feb 19, 2014 at 9:04 AM, Patrick Proniewski wrote:

> Hello,
>
> I'm currently designing a small tool with shell scripts, sqlite3 backend,
> and gnuplot for graphical output.
> My SQLite database has one table, each row being a picture name and a
> subset of its EXIF tags (date, ISO, metering mode...).
> In order to process data into gnuplot, I use SELECT to output what I need
> in a flat file.
>
> Example:
>
> For ISO = 100, I issue this request:
>
> SELECT strftime("%Y", datetime(CreateDate,"unixepoch")) as year,
> COUNT(ISO) FROM exifdata WHERE ISO = 100 AND year not null GROUP BY ISO,
> year ORDER BY year;
>
> It returns :
>
> 2008 9
> 2009 581
> 2010 3144
> 2011 768
> 2012 1195
> 2013 1270
>
> That is correct, but not enough: the database includes pictures taken in
> 2014, none of which at ISO = 100, but I need a line "2014 0" in my output
> (otherwise gnuplot mixes things when it draws stacked histograms).
>
> My question is: how can I write a request that will successfully pull a
> result for every years, even if the count is 0?
>

Maybe this:

SELECT strftime('%Y', CreateDate, 'unixepoch') AS year,
sum(iso==100)
FROM exifdata WHERE year NOT NULL
GROUP BY year
ORDER BY year;

The main trick is to use "sum(iso==100)" instead of "count(iso)".  Note
also that SQL strings use a single quote, not a double-quote.  And the
datetime() function call is redundant as strftime can do the unixepoch
conversion for you.  And you don't need to GROUP BY iso, since you are only
interested in a single iso value.



>
> I've created a second table "years", with only one column ("year"), rows
> are "2008", "2009"..., "2014". Then I've tried using JOIN to get at least a
> NULL result, without success.
>
> Full table with create statement is available here:
> http://patpro.net/~patpro/exifdata.sql.zip (106 kB).
> Any hint appreciated!
>
> Thanks,
> Patrick
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] select/count/group by question

2014-02-19 Thread Igor Tandetnik 

On 2/19/2014 9:04 AM, Patrick Proniewski wrote:

For ISO = 100, I issue this request:

SELECT strftime("%Y", datetime(CreateDate,"unixepoch")) as year, COUNT(ISO) 
FROM exifdata WHERE ISO = 100 AND year not null GROUP BY ISO, year ORDER BY year;

That is correct, but not enough: the database includes pictures taken in 2014, none of 
which at ISO = 100, but I need a line "2014 0" in my output


select strftime(...) as year, sum(ISO=100)
FROM exifdata GROUP BY year ORDER BY year;

--
Igor Tandetnik

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

[sqlite] select/count/group by question

2014-02-19 Thread Patrick Proniewski 
Hello,

I'm currently designing a small tool with shell scripts, sqlite3 backend, and 
gnuplot for graphical output.
My SQLite database has one table, each row being a picture name and a subset of 
its EXIF tags (date, ISO, metering mode...).
In order to process data into gnuplot, I use SELECT to output what I need in a 
flat file.

Example: 

For ISO = 100, I issue this request: 

SELECT strftime("%Y", datetime(CreateDate,"unixepoch")) as year, COUNT(ISO) 
FROM exifdata WHERE ISO = 100 AND year not null GROUP BY ISO, year ORDER BY 
year;

It returns :

2008 9
2009 581
2010 3144
2011 768
2012 1195
2013 1270

That is correct, but not enough: the database includes pictures taken in 2014, 
none of which at ISO = 100, but I need a line "2014 0" in my output (otherwise 
gnuplot mixes things when it draws stacked histograms).

My question is: how can I write a request that will successfully pull a result 
for every years, even if the count is 0?

I've created a second table "years", with only one column ("year"), rows are 
"2008", "2009"..., "2014". Then I've tried using JOIN to get at least a NULL 
result, without success.

Full table with create statement is available here: 
http://patpro.net/~patpro/exifdata.sql.zip (106 kB).
Any hint appreciated!

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

Re: [sqlite] SQLite for Windows Phone bug

2014-02-19 Thread Richard Hipp 
Have you activated the error and warning log to see if it gives you any
further diagnostics?

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


On Wed, Feb 19, 2014 at 12:06 AM, Martin Zikmund wrote:

> Hello, SQLite team,
>
>
> I would like to report a bug in the Windows Phone version of SQLite, that
> is already present for three releases in the row. The problem arises quite
> randomly, when SQLite refuses to insert or update some items into database
> and throws the following error: SQL logic error or missing database . I
> have checked the SQL and that database is well present, but the problem
> persists. The situation is even worse because the inserts and updates that
> fail seem to be completely random - if I compare an entry that fails and
> one that doesn't, there is nothing that should be taken as a reason for the
> error. I'm not the only one experiencing this -
> http://stackoverflow.com/questions/20474253/sqlite-3-8-2-exception-on-update-statement.
>  The interesting fact is that in the 3.8.1 version this never happened, so
> the cause must be some change between these the version 3.8.1 and version
> 3.8.2.
>
>
> Can you please check if anything can be done? My project is highly
> dependant on SQLite, but this way it is not reliable enough...
>
>
> Thank you very, very much
>
>
> Sincerely
>
>
> Martin Zikmund
>
>
>
>
>
>
> Sent from Surface Pro
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLite for Windows Phone bug

2014-02-19 Thread Simon Slavin 

On 19 Feb 2014, at 5:06am, Martin Zikmund  wrote:

> SQL logic error or missing database

The two usual causes of this under WinMob are both related to permissions and 
privileges.  The folder the database file is stored in may be protected against 
the app opening the existing database file or creating a new journal file, or 
the permissions on the database file may be protected against the app.

First make sure you are specifying the correct path.  If you didn't specify a 
full path, try temporarily doing so in your test setup and see if anything 
changes.

If that doesn't work try modifying your app to create a new text file (standard 
C file API) in the same folder as the database file normally lives.  Does it 
succeed ?

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

[sqlite] SQLite Windows Phone bug

2014-02-19 Thread Martin Zikmund 

Sorry for another mail, but I forgot to mention, that the bug seems to arise 
only in transaction. When the items are inserted individually, it seems to be 
working correctly (although I can’t confirm it 100 %).




Hello, SQLite team,




I would like to report a bug in the Windows Phone version of SQLite, that is 
already present for three releases in the row. The problem arises quite 
randomly, when SQLite refuses to insert or update some items into database and 
throws the following error: SQL logic error or missing database . I have 
checked the SQL and that database is well present, but the problem persists. 
The situation is even worse because the inserts and updates that fail seem to 
be completely random - if I compare an entry that fails and one that doesn’t, 
there is nothing that should be taken as a reason for the error. I’m not the 
only one experiencing this - 
http://stackoverflow.com/questions/20474253/sqlite-3-8-2-exception-on-update-statement
 . The interesting fact is that in the 3.8.1 version this never happened, so 
the cause must be some change between these the version 3.8.1 and version 3.8.2.




Can you please check if anything can be done? My project is highly dependant on 
SQLite, but this way it is not reliable enough…




Thank you very, very much




Sincerely




Martin Zikmund






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

[sqlite] SQLite for Windows Phone bug

2014-02-19 Thread Martin Zikmund 
Hello, SQLite team,


I would like to report a bug in the Windows Phone version of SQLite, that is 
already present for three releases in the row. The problem arises quite 
randomly, when SQLite refuses to insert or update some items into database and 
throws the following error: SQL logic error or missing database . I have 
checked the SQL and that database is well present, but the problem persists. 
The situation is even worse because the inserts and updates that fail seem to 
be completely random - if I compare an entry that fails and one that doesn’t, 
there is nothing that should be taken as a reason for the error. I’m not the 
only one experiencing this - 
http://stackoverflow.com/questions/20474253/sqlite-3-8-2-exception-on-update-statement
 . The interesting fact is that in the 3.8.1 version this never happened, so 
the cause must be some change between these the version 3.8.1 and version 3.8.2.


Can you please check if anything can be done? My project is highly dependant on 
SQLite, but this way it is not reliable enough…


Thank you very, very much


Sincerely


Martin Zikmund






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

[sqlite] Re Problem related to ticket 4c86b126f2 ?

2014-02-19 Thread Jean-Marie CUAZ 

Here is a copy and paste of the shema involved :

db1 eval {CREATE TABLE BulletinsTemp0 (\
  IDPayeMens VARCHAR(19), \
  CodSoc VARCHAR(3), \
  CodEtabliss VARCHAR(3), \
  AnneeMois VARCHAR(6), \
  Trimestre VARCHAR(1), \
  PerEmpl INTEGER, \
  SousPeriode VARCHAR(2), \
  Annee VARCHAR(4), \
  Mois VARCHAR(2), \
  Matricule VARCHAR(10), \
  CodPlan VARCHAR(3), \
  RubrUser VARCHAR(4), \
  Callback INTEGER, \
  Nature VARCHAR(12), \
  Sens INTEGER, \
  TxAssiette REAL DEFAULT 0.0, \
  Quantite REAL DEFAULT 0.0, \
  QtiteJrs REAL DEFAULT 0.0, \
  Taux REAL DEFAULT 0.0, \
  Montant REAL DEFAULT 0.0, \
  TxPatr REAL DEFAULT 0.0, \
  MontantPatronnal REAL DEFAULT 0.0, \
  Assiette REAL DEFAULT 0.0, \
  Precis REAL DEFAULT 0.0, \
  DebutRattach VARCHAR(7) DEFAULT '', \
  FinRattach VARCHAR(7) DEFAULT '', \
  Fisc INTEGER , \
  TypePrime INTEGER , \
  IndemAbs INTEGER , \
  JrAbsenceNI INTEGER , \
  MMA INTEGER , \
  TypeIDep VARCHAR(2), \
  CodRecap1 VARCHAR(10), \
  CodRecap2 VARCHAR(4), \
  CodRecap3 VARCHAR(4), \
  CodExt VARCHAR(2), \
  Ordre INTEGER DEFAULT 1, \
  Selection INTEGER DEFAULT 0, \
  PRIMARY KEY (IDPayeMens, 
SousPeriode, RubrUser) \

  ) }

There are no separate indices created on columns of BulletinsTemp0

In my preceding post, "Field1" refers to column "_CodRecap1_",
"Field2" refers to column "_RubrUser_"

The purpose of this table is to collect all historical data for a year

The process of filling BulletinsTemp0 is done in 2 steps :

1) INSERT from a table archiving old historical data from january -> 
november for the current year
2) Then INSERT from another table containing "last month" data, i.e. 
december in our case


These INSERT don't populate column CodRecap1, CodRecap2, CodRecap3, and  
CodExt. Some occurences (not all) of these column are populated 
separately from a third table "Parameters" (with column RubrUser in the 
PK) in an UPDATE statement of the following pattern :


db1 eval "SELECT Parameters.RubrUser, Parameters.CodRecap1, 
Rubriques.CodRecap2, Rubriques.CodRecap3, Rubriques.CodExt


   FROM Parameters \

WHERE (Parameters.CodRecap1 <> '')" {

db1 eval "UPDATE BulletinsTemp0 \

SET CodRecap1 = '$CodRecap1', \

CodRecap2 = '$CodRecap2', \

CodRecap3 = '$CodRecap3', \

CodExt= '$CodExt' \

WHERE (BulletinsTemp0.RubrUser = '$RubrUser')"

}


The issue is in "WHERE (BulletinsTemp0.RubrUser = '$RubrUser')" ->  
'$RubrUser' matched only the last occurence of the same value in 
BulletinsTemp0.RubrUser (december)


As said, we tried to reproduce in test scripts against new tables 
created for this purpose but with no success.


But we could reproduce the issue when running manualy (on the sql 
console of our app) the folowing :


UPDATE BulletinsTemp0 \

SET CodRecap1 = 'ZZZ' \

WHERE (BulletinsTemp0.RubrUser = '')

12 records (one per month) were present in BulletinsTemp0. whith 
BulletinsTemp0.RubrUser = '' and on each try, only the last one 
(december) was updated.
Strangely when doing from the console "SELECT 1 FROM BulletinsTemp0 
WHERE (BulletinsTemp0.RubrUser = '')", the correct answer returned 
(12 records) (sorry I forgot to say that in my first post - little tired 
currently...)


Strangely when trying to run manualy :

UPDATE BulletinsTemp0 \

SET CodRecap1 = 'ZZZ' \

WHERE (BulletinsTemp0.Nature = 'BRUT')

i.e. with a criteria on an other column than RubrUser, the instruction 
yielded the correct result.
We failed to find an other column except "RubrUser" in BulletinsTemp0  
to reproduce the pb.


Completing the info : the same

[sqlite] How to disable the use of malloc()?

2014-02-19 Thread Woody Wu 
Hi,

I want to omits the Sqlite's internal invocation of malloc(3).  What I
tried were following,

1. Provide sqlite compile time options "-DSQLITE_ZERO_MALLOC
-DSQLITE_ENABLE_MEMSYS5"
2. In the startup code of my application, call:
sqlite3_config(SQLITE_CONFIG_HEAP, my_static_array, ...)

Do you think what I did are right?  By far, the problem I met was that
I cannot successfully compile the sqlite library.  It reports
sqlite3MemMalloc, sqlite3MemFree family of functions get redefined.
After short check, I found the reason is the mem0.c and mem1.c both
got included.  I think this is not right because mem1.c is using
malloc() which is what I want to avoid.

Could someone please help me?  Thanks in advance.


-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users