[sqlite] Can't understand out of memory error

[Nemanja Čorlija]

I'm new to OS X so my apologies if solution to this problem is
obvious. I did try to google it first.
Any help or pointers in the right direction would be greatly appreciated.

My problem is that I am running out of memory when trying to load
1.6GB sqlite3 db from disk to :memmory: db on a computer with 4GB of
RAM.
This is on OS X 10.4.11 Intel with only Activity Monitor and iTerm
running, plus sqlite3 of course.

Schema of big_1.6GB_file.db3 is:
CREATE TABLE t1(col1 TEXT, col2 INT)
CREATE UNIQUE INDEX t1_idx1 ON t1(col1 COLLATE NOCASE)
Note that t1.col2 acts as a boolean filed, so it should take only 1
byte per row and not be very significant in overall db size.
t1 has 26985834 rows.

Here's a transcript of my sqlite3 session:

mbp:~/projects/sqlite3 chorlya$ ./sqlite3
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> ATTACH "big_1.6GB_file.db3" AS atch;
sqlite> CREATE TABLE t1(col1 TEXT);
sqlite> INSERT INTO t1 SELECT col1 FROM atch.t1;
sqlite> DETACH atch;
sqlite> CREATE UNIQUE INDEX t1_idx1 ON t1(col1 COLLATE NOCASE);
sqlite3(7782) malloc: *** vm_allocate(size=8421376) failed (error code=3)
sqlite3(7782) malloc: *** error: can't allocate region
sqlite3(7782) malloc: *** set a breakpoint in szone_error to debug
sqlite3(7782) malloc: *** vm_allocate(size=8421376) failed (error code=3)
sqlite3(7782) malloc: *** error: can't allocate region
sqlite3(7782) malloc: *** set a breakpoint in szone_error to debug
SQL error: out of memory
sqlite>

At this point Activity Monitor shows Real memory: 1.74 Virtual memory:
1.76 for sqlite3 process, and 423MB of free memory.
I tried sqlite3-3.5.4-osx-x86.bin from sqlite website and compiled my
own from source, and result is same in both cases.
I should also mention that I'm successfully doing this exact same
thing with same db on windows machine with 1.75GB of RAM. It does hit
the swap, but it works without any problems.


Thanks

-- 
Nemanja Čorlija <[EMAIL PROTECTED]>

Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?

[kamil.szot]

Gilles Ganault wrote:
> 
> So the options are:
> 1. use the old SQLite2 sqlite_() functions (or some class that turns this 
> into OO)
> 2. PDO to use the SQLite3 linked-in library
> 3. PDO to access the SQLite3 DLL
> 
> ... with 2 being the recommended choice.
> 

I'm new to sqlite (started to play with it something around two days ago)
but I already stumbled on some gotchas that I'd like to warn you about.

If you work with sqlite and php under linux and you want to issue queries
that change database (like UPDATE) your database file must be writable by
server, and also directory where this file is located must be writable by
webserver. Otherwise you are gonna get cryptic error "SQL logic error or
missing database" that hardly indicates what is wrong.
-- 
View this message in context: 
http://www.nabble.com/-Linux-%2B-PHP--Recommended-way-to-access-SQLite--tp14244097p14899825.html
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I get back command history in v3.5.4?

[Rob Sciuk]

On Wed, 16 Jan 2008, John Stanton wrote:


Date: Wed, 16 Jan 2008 16:32:53 -0600
From: John Stanton <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How do I get back command history in v3.5.4?

You might be having a library location problem between /usr/local/lib and 
where Ubuntu puts libraries, usr/lib.




Ubuntu also seems to differentiate between a .binary package and a 
developer package.  It may be that the INCLUDE files are not installed as 
the developer (headers+libraries) are installed, either way, it is 
possible that the configure failed as a result


You need both the libreadline.so.x and the include files (man readline)

Cheers,
Rob

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I get back command history in v3.5.4?

[John Stanton]

You might be having a library location problem between /usr/local/lib 
and where Ubuntu puts libraries, usr/lib.


Fred J. Stephens wrote:

On 1/16/08, John Stanton <[EMAIL PROTECTED]> wrote:

Do you have readline installed on your Linux machine?  If not download
the package, configure and install.  Then compile Sqlite with readline.

John,
Yes I do have readline installed, and as I said, SQLite worked fine
with the version from the Ubuntu package repositories (3.4.2). I would
just use that one, but I am concerned about the possible data
corruption bug fixed in 3.5.4.
Thanks for the reply. Any other suggestions?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sun acquires MySQL

[Slater, Chad]

<http://arstechnica.com/news.ars/post/20080116-sun-targets-open-source-l
amp-stack-with-mysql-acquisition.html>

Or this link if your mail client breaks it up:

http://tinyurl.com/2qqaa9



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Question on SQL arbitrary arrays

[Mike McGonagle]

On Jan 16, 2008 2:18 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Mike,
>
> Do you need to be able to access the individual array elements with SQL
> statements, or do you simply need to save and restore entire arrays?


The arrays are a set, so they would never be addressed as individual
elements.


