Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

Hi Dan Did you receive below? Would extracted db be useful for debugging? Regards Nick > > On 18 Jul 2018 at 22:41,wrote: > > > On 18 Jul 2018, at 14:09, Dan Kennedy wrote: > > > Easiest explanation > is that something

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

h us? > > Dan. FTS5 table is exclusively modified with triggers. If I dropped all tables except the FTS5 table and external content table would that still be useful for you? It would be around 500MB uncompressed. Have you got a way to upload it? Regards Nick ___

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

On 2018-07-10 21:17, Dan Kennedy wrote: On 07/11/2018 02:56 AM, Nick wrote: Using sqlite cli version 3.13 I have a simple schema with a virtual FTS5 table providing full index searching. It is accessed by a python application using apsw==3.13.0.post1. I could successfully use the full index

Re: [sqlite] Corrupted FTS5 index? disk image is malformed

> > On 11 Jul 2018 at 9:28 am,wrote: > > > Yours is not a contentless table. It is an "external content" table. Dan. > > > > > >Noted. Thanks for the clarification. > Regards > Nick ___

Re: [sqlite] Corrupted FTS5 index? disk image is malformed

he Black Adder The Black Adder The Black Adder The Black Adder The Black Adder The Black Adder The Black Adder The Black Adder The Black Adder sqlite> Thanks again Dan. Regards Nick ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Corrupted FTS5 index? disk image is malformed

malformed error in my years of using sqlite3 so not sure where to turn to next. Questions are: 1. Is this a known issue with FTS5 tables and if so is there a workaround? 2. It appears the FTS5 virtual table is corrupt. Is there a way to rebuild the FTS5 (drop table and recreate?) from just the

[sqlite] A coredump when select with index

My query is "SELECT x,y FROM t1 WHERE z=? COLLATE NOCASE". sqlite3Select-> sqlite3WhereBegin-> sqlite3WhereCodeOneLoopStart-> codeAllEqualityTerms-> sqlite3IndexAffinityStr And I found "Cannot access memory at address" when running pTab->aCol[x].affinity //in sqlite3IndexAffinityStr() x = 29043

Re: [sqlite] Any operation to trigger osMunmap?

So the PSS will not decrease even if the db becomes smaller after some DELETE/vacuum operations? I think it is a better way to free the mmap memory after every query automatically inside sqlite. Why not? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___

[sqlite] Any operation to trigger osMunmap?

Hi, I have one process using sqlite with “pragma mmap_size=30M”. The operations of my process is insert-select-insert-select. So the PSS(private clean) will increase along with the growing of the db which is treated as memory leak by Mem-Analysor tool. I guess calling sqlite3_close() or pragma mm

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

I find I confused several concepts of memory. Sorry for that. And I guess I finally understand what my question really is: Still there is only one process doing a SELECT * in a 256M db file. Then 256M physical memory should be used when doing the query. (Ignore the cache_size.) So the PSS of my pr

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

OK, I understand. I ran a simple program to test if mmap will cause the increasing of PSS. But I did not find the PSS increase according to showmap: addr = (char *)mmap(NULL, length, PROT_READ, MAP_SHARED, fd, 0); for(i=0; ihttp://sqlite.1065341.n5.nabble.com/

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Thanks a lot, Hick. So, if - mmap_size=256M - run only one copy of my program (has no other process to split PSS) - have a large enough amount of main memory (bigger than 256M) - a big db file (bigger than 256M) Then the PSS of my program will be about 256M. Is that correct? -- Sent from: htt

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Thanks for your explanation. I want to get a confirmation that my understanding is correct and that if I use mmap_size=256M and I have only 1 process, then the PSS of the process will always the same as the size of my db file, as unixMapfile(-1) means map the whole file. (A big db file means 256M P

[sqlite] Does mmap increase PSS?

Hi, I guess that "cache_size=2000" means PSS of my process will always less than 2M. But, when I use PRAMGA mmap_size to enable mmap, I found the PSS of my process will almost the same as my db. Is that correct? -- Sent from: http://sqlite.1065341.n5.nabble.com/

[sqlite] Dealing with SQLITE_BUSY

I use sqlite3_open() to open two connections, and I have configured journal_mode=WAL, threadsafe=2. Connection 1 is doing: sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg); sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg); sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, &zErrMsg); /

