posting an update here --
I DROPped the insert_fts TRIGGER, and now the db works fine. No more
crash on inserting a new row. So, the error was because of the trigger
CREATE TRIGGER insert_fts
AFTER INSERT ON pages
BEGIN
INSERT INTO fts_pages (rowid, page_text)
VALUES (new.page_id, new.page_text);
END;
Of course, now my fts table is not being updated automatically on insert.
Hope this helps in diagnosing the cause.
On 3/3/08, P Kishor <[EMAIL PROTECTED]> wrote:
> Dan,
>
>
>
> On 3/2/08, Dan <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > I tried this script here with the latest CVS version and
> > it didn't crash:
> >
> > CREATE TABLE pages(page_id INTEGER PRIMARY KEY, page_name TEXT,
> > page_text TEXT);
> > CREATE VIRTUAL TABLE fts_pages USING fts3(page_name, page_text);
> >
> >
> > CREATE TRIGGER delete_fts
> > AFTER DELETE ON pages
> > BEGIN
> > DELETE FROM fts_pages WHERE rowid = old.page_id;
> > END;
> >
> > CREATE TRIGGER insert_fts
> > AFTER INSERT ON pages
> > BEGIN
> > INSERT INTO fts_pages (rowid, page_text)
> > VALUES (new.page_id, new.page_text);
> > END;
> >
> > CREATE TRIGGER update_fts
> > AFTER UPDATE OF page_text ON pages
> > BEGIN
> > UPDATE fts_pages
> > SET page_text = new.page_text
> > WHERE rowid = old.page_id;
> > END;
> >
> >
> > INSERT INTO pages (page_name, page_text) VALUES ('foo', 'bar');
> >
> >
> > Can you test this in your environment?
>
>
> I am not sure what I should test in my environment. Did you mean to
> send me some script? Or, are you asking me to try the latest version
> from CVS?
>
> If the former, I don't see any script. If the latter, compiling the
> latest version via CVS would be very difficult for me as I have only
> one machine and I can't install a new, test version of the db without
> going through significant contortions.
>
>
> > If this fails to produce
> > the crash, can you post the offending database? Or send it to
> > me if you don't want it made public.
>
>
> I am still getting a crash, and only on INSERT. Well, at least, I am
> not getting a crash on UPDATE. Haven't tried DELETE.
>
> I will try and package a small version of the db and send it to you
> offline, if that is ok.
>
>
> > Also, the exact sqlite version
> > might be helpful.
> >
>
>
> I thought I had provided that above. Here you go
>
> [11:00 PM] ~/Data/punkish$ sqlite3 --version
> 3.5.6
>
>
> > Alternatively, can you compile with debugging symbols enabled and
> > post a stack trace?
> >
>
>
>
> Hmmm... that would entail recompiling an otherwise working program,
> and the same difficulty as compiling the latest version from CVS.
> Nevertheless, if you can point me to the instructions on compiling
> with "debugging symbols enabled and post(ing) a stack trace," I will
> try my best to do so.
>
> Many thanks. My work is stuck for now as I consistenly get the error.
> I am assuming the Crash Log that I posted was not much help, no?
>
>
> >
> >
> >
> > On Mar 3, 2008, at 4:33 AM, P Kishor wrote:
> >
> > > I have a fairly simple db with fts3
> > >
> > > TABLE pages (page_id INTEGER PRIMARY KEY, page_name TEXT, page_text
> > > TEXT);
> > >
> > > VIRTUAL TABLE fts_pages USING fts3 (page_name, page_text);
> > >
> > > and the following triggers --
> > >
> > > CREATE TRIGGER delete_fts
> > > AFTER DELETE ON pages
> > > BEGIN
> > > DELETE FROM fts_pages WHERE rowid = old.page_id;
> > > END;
> > >
> > > CREATE TRIGGER insert_fts
> > > AFTER INSERT ON pages
> > > BEGIN
> > > INSERT INTO fts_pages (rowid, page_text)
> > > VALUES (new.page_id, new.page_text);
> > > END;
> > >
> > > CREATE TRIGGER update_fts
> > > AFTER UPDATE OF page_text ON pages
> > > BEGIN
> > > UPDATE fts_pages
> > > SET page_text = new.page_text
> > > WHERE rowid = old.page_id;
> > > END;
> > >
> > > When I enter a new row like so via the sqlite3 (version 3.5.6) shell
> > > on my MBP with OS X 10.5.2, I get the following error
> > >
> > > sqlite> INSERT INTO pages (page_name, page_text) VALUES ('foo',
> > > 'bar');
> > > Bus error
> > >
> > > and the sqlite3 shell quits out to the bash shell.
> > >
> > > A small journal file is left, and if I enter sqlite3 again, I get the
> > > message that the db is locked. I can proceed once I delete the journal
> > > file. I have the following crash log to report
> > >
> > > Process: sqlite3 [1464]
> > > Path: /usr/local/bin/sqlite3
> > > Identifier: sqlite3
> > > Version: ??? (???)
> > > Code Type: X86 (Native)
> > > Parent Process: bash [291]
> > >
> > > Date///Time: 2008-03-02 15:24:45.729 -0600
> > > OS Version: Mac OS X 10.5.2 (9C31)
> > > Report Version: 6
> > >
> > > Exception Type: EXC_BAD_ACCESS (SIGBUS)
> > > Exception Codes: KERN_PROTECTION_FAILURE at 0x0000000000000000
> > > Crashed Thread: 0
> > >
> > > Thread 0 Crashed:
> > > 0 libsqlite3.0.dylib 0x000ba7d8 sqlite3Step + 15862
> > > 1 libsqlite3.0.dylib 0x000bde17 sqlite3_step + 75
> > > 2 libsqlite3.0.dylib 0x000a333d sqlite3_exec + 209
> > > 3 sqlite3 0x00006059 process_input + 1124
> > > 4 sqlite3 0x00006c2a main + 2527
> > > 5 sqlite3 0x00002596 start + 54
> > >
> > > Thread 0 crashed with X86 Thread State (32-bit):
> > > eax: 0x00807480 ebx: 0x000b69f3 ecx: 0x0013d808 edx: 0x00000000
> > > edi: 0x0080b04c esi: 0x00000018 ebp: 0xbfffed98 esp: 0xbfffe930
> > > ss: 0x0000001f efl: 0x00010206 eip: 0x000ba7d8 cs: 0x00000017
> > > ds: 0x0000001f es: 0x0000001f fs: 0x00000000 gs: 0x00000037
> > > cr2: 0x00000000
> > >
> > > Binary Images:
> > > 0x1000 - 0x7fe7 +sqlite3 ??? (???) /usr/local/bin/sqlite3
> > > 0x21000 - 0x37fea libedit.2.dylib ??? (???)
> > > <be5a6f391887bb96bdeeafd443cf19fb> /usr/lib/libedit.2.dylib
> > > 0x6a000 - 0xc8fff +libsqlite3.0.dylib ??? (???)
> > > /usr/local/lib/libsqlite3.0.dylib
> > > 0x8fe00000 - 0x8fe2da53 dyld 96.2 (???)
> > > <7af47d3b00b2268947563c7fa8c59a07> /usr/lib/dyld
> > > 0x9001c000 - 0x90020fff libmathCommon.A.dylib ??? (???)
> > > /usr/lib/system/libmathCommon.A.dylib
> > > 0x908cb000 - 0x908d2fe9 libgcc_s.1.dylib ??? (???)
> > > <f53c808e87d1184c0f9df63aef53ce0b> /usr/lib/libgcc_s.1.dylib
> > > 0x9126b000 - 0x9129aff7 libncurses.5.4.dylib ??? (???)
> > > <3b2ac2ca8190942b6b81d2a7012ea859> /usr/lib/libncurses.5.4.dylib
> > > 0x923c8000 - 0x92527ff3 libSystem.B.dylib ??? (???)
> > > <4899376234e55593b22fc370935f8cdf> /usr/lib/libSystem.B.dylib
> > > 0xffff0000 - 0xffff1780 libSystem.B.dylib ??? (???) /usr/lib/
> > > libSystem.B.dylib
> > >
> > > Any advice?
> >
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [email protected]
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
