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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

[R Smith]

On 2018/07/10 8:27 PM, Randall Smith wrote:
One follow-up: Do you know if the dump output is "deterministic" over 
time? That is, if I diff two dumps taken at different times, will the 
unchanged material be in the same order and so on? Or is the ordering 
effectively random?

My underlying question is "can text-comparing two DB dumps be used to determine what 
has changed?"


I am not sure if it is 100% deterministic - it probably is, however, I 
would like to point out that while parsing a dump (supposing it IS 
deterministic) is possible, writing some code to check congruence 
between two DBs at the business end of the SQLite API is significantly 
better, much easier and always 100% deterministic.


Not only that, but the sqldiff command-line utility (download page) does 
it already (though you may require a more specific result, but at a 
minimum its a good start).


Is there perhaps a specific difficulty which makes you think that 
parsing the dump would provide a better/easier insight into which data 
changed?



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

[sqlite] Round-tripping SQLite back and forth between text representation.

[Randall Smith]

From: Simon Slavin 



> I'm curious if there is some standard or normal way to convert a SQLite DB to 
> a text representation, and then recreate the DB content from the text.  
> Naively, this seems hard or impossible as a general problem, but perhaps I am 
> missing something.



Yep.  It's done a lot, to convert a database to a different SQL engine, or 
rescue data from a corrupt database.  See section 10 of







For more information read the documentation about the '.dump' and '.read' 
commands on the same page.  Or ask here.



Simon.



Thanks, Simon, for the info.



One follow-up:  Do you know if the dump output is "deterministic" over time?  
That is, if I diff two dumps taken at different times, will the unchanged 
material be in the same order and so on?  Or is the ordering effectively random?



My underlying question is "can text-comparing two DB dumps be used to determine 
what has changed?"



Thanks again for any insights.



Randall Smith








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

Re: [sqlite] Kind of pivot table

[Keith Medcalf]

cast(abs(random()) / 9223372036854775807.0 * 7 as integer)

Will fix that.  You throw away one bit of randomness (by the abs()), convert to 
a floating point value in the range of 0 but less than 1 (throwing away a few 
more bits in the conversion (since the precision of a double mantissa is only 
53 bits, not 63)), divide that into 7 buckets, and take the floor of that as an 
integer result.  This will have a uniformity equal to the underlying PRNG.

The bias inherent in a direct mod operation is insignificant when you are using 
64 bits of randomness each time ... the non-uniformity of the PRNG outweighs 
the bias of the modulus calculation at least for small values of the modulus.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke
>Sent: Monday, 9 July, 2018 10:49
>To: SQLite mailing list
>Subject: Re: [sqlite] Kind of pivot table
>
>
>> On Jul 7, 2018, at 11:49 PM, Keith Medcalf 
>wrote:
>>
>> Why not use MOD (%) as in
>>
>> ABS(RANDOM() % 6)
>
>Because modulo bias. If the RHS doesn't evenly divide the size of the
>range of RANDOM(), some results will be slightly more likely than
>others.
>
>https://zuttobenkyou.wordpress.com/2012/10/18/generating-random-
>numbers-without-modulo-bias/
>numbers-without-modulo-bias/>
>
>Of course in this case, where the range of RANDOM() is probably 2^32,
>the bias will be very small. But in some use cases the bias can be
>magnified by subsequent operations and can still skew the eventual
>result. A Google search turns up several hits that promise fairer
>algorithms.
>
>https://www.google.com/search?client=safari&rls=en&q=random+modulo+bi
>as&ie=UTF-8&oe=UTF-8
>ias&ie=UTF-8&oe=UTF-8>
>
>—Jens
>___
>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] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Clemens Ladisch]

Rob Willett wrote:
> I removed two instances of -O2 from the Makefile and, lo and behold, it 
> compiles.
>
> Sadly my database to check is approx 80GB which could be interesting.

The bottleneck is I/O speed; it does not matter whether sqlite3_analyzer uses 
ten
or twenty microseconds before waiting for the next batch of data from the disk.
There will be no noticeable difference.


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