Corrupted FTS5 index? disk image is malformed

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

Corrupted FTS5 index? disk image is malformed

Nick Brandon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corrupted FTS5 index? disk image is malformed

Dan Kennedy-4
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corrupted FTS5 index? disk image is malformed

Nick Brandon

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*';
<deleted lots of output>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corrupted FTS5 index? disk image is malformed

Dan Kennedy-4
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*';
> <deleted lots of output>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corrupted FTS5 index? disk image is malformed

Nick Brandon
 
 
 
>  
> On 11 Jul 2018 at 9:28 am,  <Dan Kennedy>  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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Nick Brandon
In reply to this post by Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Dan Kennedy-4
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 queries are all
> working and returning results as expected.
>
> I'm at a loss.
>
> Regards
> Nick
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Nick Brandon

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Nick Brandon
 
 
Hi Dan
 

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

 
Regards  
 
Nick  
 

   
 
 
>  
> On 18 Jul 2018 at 22:41,  <Nick>  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 [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>  
     
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users