Re: [sqlite] Question about threadsafe

>> So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good >> way to use. >This is the normal way to use SQLite. I ran a test and I can still find "database is locked" even if I use busy_handler(threadsafe=2, 2 connections). When thread 1 executing a writing transaction, th

Re: [sqlite] Question about threadsafe

>> is it OK to use "threadsafe=2 and >> 2 connections" in my apps if the 2 threads may write at the same time? >Yes. So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good way to use. Another possible way is "threadsafe=1 and share 1 connection", but if thread 1 begins a t

Re: [sqlite] Question about threadsafe

Thank you Keith. And there are something I want to make sure. >THREADSAFE=1 means that the sqlite3 library enforces the re-entrancy requirements via mutexes attached to the CONNECTION object. This means that the library will serialize access to the sqlite3 engine for you so that only one call (en

Re: [sqlite] Question about threadsafe

I ran several multi-threads tests these days and I want to get a confirmation that my understanding is correct. I use WAL mode and I think whether or not use the same connection with THREADSAFE=1, 2 is the key to my question. Mode 1, threadsafe=2 + multiple threads use the same connection: It is

[sqlite] -Wsign-compare warning in lempar.c

With the latest Lemon code, I get a warning under GCC with -Wsign-compare: warning: comparison between signed and unsigned integer expressions [-Wsign-compare] assert( i>=0 && i+YYNTOKEN<=sizeof(yy_lookahead)/sizeof(yy_lookahead[0]) );

Re: [sqlite] [EXTERNAL] Question about threadsafe

Yes. But Process A begin Process A insert Process B begin Process B insert Process A end Process B end In fact, begin means "BEGIN" and end means "COMMIT". So I think the result is strange. And I guess the difference between Serilaized and Multithread

Re: [sqlite] Question about threadsafe

> (a) an error result of some kind or (b) a corrupt database. I did not see any info about errmsg. > Are your processes using the same database connection or does each one > have its own ? Two processes have two sqlite3_open(). So each one has its own. > Are you checking the result codes ret

Re: [sqlite] [EXTERNAL] Question about threadsafe

Yep, Hick. We have the same understanding. But all I found is that process B did not wait for the lock and began to run directly. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org htt

[sqlite] Question about threadsafe

I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL, threadsafe=1. My understanding is that: WAL => readers and only one writer can run at the same time. threadsafe=1 => mutex is used in serialized mode so that two writers is supported. Is it correct? But

Re: [sqlite] Performance issue in different versions

I realized that the amount of memory used for the page cache is different. And I found that is the root cause. Sorry for my careless mistake. Thank you. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mail

Re: [sqlite] Performance issue in different versions

Yup, absolutely you are right. I just ran a new test using the same upper bound on the amount of memory used for the page cache, then I found a reasonable result. Thank you, Dan. I did notice the cache_size change before but you made me realize it. Thanks a lot. -- Sent from: http://sqlite.106

Re: [sqlite] Performance issue in different versions

Um, I am a OS application developer and we just upgraded the source code on our developing engine. I am sure I used the same compile-options. SQLITE_SECURE_DELETE is not set. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing

[sqlite] Performance issue in different versions

I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096 (changed since 3.12.0). I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I use speedtest1.c to test it. There are many test cases in spe

Re: [sqlite] Make some changes to the source code

Thank you Simon, I totally understand you. And still hope for someone to give me some advice about my wal+mmap (Map file when opening it and do not truncate the file) . Thanks. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mail

Re: [sqlite] Make some changes to the source code

Yup, I guess I understand you correctly about the mmap problem in the OS. I have seen some threads about it before. But I think wal+mmap is still a worthy consideration as db+mmap has already been supported even though it is disabled by default. At least I think I could use it in my own application

Re: [sqlite] Make some changes to the source code

I ran tests in my MacOS 10.12.6. -- 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] Make some changes to the source code

I use sqlite in my Android application. And I tried to run sqlite test on my MacOS PC. Some cases failed but I can not figure out it is indeed a corruption. Do you mean the corruption problems you mentioned will happen in db+mmap? I guess it should happen in both wal+mmap and db+mmap if it exists.

[sqlite] Make some changes to the source code

