Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

[OrenKishon]

According to your pragmas you have vacuum done once in a while. Vacuum alters
the rowid values of a table, unless the rowid is declared explicitly as
"INTEGER PRIMARY KEY" (probably not the case here). See  from here
  :

/If the rowid is not aliased by INTEGER PRIMARY KEY then it is not
persistent and might change. In particular the VACUUM command will change
rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore,
applications should not normally access the rowid directly, but instead use
an INTEGER PRIMARY KEY.
/

Once rowids change, the references between the fts table to the content
table are broken. 




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

[Nick]

 
 
Hi Dan
 

 
Did you receive below? Would extracted db be useful for debugging?  
 

 
Regards  
 
Nick  
 

   
 
 
>  
> On 18 Jul 2018 at 22:41,wrote:
>  
>  
>  On 18 Jul 2018, at 14:09, Dan Kennedy wrote:  >   >   >  Easiest explanation 
> is that something is writing directly to the FTS5 table, bypassing the 
> external content table.  >   >  Otherwise, it may be a bug in fts5. How large 
> is the corrupted db? Are you able to share it with us?  >   >  Dan. FTS5 
> table is exclusively modified with triggers. If I dropped all tables except 
> the FTS5 table and external content table would that still be useful for you? 
> It would be around 500MB uncompressed. Have you got a way to upload it? 
> Regards Nick ___ sqlite-users 
> mailing list sqlite-users@mailinglists.sqlite.org 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>  
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

[Nick]

On 18 Jul 2018, at 14:09, Dan Kennedy wrote:
> 
> 
> Easiest explanation is that something is writing directly to the FTS5 table, 
> bypassing the external content table.
> 
> Otherwise, it may be a bug in fts5. How large is the corrupted db? Are you 
> able to share it with us?
> 
> Dan.

FTS5 table is exclusively modified with triggers. 

If I dropped all tables except the FTS5 table and external content table would 
that still be useful for you? 

It would be around 500MB uncompressed. Have you got a way to upload it?

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

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

[Dan Kennedy]

On 07/18/2018 02:52 AM, Nick wrote:

On 2018-07-10 21:17, Dan Kennedy wrote:

On 07/11/2018 02:56 AM, Nick wrote:

Using sqlite cli version 3.13 I have a simple schema with a virtual
FTS5 table providing full index searching. It is accessed by a python
application using apsw==3.13.0.post1.

I could successfully use the full index functionality during manual
testing of the db at creation time (probably a year ago now) however,
recently I've been getting "Error: database disk image is malformed"
messages when running queries on the FTS5 virtual table.

In an attempt to explore further I downloaded the latest 3.24
version. With this latest version I used the ".backup" command to
create a copy of the file in the hope of eliminating HDD errors being
a culprit.

Running pragma quick_check and integrity_check on the copied db both
return ok.

The schema of the FTS5 table is:

CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);

The table is exclusive kept up to date using triggers:

-- Triggers to keep the FTS index up to date.

CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
   INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
   INSERT INTO [i_epg]([i_epg], rowid, [mangled_title],
[mangled_subtitle], [mangled_summary]) VALUES('delete', old.[tid],
old.[mangled_title], old.[mangled_subtitle], old.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
   INSERT INTO [i_epg]([i_epg], rowid, [mangled_title],
[mangled_subtitle], [mangled_summary]) VALUES('delete', old.[tid],
old.[mangled_title], old.[mangled_subtitle], old.[mangled_summary]);
   INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;

Running SQL queries on the normal tables all work as expected.
Digging further on the FTS5 queries I noticed the following behaviour:

SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big +
ban*';
- expect results - actually returns "Error: database disk image is
malformed" immediately

SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big +
ban*';
- expect no results - returns no results

SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{
mangled_title } : black + adder';
- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder

I've never come across a disk image malformed error in my years of
using sqlite3 so not sure where to turn to next. Questions are:

1. Is this a known issue with FTS5 tables and if so is there a
workaround?

2. It appears the FTS5 virtual table is corrupt. Is there a way to
rebuild the FTS5 (drop table and recreate?) from just the sqlite cli
tool?


Try running the FTS5 integrity-check command with the 3.24.0 command
line to ensure it really is corrupt:

  https://www.sqlite.org/fts5.html#the_integrity_check_command

The index can be rebuilt using the rebuild command:

  https://www.sqlite.org/fts5.html#the_rebuild_command

3.13.0 was about 2 years ago. There have been a couple of fixes for
fts5 corruption bugs since then. This one, for example:

  https://www.sqlite.org/src/info/9a2de4f05fabf7e7

So you may have hit a known issue. Hard to say.

Dan.



Part II

With the help from Dan the FTS5 table was fixed and then subsequently
worked as expected. For belt and braces, using the 3.24 sqlite cli
client, I created a new db with the below PRAGMA statements and then ran
".dump"' to copy over the records from the previous db.

PRAGMA legacy_file_format = off;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum = 2;
PRAGMA foreign_keys = on;
PRAGMA journal_mode = wal;
PRAGMA application_id = 19;

Both PRAGMA and FTS integrity returned ok and manual testing showed the
new db worked as expected. At the same time I've upgrade apsw to the
latest version (I saw it downloaded 3.24 file during compiling).

A number of days later I've gone back and ran the  INSERT INTO
[i_epg]([i_epg]) VALUES('integrity-check') cmd and disappointingly it
returned Error: database disk image is malformed.



Easiest explanation is that something is writing directly to the FTS5 
table, bypassing the external content table.


Otherwise, it may be a bug in fts5. How large is the corrupted db? Are 
you able to share it with us?


Dan.







However unlike my first report above the same FTS5 

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

[Nick]

On 2018-07-10 21:17, Dan Kennedy wrote:

On 07/11/2018 02:56 AM, Nick wrote:
Using sqlite cli version 3.13 I have a simple schema with a virtual 
FTS5 table providing full index searching. It is accessed by a python 
application using apsw==3.13.0.post1.


I could successfully use the full index functionality during manual 
testing of the db at creation time (probably a year ago now) however, 
recently I've been getting "Error: database disk image is malformed" 
messages when running queries on the FTS5 virtual table.


In an attempt to explore further I downloaded the latest 3.24 version. 
With this latest version I used the ".backup" command to create a copy 
of the file in the hope of eliminating HDD errors being a culprit.


Running pragma quick_check and integrity_check on the copied db both 
return ok.


The schema of the FTS5 table is:

CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);

The table is exclusive kept up to date using triggers:

-- Triggers to keep the FTS index up to date.

CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
   INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES (new.[tid], new.[mangled_title], 
new.[mangled_subtitle], new.[mangled_summary]);

END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
   INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], 
[mangled_subtitle], [mangled_summary]) VALUES('delete', old.[tid], 
old.[mangled_title], old.[mangled_subtitle], old.[mangled_summary]);

END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
   INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], 
[mangled_subtitle], [mangled_summary]) VALUES('delete', old.[tid], 
old.[mangled_title], old.[mangled_subtitle], old.[mangled_summary]);
   INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES (new.[tid], new.[mangled_title], 
new.[mangled_subtitle], new.[mangled_summary]);

END;

Running SQL queries on the normal tables all work as expected. Digging 
further on the FTS5 queries I noticed the following behaviour:


SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + 
ban*';
- expect results - actually returns "Error: database disk image is 
malformed" immediately


SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + 
ban*';

- expect no results - returns no results

SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ 
mangled_title } : black + adder';

- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder

I've never come across a disk image malformed error in my years of 
using sqlite3 so not sure where to turn to next. Questions are:


1. Is this a known issue with FTS5 tables and if so is there a 
workaround?


2. It appears the FTS5 virtual table is corrupt. Is there a way to 
rebuild the FTS5 (drop table and recreate?) from just the sqlite cli 
tool?


Try running the FTS5 integrity-check command with the 3.24.0 command
line to ensure it really is corrupt:

  https://www.sqlite.org/fts5.html#the_integrity_check_command

The index can be rebuilt using the rebuild command:

  https://www.sqlite.org/fts5.html#the_rebuild_command

3.13.0 was about 2 years ago. There have been a couple of fixes for
fts5 corruption bugs since then. This one, for example:

  https://www.sqlite.org/src/info/9a2de4f05fabf7e7

