Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote > However, other information in your message suggests that you have a > resource leak of some type somewhere. Especially, it should not take 12 > minutes to insert 3.5M rows into a simple table with an index or two > unless really long strings or blobs are involved. > >

Re: [sqlite] Huge WAL log

Paul Sanderson wrote > So from yor main loop, expanding the following code may help us > understand. > > "insert all downloaded rows" The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large

Re: [sqlite] Inserting/removing large number of rows with index

It depends in how you define "update the index". If you mean "write to disk" then this happens "once, at the end of the transaction" (the exact process differs depending on the journal mode). If you mean "change the index structure in memory" then (as already noted) the changes will happen

[sqlite] LIKE vs GLOB vs MATCH vs REGEXP

LIKE & GLOB can be overridden with user defined functions. According to https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP can be redefined. MATCH is only used in FTS queries if my understanding is

Re: [sqlite] Inserting/removing large number of rows with index

On Fri, Jan 16, 2015 at 3:47 AM, Hick Gunter wrote: > It depends in how you define "update the index". > > If you mean "write to disk" then this happens "once, at the end of the > transaction" (the exact process differs depending on the journal mode). > > If you mean "change

[sqlite] Database corrupted 28 billion bytes

I have a database that has become 28,268,814,336 bytes so downloaded the sqlite3_analyzer and it has been running for over 15-minutes. Task manager shows sqlite3_analyzer.exe using 13% and the memory stays steady at 23,768K. 19 handles, 1 thread(s). The database was a test database that has

Re: [sqlite] Huge WAL log

On 2015/01/16 11:33, Jan Slodicka wrote: The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large column values are supplied as parameters, the rest (vast majority) is passed through SQL

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Jan 16, 2015, at 5:06 AM, Scott Robison wrote: > LIKE & GLOB can be overridden with user defined functions. According to > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument > function, GLOB can be a 2 argument function, and neither MATCH nor

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Fri, Jan 16, 2015 at 5:23 AM, Jay Kreibich wrote: > > On Jan 16, 2015, at 5:06 AM, Scott Robison > wrote: > > > LIKE & GLOB can be overridden with user defined functions. According to > > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3

Re: [sqlite] Huge WAL log

Paul Sanderson wrote > Unlike a rollback journal a WAL file can have multiple copies of the same > page. > > So from yor main loop, expanding the following code may help us > understand. > > "insert all downloaded rows" > > If your inserted records is 5million separate insertions then each >

Re: [sqlite] Database corrupted 28 billion bytes

On Fri, Jan 16, 2015 at 12:18 PM, MikeD wrote: > I have a database that has become 28,268,814,336 bytes so > downloaded the sqlite3_analyzer and it has been running for over > 15-minutes. > ... > The database is still working. What should I be doing? > How about

Re: [sqlite] Database corrupted 28 billion bytes

On 1/16/15, MikeD wrote: > I have a database that has become 28,268,814,336 bytes so > downloaded the sqlite3_analyzer and it has been running for over > 15-minutes. > > Task manager shows sqlite3_analyzer.exe using 13% and the memory stays > steady at 23,768K. > 19

Re: [sqlite] Database corrupted 28 billion bytes

On 1/16/15, MikeD wrote: > I have a database that has become 28,268,814,336 bytes so > downloaded the sqlite3_analyzer and it has been running for over > 15-minutes. > > Task manager shows sqlite3_analyzer.exe using 13% and the memory stays > steady at 23,768K. > 19

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On 1/16/15, Scott Robison wrote: > LIKE & GLOB can be overridden with user defined functions. According to > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument > function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP > can be

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On 1/16/15, Scott Robison wrote: > So the only remaining question is whether there is any functional > difference between the LIKE & GLOB SQL functions and the same named > operators (other than argument order)? Is there a reason to prefer one or > the other in SQL

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Fri, Jan 16, 2015 at 5:56 AM, Richard Hipp wrote: > On 1/16/15, Scott Robison wrote: > > LIKE & GLOB can be overridden with user defined functions. According to > > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument > > function,

[sqlite] stderr inconsistently flushed across platforms

Coming from a long conversation with tests in place too, it looks like SQLite CLI is flushing errors right away in Linux, but until undefined amount of buffer in OSX or Windows, where in latter one nothing is shown until `.quit` is called [1] The only way to have a consistent behavior seems to be

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Jan 16, 2015, at 6:56 AM, Richard Hipp wrote: > On 1/16/15, Scott Robison wrote: >> LIKE & GLOB can be overridden with user defined functions. According to >> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument >> function, GLOB

Re: [sqlite] Huge WAL log