I am trying to make some changes to the source code of sqlite. As I found there will be a little improvement if I support MMAP to wal file. I guess the optimization is micro but it is useful in my test and this is a good way for me to study the code of sqlite :D 1.wal+mmap I could use unixMapfile(

[sqlite] About test_vfs.c in sqlite test

I find a file named test_vfs.c when I run the tcl tests in the source tree. When I open a wal-file with a sqlite3_file* file descriptor pFile1, it called sqlite3OsOpen(). The call tree is like the pic below: sqlite3OsOpen(pFile1) | | pVfs->xOpen ==> tvfsOpen

Re: [sqlite] Speed issue of SELECT in my application

Thank you all. As Simon said, 60ms may be a reasonable figure and I am trying to focus on the detail of my service according to all your suggestion. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailingli

Re: [sqlite] Speed issue of SELECT in my application

Jens, I totally agree with your opinion of profile. I have tried to find some useful tools to profile applications using sqlite and all I found is sqlite3_profile()(gettimeofday) and vdbe_profile(hardware time) inside sqlite. I also know a little about Time Profile of Instruments but I am using A

Re: [sqlite] Speed issue of SELECT in my application

I am confused about your table t2. It will be faster to query the table t1, but I need the content of column e and h when I query the data which means I need a extra SELECT from the table t2, is it right? At the same time, I guess it is more complicate to INSERT data into both t1 and t2. What is

Re: [sqlite] Speed issue of SELECT in my application

Thank you Smith. The table is preseted with some data in my Android system. And I guess my APPLICATION is more like a SERVICE which will be started at system boot time and speed issue happens at the time. According to the some other reasons, I have to use wal+normal journal and sync mode. >3. Star

Re: [sqlite] Speed issue of SELECT in my application

OK. Thank you for your help. -- 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] Speed issue of SELECT in my application

Thank you Simon. As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the performance of the SELECT. I find "SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b>?)" when I use EXPLAIN QUERY PLAN, so I do not need to add any index, right? Um, I guess I have nothing to do to imp

[sqlite] Speed issue of SELECT in my application

I have a table below in my application: CREATE TABLE t1 ( a INTEGER PRIMARY KEY AUTOINCREMENT, b INTEGER NOT NULL UNIQUE, c INTEGER NOT NULL, d INTEGER, e TEXT, f INTEGER, g INTEGER, h TEXT, i INTEGER, UNIQUE(

Re: [sqlite] The performance of indexed select

Thank you Keith for your useful advice. I am considering to organize the columns based on BCNF. I guess that table t3 is needed to remove functional dependency, which means I should use table t2 and t3 instead of one table t2 with 4 columns a-d. Is that right? I am not familiar with the concept B

Re: [sqlite] The performance of indexed select

Some simple SQLs: SELECT count(*) FROM t2 WHERE c = xx; (or d = xx) -- 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/sqlit

Re: [sqlite] The performance of indexed select

Thank you Simon. But I am still uncertain if it is a good way to replace column 'c'. CREATE TABLE t2(a INTEGER, b INTEGER, d INTEGER); or: CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT, d INTEGER); and then CREATE INDEX t2d ON t2(d); SELECT count(*) FROM t2 WHERE d = xx; I fin

[sqlite] The performance of indexed select

I am trying to analysis the performance of indexed select. CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); CREATE INDEX t2c ON t2(c); I think there may be much more leaf index b-tree pages whose header is '0x0A' if the length of the content of index key 'c' is always 20-25 bytes, as I notice the

Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error

On 12/07/2016 22:01, Richard Hipp wrote: OK. Another fix. Please try the latest trunk version. This version works for me. Thanks. Nick ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin

Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error

On 08/07/2016 21:54, Richard Hipp wrote: Please try again with the latest version of Lemon. Thanks. This still doesn't work for me. I created a GitHub repo to demonstrate the problem: https://github.com/nwellnhof/lemon-bug Nick ___ s

Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error

On 08/07/2016 21:54, Richard Hipp wrote: Please try again with the latest version of Lemon. Thanks. On 7/6/16, Nick Wellnhofer wrote: On 05/07/2016 18:12, Richard Hipp wrote: Please try https://www.sqlite.org/src/info/2683b375ad129117 and verify that the changes on trunk are working

Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error

Parser->yyerrcnt = -1; #endif ... #elif defined(YYNOERRORRECOVERY) ... #else /* YYERRORSYMBOL is not defined */ ... yypParser->yyerrcnt = -1; ... #endif (Another check for YYNOERRORRECOVERY isn't really needed in the third branch.

Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error

