[sqlite] sqlite3_exec and returned error

[Valery Reznic]

Hello.

I am not sure if it should go here or to devel list, so please bear with me.


I was about to use sqlite3_exe with not null errmsg parameter and became a bit 
confused.

Documentation says:
---

If an error occurs while evaluating the SQL statements passed into
sqlite3_exec(), then execution of the current statement stops and
subsequent statements are skipped.  If the 5th parameter to sqlite3_exec()
is not NULL then any error message is written into memory obtained
from sqlite3_malloc() and passed back through the 5th parameter.
To avoid memory leaks, the application should invoke sqlite3_free() on error 
message strings returned through the 5th parameter of
of sqlite3_exec() after the error message string is no longer needed.
If the 5th parameter to sqlite3_exec() is not NULL and no errors
occur, then sqlite3_exec() sets the pointer in its 5th parameter to
NULL before returning.
-


I was planing to use this function like following:

---

int res;
char *errmsg;

res = sqlite3_exec( db,  zSql, NULL, NULL,  );

if ( res !=  SQLITE_OK) {
fprintf( stderr, "%s", errmsg)
sqlite3_free( errmsg )

}

---

But then I looked at sqlite3_exec code and saw following
---
*/
SQLITE_API int sqlite3_exec(
sqlite3 *db,/* The database on which the SQL executes */
const char *zSql,  /* The SQL to be executed */
sqlite3_callback xCallback, /* Invoke this callback routine */
void *pArg,/* First argument to xCallback() */
char **pzErrMsg/* Write error messages here */
){
int rc = SQLITE_OK;/* Return code */
const char *zLeftover;  /* Tail of unprocessed SQL */
sqlite3_stmt *pStmt = 0;/* The current SQL statement */
char **azCols = 0;  /* Names of result columns */
int callbackIsInit;/* True if callback data is initialized */

if( !sqlite3SafetyCheckOk(db) ) return SQLITE_MISUSE_BKPT;

-

i.e if sqlite3SafetyCheckOk failed, then returned value is not SQLITE_OK on one 
side
and pzErrMsg will be left untouched.

Of course I can check myself for SQLITE_MISUSE_BKPT and in this cases give 
error message myself,
but it is  ugly.


Anyway looks to me as either bug in the code or in the documentation.

Or I am missing something?

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

Re: [sqlite] How often is xDisconnect called? (Was: Suggestion for syntax enhancement for virtual tables)

[Dan Kennedy]

On 01/06/2015 11:59 AM, Peter Aronson wrote:
It's this comment that makes me worry that xDisconnect can be called 
at other times than detach or close:


** When an in-memory Table object is deleted (for example when the
** schema is being reloaded for some reason), the VTable objects are not
** deleted and the sqlite3_vtab* handles are not xDisconnect()ed
** immediately. Instead, they are moved from the Table.pVTable list to
** another linked list headed by the sqlite3.pDisconnect member of the
** corresponding sqlite3 structure. They are then deleted/xDisconnected
** next time a statement is prepared using said sqlite3*. This is done
** to avoid deadlock issues involving multiple sqlite3.mutex mutexes.

I'm not sure exactly what this means, but it implies that xDisconnect 
can be called in the middle of a session.


It can. One scenario is if you ROLLBACK a transaction that includes 
schema modifications to the temp database. i.e. executing:


  BEGIN;
CREATE TEMP TABLE t2(x);
  ROLLBACK;

will cause the xDisconnect() method of all virtual tables in the temp 
database to be invoked. New sqlite3_vtab objects will be requested via 
xConnect() the next time the virtual table is accessed.


Dan.






Peter

On 1/2/2015 3:00 PM, Peter Aronson wrote:
If only the xDisconnect method is called on a virtual table create in 
the temp database at disconnect time, is that the only time 
xDisconnect will be called?  The documentation at sqlite.org doesn't 
seem to say.  Jay Krebich's Using SQLite says xDisconnect is "Called 
when a database containing a virtual table instance is detached or 
closed. Called once for each table instance."  But looking at the 
SQLite code and comments, I'm not sure this is true.  Is it?  If so, 
it would be easy enough when writing a Virtual Table Module to note 
that it is being created in the temp database, and do any required 
cleanup in xDisconnect instead of xDestroy for that instance.  But if 
xDisconnect can be called at other times, cleanup could be premature.


Best,

Peter


On Friday, January 2, 2015 12:56 AM, Dan Kennedy 
 wrote:




On 01/02/2015 01:58 PM, Hick Gunter wrote:
Temporary virtual tables sounds like an interesting concept. Does 
the xDestroy() function get called on such a beast (as opposed to 
xDisconnect() when the connection is closed)?

Just xDisconnect().

Dan.



   Should that function delete the backing store (even if a 
non-temporary virtual table is still connected)?


-Ursprüngliche Nachricht-
Von: Baruch Burstein [mailto:bmburst...@gmail.com]
Gesendet: Donnerstag, 01. Jänner 2015 08:38
An: General Discussion of SQLite Database
Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular 
tables)?


CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
___

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

[sqlite] How often is xDisconnect called? (Was: Suggestion for syntax enhancement for virtual tables)

[Peter Aronson]

It's this comment that makes me worry that xDisconnect can be called at 
other times than detach or close:


** When an in-memory Table object is deleted (for example when the
** schema is being reloaded for some reason), the VTable objects are not
** deleted and the sqlite3_vtab* handles are not xDisconnect()ed
** immediately. Instead, they are moved from the Table.pVTable list to
** another linked list headed by the sqlite3.pDisconnect member of the
** corresponding sqlite3 structure. They are then deleted/xDisconnected
** next time a statement is prepared using said sqlite3*. This is done
** to avoid deadlock issues involving multiple sqlite3.mutex mutexes.

I'm not sure exactly what this means, but it implies that xDisconnect 
can be called in the middle of a session.


Peter

On 1/2/2015 3:00 PM, Peter Aronson wrote:

If only the xDisconnect method is called on a virtual table create in the temp database 
at disconnect time, is that the only time xDisconnect will be called?  The documentation 
at sqlite.org doesn't seem to say.  Jay Krebich's Using SQLite says xDisconnect is 
"Called when a database containing a virtual table instance is detached or closed.  
Called once for each table instance."  But looking at the SQLite code and comments, 
I'm not sure this is true.  Is it?  If so, it would be easy enough when writing a Virtual 
Table Module to note that it is being created in the temp database, and do any required 
cleanup in xDisconnect instead of xDestroy for that instance.  But if xDisconnect can be 
called at other times, cleanup could be premature.

Best,

Peter


On Friday, January 2, 2015 12:56 AM, Dan Kennedy  wrote:
  



On 01/02/2015 01:58 PM, Hick Gunter wrote:

Temporary virtual tables sounds like an interesting concept. Does the 
xDestroy() function get called on such a beast (as opposed to xDisconnect() 
when the connection is closed)?

Just xDisconnect().

Dan.




   Should that function delete the backing store (even if a non-temporary 
virtual table is still connected)?

-Ursprüngliche Nachricht-
Von: Baruch Burstein [mailto:bmburst...@gmail.com]
Gesendet: Donnerstag, 01. Jänner 2015 08:38
An: General Discussion of SQLite Database
Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular tables)?

CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
___
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] Error: database disk image is malformed

[Neo Anderson]

> Are you using the same sqlite3 binary for both operations?
>
> If a database has a table definition that an older version of SQLite does not 
> understand, it will sometimes give this error. If you’re using a newer 
> version of SQLite to dump/load the files, there will be no error.
>

Yes, I am using the latest sqlite3 binary (version 3.8.7.4) for both 
operations.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PHP: squelch warning and error messages

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/2015 04:53 PM, Lev wrote:
> I'm using the PHP bindings for SQLite3. How can I squelch error
> and warning messages? I do error checking, but the failing call
> emits the messages.
> 
> Messages like:
> 
> Warning: SQLite3::prepare(): Unable to prepare statement: 1, no
> such table:

I'm guessing the "1" is a the SQLite integer error code and "no such
table: " is the error string from SQLite.  It is mystifying why this
is a warning and not an error.  As far as SQLite is concerned whatever
text it was given is an error, referencing what appears to be a zero
length table name.  There is no way the query can actually execute
after getting that error.

You sure as heck do not want to squelch error messages.  SQLite is a
library for developers and only works when you give it correct SQL.

Or in short, you squelch the diagnostics by providing acceptable SQL.
 You need to log/trace queries to find out which ones are the problems.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSrSKAACgkQmOOfHg372QRFcACaAwpOYnJwDRX3lwb3+uqSwTsT
BKsAoJ18lmnGUrNBKgPgHznYv7m0AlIW
=yR3X
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/2015 10:25 AM, Jim Wilcoxson wrote:
> It's easy to do this in Python using iterdump(),

