SQLite Forum

SQLite queries on fts5 tables hanging... maybe broken indices?
Login

SQLite queries on fts5 tables hanging... maybe broken indices?

(1) By David Stein (sfsdfd) on 2020-09-25 15:24:49 [link] [source]

I have a 750gb SQLite database with about 99% of the content stored in two fts5 tables (one names patents_fts5 and one named publications_fts5). The schema is quite basic - maybe 12 tables and 30 indices total.

Four days into a five-day database load operation, the Python script hung on a DELETE FROM command for about eight hours. Oddly, the command was just endlessly reading from the database at 50Mb/s.

I restarted the script, which skipped to the same record and then hung again on the same command.

In exploring the database, I find that:

  • Windows reports that the drive and volume are fine.

  • SQLite reports that the FTS5 tables are fine:

pragma integrity_check(publications_fts5);

ok

pragma integrity_check(patents_fts5);

ok

  • I can query each table for a few records, and it works fine:

select * from publications_fts5 limit 10;

(spits out 10 records)

select * from patents_fts5 limit 10;

(spits out 10 records)

  • Any operations involving any other tables complete fine:

select count(*) from patents;

4349151

  • HOWEVER, any query operation involving the index on publications_fts5, beyond a certain point, will hang:

select count(*) from publications_fts;

(no response; runs forever)

select count(*) from patents_fts;

(no response; runs forever)

select publication from publications_fts5 limit 1;

'10909766'

select publication from publications_fts5 where publication = '10909766' limit 1;

'10909766'

select publication from publications_fts5 where publication = '10909766';

'10909766'

(AND THEN runs forever - never finishes)

I wish that I could chalk it up to that one fts5 index having become corrupted due to (software bug / corrupted data / cosmic rays) - except:

  • Any query operations involving the index of patents_fts5, beyond a certain point, will ALSO hang in exactly the same way:

select patent from patents_fts5 limit 1;

'D500396'

select patent from patents_fts5 where patent = 'D500396' limit 1;

'D500396'

select patent from patents_fts5 where patent = 'D500396' limit 1;

'D500396'

(AND THEN runs forever - never finishes)

So it appears that two fts5 indices from two unrelated tables have become partially corrupted in exactly the same peculiar way at exactly the same point.

Anyone have any ideas?

(2.1) By Dan Kennedy (dan) on 2020-09-26 15:32:43 edited from 2.0 in reply to 1 [link] [source]

I don't think it's the FTS index that is corrupted, as none of those queries actually use the FTS index. That is, there's not a lot of opportunity for FTS5 to corrupt the data structures that those queries use. You could check for corruption at the SQLite level with:

    PRAGMA quick_check;

Can you post the db schema? (output of the shell ".schema" command)

Also, does the following also hang?

    SELECT count(*) FROM publications_fts5_content

Dan.

(3.1) By David Stein (sfsdfd) on 2020-09-29 14:49:18 edited from 3.0 in reply to 2.1 [link] [source]

Deleted

(4) By David Stein (sfsdfd) on 2020-09-29 14:49:13 in reply to 2.1 [link] [source]

Well, I just ran the exact same script on macOS for three days - figuring it might be a Windows-specific SQLite or Python implementation problem - and it exhibited the exact same behavior.

I tried:

PRAGMA quick_check;

...it ran for an hour and didn't return any results before I killed it.

The database that I have right now contains about 75% of the records that I intend to load.

Basic stats:

• Total size: 750 gigabytes

• Tables: 29

• Primary tables: 11

• Tying tables (m:n relationships between tables): 16

• FTS5 tables: 2

The full schema is here (and pretty simple):

https://pastebin.com/8rGDHFqu

(5) By Dan Kennedy (dan) on 2020-09-29 19:43:47 in reply to 4 [link] [source]

And this one?

    SELECT count(*) FROM publications_fts5_content

(6) By anonymous on 2020-10-10 12:08:02 in reply to 5 [source]

I apologize for not responding earlier. I've explored this problem a bit more, and have concluded that the operations do finish - they just take an EXTREMELY long time, for reasons that I'll discuss in another post.