Re: [sqlite] Precompiled binary for sqlite3_analyzer requires libicui18n.so.44?

[Richard Hipp]

There is now a new build of sqlite3_analyzer for linux up that should not
require libicu18n.  Let me know if the new version fails to work for you.

To build sqlite3_analyzer, you have to manually edit Makefile.linux-gcc for
your system.  It is not hard, but nor is it particularly obvious.

On Mon, Aug 23, 2010 at 10:32 PM, Bob Chapman  wrote:

> The sqlite3_analyzer precompiled binary for linux as found
> in sqlite3_analyzer-3.7.1-linux.zip appears to require
> libicui18n.so.44. Ubuntu 8.04 LTS has libicui18n.so.38.0 while
> Centos 4.8 is way back at libicui18n.so.22.0.  I can run the
> analyzer on Windows but would rather not have to transfer stuff
> back and forth.
>
> So far, I have not been astute enough to figure out how to build
> my own sqlite3_analyzer binary. The amalgamation does not
> appear to have the required source and the Makefile does not
> have a suitable target. I have made several (pseudo-random ;)
> walks through the Fossil repository but have not been smart
> enough to locate a "build your own" sqlite3_analyzer package
> for dummies. ;)
>
> And, considering the possibility that building the analyzer
> really does require ICU 4.4.x, I've downloaded and (I think ;)
> successfully built ICU Release 4.4.1 on Ubuntu 8.04 but I
> am reluctant to install it and, perhaps, do the same on the
> Centos 4.8 platform if there is an easier simpler way.
>
> Thanks in advance for any assistance and my apologies if my
> ignorance has created "noise" on the list.
>
> --
> ___
> 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] Precompiled binary for sqlite3_analyzer requires libicui18n.so.44?

[Bob Chapman]

The sqlite3_analyzer precompiled binary for linux as found
in sqlite3_analyzer-3.7.1-linux.zip appears to require
libicui18n.so.44. Ubuntu 8.04 LTS has libicui18n.so.38.0 while
Centos 4.8 is way back at libicui18n.so.22.0.  I can run the
analyzer on Windows but would rather not have to transfer stuff
back and forth.

So far, I have not been astute enough to figure out how to build
my own sqlite3_analyzer binary. The amalgamation does not
appear to have the required source and the Makefile does not
have a suitable target. I have made several (pseudo-random ;)
walks through the Fossil repository but have not been smart
enough to locate a "build your own" sqlite3_analyzer package
for dummies. ;)

And, considering the possibility that building the analyzer
really does require ICU 4.4.x, I've downloaded and (I think ;)
successfully built ICU Release 4.4.1 on Ubuntu 8.04 but I
am reluctant to install it and, perhaps, do the same on the
Centos 4.8 platform if there is an easier simpler way.

Thanks in advance for any assistance and my apologies if my
ignorance has created "noise" on the list.

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

[sqlite] SQLite version 3.7.2

[Richard Hipp]

SQLite version 3.7.2 is now available on the SQLite website:
http://www.sqlite.org/

SQLite version 3.7.2 fixes a single bug that was discovered just hours after
the release of 3.7.1.  The bug can result in corruption of the database
free-list after an incremental vacuum.  The bug had nothing whatsoever to do
with SQLite version 3.7.1 or any other recent release.  The problem had been
in the code for over a year, since version 3.6.16.  The discovery of the
problem so soon after the release of version 3.7.1 was purely coincidental.

The bug fixed in 3.7.2 can result in database corruption.  However, the
corruption caused by this bug can almost always be fixed simply by running
VACUUM on the database.  And the corruption will only occur in an
incrementally vacuumed database which at some point in time contains
hundreds of unused pages which are slowly released back to the operating
system by multiple calls to the incremental_vacuum PRAGMA.  Even then, one
must be particularly unlucky to hit the right combination of freed pages in
order to trigger the bug.  Hence the problem is quite obscure and was not
noticed for over a year.

Hundreds of lines of code where changed for version 3.7.2, but most of those
changes were to test procedures.  As is the custom with SQLite, not only was
the specific bug fixed, but new tests where put in place to detect and
prevent similar kinds of bugs elsewhere in the code.  We believe that one
should not just fix the bug, but also fix the process that generated the
bug. The only 4 working lines of code were changed for version 3.7.2:

 http://www.sqlite.org/src/fdiff?v1=2dff4076d3c994dc=5047fb303cdf6806