Be aware that the pysqlite dump implementation is incomplete in many
ways.  Some issues:

- - Doesn't dump in a transaction so will have irregularities if things
change during the dump

- - Deletes the entire sqlite_sequence table instead of the entry for
the table being dumped/restored

- - Runs ANALYZE on all tables during restore if sqlite_stat1 exists
(ignores later versions of stat), for every table restored

- - Doesn't handle virtual tables (certainly can't restore them -
probably chokes on backing them up)

- - Messes up if any table names or columns have a double quote in their
name (probably single quotes too)

- - Doesn't disable foreign key processing during restore which can
result in a huge mess

- - Who knows how many other issues

pysqlite's iterdump is 50 lines long.  The APSW dump code (also in
Python) is 230 lines, and the SQLite shell C code is about 200 lines.
 pysqlite is definitely missing many of the finer details.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSrRo8ACgkQmOOfHg372QQnfACgz5idM01KvQEDcuXWKaU9M21R
OqkAoN/TDCCgOOD5jW2Iqi/obGt57dRY
=4OWi
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A new to-be User!

[Saffa Kemokai]

Thanks Stephen, 

Appreciate your response. Saves me lots of time doing the wrong thing expecting 
the correct result. 

Norwoh -- 

- Original Message -

From: "Stephen Chrzanowski"  
To: "General Discussion of SQLite Database"  
Sent: Monday, January 5, 2015 8:52:47 PM 
Subject: Re: [sqlite] A new to-be User! 

Although SQLite can be used in a server type situation, it is more geared 
towards being an embeded database solution. 

A couple of links you should read over: 
- http://sqlite.org/whentouse.html 
- http://sqlite.org/howtocorrupt.html 



On Mon, Jan 5, 2015 at 8:04 PM, Saffa Kemokai  wrote: 

> Hi Everyone, 
> 
> I am looking for a database to replace Mysql. I plan to use Roundcube 
> webmail. I have Mysql running but listening to Roundcube user group, I hear 
> somewhat conflicting statements about mysql. Mostly people are saying it 
> makes the system very slow in Roundcube for whatever reason. Some are 
> talking about SQLite as an alternative. I have never seen SQLite in 
> operation. I have downloaded it but not yet installed. I am running FreeBSD 
> 10.0. Any hints, how-to, or recommendations will be appreciated on getting 
> started with it. 
> 
> 
> Thanks 
> 
> Norwoh-- 
> 
> 
> 
> 
> 
> ___ 
> 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] A new to-be User!

[Stephen Chrzanowski]

Although SQLite can be used in a server type situation, it is more geared
towards being an embeded database solution.

A couple of links you should read over:
- http://sqlite.org/whentouse.html
- http://sqlite.org/howtocorrupt.html



On Mon, Jan 5, 2015 at 8:04 PM, Saffa Kemokai  wrote:

> Hi Everyone,
>
> I am looking for a database to replace Mysql. I plan to use Roundcube
> webmail. I have Mysql running but listening to Roundcube user group, I hear
> somewhat conflicting statements about mysql. Mostly people are saying it
> makes the system very slow in Roundcube for whatever reason. Some are
> talking about SQLite as an alternative. I have never seen SQLite in
> operation. I have downloaded it but not yet installed. I am running FreeBSD
> 10.0. Any hints, how-to, or recommendations will be appreciated on getting
> started with it.
>
>
> Thanks
>
> Norwoh--
>
>
>
>
>
> ___
> 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] A new to-be User!

[Saffa Kemokai]

Hi Everyone, 

I am looking for a database to replace Mysql. I plan to use Roundcube webmail. 
I have Mysql running but listening to Roundcube user group, I hear somewhat 
conflicting statements about mysql. Mostly people are saying it makes the 
system very slow in Roundcube for whatever reason. Some are talking about 
SQLite as an alternative. I have never seen SQLite in operation. I have 
downloaded it but not yet installed. I am running FreeBSD 10.0. Any hints, 
how-to, or recommendations will be appreciated on getting started with it. 


Thanks 

Norwoh-- 





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

[sqlite] PHP: squelch warning and error messages

[Lev]

I'm using the PHP bindings for SQLite3. How can I squelch error and
warning messages? I do error checking, but the failing call emits the
messages.

Messages like:

Warning: SQLite3::prepare(): Unable to prepare statement: 1, no such table:

Thanks,
Levente

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

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

[Jay Kreibich]

On Jan 4, 2015, at 3:31 AM, Neo Anderson  wrote:

