Re: [sqlite] database disk image is malformed 3.7.x
On 02/08/2011 10:24 PM, Dennis Geldhof wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Dan Kennedy >> Sent: dinsdag 8 februari 2011 14:51 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] database disk image is malformed 3.7.x >> > >> Then run the resulting executable with the path to a database >> file as the first argument and "dbheader" as the second. i.e. >> >> ./showdb test.db dbheader >> >> where "test.db" is the database file name. The program prints >> out a short report that, if the database was ever written by >> 3.7.0 or newer, includes the version number of the most recent >> version to do so. > > Hi Dan, > > The tool shows me the SQLite version of the test.db3 is indeed 3007004. > So the database created with version 3.6.23.1, is once modified with a > 3.7.4 and then modified with 3.6.23.1 again. > > So I guess we run into something similar to this ticket; > http://www.sqlite.org/src/info/51ae9cad317a1 . > > One strange thing though, I also have a database with version 3007002 > which does not experience the database corruption. Can it be that the > size just matches accidentally for that database? The only problem we know of was caused by 3.7.0, which was replaced by 3.7.0.1 when the problem was discovered. Even with 3.7.0, you needed the right sequence of writes from 3.7.0 and some earlier version. I don't know how the corruption you're seeing is caused. I would like to though. Do you use auto-vacuum mode? Or incremental vacuum? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/2011 07:55 PM, BareFeetWare wrote: > Currently, I have to run pragma foreign_key_list() for each of my tables, ... Create virtual table(s) and query those as needed. Use the experience to document what worked best, any gotchas and as feedback for something official like that in SQLite. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1SGLsACgkQmOOfHg372QTIzACgy1ZRHM00YcYSl3uWbJHDq0z4 WtYAoKDNPDNirO+HpUae+t5yfLEIVHnC =0bqk -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint
> On Feb 8, 2011, at 9:07 PM, Rami Abughazaleh wrote: > >> I would like to request that "PRAGMA foreign_key_list(tableName)" display >> the name of the foreign key constraint. Thirded! Or, much better yet, move away from pragmas altogether for introspection, instead using internal tables/views like the current SQLite_Master. Then we can filter the results, such as: select Name, From_Table, To_Column from SQLite_Foreign_Key_List where To_Table = 'My Table' and To_Column = 'My_Column'; Currently, I have to run pragma foreign_key_list() for each of my tables, copy the result of each into a master table, then query that table. It's slow and requires a lot of application code and back and forth with multiple pragmas to do what SQLite is already doing internally. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
thanks. On 2/8/2011 3:24 PM, Teg wrote: > > Hello Nathan, > > It could be hardware but, my experience with my users is that anything > weird like this is virus scanner of firewall related. You can suggest > that they try telling the V scanner to ignore the folder your data > resides in and see if it makes a difference. That's my normal > suggestion to my users. Solves the problems most of the time. > > C > > > > Tuesday, February 8, 2011, 1:43:20 PM, you wrote: > > NB> That was exactly my thought. But since it is at a customer's site, I > NB> have to prove that its their virus scanner that is causing the > problems. > > > NB> On 2/8/2011 1:17 PM, Jim Morris wrote: > >> > >> Could a backup or virus scanning software be locking the database? > >> > >> On 2/8/2011 10:12 AM, Nathan Biggs wrote: > >> > I haven't tried that, but if we stop the application then restart it, > >> > everything works again. That is until we get another Disk I/O error > >> > which happens the next day. > >> > I'm wondering if the virus scanner is blocking the database write. > >> > > >> > > >> > > >> > On 2/8/2011 1:03 PM, Simon Slavin wrote: > >> >> On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: > >> >> > >> >>> Once a day, not at the same time, we are getting a disk I/O > error from > >> >>> our application using SQLite. > >> >> If you try to duplicate the database file do you get any kind of > error > >> >> then ? > >> >> > >> >> If not, use the command-line tool to run an integrity check: > >> >> > >> >> http://www.sqlite.org/pragma.html#pragma_integrity_check > >> >> > >> >> 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 > >> > > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > NB> ___ > NB> sqlite-users mailing list > NB> sqlite-users@sqlite.org > NB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Best regards, > Teg mailto:t...@djii.com > > ___ > 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] Disk I/O Error
Hello Nathan, It could be hardware but, my experience with my users is that anything weird like this is virus scanner of firewall related. You can suggest that they try telling the V scanner to ignore the folder your data resides in and see if it makes a difference. That's my normal suggestion to my users. Solves the problems most of the time. C Tuesday, February 8, 2011, 1:43:20 PM, you wrote: NB> That was exactly my thought. But since it is at a customer's site, I NB> have to prove that its their virus scanner that is causing the problems. NB> On 2/8/2011 1:17 PM, Jim Morris wrote: >> >> Could a backup or virus scanning software be locking the database? >> >> On 2/8/2011 10:12 AM, Nathan Biggs wrote: >> > I haven't tried that, but if we stop the application then restart it, >> > everything works again. That is until we get another Disk I/O error >> > which happens the next day. >> > I'm wondering if the virus scanner is blocking the database write. >> > >> > >> > >> > On 2/8/2011 1:03 PM, Simon Slavin wrote: >> >> On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: >> >> >> >>> Once a day, not at the same time, we are getting a disk I/O error from >> >>> our application using SQLite. >> >> If you try to duplicate the database file do you get any kind of error >> >> then ? >> >> >> >> If not, use the command-line tool to run an integrity check: >> >> >> >> http://www.sqlite.org/pragma.html#pragma_integrity_check >> >> >> >> 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 >> > >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> NB> ___ NB> sqlite-users mailing list NB> sqlite-users@sqlite.org NB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint
On Feb 8, 2011, at 9:07 PM, Rami Abughazaleh wrote: > I would like to request that "PRAGMA foreign_key_list(tableName)" display > the name of the foreign key constraint. Seconded! :) Unfortunately this doesn't seem to be a priority: http://www.mail-archive.com/sqlite-users@sqlite.org/msg56395.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint
Hi. Thank you for sqlite3. I would like to request that "PRAGMA foreign_key_list(tableName)" display the name of the foreign key constraint. For example, For the following table schemas: create table artist(artistid integer primary key, artistname text); create table track(trackid integer, trackname text, artistid integer, constraint FK_track_artistid_artist_artistid foreign key (artistid) references artist(artistid) ); Running the following query: PRAGMA foreign_key_list(track) Does not display the name of the foreign key constraint as expected: "FK_track_artistid_artist_artistid" sqlite manager 0.6.8 add-on for firefox 3.6.13 windows 7 ultimate x64 Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
That was exactly my thought. But since it is at a customer's site, I have to prove that its their virus scanner that is causing the problems. On 2/8/2011 1:17 PM, Jim Morris wrote: > > Could a backup or virus scanning software be locking the database? > > On 2/8/2011 10:12 AM, Nathan Biggs wrote: > > I haven't tried that, but if we stop the application then restart it, > > everything works again. That is until we get another Disk I/O error > > which happens the next day. > > I'm wondering if the virus scanner is blocking the database write. > > > > > > > > On 2/8/2011 1:03 PM, Simon Slavin wrote: > >> On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: > >> > >>> Once a day, not at the same time, we are getting a disk I/O error from > >>> our application using SQLite. > >> If you try to duplicate the database file do you get any kind of error > >> then ? > >> > >> If not, use the command-line tool to run an integrity check: > >> > >> http://www.sqlite.org/pragma.html#pragma_integrity_check > >> > >> 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 > > > ___ > 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] Disk I/O Error
On 8 Feb 2011, at 6:12pm, Nathan Biggs wrote: > I haven't tried that, but if we stop the application then restart it, > everything works again. That is until we get another Disk I/O error > which happens the next day. > I'm wondering if the virus scanner is blocking the database write. Just as likely to be a hardware failure of some sort: bad sector on the hard disk hard disk about to fail faulty cabling to hard disk faulty hard disk controller So just use your OS GUI to copy the database file. That will read every block in the file very fast and possibly trigger a fault. If that happens, you know it's not related to SQLite but an OS or hardware issue. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
Could a backup or virus scanning software be locking the database? On 2/8/2011 10:12 AM, Nathan Biggs wrote: > I haven't tried that, but if we stop the application then restart it, > everything works again. That is until we get another Disk I/O error > which happens the next day. > I'm wondering if the virus scanner is blocking the database write. > > > > On 2/8/2011 1:03 PM, Simon Slavin wrote: >> On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: >> >>> Once a day, not at the same time, we are getting a disk I/O error from >>> our application using SQLite. >> If you try to duplicate the database file do you get any kind of error >> then ? >> >> If not, use the command-line tool to run an integrity check: >> >> http://www.sqlite.org/pragma.html#pragma_integrity_check >> >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
Hello Nathan, I'd install "Procmon" set it to filter just on the problem file (and journal files) and let the software run. When you get an error, you should see the details listed in procmon". C Tuesday, February 8, 2011, 1:12:48 PM, you wrote: NB> I haven't tried that, but if we stop the application then restart it, NB> everything works again. That is until we get another Disk I/O error NB> which happens the next day. NB> I'm wondering if the virus scanner is blocking the database write. NB> On 2/8/2011 1:03 PM, Simon Slavin wrote: >> >> On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: >> >> > Once a day, not at the same time, we are getting a disk I/O error from >> > our application using SQLite. >> >> If you try to duplicate the database file do you get any kind of error >> then ? >> >> If not, use the command-line tool to run an integrity check: >> >> http://www.sqlite.org/pragma.html#pragma_integrity_check >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> NB> ___ NB> sqlite-users mailing list NB> sqlite-users@sqlite.org NB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
I haven't tried that, but if we stop the application then restart it, everything works again. That is until we get another Disk I/O error which happens the next day. I'm wondering if the virus scanner is blocking the database write. On 2/8/2011 1:03 PM, Simon Slavin wrote: > > On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: > > > Once a day, not at the same time, we are getting a disk I/O error from > > our application using SQLite. > > If you try to duplicate the database file do you get any kind of error > then ? > > If not, use the command-line tool to run an integrity check: > > http://www.sqlite.org/pragma.html#pragma_integrity_check > > 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] Disk I/O Error
On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: > Once a day, not at the same time, we are getting a disk I/O error from > our application using SQLite. If you try to duplicate the database file do you get any kind of error then ? If not, use the command-line tool to run an integrity check: http://www.sqlite.org/pragma.html#pragma_integrity_check Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Disk I/O Error
Once a day, not at the same time, we are getting a disk I/O error from our application using SQLite. Is there a more detailed way of determining the reason of the Disk I/O error. We are running on Windows XP. There is plenty of space on the disk (> 140GB available). Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding "Manual Control Of Query Plans"
On 8 Feb 2011, at 4:22pm, Sven L wrote: > Thank you very much for your detailed explanation! You're welcome. I've actually never had to think out this feature of SQLite before, so it was interesting for me too. I hope Richard or the rest of the team will correct me if I got anything wrong. > I will comment out my calls to ANALYZE, and see how my software performs. > > The reason why I added it in the first place is that users are allowed to > create their own queries, and since not all of them are SQL experts, I wanted > the engine to be as tolerant as possible. Perhaps it's a better idea to > simply crave decent SQL! Allowing users to make up their own queries on the fly does make things a little more difficult as you worked out: you can't pre-make good indexes. The big server/client databases cache temporary indexes (and share them between users), so they handle unexpected queries far better: if any query comes in that doesn't suit any indexes it simply makes up a new temporary index and keeps it in case it's needed later. SQLite can't do this because it's designed for a tiny footprint and can't chew up lots of memory or disk space without a good reason. But your initial questions did sound a little like premature optimisation and I think you'll get decent results without worrying too much about it. I'm sure the overwhelming number of SQLite users have never used ANALYZE even once. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding "Manual Control Of Query Plans"
Thank you very much for your detailed explanation! I will comment out my calls to ANALYZE, and see how my software performs. The reason why I added it in the first place is that users are allowed to create their own queries, and since not all of them are SQL experts, I wanted the engine to be as tolerant as possible. Perhaps it's a better idea to simply crave decent SQL! > From: slav...@bigfraud.org > Date: Tue, 8 Feb 2011 15:16:58 + > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans" > > > On 8 Feb 2011, at 2:39pm, Sven L wrote: > > > Is it reasonable to guess that sqlite_stat* are used only for NATURAL JOINs > > and other not-so-obvious joins? > > No. Consider this: > > SELECT * FROM myTable WHERE a=104 AND b=213 > > Suppose there are two indexes on myTable: one indexes only column a, and the > other indexes only column b. The query optimizer has to choose between them. > Which index would it be best to use ? > > The answer depends on the chunkiness of each column. If only three different > values ever appear in column a, but column b can have a thousand different > values, then it will be more efficient to use the index on column b. This > will return fewer rows which have to be scanned one-by-one for values in a. > > But you're a good programmer. Knowing that you were going to have SELECT > commands like that one above you chose to create an index on both columns > (either order, it doesn't matter). Since you have done this, the results that > ANALYZE gathers don't matter at all ! The query optimizer finds the good > index and never has to choose between two bad indexes because it has the one > perfect index which will always be best. > > So generally speaking ANALYZE matters only if the query optimizer has two or > more equally bad options. If you do your job as a database designer well it > will never need to consider chunkiness. The above explanation is simplified > but gives you the general idea. > > > In my software, the database sometimes grows up to 1-2 GB immediately due > > to the nature of the application. Hence, the guidelines for the ANALYZE > > command do not suffice for me. "Do it once every x months" they say. User > > chooses to import huge amounts of data on regular basis. He is also able to > > run custom queries, which is why I found the sqlite_stat-tables useful. > > Doesn't matter. This new data won't change the relative chunkiness of the > values in the columns. Though the precise numbers change, the /relative/ > chunkiness probably won't, so the choice of indexes probably won't need to > change either. Even less likely that it'll change by enough to change which > index the query optimiser should pick. > > Doing another ANALYZE only really matters if the character of your data > changes, which is usually when a column that used to have very big chunks > suddenly has tiny chunks. The only time this happens if when a business > changes what it does in a significant way: going from having five product > lines to 500, or from selling in three countries to selling in thirty > countries. After they've been putting in new data reflecting this new usage > for a few months, /then/ it might be useful to run ANALYZE again. > > Even then, the only thing you're changing is what the query optimizer chooses > as the best index. It might make the wrong decision and take 12ms for a > SELECT instead of 4ms. Not really a huge problem: if a delay of 8ms is > mission-critical you're probably using hardware at the edge of its > capabilities. > > 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] database disk image is malformed 3.7.x
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: dinsdag 8 februari 2011 14:51 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > Then run the resulting executable with the path to a database > file as the first argument and "dbheader" as the second. i.e. > >./showdb test.db dbheader > > where "test.db" is the database file name. The program prints > out a short report that, if the database was ever written by > 3.7.0 or newer, includes the version number of the most recent > version to do so. Hi Dan, The tool shows me the SQLite version of the test.db3 is indeed 3007004. So the database created with version 3.6.23.1, is once modified with a 3.7.4 and then modified with 3.6.23.1 again. So I guess we run into something similar to this ticket; http://www.sqlite.org/src/info/51ae9cad317a1 . One strange thing though, I also have a database with version 3007002 which does not experience the database corruption. Can it be that the size just matches accidentally for that database? Thanks, Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding "Manual Control Of Query Plans"
On 8 Feb 2011, at 2:39pm, Sven L wrote: > Is it reasonable to guess that sqlite_stat* are used only for NATURAL JOINs > and other not-so-obvious joins? No. Consider this: SELECT * FROM myTable WHERE a=104 AND b=213 Suppose there are two indexes on myTable: one indexes only column a, and the other indexes only column b. The query optimizer has to choose between them. Which index would it be best to use ? The answer depends on the chunkiness of each column. If only three different values ever appear in column a, but column b can have a thousand different values, then it will be more efficient to use the index on column b. This will return fewer rows which have to be scanned one-by-one for values in a. But you're a good programmer. Knowing that you were going to have SELECT commands like that one above you chose to create an index on both columns (either order, it doesn't matter). Since you have done this, the results that ANALYZE gathers don't matter at all ! The query optimizer finds the good index and never has to choose between two bad indexes because it has the one perfect index which will always be best. So generally speaking ANALYZE matters only if the query optimizer has two or more equally bad options. If you do your job as a database designer well it will never need to consider chunkiness. The above explanation is simplified but gives you the general idea. > In my software, the database sometimes grows up to 1-2 GB immediately due to > the nature of the application. Hence, the guidelines for the ANALYZE command > do not suffice for me. "Do it once every x months" they say. User chooses to > import huge amounts of data on regular basis. He is also able to run custom > queries, which is why I found the sqlite_stat-tables useful. Doesn't matter. This new data won't change the relative chunkiness of the values in the columns. Though the precise numbers change, the /relative/ chunkiness probably won't, so the choice of indexes probably won't need to change either. Even less likely that it'll change by enough to change which index the query optimiser should pick. Doing another ANALYZE only really matters if the character of your data changes, which is usually when a column that used to have very big chunks suddenly has tiny chunks. The only time this happens if when a business changes what it does in a significant way: going from having five product lines to 500, or from selling in three countries to selling in thirty countries. After they've been putting in new data reflecting this new usage for a few months, /then/ it might be useful to run ANALYZE again. Even then, the only thing you're changing is what the query optimizer chooses as the best index. It might make the wrong decision and take 12ms for a SELECT instead of 4ms. Not really a huge problem: if a delay of 8ms is mission-critical you're probably using hardware at the edge of its capabilities. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Surprising profiling results
On Tue, Feb 8, 2011 at 9:12 AM, Ian Hardingham wrote: > Wow - changing to that in combination with indexes on player1 and > player2 has dropped the time to 25 and 10 - an incredible improvement. > > I'll need to get my head around using combinations of queries which each > only use indexed columns. SQLite3 uses a single index per-table in a select, but it can perform the optimization suggested by Igor (OR -> UNION ALL). Use EXPLAIN QUERY PLAN to see what SQLite3 is doing. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Surprising profiling results
Hi Igor, Wow - changing to that in combination with indexes on player1 and player2 has dropped the time to 25 and 10 - an incredible improvement. I'll need to get my head around using combinations of queries which each only use indexed columns. Thanks, Ian On 08/02/2011 13:48, Igor Tandetnik wrote: > SELECT * FROM multiturnTable WHERE rowid in ( >select rowid from multiturnTable where player1 LIKE ? >union all >select rowid from multiturnTable where player2 LIKE ? > ) > AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND > p1Declined=0 AND p2Declined=0; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding "Manual Control Of Query Plans"
Is it reasonable to guess that sqlite_stat* are used only for NATURAL JOINs and other not-so-obvious joins? In my software, the database sometimes grows up to 1-2 GB immediately due to the nature of the application. Hence, the guidelines for the ANALYZE command do not suffice for me. "Do it once every x months" they say. User chooses to import huge amounts of data on regular basis. He is also able to run custom queries, which is why I found the sqlite_stat-tables useful. > From: slav...@bigfraud.org > Date: Mon, 7 Feb 2011 22:43:13 + > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans" > > > On 7 Feb 2011, at 9:37pm, Sven L wrote: > > > Thanks for input. So you're saying that creating the tables manually is not > > the same as issuing the "ANALYZE" command? > > You have no idea what the ANALYZE command does because it's not documented. > For all you know different versions of SQLite put different things in this > table. So don't try to simulate it, let SQLite do it. It's hardly difficult: > just issue that one instruction. As I wrote upthread, you might want to do it > about as often as you'd run the integrity check routine. > > By the way you may be overestimating the impact of doing this. It will matter > only in situations where the query optimizer will never have to guess how > best to do its searches. If you've created good indexes and specified your > WHERE and ORDER BY clauses well, use of this table will be very low. There is > definitely little point in doing this until the particular installation has > lots of data in its tables ... perhaps after months of having had data > entered. > > 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] database disk image is malformed 3.7.x
On 02/08/2011 08:26 PM, Dennis Geldhof wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Dan Kennedy >> Sent: dinsdag 8 februari 2011 12:33 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] database disk image is malformed 3.7.x >> > >> We're very interested in how this happened. Do you ever write to the >> db using the 3.7.4 tools? Or write to it with any other 3.7.X version? > > I have several machines over here (with developers) and the corruption > only occurs on some machines. Besides that I am not able to reproduce > this issue with a brand new database (maybe because my tools are at > version 3.7.4). I can only reproduce this issue on some of the database > that are in use more than 2 months. Looking at the description you gave > and the symptoms we have, maybe this is caused by issue; > http://www.sqlite.org/src/info/51ae9cad317a1 . Because the > System.Data.Sqlite wrapper is still at sqlite version 3.6.23.1 and the > tools update automatically we could have triggered that issue. > Is there a way to check if the database was ever opened with a sqlite > version newer than 3.6.23.1, so we can make sure it was ever edited by a > tool? Grab the C file from this link and compile it to a standalone executable. http://www.sqlite.org/src/raw/tool/showdb.c?name=471c0f8fa472e71bb7654500096a5bdb4ea1fb2a Then run the resulting executable with the path to a database file as the first argument and "dbheader" as the second. i.e. ./showdb test.db dbheader where "test.db" is the database file name. The program prints out a short report that, if the database was ever written by 3.7.0 or newer, includes the version number of the most recent version to do so. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Surprising profiling results
Ian Hardingham wrote: > I was attempting to optimise this query this weekend: > > SELECT * FROM multiturnTable WHERE (player1 LIKE '?' OR player2 LIKE > '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND > p1Declined=0 AND p2Declined=0; > > What I'll be trying next is to put an index on player1 and player2. Am > I right in thinking I only need an index on those individually - I don't > need to combine them with id or some such? Try this: SELECT * FROM multiturnTable WHERE rowid in ( select rowid from multiturnTable where player1 LIKE ? union all select rowid from multiturnTable where player2 LIKE ? ) AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0; With this, indexes on player1 and player2 (separately) should help. With the original query, they are unlikely to help. Note also that '?' is a string literal consisting of one question mark character. It is *not* a parameter placeholder. ? is a placeholder for a parameter of any type (not just integer, as you seem to assume) - the type is determined by the flavor of sqlite3_bind_* function you are using to set its value. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Surprising profiling results
I was attempting to optimise this query this weekend: SELECT * FROM multiturnTable WHERE (player1 LIKE '?' OR player2 LIKE '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0; multiturnTable has about 70,000 rows and has no explicit indexes. I was calling with arguments which produce around 8 results. The first time the query runs on starting my application it takes around 120ms (as reported by SQlite_profile). Consecutive runs vary between 80 and 100. The first thing I did was simply change the LIKE to =, like so: SELECT * FROM multiturnTable WHERE (player1 = '?' OR player2 = '?') AND ... This, surprisingly to me, had no real effect on the run time of the query. So I changed player1 and player2 to be integers, like this: SELECT * FROM multiturnTable WHERE (player1Id = ? OR player2Id = ?) AND ... I really expected this to improve the run time, but it didn't. Maybe a 5% improvement. What I'll be trying next is to put an index on player1 and player2. Am I right in thinking I only need an index on those individually - I don't need to combine them with id or some such? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: dinsdag 8 februari 2011 12:33 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > We're very interested in how this happened. Do you ever write to the > db using the 3.7.4 tools? Or write to it with any other 3.7.X version? I have several machines over here (with developers) and the corruption only occurs on some machines. Besides that I am not able to reproduce this issue with a brand new database (maybe because my tools are at version 3.7.4). I can only reproduce this issue on some of the database that are in use more than 2 months. Looking at the description you gave and the symptoms we have, maybe this is caused by issue; http://www.sqlite.org/src/info/51ae9cad317a1 . Because the System.Data.Sqlite wrapper is still at sqlite version 3.6.23.1 and the tools update automatically we could have triggered that issue. Is there a way to check if the database was ever opened with a sqlite version newer than 3.6.23.1, so we can make sure it was ever edited by a tool? > Vacuum it? We never vacuum the database. > I think the mailing list stripped your attachment. Can you put > somewhere > we can download it from? Or just mail it to me if you like. Thanks. Uploaded: http://jjb3.nl/sqlite/test.db3 (3KB) Thanks, Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed up count(distinct col)
You have: CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id)); CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag)); You can solve your problem, using pure SQL. No need to resort to the application layer. Just execute the SQL transaction below. It takes care of everything, including normalizing, assigning integer IDs to unique tags, filtering our duplicates and auto indexes. I haven't tested on your tables specifically, but I do similar all the time. Any errors should be simple name errors. begin immediate; create temp table "Movies Old" as select * from Movies; create temp table "Tags Old" from Tags; drop table Movies; drop table Tags; create table Movies ( Movie_ID integer primary key not null , Title text collate no case not null ); create table Tags ( Tag_ID integer primary key not null , Name text not null unique collate nocase ); create table Movie_Tags ( ID integer primary key not null , Movie_ID integer not null references Movies (Movie_ID) on delete cascade , Tag_ID integer not null references Tags (Tag_ID) on delete cascade , unique (Movie_ID, Tag_ID) ); insert into Movies (Movie_ID, Title) select Movie_ID, Title from "Movies Old"; insert or ignore into Tags (Name) select distinct Tag from "Tags Old"; insert into Movie_Tags (Movie_ID, Tag_ID) select distinct Movie_ID, (select Tag_ID from Tags where Name = "Tags Old".Tag) from "Tags Old"; drop table "Movies Old"; drop table "Tags Old"; commit or rollback; Note that the last line "commit or rollback" is not an actual SQLite command. You will need to manually decide at that point whether to issue a commit or rollback. If there are any errors in previous lines, use rollback. Unfortunately, SQLite does not automatically rollback all commands in a transaction if one of the commands fails (specifically create and drop commands). Then you should be able to count tags very fast by: select count(*) from Tags; In order to make foreign keys work, you need to have SQLite version 3.6.19 or later, and use this pragma when you open a connection to SQLite (ie before any commands that require use of foreign keys): pragma foreign_keys = yes; A normalized database using integer keys is fast, small and elegant. Viewing integers by users is ugly. So for viewing the Movie-Tag combinations (and data entry), you might want to create a view to make it user friendly. It depends on your user interface. Reply here if you need that. Just something like this: create view "Movie Tags View" as select Movie_Tags.ID as ID , Movies.Title as "Movie" , Tags.Name as "Tag" from Movie_Tags join Movies on Movie_Tags.Movie_ID = Movies.Movie_ID join Tags on Movie_Tags.Tag_ID = Tags.Tag_ID ; Hope this helps, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Blob in the callback of sqlite3_exec
Thanks. You remind me those NULs :) On Feb 8, 12:12 am, "Igor Tandetnik" wrote: > Ray wrote: > > What's the actual type of blob in the callback of sqlite3_exec? Is it > > string or remains binary? > > Bytes are reported as-is, with an added zero terminator. But since there's no > length indicator, you'll have trouble with embedded NULs. > > Drop sqlite3_exec, use sqlite3_prepare / sqlite3_step / sqlite3_column_bytes > / sqlite3_column_blob. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-us...@sqlite.orghttp://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] database disk image is malformed 3.7.x
On 02/08/2011 06:00 PM, Dennis Geldhof wrote: > Hi all, > > > > I experienced some strange behavior between different versions of > sqlite. Our application uses the System.Data.Sqlite wrapper > (http://sqlite.phxsoftware.com/) which is on sqlite version 3.6.23.1, > but the tools we use to view the database are on sqlite version 3.7.4. > In the application no signs of database corruption are found, but the > tools cannot open the database (tools; http://osenxpsuite.net/?xp=3 / > http://www.sqliteexpert.com/). Version 3.7.0 of SQLite introduced a field in the database file header containing the logical size of the database. So that a database file can be arbitrarily extended (AKA preallocated) without corrupting it. Earlier versions of SQLite just used the size of the file as the size of the database. http://www.sqlite.org/releaselog/3_7_0.html The symptoms you are reporting come about if the header field indicates that the database image is *larger* than the file on disk. If this is the case, version 3.7.0 and newer assume that the file is corrupt and report the error you are seeing. Earlier versions never read the header field and never see a problem. Since the integrity check passes, it is likely that the database is fine except that the header field is set incorrectly. We're very interested in how this happened. Do you ever write to the db using the 3.7.4 tools? Or write to it with any other 3.7.X version? Vacuum it? > Attached you will find a database file which is not encrypted, and all > tables are dropped. The corruption is still in there and is used to > generate the output mentioned above. I think the mailing list stripped your attachment. Can you put somewhere we can download it from? Or just mail it to me if you like. Thanks. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database disk image is malformed 3.7.x
Hi all, I experienced some strange behavior between different versions of sqlite. Our application uses the System.Data.Sqlite wrapper (http://sqlite.phxsoftware.com/) which is on sqlite version 3.6.23.1, but the tools we use to view the database are on sqlite version 3.7.4. In the application no signs of database corruption are found, but the tools cannot open the database (tools; http://osenxpsuite.net/?xp=3 / http://www.sqliteexpert.com/). To verify the behavior, I downloaded different versions of the sqlite windows commandline tool from the sqlite website and ran the following commands; C:\tmp\malformed>3_6_23_sqlite3.exe test.db3 "pragma integrity_check;" ok C:\tmp\malformed>3_6_23_1_sqlite3.exe test.db3 "pragma integrity_check;" ok C:\tmp\malformed>3_7_0_sqlite3.exe test.db3 "pragma integrity_check;" Error: database disk image is malformed C:\tmp\malformed>3_7_4_sqlite3.exe test.db3 "pragma integrity_check;" Error: database disk image is malformed Another thing that might be interesting, is that when you run "VACUUM;" with a 3.6.x version on the database, the corruption seems to be solved for the 3.7.x versions. The corruption seems to occur after running a number of CREATE TABLE / INSERT / UPDATE / ALTER TABLE / DROP TABLE commands in one transaction. Normally the database is encrypted using the encryption provided by the System.Data.Sqlite wrapper, but the corruption also remains when decrypting the database. Attached you will find a database file which is not encrypted, and all tables are dropped. The corruption is still in there and is used to generate the output mentioned above. Can someone help me out with this issue? Regards, Dennis Geldhof This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger to filter out characters
As has been sugested, use something outside SQLite. I'm thinking about regex. In C++ there's a regex library. Marian Cascaval ___ 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