Special thanks to Filip Navara for finding and reporting the problem with
incremental vacuum.

Please report any other problems to the sqlite-users@sqlite.org mailing
list, or directly to me.  Thanks.

-- 
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] database writes by multiple processes (on a very old computer)

[Chad Rebuck]

Simon, Roger,

Thanks for the replies.

Simon, I took some of your advice and redesigned my script to use smaller
database files at least for my testing.  However, I still run into this
message after starting the second process and my two processes get stuck so
no inserts/deletes occur after that moment.  I'll also google "wal" as you
suggest.

PHP Warning:  SQLite3::exec(): database is locked in
...populate-traceroute2.php on line 53.  Line 53 below contains
"$smallDb->exec($query2)".

Roger, I read the page you provided, but I think I need to read some more
before fully understanding what I've done wrong.  I didn't mention it until
now, but with my sqlite 2 database and an older version of php I didn't see
this issue last week.  I was working with smaller files then but now even
with small files I have the locking issue.

I would appreciate if anyone can guide me a little more on how to correct
this.  Here are the relevant code snippets.  There are probably more
efficient ways to handle this but I am fairly new to php programming and
only sql, so excuse the mess :)


function getTraceroute($smallDb) {


while($Results = $smallDb->query('SELECT * FROM tworklist limit 1'))
{
$entry = $Results->fetchArray(SQLITE3_ASSOC);
if (!($entry)) {  break; }

$query2 = "delete from tworklist where rowid=$rowid";
echo "result of delete = " . $smallDb->exec($query2) . "\n";

$query2 = "insert into tresults (rowid,ip,traceroute) values
('$rowid', '$ip','$new')";
echo "result of update = " . $smallDb->exec($query2) . "\n";
}
}


On Mon, Aug 23, 2010 at 4:47 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 08/23/2010 08:21 PM, Chad Rebuck wrote:
> > I am new to databases so perhaps there is something very basic I am
> > overlooking here.
>
> You may inadvertently be causing the busy errors yourself.  Have you
> read this:
>
>  http://www.sqlite.org/lockingv3.html
>
> A simple example is if you have a select query in process 1 from which
> you read one result at a time, process and continue with the query:
>
>   for row in ("select * from db"):
>  ... do work with row ...
>
> This will block writes in the second process since the select is always
> live.  Workarounds include using WAL mode, gathering all results from a
> select before processing them, and using one thread/process to do the
> database work dispatching work items to child threads/processes.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxy3lIACgkQmOOfHg372QTTyACgx4qhZSQ5bMjREOYuZBlOG6fM
> UbsAnRZDEYZMRcrKUrDiHpPRKs4mhx10
> =Af1h
> -END PGP SIGNATURE-
> ___
> 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] database writes by multiple processes (on a very old computer)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2010 08:21 PM, Chad Rebuck wrote:
> I am new to databases so perhaps there is something very basic I am
> overlooking here.

You may inadvertently be causing the busy errors yourself.  Have you
read this:

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

A simple example is if you have a select query in process 1 from which
you read one result at a time, process and continue with the query:

   for row in ("select * from db"):
  ... do work with row ...

This will block writes in the second process since the select is always
live.  Workarounds include using WAL mode, gathering all results from a
select before processing them, and using one thread/process to do the
database work dispatching work items to child threads/processes.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxy3lIACgkQmOOfHg372QTTyACgx4qhZSQ5bMjREOYuZBlOG6fM
UbsAnRZDEYZMRcrKUrDiHpPRKs4mhx10
=Af1h
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database writes by multiple processes (on a very old computer)

[Simon Slavin]

On 23 Aug 2010, at 3:51pm, Chad Rebuck wrote:

> I have a php script that goes though a 60,000 row table (currently a 100mb
> sqlite 3 format database that could grow to 200mb) and looks for rows that
> are missing data.  Then the script retrieves data (whois queries and
> traceroute results) and inserts it into the table.  I want to run
> many occurrences of this script to populate the database quicker, but I am
> getting into trouble with the database being locked constantly if I try to
> run only two instances of the script.

Thanks for that description which saves a lot of tedious Q before we can 
figure out what you want.

> The faq indicates concurrency really isn't necessary for most situations
> with the overall speeds of today's computers.  However, my server is a 12
> year old ibm pc running linux with 64m of memory.   The harddisk speed is
> very slow.  Is this going to prohibit running more than one process that
> writes to a single database table?

