Re: [sqlite] Malformed database error when using FTS3/4
On 10/13/2011 10:45 PM, Wendland, Michael wrote:
I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather strange.
CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
INSERT INTO fts (tags) VALUES ('tag1');
SELECT * FROM fts WHERE tags MATCH 'tag1';
Now close the database and reopen it, then try the following statements.
UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
SELECT * FROM fts WHERE tags MATCH 'tag1';
Is anyone able to help? (I haven't accidentally posted this to the wrong list
have I?)
Thanks for reporting this. Now fixed here:
http://www.sqlite.org/src/info/7e24645be2
Bug report:
http://www.sqlite.org/src/info/9fd058691b
You are correct in that the bug only occurs when the FTS
table contains exactly one row. Bug was introduced in version
3.7.7.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database error when using FTS3/4
On 13 Oct 2011 at 16:59, Filip Navara wrote:
> Reproduced on Windows, SQLite 3.7.8.
Seems OK here, OS X 10.7.2:
Second-Mini% sqlite3 wiggy
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
sqlite> INSERT INTO fts (tags) VALUES ('tag1');
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tags
--
tag1
sqlite> ^D
Second-Mini% sqlite3 wiggy
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tags
--
tag1
sqlite>
--
Cheers -- Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database error when using FTS3/4
On Oct 13, 2011, at 5:59 PM, Filip Navara wrote:
> Reproduced on Windows, SQLite 3.7.8.
Works ok on Mac OS X 10.6.8.
$ sqlite3 -version
3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177
sqlite> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
sqlite> INSERT INTO fts (tags) VALUES ('tag1');
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tag1
sqlite> UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tag1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database error when using FTS3/4
Reproduced on Windows, SQLite 3.7.8.
F.
On Thu, Oct 13, 2011 at 5:45 PM, Wendland, Michael
wrote:
> Hello,
>
> I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather
> strange. It's reproducible so far as I know.
>
> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
> INSERT INTO fts (tags) VALUES ('tag1');
> SELECT * FROM fts WHERE tags MATCH 'tag1';
>
> You can run an UPDATE to change the value in the tags field and rerun that
> SELECT statement without error. Do not create a second row, as this error
> seems to appear only when there is a single row in the virtual table. Now
> close the database and reopen it, then try the following statements.
>
> UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
> SELECT * FROM fts WHERE tags MATCH 'tag1';
>
> That SELECT returns "Error: database disk image is malformed". Taking a dump
> before and after the UPDATE shows that a segdir entry is being assigned a
> different value despite the tags entry remaining exactly the same. Changing
> the value further does not rectify the situation, nor does using a different
> string. Running a "PRAGMA integrity_check" returns "ok", so the underlying
> database is fine (and you can use other tables and any rows you insert after
> just fine). Inserting a new row and then changing the value rectifies the
> problem. Tests seem to indicate that it doesn't matter what the rowid is (or
> the sequence of inserts and deletes have been run on the virtual table
> beforehand), the problem appears when changing the only row in the virtual
> table.
>
> I've reproduced this using a CLI compiled from the autoconf source
> amalgamation on RHEL 5.5 (in a VM) and the precompiled Windows CLI.
>
> Is anyone able to help? (I haven't accidentally posted this to the wrong
> list have I?)
>
> - Michael
> ___
> 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] Malformed database error when using FTS3/4
Hello,
I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather strange.
It's reproducible so far as I know.
CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
INSERT INTO fts (tags) VALUES ('tag1');
SELECT * FROM fts WHERE tags MATCH 'tag1';
You can run an UPDATE to change the value in the tags field and rerun that
SELECT statement without error. Do not create a second row, as this error
seems to appear only when there is a single row in the virtual table. Now
close the database and reopen it, then try the following statements.
UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
SELECT * FROM fts WHERE tags MATCH 'tag1';
That SELECT returns "Error: database disk image is malformed". Taking a dump
before and after the UPDATE shows that a segdir entry is being assigned a
different value despite the tags entry remaining exactly the same. Changing
the value further does not rectify the situation, nor does using a different
string. Running a "PRAGMA integrity_check" returns "ok", so the underlying
database is fine (and you can use other tables and any rows you insert after
just fine). Inserting a new row and then changing the value rectifies the
problem. Tests seem to indicate that it doesn't matter what the rowid is (or
the sequence of inserts and deletes have been run on the virtual table
beforehand), the problem appears when changing the only row in the virtual
table.
I've reproduced this using a CLI compiled from the autoconf source amalgamation
on RHEL 5.5 (in a VM) and the precompiled Windows CLI.
Is anyone able to help? (I haven't accidentally posted this to the wrong list
have I?)
- Michael
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