On 16 Jan 2015, at 12:39pm, Jan Slodicka wrote: > Thanks to your post I discovered multiple-row inserts so that I now > understand what you asked. Just a note that multiple-row inserts were added to SQLite relatively recently (2012-03-20 (3.7.11)) and, because SQLite does only

Re: [sqlite] Database corrupted 28 billion bytes

On 16 Jan 2015, at 11:18am, MikeD wrote: > Just terminated sqlite3_analyzer. You can let it run. Overnight if need be. Its CPU usage will never increase much past what you've already seen and memory usage shouldn't be excessive. Simon.

Re: [sqlite] Huge WAL log

RSmith wrote >> >> The code schema is as follows: >> >> foreach table >> { >> BEGIN >> INSERT INTO table VALUES() >> INSERT INTO table VALUES() >> ... >> COMMIT >> } >> >> Large column values are supplied as parameters, the rest (vast majority) >> is >> passed

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On 16 Jan 2015, at 12:23pm, Jay Kreibich wrote: > They can all be (re)defined, some just happen to have default functions: > > https://www.sqlite.org/lang_expr.html#like Might be worth noting here that there can be a danger in replacing the definitions of default functions.

[sqlite] Shell tool improvement request

(following description simplified) I have a text file I wanted to .import into a table. The text file has two columns separated by a tab: a word and a number. It starts off like this: ! 32874624 " 239874242 # 98235252 $ 438743824 % 324872489 & 39854724 a

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote >> Thanks to your post I discovered multiple-row inserts so that I now >> understand what you asked. > > Just a note that multiple-row inserts were added to SQLite relatively > recently (2012-03-20 (3.7.11)) and, because SQLite does only > database-level locking, its overhead

Re: [sqlite] Database corrupted 28 billion bytes

Found table that was huge, it was named MyTable. It was created with: Create Table MyTable(comment); select max(rowid),* from MyTable; 80002 "This","is"," 4" Drop table Mytable; Vacuum. It went down to a little over 3,000,000 bytes. I'm going to include a filesize routine

Re: [sqlite] Database corrupted 28 billion bytes

>What is the output from the following: > PRAGMA page_size; > PRAGMA journal_mode; > PRAGMA freelist_count; > PRAGMA page_count; 1024 delete 0 27606264 The sqlite3_analyzer,exe is running. - May -- View this message in context:

Re: [sqlite] Database corrupted 28 billion bytes

sqlite3_analyzer.exe ended with 16 INSERT statements showing in my Windows 7 CMD console screen: They look similar to this one: INSERT INTO space_used VALUES(‘Deductions’,’Deductions’,0,0,0,0,0,0,16,0,0,1024); I remember helping someone try to get a UNION statement correct a few months ago.

Re: [sqlite] Database corrupted 28 billion bytes

Last statement 2 statements were: insert into space_used values(‘MyTable,”MyTable,0,728251738,704277138,588,0,0,26,285986,23974601,0,34710955,683321040,0,12,24842841088); COMMIT; - May -- View this message in context:

Re: [sqlite] Shell tool improvement request

On 2015/01/16 18:33, Simon Slavin wrote: (following description simplified) I have a text file I wanted to .import into a table. The text file has two columns separated by a tab: a word and a number. It starts off like this: ! 32874624 " 239874242 # 98235252 $ 438743824

Re: [sqlite] Database corrupted 28 billion bytes

On 1/16/15, MayW wrote: > Found table that was huge, it was named MyTable. > It was created with: > Create Table MyTable(comment); > > select max(rowid),* from MyTable; > 80002 "This","is"," 4" > > Drop table Mytable; > Vacuum. > It went down to a little

Re: [sqlite] Database corrupted 28 billion bytes

On 1/16/15, MayW wrote: > sqlite3_analyzer.exe ended with 16 INSERT statements showing in my Windows 7 > CMD console screen: > The interesting information was in the part that scrolled off the top of you console. I suggest you rerun the command, directing output into a

Re: [sqlite] Shell tool improvement request

RSmith wrote on Friday, January 16, 2015 1:08 PM > > On 2015/01/16 18:33, Simon Slavin wrote: > > > > Error: mytextfile.txt line 13588392: expected 2 columns of data but > > found 1 > > > > Naturally I spent some time looking near the end of the file to > figure out what was wrong where the

Re: [sqlite] sqlite3 tool bug

I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

On Jan 16, 2015 8:05 AM, "Simon Slavin" wrote: > > > On 16 Jan 2015, at 12:23pm, Jay Kreibich wrote: > > > They can all be (re)defined, some just happen to have default functions: > > > > https://www.sqlite.org/lang_expr.html#like > > Might be worth noting

Re: [sqlite] Huge WAL log

