On VACUUM I get "constraint failed"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

On VACUUM I get "constraint failed"

John Duprey
We have an SQLite database with a table that has about a million rows.
 We do a lot of (thousands) of deletes and inserts in this table.
Recently, deleting rows has become slow.  I assumed the slow down was
a result of fragmentation or wasted space due to the deletes.
Therefore, I thought vacuuming would fix it.  While trying to VACUUM
the database, I got this error from sqlite3:
"SQL error: constraint failed"

Does anyone have any ideas what constraint could be failing and how to fix it?

I've attached the schema of the db.. if anyone thinks its relevant.

Thanks,

-John

schema.sql (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: On VACUUM I get "constraint failed"

Preston Zaugg
Hard to tell from the schema that you attached where the problem might be...
but try doing a .dump from the command line util and re-importing into a
clean db, it should where expose the issue is.

sqlite> .output db.sql
sqlite> .dump
sqlite> .exit

sqlite3 -init db.sql test.db

hope this helps
--preston

>From: John Duprey <[hidden email]>
>Reply-To: [hidden email]
>To: [hidden email]
>Subject: [sqlite] On VACUUM I get "constraint failed"
>Date: Thu, 20 Oct 2005 14:01:10 -0400
>
>We have an SQLite database with a table that has about a million rows.
>  We do a lot of (thousands) of deletes and inserts in this table.
>Recently, deleting rows has become slow.  I assumed the slow down was
>a result of fragmentation or wasted space due to the deletes.
>Therefore, I thought vacuuming would fix it.  While trying to VACUUM
>the database, I got this error from sqlite3:
>"SQL error: constraint failed"
>
>Does anyone have any ideas what constraint could be failing and how to fix
>it?
>
>I've attached the schema of the db.. if anyone thinks its relevant.
>
>Thanks,
>
>-John


><< schema.sql >>


Reply | Threaded
Open this post in threaded view
|

Re: On VACUUM I get "constraint failed"

John Duprey
Preston,

Thanks for the advice.  I dumped the sql and attempted to recreate the
db and got this error:
>sqlite3 -init db.sql test.db
Loading resources from db.sql
INSERT INTO "category_meta_information" VALUES(NULL, NULL, NULL, NULL, NULL);
SQL error: category_meta_information.taxonomy_id may not be NULL
SQLite version 3.2.0
Enter ".help" for instructions
sqlite> .quit

The schema should have prevented a row like that from ever occurring.
I'm not sure how it got there!  Anyway, I removed the I removed that
row from the sql file and was able to recreate the db.  I was unable
to delete the row in the original taxonomy db.

sqlite> select rowid,* from category_meta_information where taxonomy_id is NULL;
40|||||

sqlite> delete from category_meta_information where rowid=40;

sqlite> select rowid,* from category_meta_information where taxonomy_id is NULL;
40|||||

This is very curious..

On 10/21/05, Preston Zaugg <[hidden email]> wrote:

> Hard to tell from the schema that you attached where the problem might be...
> but try doing a .dump from the command line util and re-importing into a
> clean db, it should where expose the issue is.
>
> sqlite> .output db.sql
> sqlite> .dump
> sqlite> .exit
>
> sqlite3 -init db.sql test.db
>
> hope this helps
> --preston
>
> >From: John Duprey <[hidden email]>
> >Reply-To: [hidden email]
> >To: [hidden email]
> >Subject: [sqlite] On VACUUM I get "constraint failed"
> >Date: Thu, 20 Oct 2005 14:01:10 -0400
> >
> >We have an SQLite database with a table that has about a million rows.
> >  We do a lot of (thousands) of deletes and inserts in this table.
> >Recently, deleting rows has become slow.  I assumed the slow down was
> >a result of fragmentation or wasted space due to the deletes.
> >Therefore, I thought vacuuming would fix it.  While trying to VACUUM
> >the database, I got this error from sqlite3:
> >"SQL error: constraint failed"
> >
> >Does anyone have any ideas what constraint could be failing and how to fix
> >it?
> >
> >I've attached the schema of the db.. if anyone thinks its relevant.
> >
> >Thanks,
> >
> >-John
>
>
> ><< schema.sql >>
>
>
>