> I have a customer database which appears corrupt. When I run sqlite3 mydb and 
> PRAGMA integrity_check I got (literally, no other lines):
> 
> Error: database disk image is malformed
> 
> However, I can .dump and .read to create a new database. The new database 
> works fine and the most surprising result is the old and new database files 
> are exactly of the same size. But running cmp old.db new.db gave me:
> 
> differ: char 27, line 1
> 
> My question is why .dump works but sqlite3 thinks the file is corrupt. 
> Attached please find old.db and new.db in a zip package. Hope this can help 
> improve sqlite. 


Are you using the same sqlite3 binary for both operations?

If a database has a table definition that an older version of SQLite does not 
understand, it will sometimes give this error.  If you’re using a newer version 
of SQLite to dump/load the files, there will be no error.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Jay Kreibich]

On Jan 5, 2015, at 5:03 PM, Eduardo Morras  wrote:

> On Mon, 5 Jan 2015 14:42:28 -0600
> Jay Kreibich  wrote:
> 
>> 
>> 
>> On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson  wrote:
>> 
>>> Simon - instead of using vacuum, it's much faster to create a new
>>> database from the old one, then rename it.  It's easy to do this in
>>> Python using iterdump(), or you can connect to the new (empty)
>>> database, do your create table statements, attach the old database
>>> as olddb, then do:
>>> 
>>> insert into table1 select * from olddb.table1;
>>> insert into table2 select 8 from olddb.table2;
>>> 
>>> This also lets you do the create table stmts w/o indexes, and add
>>> the indexes after the inserts.  Not sure if that is faster or not
>>> for your data.
>> 
>> If you look at code for VACUUM, that’s more or less what it does…
>> only it is very smart about it, properly preserving ROWID values, as
>> well as exact table definitions, sequences, analytics, and all the
>> meta-data in the database.
> 
> Sqlite3 Backup API should work too, and covers your vacuum to file proposal.

No, the backup API does a page-by-page copy.  It’s goal is to make a 
bit-perfect copy of the original database file.  It makes no attempt to VACUUM 
the database as it is backed-up, and has no understanding of the data it is 
copying.  The backup-API does not remove empty space or re-order pages, nor 
does it allow changes to database page size and other alterations supported by 
VACUUM.  The backup API also operates outside the SQL transaction system.

VACUUM TO would be a simplified version of VACUUM that only requires 2x the 
disk space (rather than 3x) and much, much less I/O (possibly 1/3 or so).  It 
would be faster for many applications that control all access to a database, 
such as applications that use SQLite DBs as their document file format.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





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

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

[Eduardo Morras]

On Sun, 4 Jan 2015 17:31:14 +0800
Neo Anderson  wrote:

> I have a customer database which appears corrupt. When I run sqlite3
> mydb and PRAGMA integrity_check I got (literally, no other lines):
> 
> Error: database disk image is malformed
> 
> However, I can .dump and .read to create a new database. The new
> database works fine and the most surprising result is the old and new
> database files are exactly of the same size. But running cmp old.db
> new.db gave me:
> 
> differ: char 27, line 1
> 
> My question is why .dump works but sqlite3 thinks the file is
> corrupt. Attached please find old.db and new.db in a zip package.
> Hope this can help improve sqlite.
> 

Char 27 is on Sqlite3 header. It tracks the file change counter. When you .dump 
the counter is increased in your old.db by 1, that's the difference. Both files 
has the same corruption.

The mail list has attachment set to off, so they're deleted before remailed.

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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Eduardo Morras]

On Mon, 5 Jan 2015 14:42:28 -0600
Jay Kreibich  wrote:

> 
> 
> On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson  wrote:
> 
> > Simon - instead of using vacuum, it's much faster to create a new
> > database from the old one, then rename it.  It's easy to do this in
> > Python using iterdump(), or you can connect to the new (empty)
> > database, do your create table statements, attach the old database
> > as olddb, then do:
> > 
> > insert into table1 select * from olddb.table1;
> > insert into table2 select 8 from olddb.table2;
> > 
> > This also lets you do the create table stmts w/o indexes, and add
> > the indexes after the inserts.  Not sure if that is faster or not
> > for your data.
> 
> If you look at code for VACUUM, that’s more or less what it does…
> only it is very smart about it, properly preserving ROWID values, as
> well as exact table definitions, sequences, analytics, and all the
> meta-data in the database.

Sqlite3 Backup API should work too, and covers your vacuum to file proposal.

