Re: [sqlite] SQLITE 3.7.3 bug report (shell) - outp ut in column mode does not align UTF8-strings correctl y

2010-11-25 Thread Niklas Bäckman
Igor Tandetnik writes: > > Niklas Bäckman wrote: > > Columns with special characters like ("å" "ä" "å") get too short widths when > > output. > > > > I guess this is due to the shell not counting actual UTF8 *characters/code > > points* when calculating the

[sqlite] Confused about Multiple Indexes

2010-11-25 Thread Mohit Sindhwani
Hi All, I am looking at a database that someone created earlier and for one of the table, the schema shows: CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, ...); CREATE INDEX IDX1 on tx(name ASC); CREATE INDEX IDX2 on tx(type, search_name ASC); I am under the impression

Re: [sqlite] Queries getting slow with concurrency

2010-11-25 Thread Simon Slavin
On 25 Nov 2010, at 10:15pm, Prakash Reddy Bande wrote: > I have observed that when multiple processes from "different" hosts are > concurrently doing the queries, the performance of sqlite degrades > considerably. > Queries are read only hence I believe that there should not be any locking >

[sqlite] Queries getting slow with concurrency

2010-11-25 Thread Prakash Reddy Bande
Hi, I have observed that when multiple processes from "different" hosts are concurrently doing the queries, the performance of sqlite degrades considerably. Queries are read only hence I believe that there should not be any locking issues (no busy handler called). I will dig a little more in

Re: [sqlite] WAL file size

2010-11-25 Thread Pavel Ivanov
> So, if you have a large scale system, you have two choices: block during > the checkpoint, or suffer from huge WAL file (and a crash if the file is > too big). For a large scale system you have a third choice: use some other RDBMS which is implemented in one process and has a much better

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Simon Slavin
On 25 Nov 2010, at 5:25pm, Tito Ciuro wrote: > 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would > fail right away is it wasn't a valid database. > 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds. > > However, I'm wondering whether (1) is redundant. If

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Tito Ciuro
Hi Nick, On Nov 25, 2010, at 2:09 PM, Nick Shaw wrote: > As Igor points out, that may not fail if the corruption is in a table > you don't query. > > In this case, the databases I'm working with are either small enough for > the speed of an integrity check not to be an issue, or the integrity

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Nick Shaw
Tito Ciuro wrote: > On 25 Nov 2010, at 12:51, Igor Tandetnik wrote: > > Run "PRAGMA integrity_check" right after opening. > > That could be a potentially slow operation if the database is valid and contains lots of records. > Wouldn't be better to issue a SELECT statement and

Re: [sqlite] copying data from memory to file

2010-11-25 Thread Igor Tandetnik
Berryl Hesh wrote: > After generating an in memory db schema, I use the attach command to > replicate that schema to a file Consider using backup API: http://www.sqlite.org/c3ref/backup_finish.html -- Igor Tandetnik ___

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Simon Slavin
On 25 Nov 2010, at 4:06pm, Tito Ciuro wrote: > Wouldn't be better to issue a SELECT statement and let SQLite come back with > a SQLITE_NOTADB error right away? In one of my applications I used to do a SELECT on sqlite_master. This confirms that the file exists, that it's not blank, and that

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Jim Wilcoxson
On Thu, Nov 25, 2010 at 11:06 AM, Tito Ciuro wrote: > On 25 Nov 2010, at 12:51, Igor Tandetnik wrote: > > > Run "PRAGMA integrity_check" right after opening. > > That could be a potentially slow operation if the database is valid and > contains lots of

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Tito Ciuro
On 25 Nov 2010, at 12:51, Igor Tandetnik wrote: > Run "PRAGMA integrity_check" right after opening. That could be a potentially slow operation if the database is valid and contains lots of records. Wouldn't be better to issue a SELECT statement and let SQLite come back

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Igor Tandetnik
Nick Shaw wrote: >> Do you really want SQLite to scan a gigabytes-large file every time it's >> opened? > > Depends - obviously not if you need quick access to the data, however if > the gigabytes-large file is corrupt, do you really want to wait until > you execute

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

2010-11-25 Thread Igor Tandetnik
Niklas Bäckman wrote: > Columns with special characters like ("å" "ä" "å") get too short widths when > output. > > I guess this is due to the shell not counting actual UTF8 *characters/code > points* when calculating the widths, but instead only > counting the plain bytes in

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Nick Shaw
Igor Tandetnik wrote: > > > Is there a reason that sqlite3_open() will happily "open" a non-sqlite > > file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which > > would seem a more obvious return value? > > SQLite doesn't actually touch the file until the first

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Max Vlasov
On Thu, Nov 25, 2010 at 6:08 PM, Nick Shaw wrote: > Hi all, > > > > So... Assuming the above is how sqlite is meant to work, what would be > the best way to ascertain if a successfully opened file is ok to use? > Currently once the file has been opened by

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Igor Tandetnik
Nick Shaw wrote: > Is there a reason that sqlite3_open() will happily "open" a non-sqlite > file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which > would seem a more obvious return value? SQLite doesn't actually touch the file until the first

[sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Nick Shaw
Hi all, Is there a reason that sqlite3_open() will happily "open" a non-sqlite file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which would seem a more obvious return value? It will also happily open a sqlite database that it will later say is corrupt when I try to query some

Re: [sqlite] WAL file size

2010-11-25 Thread Ben Harper
> threads reading and writing to the DB non-stop, there always will be at > least one reader who block the checkpoint from being completed, thus It is in essence writing, not reading, that blocks the checkpointing. If you stopped writing for a while, the checkpointer would catch up, and it could

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

2010-11-25 Thread Dan Kennedy
On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote: > Hi all, > > I'm noticing a new failure with SQLite 3.7.3 as compared to the previous > version I was using, 3.6.23.1. Are you able to share the database and the query that causes the assert() to fail?

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Simon Slavin
On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > In WAL mode with synchronous=NORMAL, when the user commits > a transaction, it is written into the WAL file. No sync > until a checkpoint happens. So if the power fails, you might > lose all the transactions that have been written into the WAL >

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Dan Kennedy
On 11/25/2010 08:49 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > >> On 11/25/2010 03:24 PM, Twylite wrote: >>> > > Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't > like. > >>> I am seeking technical information on the durability of

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Simon Slavin
On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > On 11/25/2010 03:24 PM, Twylite wrote: >> Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't like. >> I am seeking technical information on the durability of transactions >> with journal_mode=WAL and

Re: [sqlite] copying data from memory to file

2010-11-25 Thread Simon Davies
On 24 November 2010 18:16, Berryl Hesh wrote: > Hello: > > After generating an in memory db schema, I use the attach command to > replicate that schema to a file - at least that is my intention. I get > a no such table error, so something is wrong somewhere. Can someone > get

Re: [sqlite] Cppcheck error

2010-11-25 Thread Richard Hipp
On Wed, Nov 24, 2010 at 6:49 PM, Stephen Blessing < stephen.bless...@datasplice.com> wrote: > Hi SQLite folks, > > I ran cppcheck and got this error: > > [./PlugIns/SQLiteDataStore/SQLite/sqlite3.c:13028]:(error) Common realloc > mistake: "p" nulled but not freed upon failure. > This should be a

[sqlite] Cppcheck error

2010-11-25 Thread Stephen Blessing
Hi SQLite folks, I ran cppcheck and got this error: [./PlugIns/SQLiteDataStore/SQLite/sqlite3.c:13028]:(error) Common realloc mistake: "p" nulled but not freed upon failure. /* ** Like realloc(). Resize an allocation previously obtained from ** sqlite3MemMalloc(). ** ** For this low-level

[sqlite] copying data from memory to file

2010-11-25 Thread Berryl Hesh
Hello: After generating an in memory db schema, I use the attach command to replicate that schema to a file - at least that is my intention. I get a no such table error, so something is wrong somewhere. Can someone get me on the right track? Cheers, Berryl

Re: [sqlite] Reducing time to create indexes

2010-11-25 Thread Paul Sanderson
Thats interesting Max, I'll look at that. There are two main string fields one has an average of about 15 characters per field with a max of about 100. The other has an average length of about 150 characters with a max of about 250 (although occasionally up to 500 or so) It may be possible to

Re: [sqlite] Reducing time to create indexes

2010-11-25 Thread Max Vlasov
On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > I have a table with over 1 million rows and 20+ columns all of which > are indexed, I reasonably regularly recreate the table with new data > and find that the indexing process takes about 30 minutes. Are

Re: [sqlite] autoindex1 test fail on linux debian

2010-11-25 Thread Yoni
That worked. Thanks for the answer. On 25/11/2010 11:00 AM, Dan Kennedy wrote: > On 11/25/2010 02:43 PM, Yoni wrote: >> Hi, >> >> I get the following fail when running sqlite3 tests, under linux debian, >> with TCL 8.4. >> Code is latest from fossil. >> Any help appreciated. > > Try with 8.5 or

Re: [sqlite] WAL file size

2010-11-25 Thread Yoni
Hi, > In which case the writer would have to block on the reader, > which is something that the current system doesn't suffer from. I agree when we are talking about small systems, which does not read/write allot. But, on a larger scale system, who have a several threads reading and writing

Re: [sqlite] autoindex1 test fail on linux debian

2010-11-25 Thread Dan Kennedy
On 11/25/2010 02:43 PM, Yoni wrote: > Hi, > > I get the following fail when running sqlite3 tests, under linux debian, > with TCL 8.4. > Code is latest from fossil. > Any help appreciated. Try with 8.5 or newer. ___ sqlite-users mailing list

[sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

2010-11-25 Thread Philip Graham Willoughby
Hi all, I'm noticing a new failure with SQLite 3.7.3 as compared to the previous version I was using, 3.6.23.1. Error message: -- Assertion failed: (memIsValid([i])), function sqlite3VdbeExec, file .../sqlite3.c, line 64507. Information from the time of error:

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Dan Kennedy
On 11/25/2010 03:24 PM, Twylite wrote: > Hi, > > I am seeking technical information on the durability of transactions > with journal_mode=WAL and synchronous=NORMAL. > > Specifically, in the event of a power failure, can the following ever > happen: > (1) Loss of the last transaction completed. >

[sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Twylite
Hi, I am seeking technical information on the durability of transactions with journal_mode=WAL and synchronous=NORMAL. Specifically, in the event of a power failure, can the following ever happen: (1) Loss of the last transaction completed. (2) Loss of some indeterminate number of recent