Re: [sqlite] ANALYZE necessary after database upgrade?
On 18 Aug 2011, at 2:03pm, Kristoffer Danielsson wrote: > Hi, The problem is that the database is around 100 MB large (the error goes > away if I remove unimportant data). Also, it contains data I'd like to keep > private. I do have a specific select query that produces what I believe is a > data error (tested in the latest SQLite version). First run an integrity check on the database: http://www.sqlite.org/pragma.html#pragma_integrity_check Then if that doesn't spot any problems ... Make a copy of the database, then working with the copy, delete data which doesn't matter to your query, leaving just a small database with perhaps one or two rows which demonstrate the problem. If you can do this, you can then run the SQL command 'VACUUM' on the resulting database and this will dramatically reduce the file size. Once you'd done this, test that your demonstration of the problem still works. Then you might think about either anonymising the data so it doesn't contain anything sensitive, or sending the small file to Doctor Hipp with an explanation of the problem. On 18 Aug 2011, at 2:10pm, Kristoffer Danielsson wrote: > http://www.mailinglistarchive.com/html/sqlite-users@sqlite.org/2011-04/msg00315.html Oh. Those two queries could easily return different rows if you have a NULL in the JOINing columns. Any chance of that ? Otherwise, identify which query is returning incorrect data and break it down to see which clause is causing the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
On Thu, Aug 18, 2011 at 9:03 AM, Kristoffer Danielsson < kristoffer.daniels...@live.se> wrote: > > Hi, The problem is that the database is around 100 MB large (the error goes > away if I remove unimportant data). Also, it contains data I'd like to keep > private. I do have a specific select query that produces what I believe is a > data error (tested in the latest SQLite version). Can I zip the database and > send it to the sqlite team for analysis? > Please send a link were we can download the database, and the original text of the queries you think are incorrect to "supp...@sqlite.org". Tnx. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
I did actually report this before, but nobody answered:http://www.mailinglistarchive.com/html/sqlite-users@sqlite.org/2011-04/msg00315.html As you can see, the query does not contain a LIMIT clause. > From: d...@sqlite.org > Date: Thu, 18 Aug 2011 09:00:23 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANALYZE necessary after database upgrade? > > On Thu, Aug 18, 2011 at 8:50 AM, Kristoffer Danielsson < > kristoffer.daniels...@live.se> wrote: > > > > > > > > > > > I have noticed that certain (complex) select queries return unexpected data > > (missing rows) on my upgraded SQLite databases.My guess is that the > > optimizer makes an erroneous decision on some index (I'm using both > > sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary > > to run the ANALYZE command after upgrading (altering table columns etc) a > > database? Thanks! > > > > SQLite should *never* return an incorrect answer because of a failure to > ANALYZE. All ANALYZE should do is make the answer come back faster. > > Note that some non-deterministic queries (such as using a LIMIT without an > ORDER BY) might return different results after ANALYZE because it chooses a > different query plan. But in cases like this, that is not an error - the > use of LIMIT without an ORDER BY gives an undefined result. > > If you find a case where SQLite is giving an incorrect result, please send > us details so that we can track down and fix the problem. > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
Hi, The problem is that the database is around 100 MB large (the error goes away if I remove unimportant data). Also, it contains data I'd like to keep private. I do have a specific select query that produces what I believe is a data error (tested in the latest SQLite version). Can I zip the database and send it to the sqlite team for analysis? > From: slav...@bigfraud.org > Date: Thu, 18 Aug 2011 13:57:31 +0100 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANALYZE necessary after database upgrade? > > > On 18 Aug 2011, at 1:50pm, Kristoffer Danielsson wrote: > > > I have noticed that certain (complex) select queries return unexpected data > > (missing rows) on my upgraded SQLite databases. > > What do you mean by 'upgraded' ? > > > My guess is that the optimizer makes an erroneous decision on some index > > (I'm using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? > > Depends what you mean by 'missing rows'. If you think SQLite is giving you > /wrong/ information, please describe it in more detail, showing both the > output of the query you think is wrong, and some other output from the > database showing why you think it's wrong. > > > Is it necessary to run the ANALYZE command after upgrading (altering table > > columns etc) a database? Thanks! > > ANALYZE just helps SQLite decide what the fastest way to do something is. It > should never change which rows are changed or returned. So it will > definitely not fix a data problem. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
On Thu, Aug 18, 2011 at 8:50 AM, Kristoffer Danielsson < kristoffer.daniels...@live.se> wrote: > > > > > I have noticed that certain (complex) select queries return unexpected data > (missing rows) on my upgraded SQLite databases.My guess is that the > optimizer makes an erroneous decision on some index (I'm using both > sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary > to run the ANALYZE command after upgrading (altering table columns etc) a > database? Thanks! > SQLite should *never* return an incorrect answer because of a failure to ANALYZE. All ANALYZE should do is make the answer come back faster. Note that some non-deterministic queries (such as using a LIMIT without an ORDER BY) might return different results after ANALYZE because it chooses a different query plan. But in cases like this, that is not an error - the use of LIMIT without an ORDER BY gives an undefined result. If you find a case where SQLite is giving an incorrect result, please send us details so that we can track down and fix the problem. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
On 18 Aug 2011, at 1:50pm, Kristoffer Danielsson wrote: > I have noticed that certain (complex) select queries return unexpected data > (missing rows) on my upgraded SQLite databases. What do you mean by 'upgraded' ? > My guess is that the optimizer makes an erroneous decision on some index (I'm > using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Depends what you mean by 'missing rows'. If you think SQLite is giving you /wrong/ information, please describe it in more detail, showing both the output of the query you think is wrong, and some other output from the database showing why you think it's wrong. > Is it necessary to run the ANALYZE command after upgrading (altering table > columns etc) a database? Thanks! ANALYZE just helps SQLite decide what the fastest way to do something is. It should never change which rows are changed or returned. So it will definitely not fix a data problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANALYZE necessary after database upgrade?
I have noticed that certain (complex) select queries return unexpected data (missing rows) on my upgraded SQLite databases.My guess is that the optimizer makes an erroneous decision on some index (I'm using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary to run the ANALYZE command after upgrading (altering table columns etc) a database? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