>
>
> If the latter is the case, you  should look at storing your entire
> floating point array directly into the database as a BLOB. You will have
> one database record for each array, and loading and saving an array is
> very straight forward.


Well, this is what I would like to do, but currently my usage of SQLite
doesn't include support for Blobs. That will be in version 2. Time to get to
writing some code...

Thanks,

Mike



>
>
> HTH
> Dennis Cote
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Peace may sound simple—one beautiful word— but it requires everything we
have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician

Re: [sqlite] Question on SQL arbitrary arrays

[Dennis Cote]

Mike McGonagle wrote:


Basically, I am trying to connect SQLite up to Pure Data (a dataflow
programming language), and I am creating arbitrary lists of floating point
numbers that I would like to be able to store as sets. The SQL code from my
original message is what I am currently using for the array stuff. Just one
big table with each element tagged for grouping and another for their order.

It sounds like what you described would be great, if it were only in SQLite.

I had thought about the idea of creating everything as a long string,
separated by a space, and storing them as a group. I just wasn't sure if
this was the "accepted" way to do it in SQL.


  

Mike,

Do you need to be able to access the individual array elements with SQL 
statements, or do you simply need to save and restore entire arrays?


If the latter is the case, you  should look at storing your entire 
floating point array directly into the database as a BLOB. You will have 
one database record for each array, and loading and saving an array is 
very straight forward.


HTH
Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Compiling 3.5.4 on FreeBSD ...

[Rob Sciuk]

Dear Digest,

I just compiled 3.5.4 on FreeBSD 6.2-RELEASE, and noticed that while
I'm *NOT* using threads, I still require linking with the re-entrant C 
library -lc_r option, else I get a runtime error ... I don't require
the -lpthread option, but I *DO* require -lc_r.  Is this a new 
requirement?


Cheers,
Rob Sciuk

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Berkeley DB vs. SQLite for threaded application

[Tomas Lee]

On 2008 January 16 (Wed) 04:57:42am PST, [EMAIL PROTECTED] wrote:
> Tomas Lee <[EMAIL PROTECTED]> wrote:
> > I've got an application that has Berkeley DB embedded in it.  I want
> > to replace Berkeley DB with SQLite.  (I plan to use the 3.5.4
> > almagamation, which is the latest I could find.)  The thing is, this
> > application uses threads.  I know threads are evil, but this
> > application uses them, and there it is.  So, I wanted to understand
> > what I had to do to be safe.
> > 
> > As I understand it, Berkeley DB has free-threaded database handles, so
> > my application can open a Berkeley DB database connection and have all
> > of its thread use that same connection.  But SQLite doesn't allow
> > this, so I'm going to have to change things.  In theory, I could just
> > open and close a new connection whenever I want to access the
> > database.  How much a performance hit is that?
> > 
> 
> As of version 3.5.0, SQLite allows free-threaded database handles.

Ah!  That makes things a lot easier.  Thanks!

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I get back command history in v3.5.4?

[Fred J. Stephens]

On 1/16/08, John Stanton <[EMAIL PROTECTED]> wrote:
> Do you have readline installed on your Linux machine?  If not download
> the package, configure and install.  Then compile Sqlite with readline.
John,
Yes I do have readline installed, and as I said, SQLite worked fine
with the version from the Ubuntu package repositories (3.4.2). I would
just use that one, but I am concerned about the possible data
corruption bug fixed in 3.5.4.
Thanks for the reply. Any other suggestions?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?

[kamil.szot]

Kees Nuyt wrote:
> 
> On Tue, 15 Jan 2008 11:33:06 -0800 (PST), "kamil.szot"
> <[EMAIL PROTECTED]> wrote:
>>With 2 (and probably 3) you will not be able to retrieve metadata (for
>>example column names) for empty table.
>>The only way of getting column names in PDO is executing "SELECT * FROM
>>table" query and then retrieving metadata using columnCount() and
>>getColumnMeta() methods of PDOStatement object returned by PDO->query()
>>method. But in case of sqlite: (meaning sqlite3) databases getColumnMeta()
>>returns nothing if result set is empty. In case sqlite2: driver
>>getColumnMeta() always throws exception.
>>
>>So if you will be needing to inspect your tables at runtime then I'm
>>recommending first solution.
>>
>>If anyone knows how to get field names of a table from sqlite3 database in
>>PHP please share your knowledge with the world. :-)
> 
> There's a PRAGMA for that purpose.
> 
> $sql = "PRAGMA table_info('tablename')";
> $res = $this->query($sql);
> 
> Example result set:
> cid  name typenotnull   dflt_value  pk
> 0jobidINTEGER 991
> 1TSN  CHAR(4) 0 0
> 2jobprio  INTEGER 0 9   0
> 3status   CHAR(1) 0 'W' 0
> 4userid   VARCHAR(8)  990
> etc.
> 
> The columns of this resultset have metadata, just like a normal resultset.
> 
> Tested with:
> php, php_pdo.dll, php_pdo_sqlite_external.dll v5.2.5.5 (2007-11-08,
> Windows), 
> sqlite3.dll  v3.5.4 (2007-12-14)
> 