It depends on which part of the single-process version is the bottleneck.  It 
may be that the slowest element of your task is writing to the database file.  
If that's the case then splitting the job into several different occurrences 
will make no difference, since they all have to write to the same database file 
in the end anyway, and only one process can do that at once.  A slow hard disk 
suggests that this is the case: intensive disk activity like writing to a SQL 
database is probably occupying most of your application's time.

If the task of finding the missing data was taking the majority of the time you 
might get some advantage from using multiple processes: one might operate while 
another was waiting for network activity, for example.

Given the limitations of your hardware you might consider a different way of 
splitting up your application: have one task that finds all entries with 
missing data.  Then have many (parallel ?) processes find that information but 
write it to a long text file.  Then finally have another process that reads the 
textfile and makes changes to the database all in one giant transaction.  That 
might speed things up.

> I am new to databases so perhaps there is something very basic I am
> overlooking here.  Wanted to give sqlite a real effort before trying mysql.

A slow hard disk will cause the same problem here.  In fact, since MySQL is 
larger and more complicated than SQLite, it does more work when writing the 
file to disk, so you may get even worse results with it.  On the other hand 
MySQL makes heavy use of caching, so if you have enough memory free it needs to 
do less disk activity.

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

Re: [sqlite] EXTERNAL: Possible database corruption bug in SQLite

[Black, Michael (IS)]

Since I love to debug code and I needed a distraction
 
Problem was introduced on 2009-06-05 update
 
Several SQLITE_CORRUPT_BKPT were inserted (so this corruption may have been 
going for a while).
 
And here's where the corruption is returned:
 
iPage = get4byte([8+closest*4]);
printf("iPage=%p aData=%p\n",iPage,aData);
if( iPage>mxPage ){
  printf("X7 iPage=%p, closest=%d ",iPage,closest);
  rc = SQLITE_CORRUPT_BKPT;
  goto end_allocate_page;
}

./sqlite3 test.db 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] database writes by multiple processes (on a very old computer)

[Chad Rebuck]

I have a php script that goes though a 60,000 row table (currently a 100mb
sqlite 3 format database that could grow to 200mb) and looks for rows that
are missing data.  Then the script retrieves data (whois queries and
traceroute results) and inserts it into the table.  I want to run
many occurrences of this script to populate the database quicker, but I am
getting into trouble with the database being locked constantly if I try to
run only two instances of the script.

The faq indicates concurrency really isn't necessary for most situations
with the overall speeds of today's computers.  However, my server is a 12
year old ibm pc running linux with 64m of memory.   The harddisk speed is
very slow.  Is this going to prohibit running more than one process that
writes to a single database table?

I am new to databases so perhaps there is something very basic I am
overlooking here.  Wanted to give sqlite a real effort before trying mysql.

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

Re: [sqlite] rowid column as a FOREIGN KEY

[Oliver Peters]

Pavel Ivanov  writes:

> 
> > I can't understand why is it a bad practice to use database-provided
>>features?
> 
> You can use it when you are selecting. And even in this case you
> should use caution because without explicit column declared by you
> SQLite can change rowids without notice. And for foreign keys it's
> mandatory to reference to real columns.
> 
> > I think this is a bug. It should be fixed.
> 
> Nope, it's not a bug.
> 

[...]

Maybe it isn't a bug but the documentation should leave as little possibilities
as feasible for interpretation.

This seems to be a problem of wiggle room - it's the same if we talk about self
reference in connection with foreign keys
(http://thread.gmane.org/gmane.comp.db.sqlite.general/58688). You can't know
that it is only applicable to two different(!) tables only reading the
documentation.

Oliver

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

Re: [sqlite] rowid column as a FOREIGN KEY

[Pavel Ivanov]

> I can't understand why is it a bad practice to use database-provided features?

You can use it when you are selecting. And even in this case you
should use caution because without explicit column declared by you
SQLite can change rowids without notice. And for foreign keys it's
mandatory to reference to real columns.

> I think this is a bug. It should be fixed.

Nope, it's not a bug.

> I just wanted to write less text in my queries.

Where does explicitly declared column containing rowid introduce more
text to your queries? Could you give us examples? I bet in all of them
either you don't understand something or you use non-reliable
techniques which should be ditched at some point anyway if you want to
write easily readable and supportable application.


Pavel

On Mon, Aug 23, 2010 at 9:50 AM, inst  wrote:
>> Make it your rule of thumb: don't ever use rowid, declare your own
>> column as "integer primary key" and use it. It will come at no cost
>> for you and everything else will work much better.
>
> I just wanted to write less text in my queries.
> I can't understand why is it a bad practice to use database-provided features?
>
> I think this is a bug. It should be fixed.
> ___
> 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] Possible database corruption bug in SQLite