You can, from sqlite3 shell, .dump the database to .sql file or pipe output to 
gzip or xz and use the compressed file for import again. Don't need full 
compression to minimize the io, -6 for gzip and -3 for xz is enough.

> 
>   -j


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

[sqlite] Error: database disk image is malformed

[Neo Anderson]

I have a customer database which appears corrupt. When I run sqlite3 mydb and 
PRAGMA integrity_check I got (literally, no other lines):

Error: database disk image is malformed

However, I can .dump and .read to create a new database. The new database works 
fine and the most surprising result is the old and new database files are 
exactly of the same size. But running cmp old.db new.db gave me:

differ: char 27, line 1

My question is why .dump works but sqlite3 thinks the file is corrupt. Attached 
please find old.db and new.db in a zip package. Hope this can help improve 
sqlite.___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Jay Kreibich]

On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson  wrote:

> Simon - instead of using vacuum, it's much faster to create a new
> database from the old one, then rename it.  It's easy to do this in
> Python using iterdump(), or you can connect to the new (empty)
> database, do your create table statements, attach the old database as
> olddb, then do:
> 
> insert into table1 select * from olddb.table1;
> insert into table2 select 8 from olddb.table2;
> 
> This also lets you do the create table stmts w/o indexes, and add the
> indexes after the inserts.  Not sure if that is faster or not for your
> data.

If you look at code for VACUUM, that’s more or less what it does… only it is 
very smart about it, properly preserving ROWID values, as well as exact table 
definitions, sequences, analytics, and all the meta-data in the database.

The “copy to new database” accounts for the 2x size requirement.  In the case 
of VACUUM, the data is then copied from the fresh DB back to the original 
database file in a transaction-safe way; this touches every page in the 
original file, requiring a rollback log of some sort (journal or WAL)— and that 
accounts for the 3x space requirement.

While you can roll your own, the method you propose is not transaction safe and 
is difficult to use in a production environment unless you know you can shut 
down all services using the DB and force them to re-open connections to the new 
file.  That’s true of some applications, but far from all of them.

About five years ago I proposed a VACUUM TO  variant of VACUUM that would 
more or less do what you’re talking about, without the copy-back operation.  It 
would only require 2x the disk space, and be much faster for applications that 
were able to use it.  I still think it would be a useful, low-cost feature.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg50941.html

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





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

[sqlite] VACUUM requires 6.7 times space ?

[Jim Wilcoxson]

Simon - instead of using vacuum, it's much faster to create a new
database from the old one, then rename it.  It's easy to do this in
Python using iterdump(), or you can connect to the new (empty)
database, do your create table statements, attach the old database as
olddb, then do:

insert into table1 select * from olddb.table1;
insert into table2 select 8 from olddb.table2;

This also lets you do the create table stmts w/o indexes, and add the
indexes after the inserts.  Not sure if that is faster or not for your
data.

Good luck!
Jim
-- 
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Jay Kreibich]

On Jan 5, 2015, at 8:43 AM, Nelson, Erik - 2  
wrote:

> RSmith wrote on Monday, January 05, 2015 7:43 AM
>> 
>> 
>>> On 01/05/2015 06:22 PM, Simon Slavin wrote:
 I have a database file which is 120GB in size.  It consists of two huge 
 tables and an index. //...
>>> Probably running out of space wherever temp files are created.
>> 
> 
> I ran into this a while ago- used pragma temp_store_directory

Which is, for good or bad, depreciated.  Not sure why.

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Nelson, Erik - 2]

Simon Slavin wrote on  Monday, January 05, 2015 10:40 AM
> 
> On 5 Jan 2015, at 2:43pm, Nelson, Erik - 2
>  wrote:
> 
> > RSmith wrote on Monday, January 05, 2015 7:43 AM
> >
> >> I haven't done this, but I seem to remember there was a way to tell
> >> SQLite where to make temp files, or override the system default at
> >> any rate - which may help.
> >
> > I ran into this a while ago- used pragma temp_store_directory
> 
> That's what I'm trying now.  Unfortunately the directory I'm trying to
> use has spaces in and is several folders down a hierarchy.  I'm just
> hoping that the string I've used to set the path doesn't need spaces or
> slashes escaped.
> 
> Simon.

If you're using the command line, you could try a relative reference like "."

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[Simon Slavin]

On 5 Jan 2015, at 2:43pm, Nelson, Erik - 2  
wrote:

> RSmith wrote on Monday, January 05, 2015 7:43 AM
> 
>> I haven't done this, but I seem to remember there was a way to tell
>> SQLite where to make temp files, or override the system default at any
>> rate - which may help.
> 
> I ran into this a while ago- used pragma temp_store_directory

That's what I'm trying now.  Unfortunately the directory I'm trying to use has 
spaces in and is several folders down a hierarchy.  I'm just hoping that the 
string I've used to set the path doesn't need spaces or slashes escaped.

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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Nelson, Erik - 2]

RSmith wrote on Monday, January 05, 2015 7:43 AM
> 
> On 2015/01/05 13:32, Dan Kennedy wrote:
> > On 01/05/2015 06:22 PM, Simon Slavin wrote:
> >> I have a database file which is 120GB in size.  It consists of two
> huge tables and an index. //...
> > Probably running out of space wherever temp files are created.
> 
> I haven't done this, but I seem to remember there was a way to tell
> SQLite where to make temp files, or override the system default at any
> rate - which may help.
> 

I ran into this a while ago- used pragma temp_store_directory

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM requires 6.7 times space ?

[RSmith]

On 2015/01/05 13:32, Dan Kennedy wrote:

On 01/05/2015 06:22 PM, Simon Slavin wrote:

I have a database file which is 120GB in size.  It consists of two huge tables 
and an index. //...

Probably running out of space wherever temp files are created.


I haven't done this, but I seem to remember there was a way to tell SQLite where to make temp files, or override the system default 
at any rate - which may help.


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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Simon Slavin]

On 5 Jan 2015, at 11:32am, Dan Kennedy  wrote:

> Probably running out of space wherever temp files are created.

Oh, that makes a lot of sense.  The free space on the boot volume for that 
system is only 37GB.  Okay, I can move the file to another computer.

Thanks for the fast and useful answer and to Igor for another possibility.

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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Igor Korot]

Hi, Simon,

On Mon, Jan 5, 2015 at 6:32 AM, Dan Kennedy  wrote:
> On 01/05/2015 06:22 PM, Simon Slavin wrote:
>>
>> I have a database file which is 120GB in size.  It consists of two huge
>> tables and an index.
>> Its journal_mode is DELETE.
>>
>> It is on a partition with 803GB of free space.  By my calculations I have
>> 6.7 times the amount of free space as the database is taking up.
>>
>> I use the SQLite shell tool version 3.7.9 to run VACUUM on it.  The Shell
>> tool bails out reporting
>>
>> CPU Time: user 2113.596836 sys 437.660032
>> Error: near line 5 : database or disk full.
>>
>> My understanding is that VACUUM can't take more than three times the
>> current size of the database file.  What does the above error mean under
>> these circumstances ?
>
>
> Probably running out of space wherever temp files are created.

Or the disk is failing...

Thank you.

>
>
> ___
> 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] VACUUM requires 6.7 times space ?

[Dan Kennedy]

On 01/05/2015 06:22 PM, Simon Slavin wrote:

I have a database file which is 120GB in size.  It consists of two huge tables 
and an index.
Its journal_mode is DELETE.

It is on a partition with 803GB of free space.  By my calculations I have 6.7 
times the amount of free space as the database is taking up.

I use the SQLite shell tool version 3.7.9 to run VACUUM on it.  The Shell tool 
bails out reporting

CPU Time: user 2113.596836 sys 437.660032
Error: near line 5 : database or disk full.

My understanding is that VACUUM can't take more than three times the current 
size of the database file.  What does the above error mean under these 
circumstances ?


Probably running out of space wherever temp files are created.

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

[sqlite] VACUUM requires 6.7 times space ?

[Simon Slavin]

I have a database file which is 120GB in size.  It consists of two huge tables 
and an index.  
Its journal_mode is DELETE.

It is on a partition with 803GB of free space.  By my calculations I have 6.7 
times the amount of free space as the database is taking up.

I use the SQLite shell tool version 3.7.9 to run VACUUM on it.  The Shell tool 
bails out reporting 

CPU Time: user 2113.596836 sys 437.660032
Error: near line 5 : database or disk full.

My understanding is that VACUUM can't take more than three times the current 
size of the database file.  What does the above error mean under these 
circumstances ?

I'm currently running "PRAGMA integrity_check" but I have no reason to believe 
that the database is corrupt, I'm just desperate.

If nobody comes up with any suggestions my next tactic is to DROP the index, do 
the VACUUM, then rebuild the index.  I suspect that all three of those 
operations will be overnight runs so again I'll write a script which does all 
three and use the shell tool's ".read" function.

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