[sqlite] Database corruption with fts table after closing/reopening the database

[Julien LF]

Hello,

I'm testing the fts extensions and experiencing a 'database disk image
is malformed' problem that I can reproduce the following way:
- Create a database
- Open the database to insert/update rows. 'match' queries work at this point
- Close the database
- Open the database. 'match' queries still work at this point
- Update a row
- 'match' queries generate a 'database disk image is malformed'
message, but normal selects work fine.

This is sqlite 3.7.7.1 on NetBSD 5.1. I tried both fts3 and fts4.

You'll find the schema below, followed by the complete detailed steps
to produce the error. Is there something I'm missing?

Regards,
Julien

CREATE VIRTUAL TABLE page USING fts4 (
url,
title,
content
);

vnetbsd$ sqlite3 wiki.db < ./schema.sql
vnetbsd$ sqlite3 ./wiki.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> insert into page(url,title,content) values("intro","intro","intro");
sqlite> select * from page where url match 'intro';
intro|intro|intro
sqlite> update page set url="intro", title="intro", content="introm"
where rowid=1;
sqlite> select * from page where url match 'intro';
intro|intro|introm
sqlite> .q
vnetbsd$ sqlite3 ./wiki.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from page where url match 'intro';
intro|intro|introm
sqlite> update page set url="intro", title="intro", content="intro"
where rowid=1;
sqlite> select * from page where url match 'intro';
Error: database disk image is malformed
sqlite> select * from page;
intro|intro|intro
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Kevin Benson]

Did you try surrounding them strings with single quotes, instead ?
--
--
--
--ô¿ô--
K e V i N

[Julien LF]

> Did you try surrounding them strings with single quotes, instead ?

I got the same problem using single quotes.
However the machine I was performing those tests on is a vmware virtual machine.
Performing the same steps on physical hardware (identical os & sqlite
version) worked fine, so I guess there is some inconsistency somewhere
between NetBSD and vmware at the disk writing level.

Sorry for the noise.

Julien

[Simon Slavin]

On 10 Feb 2012, at 11:01am, Julien LF wrote:

>> Did you try surrounding them strings with single quotes, instead ?
>
> I got the same problem using single quotes.
> However the machine I was performing those tests on is a vmware virtual machine.
> Performing the same steps on physical hardware (identical os & sqlite
> version) worked fine, so I guess there is some inconsistency somewhere
> between NetBSD and vmware at the disk writing level.
>
> Sorry for the noise.

Actually I'm a little concerned at that. SQLite may be marvellous but it's not mystical. Many other programs use similar patterns of disk reading and writing. You seem to have found a serious fault in the virtual machine system.

Simon.