[Richard Hipp]

A user has provided us with a script that appears to result in SQLite
database corruption.  The problem has existed in all versions of SQLite
going back to 3.6.16 in June of 2009.  A bisect shows that the problem was
injected on 2009-06-17.

The problem appears to be associated with incremental vacuum.  We are still
working to characterize the problem more precisely.

The discovery of this bug so close to the release of version 3.7.1 is a
coincidence.  Nevertheless, we will likely be issuing version 3.7.2 within a
few days in order to fix the problem.  So if you are thinking of upgrading
to 3.7.1, you might want to hold back for a day or two to see what our
investigation of this new problem turns up.

-- 
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] rowid column as a FOREIGN KEY

[inst]

> Make it your rule of thumb: don't ever use rowid, declare your own
> column as "integer primary key" and use it. It will come at no cost
> for you and everything else will work much better.

I just wanted to write less text in my queries.
I can't understand why is it a bad practice to use database-provided features?

I think this is a bug. It should be fixed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rowid column as a FOREIGN KEY

[Pavel Ivanov]

Make it your rule of thumb: don't ever use rowid, declare your own
column as "integer primary key" and use it. It will come at no cost
for you and everything else will work much better.
Here is simplified example of your problem and solution:

sqlite> pragma foreign_keys=on;
sqlite> create table t (n int);
sqlite> create table tt (n int);
sqlite> create table ttt (a int, b int, foreign key (a) references t
(rowid), foreign key (b) references tt (rowid));
sqlite> insert into t values (1);
sqlite> insert into tt values (1);
sqlite> insert into ttt values (1, 1);
Error: foreign key mismatch
sqlite> drop table t;
sqlite> drop table tt;
sqlite> drop table ttt;
sqlite> create table t (id integer primary key, n int);
sqlite> create table tt (id integer primary key, n int);
sqlite> create table ttt (a int, b int, foreign key (a) references t
(id), foreign key (b) references tt (id));
sqlite> insert into t (n) values (1);
sqlite> insert into tt (n) values (1);
sqlite> insert into ttt values (1, 1);
sqlite> delete from t;
Error: foreign key constraint failed
sqlite>


Pavel

On Mon, Aug 23, 2010 at 9:23 AM, inst  wrote:
> Thanks for your answer, Oliver.
>
>> http://www.sqlite.org/foreignkeys.html
> Yes, I have already read this before mailing here.
>
>> PRAGMA foreign_keys
> Yes, I knew about this PRAGMA.
>
> Ok, I'll try to describe my problem with one example. Imagine we have
> to store information about pages and the keywords. Any page may
> contain any number of keywords, but any keyword may also belong to any
> number of pages. So I'm trying to implement many-to-many relationship.
>
> Here is small copy and paste from my terminal:
>
> SQLite version 3.7.0.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> PRAGMA foreign_keys = ON;
> sqlite> CREATE TABLE pages (
>   ...> address VARCHAR ( 128 ) DEFAULT '/',
>   ...> body TEXT DEFAULT ''
>   ...> );
> sqlite> CREATE TABLE keywords ( word VARCHAR ( 64 ) NOT NULL UNIQUE );
> sqlite> CREATE TABLE relations (
>   ...> page INTEGER NOT NULL,
>   ...> keyword INTEGER NOT NULL,
>   ...> FOREIGN KEY ( page ) REFERENCES pages( rowid ) ON UPDATE
> CASCADE ON DELETE CASCADE,
>   ...> FOREIGN KEY ( keyword ) REFERENCES keywords( rowid ) ON UPDATE
> CASCADE ON DELETE CASCADE
>   ...> );
> sqlite> INSERT INTO pages ( body ) VALUES ( '' );
> sqlite> INSERT INTO pages ( address ) VALUES ( '/contacts' );
> sqlite> INSERT INTO keywords ( word ) VALUES ( 'word1' );
> sqlite> INSERT INTO keywords ( word ) VALUES ( 'word2' );
>
> As for this place all were ok and here is the problem begin:
>
> sqlite> INSERT INTO relations VALUES ( 1,2 );
> Error: foreign key mismatch
> sqlite> INSERT INTO relations VALUES ( 1,1 );
> Error: foreign key mismatch
>
> Can anyone please tell what I did wrong?
> ___
> 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] Better way to export sqlite3 data than pipe?