Thanks man! You made my day!

To no ones surprise it also works under Linux PHP 5.2.0.8 and libqlite3
3.3.8

-- 
View this message in context: 
http://www.nabble.com/-Linux-%2B-PHP--Recommended-way-to-access-SQLite--tp14244097p14881348.html
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Berkeley DB vs. SQLite for threaded application

[drh]

Tomas Lee <[EMAIL PROTECTED]> wrote:
> I've got an application that has Berkeley DB embedded in it.  I want
> to replace Berkeley DB with SQLite.  (I plan to use the 3.5.4
> almagamation, which is the latest I could find.)  The thing is, this
> application uses threads.  I know threads are evil, but this
> application uses them, and there it is.  So, I wanted to understand
> what I had to do to be safe.
> 
> As I understand it, Berkeley DB has free-threaded database handles, so
> my application can open a Berkeley DB database connection and have all
> of its thread use that same connection.  But SQLite doesn't allow
> this, so I'm going to have to change things.  In theory, I could just
> open and close a new connection whenever I want to access the
> database.  How much a performance hit is that?
> 

As of version 3.5.0, SQLite allows free-threaded database handles.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[Mark Riehl]

Is there a way to merge these in such a way that I could make this
from a system call?  For example, let's say I want to create this dump
file from a Perl or shell script using a system call.  Can I do that?

Thanks,
Mark

On Jan 15, 2008 4:59 PM, Rich Shepard <[EMAIL PROTECTED]> wrote:
> On Tue, 15 Jan 2008, Rob Sciuk wrote:
>
> >> Is there a way to dump out the contents of the database w/o having the
> >> CREATE TABLE statements?  For example, I can do the following:
>
> >> However, foo.dmp contains all of the CREATE TABLE statements.  I just
> >> want all of the INSERT INTO statements associated with this database.
>
> > Actually, this looks like a reasonable enhancement request.  As it does 
> > *NOT*
> > affect the API in any way, and PostGres allows separate schema and data 
> > dumps
> > (the default being both) in psql, there should be no reason not to put it
> > into the sqlite3 tool, unless I'm missing something??  Am I?
>
>Not needed; it already exists.
>
>Change the mode to 'insert', select a file name for your output, then run
> the select statement. For example:
>
> sqlite> .m insert
> sqlite> .o table-values.sql
> sqlite> Select * from Tablename;
> sqlite> .o stdout
> sqlite> .m list
>
>The last two commands reset the output and mode back to the interactive
> values.
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax: 503-667-8863
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Berkeley DB vs. SQLite for threaded application

[RaghavendraK 70574]

I would suggest you to go for thread specific sqlite handles.
This would make each thread operate in its own db connection handle.

threadID = getthreadID();
if(threadIDHashList.find( threadID)== false)
  {
//new thread,just add this threadID to list
//create new DB connection and save it as part of TSD*threadspecific data.
  }

regards
ragha
**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Tomas Lee <[EMAIL PROTECTED]>
Date: Wednesday, January 16, 2008 12:54 pm
Subject: [sqlite] Berkeley DB vs. SQLite for threaded application

> I've got an application that has Berkeley DB embedded in it.  I want
> to replace Berkeley DB with SQLite.  (I plan to use the 3.5.4
> almagamation, which is the latest I could find.)  The thing is, this
> application uses threads.  I know threads are evil, but this
> application uses them, and there it is.  So, I wanted to understand
> what I had to do to be safe.
> 
> As I understand it, Berkeley DB has free-threaded database 
> handles, so
> my application can open a Berkeley DB database connection and have all
> of its thread use that same connection.  But SQLite doesn't allow
> this, so I'm going to have to change things.  In theory, I could just
> open and close a new connection whenever I want to access the
> database.  How much a performance hit is that?
> 
>  tells me that I can move a connection
> handle across threads as long as that connection is holding no fcntl()
> locks, and that this is due to fcntl() bugs on some OSes, like 
> RedHat9.But what if I'm running on an OS that doesn't have these 
> bugs -- do
> I have to worry about moving a connection handle across threads?  And
> how can I tell if my OS has these bugs?
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite as a IPC mechanism

[Roger Binns]

Joshua D. Boyd wrote:
> I have a system that currently consists of 2 C programs and 3 python
> programs.  Currently the python programs transfer data between
> themselves via pickles.  The C programs transfer data between themselves
> via streaming structs, and the C programs talk to one of the python
> programs via a fairly ugly text over socket method.  All of the programs
> are threaded.

Since you posted using a Linux mailer I'd assume your system is Linux
based.  I think you'll find that dbus is an excellent solution to your
requirements and it works just fine with C and python based programs.

http://en.wikipedia.org/wiki/D-Bus
http://www.freedesktop.org/wiki/Software/dbus

D-Bus deals with threading just fine.  It also has a nice notification
mechanism named 'signals'.

Roger

-
To unsubscribe, send email to [EMAIL PROTECTED]
-