Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index
On 15 Mar 2019, at 7:02pm, niklas wrote: > The data used for sqlite_stat1 in create.txt is taken from the real data, > it's copied from the sql-dump generated just after running ANALYZE. Okay. I should have guessed that. Sorry for doubting you. You seem to have figured out a work-around for now. Good luck with it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index
The data used for sqlite_stat1 in create.txt is taken from the real data, it's copied from the sql-dump generated just after running ANALYZE. I only wanted to include the minimum amount of data the demonstrate the issue so I omitted all other tables, views and data. As I understand it sqlite only checks the statN table data when planning the queries and not the actual data in the real tables. I have dropped the sqlite_stat1 table for now and so disabled analyze functionality. This way I avoid the slow queries. Running ANALYZE on the database again will immediately make them return though. -- 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] Malformed schema (orphan index) on particular db with the version 3.27.2
On 16 Mar 2019, at 9:34am, Max Vlasov wrote: > So I suspect that the file is a very rare example of inconsistency of data > not compatible with recent version, but working with older ones. Integrity-checking has been improved since 3.26.0. Newer versions of SQLite check for types of corruption that older versions didn't detect. So your database file is actually corrupt for both versions. It's just that the integrity_check routine from the old one didn't notice that type of error. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed schema (orphan index) on particular db with the version 3.27.2
I noticed I replied directly to drh with the integrity_check answer
So I repeat the information here
PRAGMA integrity_check in 3.26.0
says
*** in database main ***
Page 3 is never used
{ skipped about 20 lines }
Page 268 is never used
Additional information:
- My logic behind the opening includes querying Pragma encoding. So the
error message in question appear on this particular query and not when the
db is opened.
- Today I made a full scan of folder that might contain sqlite bases (based
on excluding obvious files with non-db extensions and checking the rest for
the starting sequence) and within hundreds of sqlite bases (some of them
though are identical backups), only this one and derivatives reveals the
error when performing the PRAGMA on them.
So I suspect that the file is a very rare example of inconsistency of data
not compatible with recent version, but working with older ones.
Max
On Fri, 15 Mar 2019 at 18:00, Richard Hipp wrote:
> On 3/15/19, Max Vlasov wrote:
> >
> > But 3.27.2 said
> > malformed database schema ({sqlite_autoindex_mytablename}_1) - orphan
> > index.
>
> This error message arises from enhanced early detection of corrupt
> database files that was added to version 3.27.x. Do you still have
> the original database, before you VACUUM-ed it? If so, what does it
> say if you run "PRAGMA integrity_check" on that database.
>
> Can you share the database with me, through private email? There is
> always the possibility that the enhanced early detection of corrupt
> databases is giving a false-positive.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