[Jay A. Kreibich]

On Mon, Aug 23, 2010 at 08:09:51AM -0500, Peng Yu scratched on the wall:
> Hi,
> 
> Since I don't find a command that can directly export the data into a
> file, I use pipe to export data from sqlite3 to a tsv file. Is there a
> better way to do so?A

  See ".output "

   -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] Help with database corruption?

[Filip Navara]

I have uploaded a slightly different (and smaller) version of the
script. It triggers the bug in both version 3.7.0.1 and 3.7.1 on my
machine.

Thanks for testing,
F.

On Mon, Aug 23, 2010 at 3:00 PM, Black, Michael (IS)
 wrote:
> I confirmed this shows a malformed result on 3.6.23.1 and 3.7.0.1
>
> But it runs just fine under 3.7.1
>
> So apparently whatever bug you triggered has been fixed now.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Filip Navara
> Sent: Mon 8/23/2010 7:43 AM
> To: General Discussion of SQLite Database
> Cc: d...@hwaci.com
> Subject: EXTERNAL:Re: [sqlite] Help with database corruption?
>
>
>
> I can now reliably corrupt the database using standard commands. An
> SQL script can be downloaded at the address below that creates a
> database and then stresses it until a corruption happens.
>
> http://www.emclient.com/temp/sqlite_corrupt_log.zip
>
> Please help fix the problem or at least confirm that others can
> reproduce it using the same script.
>
> Best regards,
> Filip Navara
>
> On Fri, Aug 20, 2010 at 5:25 PM, Filip Navara  wrote:
>> Hello,
>>
>> is there anybody willing to help analyze corrupted database for
>> possible bug in SQLite?
>>
>> It is a database file taken from one of our test machines and it is
>> only few days old at most. The database file was only ever accessed
>> with SQLite 3.7.0.1. It has page size of 1024, WAL mode,
>> synchronous=FULL and incremental vacuum. No power failure happened on
>> that machine. Apparently somewhere during the course of execution of
>> our application the database free page list become corrupted. This is
>> for the third time this week the same error happened, but only this
>> time I have a copy of the database file. It is 887 Mb big (although
>> the real data consume less than 2 Mb) and so it is rather problematic
>> to provide the file. A copy is available at
>> http://www.emclient.com/temp/mail_data.zip.
>>
>> The database was accessed on Windows machine using SQLite 3.7.0.1
>> 64-bit build. The following statements are the only ones that were
>> executed against the database besides SELECTs and initial schema
>> definition:
>>
>> INSERT INTO LocalMailContents (
>>   "id", "partName", "contentType", "contentId",
>>   "contentDescription", "contentTransferEncoding",
>>   "contentMD5", "contentDisposition", "contentLanguage",
>>   "contentLocation", "partHeader", "partBody",
>>   "synchronizationKey", "contentLength")
>>   VALUES
>>   (@id, @partName, @contentType, @contentId, @contentDescription,
>>   �...@contenttransferencoding, @contentMD5, @contentDisposition,
>>   �...@contentlanguage, @contentLocation, @partHeader, @partBody,
>>   �...@synchronizationkey, @contentLength)
>> UPDATE LocalMailContents SET synchronizationk...@synchronizationkey
>> WHERE i...@id AND partna...@partname
>> UPDATE LocalMailContents SET parthead...@partheader WHERE i...@id AND
>> partna...@partname
>> UPDATE LocalMailContents SET partbo...@partcontent WHERE i...@id AND
>> partna...@partname
>> DELETE FROM LocalMailContents WHERE id IN ();
>> PRAGMA freelist_count;
>> PRAGMA incremental_vacuum();
>>
>> The error messages produced by "pragma integrity_check" are
>>
>> *** in database main ***
>> Main freelist: invalid page number 866828
>> Page 396 is never used
>> Page 473 is never used
>> Page 14780 is never used
>> Page 14915 is never used
>> Page 153649 is never used
>> Page 210894 is never used
>> Page 319247 is never used
>> Page 397006 is never used
>> Page 416545 is never used
>> Page 416636 is never used
>> Page 416704 is never used
>> Page 416705 is never used
>> Page 416706 is never used
>> Page 416707 is never used
>> Page 416708 is never used
>> Page 416710 is never used
>> Page 416711 is never used
>> ...
>>
>> I tried to locate the missing freelist pages in the database file and
>> they definitely were there at some point, but I am not familiar enough
>> with the file format to track the whole freelist and find where the
>> corruption exactly happen. All I know is that page 388 is corrupted
>> and points to a location outside of the database file.
>>
>> Thanks,
>> Filip Navara
>>
> ___
> 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] rowid column as a FOREIGN KEY