On 05/07/2016 17:15, Richard Hipp wrote: On 7/5/16, Nick Wellnhofer wrote: No, this doesn't fix my problem. The check-in only changes the "#ifdef YYERRORSYMBOL" branch which I don't define. But if I add the change to the "YYERRORSYMBOL is not defined" branc

Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error

No, this doesn't fix my problem. The check-in only changes the "#ifdef YYERRORSYMBOL" branch which I don't define. But if I add the change to the "YYERRORSYMBOL is not defined" branch as well, everything works as expected. Thanks for the quick response! Nick On

[sqlite] Lemon doesn't reset `yyerrcnt` after error

that causes a syntax error, the error is reported for the first time. But subsequent errors aren't reported anymore. I presume this is because `yyerrcnt` should be reset to -1 somewhere in the code. Nick ___ sqlite-users mailing list sqlite-users@ma

Re: [sqlite] Online/Hot backup of WAL journalling mode database

start over again. So if you have frequent writes then theoretically the backup API would not complete. In an ideal world the backup API would only copy pages altered during the write rather than start over. Regards Nick ___ sqlite-users mailing list

Re: [sqlite] Online/Hot backup of WAL journalling mode database

n for > checkpointing and disabling it between backups sounds bad. > I would say the docs (https://www.sqlite.org/wal.html#how_to_checkpoint) do not imply application initiated checkpoints is a bad idea. Regards Nick ___ sqlite-users mailing

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 11 Dec 2014, at 10:08, Dan Kennedy wrote: > On 12/11/2014 05:49 AM, Nick wrote: >> On 10 Dec 2014, at 07:35, Dan Kennedy wrote: >> >>> Strictly speaking the database file may not be well-formed even if there is >>> no ongoing checkpoint. If: >>>

Re: [sqlite] Online/Hot backup of WAL journalling mode database

corruption be detected by running PRAGMA quick_check / integrity_check? Having the occasional backup db corrupted would be tolerable. Regards Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 10 Dec 2014, at 02:36, Simon Slavin wrote: > > On 10 Dec 2014, at 12:30am, Nick wrote: > >> That's interesting Simon I didn't expect the database not to be trustworthy. > > The database will be trustworthy at any instant. Your copy of it will be >

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 9 Dec 2014, at 22:06, Simon Slavin wrote: > > On 9 Dec 2014, at 8:57pm, Nick wrote: > >> Environment is Linux with multiple (c. 4-6) processes accessing a single >> sqlite database named "test.db". >> >> Backup: >> - New process started

[sqlite] Online/Hot backup of WAL journalling mode database

-shm" and "-wal" file). - exit process Restore: - rsync the file "test.db" from another drive/location. Regards Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help on SQLite In-Memory Mode

Thanks, Joe! But it did not work for me. Still got the error: System.ArgumentException: Invalid ConnectionString format for parameter "FullUri" Nick Bao DL_DEV_4/DL_DEV_19, VP, Dalian Office - AvePoint, Inc. P: +86.411.8473.6866 | F: 159.0496.1680 | nick@avepoint.com Follow us o

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

Richard, you are my hero. :) On Mon, Jun 30, 2014 at 9:31 AM, Richard Hipp wrote: > A 64-bit Windows DLL is now available at > http://www.sqlite.org/download.html > > > On Mon, Jun 30, 2014 at 12:26 PM, Richard Hipp wrote: > > > > > > > > > On

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

n postgres, for example, because they never updated the addressing code when they built a 64bit version -- a lesson I learned the hard way. ) On Monday, June 30, 2014, Simon Slavin wrote: > > On 30 Jun 2014, at 4:58pm, Nick Eubank > wrote: > > > Thanks Cory -- is this fixed in

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

Thanks Cory -- is this fixed in 64 bit versions of SQLite? I know postgres never changed memory address variable storage in the 64 bit so the problem persists. Also: any advice on getting a 64bit installation for someone who doesn't really know how to compile C? Thanks! Nick On Monday, Ju

[sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

of the 32 bit build (on another project I discovered 32bit windows is limited to 1900 mb per thread due to how memory addresses are stored)? If so, any advice on installing 64bit version on Windows 8 for someone with zero experience compiling C? Thank you so m

[sqlite] SQLite for single user data manipulation

ool for data that's too big to read into RAM and manipulate with the usual suspects (R, Stata, Matlab, etc.). Thanks all! Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] EF6 + System.Data.Sqlite SaveChanges() not working?

could point me to the right direction, that'd be fantastic, thanks so much Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_ENABLE_STAT3/4 causing expensive statement recompilation

r this recompilation is dramatically outweighing any potential savings that a better query plan could offer! Has anyone else had a similarly negative experience with SQLITE_ENABLE_STAT3/4? Cheers, Nick ___ sqlite-users mailing list sqlite-users@sqlite.o

Re: [sqlite] Resolving Database locking issues in a multi-user environment

atabase at the same time, resulting in database corruption*." Seems pretty clear to me from that statement that SqLite would not suite your system's requirements as a central database. Nick. On Fri, Oct 19, 2012 at 4:21 PM, Nick Shaw wrote: > Richard Hipp wrote: > > Ben Mor

Re: [sqlite] Resolving Database locking issues in a multi-user environment

nt factors. (See http://www.sqlite.org/whentouse.html for specific details, as Simon Slavin recommended). Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

afraid I don't see the problem since the pre-built is slower than your > executable for me. > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Advanced GEOINT Solutions Operating Unit > Northrop Grumman Information Systems > > > >

Re: [sqlite] SQLite SELECT performance problem

ery: http://dl.dropbox.com/u/74970714/database.sqlite Thanks to anyone who can help! 2012/5/25 Simon Slavin : > > On 24 May 2012, at 11:13pm, Nick wrote: > >> In my initial message I described some proof-of-concept that I've done. >> >> I downloaded sqlite3.exe (An SQLi

Re: [sqlite] SQLite SELECT performance problem

imon Slavin > >> >> On 24 May 2012, at 8:59pm, Nick wrote: >> >> > So why does a prebuilt, downloaded from the sqlite website, command >> > line tool takes only 4 seconds, while the same tool, built by me, >> > takes 4 times longer time to execute?

Re: [sqlite] SQLite SELECT performance problem

xecuted in the original (downloaded from sqlite.org) sqlite3.exe a command: pragma compile_options; and made sure all these options (#defines) were set in my own built of sqlite3.exe 2012/5/25 Simon Slavin : > > On 24 May 2012, at 8:59pm, Nick wrote: > >> So why does a pre

Re: [sqlite] SQLite SELECT performance problem

Yes, i have tried it. Here's what sqlite3.exe, that i just built, returns: sqlite> pragma compile_options ; TEMP_STORE=1 THREADSAFE=0 sqlite> Still getting these 14 seconds. I am using Visual Studio 2008 for building.. 2012/5/24 Richard Hipp : > On Thu, May 24, 2012 at 3:59 P

[sqlite] SQLite SELECT performance problem

Hello! I have a program that does some math in an SQL query. There are hundreds of thousands rows (some device measurements) in an SQLite table, and using this query, the application breaks these measurements into groups of, for example, 1 records, and calculates the average for each group. The

Re: [sqlite] Unicode problem when setting PRAGMA journal_mode

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nick Shaw Sent: 11 April 2012 16:29 To: General Discussion of SQLite Database Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode -Original Message

Re: [sqlite] Unicode problem when setting PRAGMA journal_mode

l_mode = DELETE". I also did Simon's suggestion of setting the PRAGMA to what it already was set to (confirmed it was set to DELETE) - made no difference. Something seems amiss. I'll do some more debugging and get back to you all. Could be an error in my code which only occur

Re: [sqlite] Unicode problem when setting PRAGMA journal_mode

each. > > The results might reveal something. Oooh, good plan, thanks Dan, I'll try that. Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode problem when setting PRAGMA journal_mode

it to what it currently is (it *should* be DELETE) and see what it does. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode problem when setting PRAGMA journal_mode

Realised I made a typo below: should have said "PRAGMA journal_mode = DELETE" (though setting it to WAL or OFF causes the same problem). -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nick Shaw Sent: 05 April 201

[sqlite] Unicode problem when setting PRAGMA journal_mode

it on first opening. Any ideas what could be wrong? We're using SqLite build v3.7.11. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Visual Studio 2008 Express and sqlite3.c

nts for call >..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!' Looks like something's not right with the definition of the LikeOp struct in your copy of the file. I've got the exact same amalgamation in my VS2008 project

Re: [sqlite] storing and comparing dates in sqlite

01/01/1970)) - so in an INTEGER field, etc. Depends how you're going to use it. Either way I've suggested has pros/cons. Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dotolee Sent: 23 January 2012 1

Re: [sqlite] EXT :Re: Poor performance with nested query in outer join

ral join v where id = 1; IIUC, when I execute the query on the second line, SQLite replaces the use of "v" with v's definition, "select * from b where ...", so as far as the query planner is concerned there is a subquery. The only way to avoid the subquery is not to use

Re: [sqlite] Poor performance with nested query in outer join

as I can tell. So if you LEFT JOIN with a VIEW you always get this problem. Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Poor performance with nested query in outer join

(and I would've been very surprised if it had, because they are exactly the same operation). Rather, the difference between my query and your query is that I have a subquery (select * from b) and you don't. Nick ___ sqlite-users mailing lis

Re: [sqlite] Poor performance with nested query in outer join

Simon Slavin writes: > On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote: > >> select * from a left natural join (select * from b) where id = 1; > > Try not to use sub-selects when you can use a JOIN instead. > Especially don't use them in combination. If you exp

[sqlite] Poor performance with nested query in outer join

join instead of an outer join, or if I replace "select * from b" with just "b" (but, as I mentioned above, I can't do that in reality). This happens in SQLite 3.7.9, as well as the latest version from Fossil. Is it a bug, or am I just

Re: [sqlite] Very slow processing of some SELECT statements

On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote: > Is it normal that fromuid of the table exits is STRING ? > I think it should be TEXT to be surely processed as text and not float That was an error. However it shouldn't take SQLite 2.5 seconds to handle *any* numeric literal. Especially as i

Re: [sqlite] Very slow processing of some SELECT statements

On 18/10/2011, at 3:38 PM, Dan Kennedy wrote: > Now fixed here: > > http://www.sqlite.org/src/ci/59bb999c8b?sbs=0 Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

00.zip So the test was confirmed using sqlite3.exe, from the SQLite3 site, as distributed. - Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

On 17/10/2011, at 8:33 PM, Nick Gammon wrote: > ... > The following SQL: > > ... > SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC'; > > > Takes over 2 seconds to execute (in particular, the SELECT statement). > Further to the

[sqlite] Very slow processing of some SELECT statements

mber with an exponent. I draw your attention to the fact that the string being searched for is quoted, and that it is declared as a text field in the database. Any suggestions welcomed. - Nick Gammon ___ sqlite-users mailing list sqlite-users@sqlite

[sqlite] attach readonly db to main db that is readwrite

My primary database is opened for read/write. I use the ATTACH command to attach a second database that lives in a read-only filesystem. It appears to mostly work, but is there anything I should be aware of or concerned about? One thing I noticed is if I run "ANALYZE" once the read-only is attac

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

is PRAGMA I did not realise. Similar to the page_count PRAGMA I would have intuitively expected max_page_count would not need restating every time the database file is open. Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.o

[sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

Hi When performing some testing I noticed that SQLite was not enforcing the max_page_count pragma. Using the test case below is appears SQLite is not remembering the max_page_count across closing and opening a database file. Instead of the expected 12800 from PRAGMA max_page_count; I got 107374

Re: [sqlite] Making data and tables persistent

S crash occurs before you commit them), the next time you opened the database, sqlite would rollback the uncommitted changes in the journal file (I assume). Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Biggest number in an INTEGER field

> Guys, an an SQLite3 INTEGER field what is the maximum number that fits in an INTEGER PRIMARY KEY field? According to http://www.sqlite.org/faq.html#q1, it is 9223372036854775807. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.

Re: [sqlite] Sqlite Files

There's always the SQLite manager for FireFox add-on: http://code.google.com/p/sqlite-manager/. I use it quite a lot to quickly check the data inside my own sqlite databases. Has a pretty intuitive GUI. Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-

Re: [sqlite] Transaction speed too slow?

thers would run the script below. Thanks in advance Nick On 9 May 2011, at 13:31, Black, Michael (IS) wrote: > You do realize the number they quote is a MAXnot necessarily what you'll > get. > > > > With 16 transactions per second you're writing on transaction

  1   2   >