On 01/17/2015 12:04 AM, Jan Slodicka wrote: Simon Slavin-3 wrote Thanks to your post I discovered multiple-row inserts so that I now understand what you asked. Just a note that multiple-row inserts were added to SQLite relatively recently (2012-03-20 (3.7.11)) and, because SQLite does only

[sqlite] System.Data.Sqlite, not using parameterized query, how to encode special characters

Hi, Some of my single row inserts use parameterized queries today to make encoding easier. For example having embedded single quotes in strings etc. Now, I would like to take advantage of the multi row inserts as described in

Re: [sqlite] Client/Server Best Practices

On Sat, 10 Jan 2015 00:58:25 -0700 "Keith Medcalf" wrote: > > there's no way to hook two SELECTs together to make them see one > >database state. That's what JOIN is for. :-) > > Yes, it is a part of the SQL Standard isolation levels in excess of > the default default of

Re: [sqlite] sqlite3 tool bug

On 1/16/15, Dave Dyer wrote: > > I have a class of database for which using sqlite3 to create > a copy via the "pipe" method fails. Using an explicit intermediate > file seems to work ok. > > I can supply a sample database to anyone interested in investigating. Is the

Re: [sqlite] System.Data.Sqlite, not using parameterized query, how to encode special characters

On 16 Jan 2015, at 7:06pm, Andy (KU7T) wrote: > If I just build the sql string myself, how can I solve encoding > issues as mentioned above? There's only one character which should cause a problem with embedded strings: the apostrophe. And you escape this by doubling it.

Re: [sqlite] sqlite3 tool bug

On 1/16/15, Dave Dyer wrote: > I have a class of database for which using sqlite3 to create > a copy via the "pipe" method fails. Using an explicit intermediate > file seems to work ok. > > I can supply a sample database to anyone interested in investigating. Rather

Re: [sqlite] sqlite3 tool bug

On 16 Jan 2015, at 6:38pm, Dave Dyer wrote: > I have a class of database for which using sqlite3 to create > a copy via the "pipe" method fails. Using an explicit intermediate > file seems to work ok. Which version of Windows are you using ? You can type 'ver' at

Re: [sqlite] Huge WAL log

Some data using python+apsw for 10,000,000 records: So, embeding the values in the SQL (ie, as text statements) and preparing a "new" statement each time is the least efficient, Multiple Values that are embedded in the SQL are more efficient, up to about 50/100 values per statement (50%

Re: [sqlite] Client/Server Best Practices

On 1/16/15, James K. Lowden wrote: > On Sat, 10 Jan 2015 00:58:25 -0700 > "Keith Medcalf" wrote: > >> > there's no way to hook two SELECTs together to make them see one >> >database state. That's what JOIN is for. :-) >> >> Yes, it is a part of the

Re: [sqlite] database is locked

Wow, thank you Hick! I will try it. Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Hick Gunter [h...@scigames.at] Sent: Thursday, January 15, 2015 1:34 AM To: 'General Discussion of SQLite Database' Subject:

Re: [sqlite] sqlite3 tool bug

> >Rather than the full database, can you show us the full schema of this >database, including triggers? It's a very simple database, no triggers or coalitions. The problem is most likely a buffer overrun because of a very long literal string field. I've sent a minimal sample to drh

[sqlite] Increase page_size on existing db with data and WAL

Hi, When I read this: http://sqlite.org/pragma.html#pragma_page_size it seems to argue that I can never change the page_size on a non-empty db that is running in WAL mode. Any tips how I can still do this? Thanks Andy ___ sqlite-users mailing

Re: [sqlite] Increase page_size on existing db with data and WAL

On 1/16/15, Andy (KU7T) wrote: > Hi, > > > > When I read this: http://sqlite.org/pragma.html#pragma_page_size it seems > to > argue that I can never change the page_size on a non-empty db that is > running in WAL mode. Any tips how I can still do this? > > Change out of WAL mode,

Re: [sqlite] sqlite3 tool bug

On 1/16/15, Dave Dyer wrote: > > I have a class of database for which using sqlite3 to create > a copy via the "pipe" method fails. Using an explicit intermediate > file seems to work ok. > The pipe method works fine for me on Linux. I'm guess this is a case of the

Re: [sqlite] sqlite3 tool bug

> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on

Re: [sqlite] sqlite3 tool bug

> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on

Re: [sqlite] sqlite3 tool bug

On 1/16/15, Dave Dyer wrote: > >> >>The pipe method works fine for me on Linux. >> >>I'm guess this is a case of the windows command-line shell doing some >>character translations in the pipe, rather than just shipping the >>bytes through the pipe unaltered. > > Ouch.