So you may have hit a known issue. Hard to say.

Dan.



Part II

With the help from Dan the FTS5 table was fixed and then subsequently 
worked as expected. For belt and braces, using the 3.24 sqlite cli 
client, I created a new db with the below PRAGMA statements and then ran 
".dump"' to copy over the records from the previous db.


PRAGMA legacy_file_format = off;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum = 2;
PRAGMA foreign_keys = on;
PRAGMA journal_mode = wal;
PRAGMA application_id = 19;

Both PRAGMA and FTS integrity returned ok and manual testing showed the 
new db worked as expected. At the same time I've upgrade apsw to the 
latest version (I saw it downloaded 3.24 file during compiling).


A number of days later I've gone back and ran the  INSERT INTO 
[i_epg]([i_epg]) VALUES('integrity-check') cmd and disappointingly it 
returned Error: database disk image is malformed.


However unlike my first report above the same FTS5 queries are all 
working and returning results as expected.


I'm at a loss.

Regards
Nick
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Corrupted FTS5 index? disk image is malformed

[Nick]

 
 
 
>  
> On 11 Jul 2018 at 9:28 am,wrote:
>  
>  
>  Yours is not a contentless table. It is an "external content" table. Dan. 

 
>  
>  
>
>  
>  
>Noted. Thanks for the clarification.
> Regards  
 
> Nick
 
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted FTS5 index? disk image is malformed

[Dan Kennedy]

On 07/11/2018 04:04 AM, Nick wrote:

On 10 Jul 2018, at 21:17, Dan Kennedy wrote:

Try running the FTS5 integrity-check command with the 3.24.0 command line to 
ensure it really is corrupt:

  https://www.sqlite.org/fts5.html#the_integrity_check_command

The index can be rebuilt using the rebuild command:

  https://www.sqlite.org/fts5.html#the_rebuild_command

3.13.0 was about 2 years ago. There have been a couple of fixes for fts5 
corruption bugs since then. This one, for example:

  https://www.sqlite.org/src/info/9a2de4f05fabf7e7

So you may have hit a known issue. Hard to say.

Dan.


Thanks Dan.

Reading the webpage it says it doesn't work for contentless FTS5 but ran the 
commands anyway


Yours is not a contentless table. It is an "external content" table.

Dan.





sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('integrity-check');
Error: database disk image is malformed
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('rebuild');
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('integrity-check');
sqlite>

Running previous commands also seem to show its been fixed

sqlite> SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + 
ban';
sqlite> SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + 
ban*';

sqlite> SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ 
mangled_title } : black + adder';
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
sqlite>

Thanks again Dan.

Regards
Nick

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



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

Re: [sqlite] Corrupted FTS5 index? disk image is malformed

[Nick]

On 10 Jul 2018, at 21:17, Dan Kennedy wrote:
>> 
> 
> Try running the FTS5 integrity-check command with the 3.24.0 command line to 
> ensure it really is corrupt:
> 
>  https://www.sqlite.org/fts5.html#the_integrity_check_command
> 
> The index can be rebuilt using the rebuild command:
> 
>  https://www.sqlite.org/fts5.html#the_rebuild_command
> 
> 3.13.0 was about 2 years ago. There have been a couple of fixes for fts5 
> corruption bugs since then. This one, for example:
> 
>  https://www.sqlite.org/src/info/9a2de4f05fabf7e7
> 
> So you may have hit a known issue. Hard to say.
> 
> Dan.
> 

Thanks Dan.

Reading the webpage it says it doesn't work for contentless FTS5 but ran the 
commands anyway

sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('integrity-check');
Error: database disk image is malformed
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('rebuild');
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('integrity-check');
sqlite> 

Running previous commands also seem to show its been fixed

sqlite> SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + 
ban';
sqlite> SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + 
ban*';

sqlite> SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ 
mangled_title } : black + adder';
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
sqlite> 

Thanks again Dan.

Regards
Nick

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

Re: [sqlite] Corrupted FTS5 index? disk image is malformed

[Dan Kennedy]

On 07/11/2018 02:56 AM, Nick wrote:

Using sqlite cli version 3.13 I have a simple schema with a virtual FTS5 table 
providing full index searching. It is accessed by a python application using 
apsw==3.13.0.post1.

I could successfully use the full index functionality during manual testing of the db at 
creation time (probably a year ago now) however, recently I've been getting "Error: 
database disk image is malformed" messages when running queries on the FTS5 virtual 
table.

In an attempt to explore further I downloaded the latest 3.24 version. With this latest 
version I used the ".backup" command to create a copy of the file in the hope 
of eliminating HDD errors being a culprit.

Running pragma quick_check and integrity_check on the copied db both return ok.

The schema of the FTS5 table is:

CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);

The table is exclusive kept up to date using triggers:

-- Triggers to keep the FTS index up to date.

CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
   INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES (new.[tid], new.[mangled_title], 
new.[mangled_subtitle], new.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
   INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title], 
old.[mangled_subtitle], old.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
   INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title], 
old.[mangled_subtitle], old.[mangled_summary]);
   INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES (new.[tid], new.[mangled_title], 
new.[mangled_subtitle], new.[mangled_summary]);
END;

Running SQL queries on the normal tables all work as expected. Digging further 
on the FTS5 queries I noticed the following behaviour:

SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect results - actually returns "Error: database disk image is malformed" 
immediately

SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect no results - returns no results

SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : 
black + adder';
- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder

I've never come across a disk image malformed error in my years of using 
sqlite3 so not sure where to turn to next. Questions are:

1. Is this a known issue with FTS5 tables and if so is there a workaround?

2. It appears the FTS5 virtual table is corrupt. Is there a way to rebuild the 
FTS5 (drop table and recreate?) from just the sqlite cli tool?


Try running the FTS5 integrity-check command with the 3.24.0 command 
line to ensure it really is corrupt:


  https://www.sqlite.org/fts5.html#the_integrity_check_command

The index can be rebuilt using the rebuild command:

  https://www.sqlite.org/fts5.html#the_rebuild_command

3.13.0 was about 2 years ago. There have been a couple of fixes for fts5 
corruption bugs since then. This one, for example:


  https://www.sqlite.org/src/info/9a2de4f05fabf7e7

So you may have hit a known issue. Hard to say.

Dan.





Regards
Nick


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



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

[sqlite] Corrupted FTS5 index? disk image is malformed

[Nick]

Using sqlite cli version 3.13 I have a simple schema with a virtual FTS5 table 
providing full index searching. It is accessed by a python application using 
apsw==3.13.0.post1.

I could successfully use the full index functionality during manual testing of 
the db at creation time (probably a year ago now) however, recently I've been 
getting "Error: database disk image is malformed" messages when running queries 
on the FTS5 virtual table.

In an attempt to explore further I downloaded the latest 3.24 version. With 
this latest version I used the ".backup" command to create a copy of the file 
in the hope of eliminating HDD errors being a culprit.

Running pragma quick_check and integrity_check on the copied db both return ok. 

The schema of the FTS5 table is:

CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);

The table is exclusive kept up to date using triggers:

-- Triggers to keep the FTS index up to date.

CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
  INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES (new.[tid], new.[mangled_title], 
new.[mangled_subtitle], new.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
  INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title], 
old.[mangled_subtitle], old.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
  INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title], 
old.[mangled_subtitle], old.[mangled_summary]);
  INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle], 
[mangled_summary]) VALUES (new.[tid], new.[mangled_title], 
new.[mangled_subtitle], new.[mangled_summary]);
END;

Running SQL queries on the normal tables all work as expected. Digging further 
on the FTS5 queries I noticed the following behaviour:

SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect results - actually returns "Error: database disk image is malformed" 
immediately

SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect no results - returns no results

SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : 
black + adder';
- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder

I've never come across a disk image malformed error in my years of using 
sqlite3 so not sure where to turn to next. Questions are:

1. Is this a known issue with FTS5 tables and if so is there a workaround?

2. It appears the FTS5 virtual table is corrupt. Is there a way to rebuild the 
FTS5 (drop table and recreate?) from just the sqlite cli tool?

Regards
Nick


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