[inst]

Thanks for your answer, Oliver.

> http://www.sqlite.org/foreignkeys.html
Yes, I have already read this before mailing here.

> PRAGMA foreign_keys
Yes, I knew about this PRAGMA.

Ok, I'll try to describe my problem with one example. Imagine we have
to store information about pages and the keywords. Any page may
contain any number of keywords, but any keyword may also belong to any
number of pages. So I'm trying to implement many-to-many relationship.

Here is small copy and paste from my terminal:

SQLite version 3.7.0.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE pages (
   ...> address VARCHAR ( 128 ) DEFAULT '/',
   ...> body TEXT DEFAULT ''
   ...> );
sqlite> CREATE TABLE keywords ( word VARCHAR ( 64 ) NOT NULL UNIQUE );
sqlite> CREATE TABLE relations (
   ...> page INTEGER NOT NULL,
   ...> keyword INTEGER NOT NULL,
   ...> FOREIGN KEY ( page ) REFERENCES pages( rowid ) ON UPDATE
CASCADE ON DELETE CASCADE,
   ...> FOREIGN KEY ( keyword ) REFERENCES keywords( rowid ) ON UPDATE
CASCADE ON DELETE CASCADE
   ...> );
sqlite> INSERT INTO pages ( body ) VALUES ( '' );
sqlite> INSERT INTO pages ( address ) VALUES ( '/contacts' );
sqlite> INSERT INTO keywords ( word ) VALUES ( 'word1' );
sqlite> INSERT INTO keywords ( word ) VALUES ( 'word2' );

As for this place all were ok and here is the problem begin:

sqlite> INSERT INTO relations VALUES ( 1,2 );
Error: foreign key mismatch
sqlite> INSERT INTO relations VALUES ( 1,1 );
Error: foreign key mismatch

Can anyone please tell what I did wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Better way to export sqlite3 data than pipe?

[Simon Davies]

On 23 August 2010 14:09, Peng Yu  wrote:
> Hi,
>
> Since I don't find a command that can directly export the data into a
> file, I use pipe to export data from sqlite3 to a tsv file. Is there a
> better way to do so?

There is an alternative to using pipe:
http://www.sqlite.org/sqlite.html and look for "Writing results to a
file" on that page.

>
> $ cat main.sh
> #!/usr/bin/env bash
>
> rm -rf main.db3
> sqlite3 main.db3 '.read main.sql' > main.txt
>
> $ cat main.sql
> create table A (name text, position integer);
>
> insert into A values('a', 1);
> insert into A values('b', 10);
> .separator ','
> select * from A;
> $ ./main.sh
> $ cat main.txt
> a,1
> b,10
>
>
> --
> Regards,
> Peng

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

[sqlite] Better way to export sqlite3 data than pipe?

[Peng Yu]

Hi,

Since I don't find a command that can directly export the data into a
file, I use pipe to export data from sqlite3 to a tsv file. Is there a
better way to do so?

$ cat main.sh
#!/usr/bin/env bash

rm -rf main.db3
sqlite3 main.db3 '.read main.sql' > main.txt

$ cat main.sql
create table A (name text, position integer);

insert into A values('a', 1);
insert into A values('b', 10);
.separator ','
select * from A;
$ ./main.sh
$ cat main.txt
a,1
b,10


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

Re: [sqlite] Help with database corruption?

[Black, Michael (IS)]

I confirmed this shows a malformed result on 3.6.23.1 and 3.7.0.1
 
But it runs just fine under 3.7.1
 
So apparently whatever bug you triggered has been fixed now.
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Filip Navara
Sent: Mon 8/23/2010 7:43 AM
To: General Discussion of SQLite Database
Cc: d...@hwaci.com
Subject: EXTERNAL:Re: [sqlite] Help with database corruption?



I can now reliably corrupt the database using standard commands. An
SQL script can be downloaded at the address below that creates a
database and then stresses it until a corruption happens.

http://www.emclient.com/temp/sqlite_corrupt_log.zip

Please help fix the problem or at least confirm that others can
reproduce it using the same script.

Best regards,
Filip Navara

On Fri, Aug 20, 2010 at 5:25 PM, Filip Navara  wrote:
> Hello,
>
> is there anybody willing to help analyze corrupted database for
> possible bug in SQLite?
>
> It is a database file taken from one of our test machines and it is
> only few days old at most. The database file was only ever accessed
> with SQLite 3.7.0.1. It has page size of 1024, WAL mode,
> synchronous=FULL and incremental vacuum. No power failure happened on
> that machine. Apparently somewhere during the course of execution of
> our application the database free page list become corrupted. This is
> for the third time this week the same error happened, but only this
> time I have a copy of the database file. It is 887 Mb big (although
> the real data consume less than 2 Mb) and so it is rather problematic
> to provide the file. A copy is available at
> http://www.emclient.com/temp/mail_data.zip.
>
> The database was accessed on Windows machine using SQLite 3.7.0.1
> 64-bit build. The following statements are the only ones that were
> executed against the database besides SELECTs and initial schema
> definition:
>
> INSERT INTO LocalMailContents (
>   "id", "partName", "contentType", "contentId",
>   "contentDescription", "contentTransferEncoding",
>   "contentMD5", "contentDisposition", "contentLanguage",
>   "contentLocation", "partHeader", "partBody",
>   "synchronizationKey", "contentLength")
>   VALUES
>   (@id, @partName, @contentType, @contentId, @contentDescription,
>@contentTransferEncoding, @contentMD5, @contentDisposition,
>@contentLanguage, @contentLocation, @partHeader, @partBody,
>@synchronizationKey, @contentLength)
> UPDATE LocalMailContents SET synchronizationk...@synchronizationkey
> WHERE i...@id AND partna...@partname
> UPDATE LocalMailContents SET parthead...@partheader WHERE i...@id AND
> partna...@partname
> UPDATE LocalMailContents SET partbo...@partcontent WHERE i...@id AND
> partna...@partname
> DELETE FROM LocalMailContents WHERE id IN ();
> PRAGMA freelist_count;
> PRAGMA incremental_vacuum();
>
> The error messages produced by "pragma integrity_check" are
>
> *** in database main ***
> Main freelist: invalid page number 866828
> Page 396 is never used
> Page 473 is never used
> Page 14780 is never used
> Page 14915 is never used
> Page 153649 is never used
> Page 210894 is never used
> Page 319247 is never used
> Page 397006 is never used
> Page 416545 is never used
> Page 416636 is never used
> Page 416704 is never used
> Page 416705 is never used
> Page 416706 is never used
> Page 416707 is never used
> Page 416708 is never used
> Page 416710 is never used
> Page 416711 is never used
> ...
>
> I tried to locate the missing freelist pages in the database file and
> they definitely were there at some point, but I am not familiar enough
> with the file format to track the whole freelist and find where the
> corruption exactly happen. All I know is that page 388 is corrupted
> and points to a location outside of the database file.
>
> Thanks,
> Filip Navara
>
___
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] rowid column as a FOREIGN KEY

[Oliver Peters]

inst  writes:

>  
> Do anyone encounter this problem or I just did something wrong?
> 


If you've an example (CREATE & INSERT/DELETE statements or whatever clarifies
what you do) the possibility that someone can give you a quick and meaningful
answer will increase enormous.

My guess: you did something wrong -
just to test yourself: do you know
 PRAGMA foreign_keys=ON;

?

If not take a look at the documentation (http://www.sqlite.org/foreignkeys.html)

greetings
Oliver

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

Re: [sqlite] Help with database corruption?

[Filip Navara]

I can now reliably corrupt the database using standard commands. An
SQL script can be downloaded at the address below that creates a
database and then stresses it until a corruption happens.

http://www.emclient.com/temp/sqlite_corrupt_log.zip

Please help fix the problem or at least confirm that others can
reproduce it using the same script.

Best regards,
Filip Navara

On Fri, Aug 20, 2010 at 5:25 PM, Filip Navara  wrote:
> Hello,
>
> is there anybody willing to help analyze corrupted database for
> possible bug in SQLite?
>
> It is a database file taken from one of our test machines and it is
> only few days old at most. The database file was only ever accessed
> with SQLite 3.7.0.1. It has page size of 1024, WAL mode,
> synchronous=FULL and incremental vacuum. No power failure happened on
> that machine. Apparently somewhere during the course of execution of
> our application the database free page list become corrupted. This is
> for the third time this week the same error happened, but only this
> time I have a copy of the database file. It is 887 Mb big (although
> the real data consume less than 2 Mb) and so it is rather problematic
> to provide the file. A copy is available at
> http://www.emclient.com/temp/mail_data.zip.
>
> The database was accessed on Windows machine using SQLite 3.7.0.1
> 64-bit build. The following statements are the only ones that were
> executed against the database besides SELECTs and initial schema
> definition:
>
> INSERT INTO LocalMailContents (
>   "id", "partName", "contentType", "contentId",
>   "contentDescription", "contentTransferEncoding",
>   "contentMD5", "contentDisposition", "contentLanguage",
>   "contentLocation", "partHeader", "partBody",
>   "synchronizationKey", "contentLength")
>   VALUES
>   (@id, @partName, @contentType, @contentId, @contentDescription,
>   �...@contenttransferencoding, @contentMD5, @contentDisposition,
>   �...@contentlanguage, @contentLocation, @partHeader, @partBody,
>   �...@synchronizationkey, @contentLength)
> UPDATE LocalMailContents SET synchronizationk...@synchronizationkey
> WHERE i...@id AND partna...@partname
> UPDATE LocalMailContents SET parthead...@partheader WHERE i...@id AND
> partna...@partname
> UPDATE LocalMailContents SET partbo...@partcontent WHERE i...@id AND
> partna...@partname
> DELETE FROM LocalMailContents WHERE id IN ();
> PRAGMA freelist_count;
> PRAGMA incremental_vacuum();
>
> The error messages produced by "pragma integrity_check" are
>
> *** in database main ***
> Main freelist: invalid page number 866828
> Page 396 is never used
> Page 473 is never used
> Page 14780 is never used
> Page 14915 is never used
> Page 153649 is never used
> Page 210894 is never used
> Page 319247 is never used
> Page 397006 is never used
> Page 416545 is never used
> Page 416636 is never used
> Page 416704 is never used
> Page 416705 is never used
> Page 416706 is never used
> Page 416707 is never used
> Page 416708 is never used
> Page 416710 is never used
> Page 416711 is never used
> ...
>
> I tried to locate the missing freelist pages in the database file and
> they definitely were there at some point, but I am not familiar enough
> with the file format to track the whole freelist and find where the
> corruption exactly happen. All I know is that page 388 is corrupted
> and points to a location outside of the database file.
>
> Thanks,
> Filip Navara
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] rowid column as a FOREIGN KEY

[inst]

Hi,

Recently I've found one problem in SQLite and I think that this is a bug.

Are there any ways to use rowid column in one table as a foreign key in another?
By the rowid I mean internal column that used as an alias for INTEGER
PRIMARY KEY columns.

As described at documents, SQLite supports foreign keys as of version 3.6.19.
When I use a new version of SQLite it doesn't do anything to save a
referential integrity.
I also tried to use utility .genfkey in older version to create
TRIGGERS for my database but it says that there is no such column in
parent table or something like this.

Do anyone encounter this problem or I just did something wrong?

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

Re: [sqlite] SQLite Version 3.7.1

[Richard Hipp]

On Mon, Aug 23, 2010 at 5:50 AM, Alexey Pechnikov wrote:

> Richard, how to set the default value for SQLITE_FCNTL_CHUNK_SIZE? And
> is it possible to change this from sqlite3 shell and from tcl
> interface?
>
>
There is no way to set the default value - the value must be set anew with
each connection.

After we gain experience with this setting, and determine (via actual
measurement) whether or not it is useful in reducing disk fragmentation, we
will consider providing a PRAGMA interface for it.


> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] curses vs ncurses

[Yury G. Kudryashov]

Hi!

Could you please replace
   AC_SEARCH_LIBS(tgetent, curses, [], [])
by
   AC_SEARCH_LIBS(tgetent, curses ncurses ncursesw, [], [])
in configure.ac? This would help finding libncurses.so on systems lacking 
compatibility libcurses.so symlink.

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

Re: [sqlite] SQLite Version 3.7.1

[Alexey Pechnikov]

Richard, how to set the default value for SQLITE_FCNTL_CHUNK_SIZE? And
is it possible to change this from sqlite3 shell and from tcl
interface?

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users