Re: [sqlite] sqlite3 tool bug

>>>I'm guess this is a case of the windows command-line shell doing some >>>character translations in the pipe, rather than just shipping the >>>bytes through the pipe unaltered. >> Ouch. That basically means the "pipe" method shouldn't ever be >> used on windows. >Not, at least, when your

Re: [sqlite] sqlite3 tool bug

On 16 Jan 2015, at 9:01pm, Keith Medcalf wrote: >> Not, at least, when your database contains string data with unusual >> characters that Windows feels like it should translate for you... > > I think that pretty much limits one to the 7-bit ASCII character set ... Why on

Re: [sqlite] sqlite3 tool bug

> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe

Re: [sqlite] sqlite3 tool bug

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2015 01:05 PM, Simon Slavin wrote: > Why on earth would an operating system programmer bother to put any > translation into piping You have a system with a bunch of apps installed. You then upgrade to a new version of the operating system

Re: [sqlite] sqlite3 tool bug

On Fri, Jan 16, 2015 at 2:02 PM, Dave Dyer wrote: > the input side of the pipe. Perhaps there is some windows conditioning > that ought to be done by sqlite, on STDIN, to make it into a binary data > source ? You should be able to do a freopen(NULL, "rb", stdin); to

[sqlite] sqlite3 shell in windows

Hello all, I had the bright idea yesterday of trying to use an extension module in Windows. I found myself a bit confused, and the messages and documentation were not as helpful as they might have been. I suspect I had a 32/64 bit mismatch in one case, and that sqlite3 wasn't compiled with

Re: [sqlite] sqlite3 tool bug

On Friday, 16 January, 2015 14:05, Simon Slavin said: >On 16 Jan 2015, at 9:01pm, Keith Medcalf wrote: >>> Not, at least, when your database contains string data with unusual >>> characters that Windows feels like it should translate for you... >> I

Re: [sqlite] sqlite3 shell in windows

On Fri, Jan 16, 2015 at 2:21 PM, James K. Lowden wrote: > 5. The 32-bit windows sqlite3 shell supports extensions? Yes, it does. The version on sqlite.org is compiled with that support. > 6. The above messages come from the OS, and result from LoadLibrary > failing?

Re: [sqlite] sqlite3 tool bug

On Fri, 16 Jan 2015 10:38:54 -0800 Dave Dyer wrote: > [$] sqlite3 po.sqlite .dump | sqlite3 po2.sqlite > Error: incomplete SQL: INSERT INTO "imageblob" VALUES(1,'G:\share Perhaps try -echo, to display the incomplete SQL? I'm skeptical of the notion that cmd.exe is

Re: [sqlite] sqlite3 tool bug

>You have a system with a bunch of apps installed. You then upgrade to >a new version of the operating system and a whole bunch of the apps >break. Do you think people blame the apps or the operating system? >Do you think anyone takes the apps apart and blames them for using the >wrong apis

Re: [sqlite] sqlite3 tool bug

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2015 02:35 PM, James K. Lowden wrote: > I'm skeptical of the notion that cmd.exe is diddling with your data > en route to the pipe. Almost certainly the reason is that stdout and stdin are in character mode. It requires extra code to put

Re: [sqlite] sqlite3 tool bug

On 16 Jan 2015, at 10:27pm, Keith Medcalf wrote: > [snip] The long and the short of it is that the interprocess pipe in Windows > connects to cooked channels because it never occurred to anyone at Microsoft > that this was undesirable and irrational. Thanks for this long

Re: [sqlite] sqlite3 tool bug

On Fri, Jan 16, 2015 at 3:48 PM, Keith Medcalf wrote: > > >You have a system with a bunch of apps installed. You then upgrade to > >a new version of the operating system and a whole bunch of the apps > >break. Do you think people blame the apps or the operating system? >

Re: [sqlite] sqlite3 shell in windows

>I had the bright idea yesterday of trying to use an extension module in >Windows. I found myself a bit confused, and the messages and >documentation were not as helpful as they might have been. I suspect I >had a 32/64 bit mismatch in one case, and that sqlite3 wasn't compiled >with

[sqlite] sqlite3 tool bug

I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3

[sqlite] sqlite3 tool bug

> >Rather than the full database, can you show us the full schema of this >database, including triggers? It's a very simple database, no triggers or coalitions. The problem is most likely a buffer overrun because of a very long literal string field. I've sent a minimal sample to drh

[sqlite] sqlite3 tool bug

> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on

[sqlite] sqlite3 tool bug

> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on

[sqlite] sqlite3 tool bug

> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe

[sqlite] sqlite3 tool bug

I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3

[sqlite] sqlite3 tool bug

> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe