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 is writing directly to the FTS5 table, bypassing the > external content table. > > Otherwise, it may be a bug in fts5. How large > is the corrupted db? Are you able to share it with 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 ___ sqlite-users > mailing list sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II
On 18 Jul 2018, at 14:09, Dan Kennedy wrote: > > > Easiest explanation is that something is writing directly to the FTS5 table, > bypassing the external content table. > > Otherwise, it may be a bug in fts5. How large is the corrupted db? Are you > able to share it with 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 functionality during manual
testing of the db at creation time (probably a year ago now) however,
recently I've been getting "Error: database disk image is malformed"
messages when running queries on the FTS5 virtual table.
In an attempt to explore further I downloaded the latest 3.24 version.
With this latest version I used the ".backup" command to create a copy
of the file in the hope of eliminating HDD errors being a culprit.
Running pragma quick_check and integrity_check on the copied db both
return ok.
The schema of the FTS5 table is:
CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);
The table is exclusive kept up to date using triggers:
-- Triggers to keep the FTS index up to date.
CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
INSERT INTO [i_epg]([i_epg], rowid, [mangled_title],
[mangled_subtitle], [mangled_summary]) VALUES('delete', old.[tid],
old.[mangled_title], old.[mangled_subtitle], old.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
INSERT INTO [i_epg]([i_epg], rowid, [mangled_title],
[mangled_subtitle], [mangled_summary]) VALUES('delete', old.[tid],
old.[mangled_title], old.[mangled_subtitle], old.[mangled_summary]);
INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
Running SQL queries on the normal tables all work as expected. Digging
further on the FTS5 queries I noticed the following behaviour:
SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big +
ban*';
- expect results - actually returns "Error: database disk image is
malformed" immediately
SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big +
ban*';
- expect no results - returns no results
SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{
mangled_title } : black + adder';
- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder
I've never come across a disk image 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 cli
tool?
Try running the FTS5 integrity-check command with the 3.24.0 command
line to ensure it really is corrupt:
https://www.sqlite.org/fts5.html#the_integrity_check_command
The index can be rebuilt using the rebuild command:
https://www.sqlite.org/fts5.html#the_rebuild_command
3.13.0 was about 2 years ago. There have been a couple of fixes for
fts5 corruption bugs since then. This one, for example:
https://www.sqlite.org/src/info/9a2de4f05fabf7e7
So you may have hit a known issue. Hard to say.
Dan.
Part II
With the help from Dan the FTS5 table was fixed and then subsequently
worked as expected. For belt and braces, using the 3.24 sqlite cli
client, I created a new db with the below PRAGMA statements and then ran
".dump"' to copy over the records from the previous db.
PRAGMA legacy_file_format = off;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum = 2;
PRAGMA foreign_keys = on;
PRAGMA journal_mode = wal;
PRAGMA application_id = 19;
Both PRAGMA and FTS integrity returned ok and manual testing showed the
new db worked as expected. At the same time I've upgrade apsw to the
latest version (I saw it downloaded 3.24 file during compiling).
A number of days later I've gone back and ran the INSERT INTO
[i_epg]([i_epg]) VALUES('integrity-check') cmd and disappointingly it
returned Error: database disk image is malformed.
However unlike my first report above the same FTS5 queries are all
working and returning results as expected.
I'm at a loss.
Regards
Nick
___
sqlite-users mailing li
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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted FTS5 index? disk image is malformed
On 10 Jul 2018, at 21:17, Dan Kennedy wrote:
>>
>
> Try running the FTS5 integrity-check command with the 3.24.0 command line to
> ensure it really is corrupt:
>
> https://www.sqlite.org/fts5.html#the_integrity_check_command
>
> The index can be rebuilt using the rebuild command:
>
> https://www.sqlite.org/fts5.html#the_rebuild_command
>
> 3.13.0 was about 2 years ago. There have been a couple of fixes for fts5
> corruption bugs since then. This one, for example:
>
> https://www.sqlite.org/src/info/9a2de4f05fabf7e7
>
> So you may have hit a known issue. Hard to say.
>
> Dan.
>
Thanks Dan.
Reading the webpage it says it doesn't work for contentless FTS5 but ran the
commands anyway
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('integrity-check');
Error: database disk image is malformed
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('rebuild');
sqlite> INSERT INTO [i_epg]([i_epg]) VALUES('integrity-check');
sqlite>
Running previous commands also seem to show its been fixed
sqlite> SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big +
ban';
sqlite> SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big +
ban*';
sqlite> SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{
mangled_title } : 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
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
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 functionality during manual testing of
the db at creation time (probably a year ago now) however, recently I've been
getting "Error: database disk image is malformed" messages when running queries
on the FTS5 virtual table.
In an attempt to explore further I downloaded the latest 3.24 version. With
this latest version I used the ".backup" command to create a copy of the file
in the hope of eliminating HDD errors being a culprit.
Running pragma quick_check and integrity_check on the copied db both return ok.
The schema of the FTS5 table is:
CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);
The table is exclusive kept up to date using triggers:
-- Triggers to keep the FTS index up to date.
CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title],
old.[mangled_subtitle], old.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title],
old.[mangled_subtitle], old.[mangled_summary]);
INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
Running SQL queries on the normal tables all work as expected. Digging further
on the FTS5 queries I noticed the following behaviour:
SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect results - actually returns "Error: database disk image is malformed"
immediately
SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect no results - returns no results
SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } :
black + adder';
- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder
I've never come across a disk image 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 cli tool?
Regards
Nick
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 while in fact it has only 8394 records. I am wondering if there is something wrong with my DISK file? Or is it possible that the aCol[x] is in MEMORY? Is there any way to know what happened? Thanks. -- 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] 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 mmap_size=0 after querys may free the PSS but that is not a good way for my process. So I am wondering is there any other way to free the PSS? As I find unixUnmapfile() will be called when nFetchOut back to 0 but I do not know what operation may trigger that. Thanks for any light you can shed. -- 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] [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 program should be 256M at that time. That is OK. But from now on, the PSS will be 256M for a long time as my process will be active for hours doing insert-select-insert-select without closing. My system can not afford a 256M-PSS program. In another word, the most important thing is there is no opportunity to call unmmap() in my program. Can I find a reasonable time to call unmmap or sqlite3OsUnfetch? Or is there any way to solve the problem other than pragma mmap_size=2M? Really thanks 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] [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; i
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: 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] [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 PSS) Is that correct? In fact I had expected mmap only took up virtual memory instead of PSS. -- 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
[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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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, ); sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, ); sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, ); //SQLITE_BUSY sqlite3_exec(db1, "COMMIT", 0, 0, ); I got that SQLITE_BUSY as connection 2 was writing the db at the same time. I have called sqlite3_busy_timeout() but I find that it does not work if INSERT runs after a SELECT within BEGIN and COMMIT. Is it expected? -- 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] 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, thread 2 runs the code below
at the same time:
sqlite3_exec("BEGIN")
//SELECT
sqlite3_prepare_v2("SELECT * FROM t1;");
sqlite3_step;
sqlite3_reset;
//INSERT
sqlite3_exec("INSERT INTO t1 VALUES(1, 1, \"aweagsr\")");//database is
locked
sqlite3_exec("COMMIT");
Writing in thread 1 will no block SELECTs in thread 2 as I use WAL. But the
INSERT within the transaction of thread 2 still returns SQLITE_BUSY.
I think I have used sqlite3_busy_timeout() in right way and I find that
sqliteDefaultBusyCallback() did not be called.
Is it expected?
--
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] 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 transaction, then the SQL of thread 2 will also be executed within the transaction I guess. That may cause some unpredictable problems. BTW, if I use "threadsafe=0 and more than 1 connection", there will not be "database is locked" any more even if two threads writing at the same time, as mutex is disabled on core. Is it correct? -- 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] 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 (entrance) per connection is permitted to proceed. Yes. That's the reason why I think my applications can use "threadsafe=1 + share one connection" directly and sqlite will meeting the entrance requirements by itself. >Other entrances (calls) will *wait* until the in-progress call is complete before proceeding. I see the word "wait". But could you give me any advises to find the code when sqlite waiting or retrying? >THREADSAFE=2 means that the sqlite3 library WILL NOT ENFORCE the re-entrancy requirements via mutexes attatched to the CONNECTION object. Yes. So I can not use "threadsafe=2 + share one connection" in my apps, as I may have more than one call at a time. >The limitation of only ONE entrance per connection object at one time is still in effect however, so if you violate the rules then AHWBL. I want to make sure that it is not OK to read and write concurrently within the same transaction(two thread share one connection), but it is OK to read and write concurrently if I have two different connection and use WAL. Is it correct? >Therefore IF you use THREADSAFE=2 *AND* open a new connection dedicated to each thread, THEN you can be sure that you are meeting the entrance requirements provided that only calls against that connection (or objects derived therefrom) are made on the thread which owns that connection, and from NO OTHER THREAD. Sorry, I still can not understand. Um, or, is it OK to use "threadsafe=2 and 2 connections" in my apps if the 2 threads may write at the same time? thanks. -- 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] 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 not threadsafe; Mode 2, threadsafe=2 + each thread runs a sqlite3_open(): It is threadsafe which means reading and writing can proceed concurrently, but only one writer at a time. PRAGMA busy_timeout() may avoid “db is locked” when writing. Mode 3, threadsafe=1 + each thread runs a sqlite3_open(): Same with mode 2, as threadsafe=1 is only supported the ability of a handle to be used by more than one thread. Mode 4, threadsafe=1 + multiple threads use the same connection: Reading and writing can proceed concurrently; Two writers can start at the same time and sqlite will make them Serialized(but how? guess some threads will be blocked and retry, but I can not find it in the source code). In general, WAL make reading and writing concurrent - not just serial, but writing and writing can only be serial. So writers should use busy_timeout() to retry(Mode 2), or, use the same connection and the RETRY operation will be done by sqlite(Mode 4). I think sqlite is threadsafe means the integrity of database is guaranteed. And there will not be any crash or corruption if applications use sqlite the way like mode 2 and 4 above. Is it right? Thanks. -- 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
[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]) ); ^ Nick ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 is that if it is allowed to shared the structure sqlite3 *db (together with prepared statement) among threads. If I use Serilaized mode, then I could run sqlite3_open(db) for only one time and all the threads could use the unique "db". Is it right? -- 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] 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 returned by all the API calls ?
Yes. I use speedtest1.c as model code.
speedtest1_exec("BEGIN");
speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); -- %d times",
n);
for(i=1; i<=n; i++){
rc = sqlite3_bind_int64(g.pStmt, 1, i);
rc = sqlite3_bind_int(g.pStmt, 2, i);
rc = sqlite3_bind_text(g.pStmt, 3, "hello", -1, SQLITE_STATIC);
speedtest1_run();
}
speedtest1_exec("COMMIT");
And I have checked rc = SQLITE_OK.
> Can you reliably get less than 2 rows ?
Yes, always less than 2.
Process A inserts 1-1 and process B inserts 10001-2. I found that
the first few rows is missing in the result. I mean there is no 10001-10xxx.
> Does the problem go away if you use threadsafe = 2 ?
The problem is still here.
--
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] [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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 I ran a simple test: Two processes will run sqlite3_open() respectively to open the same db. Then both of the two processes will insert 1 records(in Transaction) into the db simultaneously. But I find that: Process A begin Process A insert Process B begin Process B insert Process A end Process B end Which I guess the Process B did not sleep at all? And the count of records is less than 2 at last. So I think multiple write threads is not allowed even though I use threadsafe=1 in wal mode? -- 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] 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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.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] 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 list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 speedtest1.c and case 270 is a DELETE case which is the most time-consuming one. There is a result. (different version + different page_size) 3.16.2+4096 3.16.2+1024 3.9.2+4096 3.9.2+1024 Case 270: 5.695s 5.908s 2.307s 6.130s TOTAL 75.182s79.811s 58.723s 81.732s It is easy to find 3.9.2+4096 is extremely faster than others. And page_size has great effect on 3.9.2 but has only a small effect on 3.16.2. But why? I think 3.16.2 should faster than 3.9.2 according to the measurements of sqlite (https://www.sqlite.org/cpu.html) but I guess 3.9.2 used 1024 at that time. Could someone give me any explanations? The result is strange but I think it is reproducible if we set SQLITE_DEFAULT_PAGE_SIZE=4096 in 3.9.2. Thank you. -- 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
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 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
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 until I find the mmap problem in my system. The one thing that bothers me the most is that I have no way to check my code, as there is a testvfs in sqlite test. So could you please review my train of thought about my wal+mmap? (Map file when opening it and do not truncate the file) By the way, is there a possibly way to submit patch to sqlite? Thank you Simon. -- 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 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. But I have not found it until now even though I have heard about the mmap+OS problem from the community. And Simon, do you have any idea about the test_vfs problem? And is it OK to change the code as 1.wal+mmap mentioned besides the mmap problem? -- 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
[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() to map the wal file while MMAP may cause SIGBUS if the mapped file is truncated. This could happen when reseting the wal file, in another word, if journal_size_limit is reached or SQLITE_CHECKPOINT_TRUNCATE is called. But I guess it works if these two APIs will always not be called in my application. So, I want to create file holes to get a 4M wal-file in sqlite3WalOpen(), and always set journal_size_limit to 4M. Then mmap will be supported by simply calling unixMapfile(4M) in sqlite3WalOpen(). After that, memcpy() instead of read() will be used when read the first 4M of wal file. I am wondering if it is all right in my Android applications? 2.Further more. I know mmap is supported when fetching db file: To map file: In getPageMMap(), sqlite3OsFetch() MMAPs the whole db file, and return the mapped page through *pData. Then pagerAcquireMapPage will obtain a page reference PgHdr based on the pData. (A small question here, why pData is needed? As xRead() will always use memcpy instead of read() after unixMapfile(-1) is called.) sqlite3OsFileControlHint is called to remap the db file when the db grows as a result of a checkpoint. To avoid SIGBUS: Process will catch the CHANGE of other processes by comparing pWal->hdr.iChange and the corresponding number in wal-index. Whenever a read, write or checkpoint operation happens, unixUnmapfile() will be called if there is a CHANGE. 3.Thus another way of wal+mmap: I want to use pWal->hdr.unused to catch the CHANGE when other process truncate the wal file(journal_size_limit or SQLITE_CHECKPOINT_TRUNCATE). Then I will check the hdr.unused to call unixMapfile(-1) before whenever sqlite3OsRead(pWal->pWalFd) is called. Is there a better timing to remap the file? Just like sqlite3WalBeginReadTransaction and walcheckpoint in db+mmap; I run sqlite test to check my code, but I find pVfs->szOsFile is 16 when test_vfs.c is called, which means pRet->pWalFd is no longer a unixFile struct. At this time, sqlite3OsOpen() binds to tvfsOpen() instead of unixOpen(). So I cannot use unixMapfile() and the test that uses test_vfs.c will not pass. So could you give me some advices to pass the test? Thank you. -- 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
[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 | | sqlite3OsOpen(pFile2) | | pVfs->xOpen ==> unixOpen(pFile2) In some tests, test_vfs.c is involved and pVfs->xOpen() will bind to tvfsOpen() instead of unixOpen() directly. And I find the address of pFile has changed to pFile2 when sqlite3OsOpen() is called in the second time. Then unixOpen will initialize pFile2, such as set nFetchOut and some other member elements in struct unixFile to 0. But the nFetchOut of pFile1 may not be 0 when sqlite3OsOpen(pFile1) returns. It makes me confused as I find db will not crash even if all the member elements of the unixFile is not correct. Could it be said that these elements will not be used before they are set to a right value? And what is test_vfs.c for? I am new bee to sqlite test and vfs. Could anyone explain me it is correct that the nFetchOut of pFile1 is not 0 when sqlite3OsOpen(pFile1) returns? I am really confused. Thanks for any light you can shed. -- 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 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 Android. So, what is the tool you mentioned such as ‘sample’ tool? And do you mean CPU profiler (gperftools og Google) is useful to profile sqlite? As I am not familiar about this tool. Thanks. -- 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
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 more important is that, I think it is a better way to establish my tables according to however the source data is. -- 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 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. Start a transaction and hold the DB read locks for the duration of your application (again, if it won't need writing) I only have several single SELECT at boot time and I guess it is not necessary to use TRANSACTION? And there are still some write ops in my service. >4. Ensure there are no other time-consuming bits of code in the sqlite api RESET()-STEP() loop. I think I only use sqlite api in some normal ways. And I do not know more details about what happens during the boot time. -- 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
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 improve the performance. Thanks. -- 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
[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(b, i) ); And I’ve got some speed issues when I query the db: SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 1; It needs almost 60ms as there are about 100 records with some long TEXT data in the TEXT columns. I am wondering if it is needed to add ANY INDEX to improve the performance of the SELECT? Thanks. -- 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] 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 BCNF, and I want to make sure that if it is recommended to create my tables in the way you wrote. Thanks -- 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] 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/sqlite-users
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 find it is indeed faster than t2(c). Or in another word, if a TEXT column has similar meaning with an INTEGER column in my applications,(such as use userID instead of userName, still the way that the data works in my head:) ) is it recommended to use INTEGER one in order to get a less index pages? One more small question: > For instance, once SQLite has found the right entry in the index it might > need to look up that entry in the table to retrieve values which are not > in the index. I understand the execution process you said. And in my opinion, sqlite should fetch pages when looking up the entry both in the index and then in the table. But I only found pages with '0x0A' and '0x02' when getPageNormal() is called during the time running select SQL. Could you give me any advises to find the code when sqlite fetching the '0x0D' pages? Thanks. -- 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
[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 format of index inside sqlite consist of the index key and rowid. I can establish mapping relation between column 'c' and a new INTEGER column 'd'. Then I am wondering if it is reasonable to create new index t2(d) to get a better performance, as sqlite stores INTEGER in a variable-length way which means there will be less index pages. So if it is correct that the performance of indexed select is up to the number of index pages which is fetched in getPageNormal() within the select? I think it has positive correlation but I do not know if it is the major constraint. And does sqlite have a profile tool to get call tree or execution time of each functions? All I know is VDBE_PROFILE. Thanks for any light you can shed. I want to profile sqlite -- 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] 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/mailman/listinfo/sqlite-users
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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 <wellnho...@aevum.de> 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. Thanks.
Still doesn't work for me. The structure of the #ifdefs in `Parse` is:
#ifdef YYERRORSYMBOL
...
#elif defined(YYNOERRORRECOVERY)
...
#else /* YYERRORSYMBOL is not defined */
...
#endif
Your first check-in modifies the first branch, your second check-in the
second
branch, resulting in:
#ifdef YYERRORSYMBOL
...
#ifndef YYNOERRORRECOVERY
yypParser->yyerrcnt = -1;
#endif
...
#elif defined(YYNOERRORRECOVERY)
...
#ifndef YYNOERRORRECOVERY
yypParser->yyerrcnt = -1;
#endif
...
#else /* YYERRORSYMBOL is not defined */
...
#endif
The change to the second branch has no effect because YYNOERRORRECOVERY is
always defined. My patch modifies the third branch ("YYERRORSYMBOL is not
defined"). This fixes code that defines neither YYERRORSYMBOL nor
YYNOERRORRECOVERY. I think the code should look like this:
#ifdef YYERRORSYMBOL
...
#ifndef YYNOERRORRECOVERY
yypParser->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.
It will always be undef.)
Nick
--
aevum GmbH
Nadistr. 12
80809 München
Germany
Tel: +49 89 35747589
http://aevum.de/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error
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. Thanks.
Still doesn't work for me. The structure of the #ifdefs in `Parse` is:
#ifdef YYERRORSYMBOL
...
#elif defined(YYNOERRORRECOVERY)
...
#else /* YYERRORSYMBOL is not defined */
...
#endif
Your first check-in modifies the first branch, your second check-in the second
branch, resulting in:
#ifdef YYERRORSYMBOL
...
#ifndef YYNOERRORRECOVERY
yypParser->yyerrcnt = -1;
#endif
...
#elif defined(YYNOERRORRECOVERY)
...
#ifndef YYNOERRORRECOVERY
yypParser->yyerrcnt = -1;
#endif
...
#else /* YYERRORSYMBOL is not defined */
...
#endif
The change to the second branch has no effect because YYNOERRORRECOVERY is
always defined. My patch modifies the third branch ("YYERRORSYMBOL is not
defined"). This fixes code that defines neither YYERRORSYMBOL nor
YYNOERRORRECOVERY. I think the code should look like this:
#ifdef YYERRORSYMBOL
...
#ifndef YYNOERRORRECOVERY
yypParser->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.
It will always be undef.)
Nick
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error
On 05/07/2016 17:15, Richard Hipp wrote:
On 7/5/16, Nick Wellnhofer <wellnho...@aevum.de> 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" branch as well, everything works as
expected.
Can you show me your patch, please?
Here it is.
Nick
diff --git a/lemon/lempar.c b/lemon/lempar.c
index 112d0bd..25a9525 100644
--- a/lemon/lempar.c
+++ b/lemon/lempar.c
@@ -919,6 +919,7 @@ void Parse(
if( yyendofinput ){
yy_parse_failed(yypParser);
}
+ yypParser->yyerrcnt = -1;
yymajor = YYNOCODE;
#endif
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 05/07/2016 14:48, Richard Hipp wrote: Please try the latest check-in (https://www.sqlite.org/src/info/91889fa30e84760e) and let me know whether or not it clears your problem. On 7/5/16, Nick Wellnhofer <wellnho...@aevum.de> wrote: Hello, I hope this is right place to report Lemon issues. I ran into a problem after upgrading to the latest version of the Lemon source code from `trunk`. The following commit removed the initialization of `yyerrcnt` from the `Parse` function: http://www.sqlite.org/src/fdiff?sbs=1=872383ebf36c13fd=8569dd3e4c22831e Then this commit added the initialization to `ParseAlloc`: http://www.sqlite.org/src/fdiff?sbs=1=f06b7e98a6b7efb4=66a16b5e00fefff2 I'd like to use a parser instance for multiple inputs without reallocating. If I parse input 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- aevum GmbH Nadistr. 12 80809 München Germany Tel: +49 89 35747589 http://aevum.de/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lemon doesn't reset `yyerrcnt` after error
Hello, I hope this is right place to report Lemon issues. I ran into a problem after upgrading to the latest version of the Lemon source code from `trunk`. The following commit removed the initialization of `yyerrcnt` from the `Parse` function: http://www.sqlite.org/src/fdiff?sbs=1=872383ebf36c13fd=8569dd3e4c22831e Then this commit added the initialization to `ParseAlloc`: http://www.sqlite.org/src/fdiff?sbs=1=f06b7e98a6b7efb4=66a16b5e00fefff2 I'd like to use a parser instance for multiple inputs without reallocating. If I parse input 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Online/Hot backup of WAL journalling mode database
On 11 Dec 2014, at 20:39, David King wrote: > Why are you trying to hard to avoid using the backup API? It sounds like it > does exactly what you want Backup API works great if you have periods of no writing. However, if a process writes during the backup then the API would stop and 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 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 11 Dec 2014, at 10:43, Simon Slavin wrote: > > I don't know enough about the internals of SQLite to be sure, but various > parts of me are concerned that this is a bad idea. I don't know what WAL > mode would be like without checkpointing but there has to be a reason 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 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 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: >>> >>> a) process A opens a read transaction, >>> b) process B opens and commits a write transaction to the database, >>> c) process C checkpoints the db, >>> >>> then the db file considered without the *-wal file may be corrupt. The >>> problem comes about because process C can only checkpoint frames up until >>> the start of B's transaction. And there is an optimization that will >>> prevent it from copying any earlier frames for which there exists a frame >>> in B's transaction that corresponds to the same database page. So it >>> effectively copis only a subset of the modifications made by earlier >>> transactions into the db file - not necessarily creating a valid db file. >> Can this corruption be detected by running PRAGMA quick_check / >> integrity_check? Having the occasional backup db corrupted would be >> tolerable. > > In many cases, but not generally. There would exist cases where a part of a > committed transaction was lost, or the values in unindexed columns where > replaced, that sort of thing. Ok. Presumably a SQLITE_CHECKPOINT_FULL or SQLITE_CHECKPOINT_RESTART checkpoint mode would ensure the db file is valid? 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 07:35, Dan Kennedy wrote: > Strictly speaking the database file may not be well-formed even if there is > no ongoing checkpoint. If: > > a) process A opens a read transaction, > b) process B opens and commits a write transaction to the database, > c) process C checkpoints the db, > > then the db file considered without the *-wal file may be corrupt. The > problem comes about because process C can only checkpoint frames up until the > start of B's transaction. And there is an optimization that will prevent it > from copying any earlier frames for which there exists a frame in B's > transaction that corresponds to the same database page. So it effectively > copis only a subset of the modifications made by earlier transactions into > the db file - not necessarily creating a valid db file. Can this 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 <maill...@css-uk.net> 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 > corrupt because the file will be changing while you are copying it. > >> In WAL mode I thought the database file is only written to when >> checkpointing. Have I misunderstood this journaling mode? > > How do you intend to prevent your other processes from checkpointing while > you take the backup ? You can disable checkpointing for your own connection > to the database but not for the connections other processes have. All the processes would have automatic checkpointing disabled. Just the backup process would perform the checkpoint. >> Again I may have misunderstood the docs around the Backup API, does it not >> start again from the beginning copying pages if another process writes to >> the database during the process? In practice could it successfully backup a >> 2GB database that is being written to once a second? > > Not if the writing never stopped. But there's no way to take a copy of a > file which is constantly being rewritten. rsync can't do it either. How can > anything copy a file which is constantly being modified ? > > You can BEGIN EXCLUSIVE and then END once your backup is finished. That > should prevent other processes writing to the file. You will have to deal > with what happens if your BEGIN EXCLUSIVE times out, and you will have to put > long timeouts in your other processes so they can handle the file being > locked long enough for the entire copy to be taken. That's the only way I > can think of to do it. And yes, it will prevent writing to the database > while it's being copied. > > On the other hand, there's a different way to clone a database: log the > changes. > > When something issues an INSERT/DELETE/UPDATE command, execute the command > but also append a copy of that command to a text file somewhere. When you > want to bring your backup copy up-to-date, take a copy of the log file, then > execute all the commands in it to your out-of-date copy. > > You need a method of zeroing out the log file, or knowing where you got to on > your last backup. Thanks for the info, Simon. 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 9 Dec 2014, at 22:06, Simon Slavin wrote: > > On 9 Dec 2014, at 8:57pm, Nick <maill...@css-uk.net> wrote: > >> Environment is Linux with multiple (c. 4-6) processes accessing a single >> sqlite database named "test.db". >> >> Backup: >> - New process started using cronjob to initiate application checkpoint until >> completion. >> - rsync diff the file "test.db" to another drive/location (specifically >> ignoring the "-shm" and "-wal" file). >> - exit process >> >> Restore: >> - rsync the file "test.db" from another drive/location. > > Will not be trustworthy if the database is being written to during the rsync > operations. Recommend either of the following: > > A) Ensure all processes besides the backup process have the database closed > while it is being copied. Establish some kind of semaphore so they can tell > when it's safe to open the database again. > > B) Use the SQLite Backup API which was invented to do what you want. > > Simon. That's interesting Simon I didn't expect the database not to be trustworthy. In WAL mode I thought the database file is only written to when checkpointing. Have I misunderstood this journaling mode? Again I may have misunderstood the docs around the Backup API, does it not start again from the beginning copying pages if another process writes to the database during the process? In practice could it successfully backup a 2GB database that is being written to once a second? Regards Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Online/Hot backup of WAL journalling mode database
Hi, I'd like to check my understanding of Sqlite in WAL journalling mode. With automatic checkpointing turned off would the following psuedo-code result in a online backup approach that allows robust restore of the database with data fresh up to the last checkpoint? Environment is Linux with multiple (c. 4-6) processes accessing a single sqlite database named "test.db". Backup: - New process started using cronjob to initiate application checkpoint until completion. - rsync diff the file "test.db" to another drive/location (specifically ignoring the "-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 on Facebook, Twitter and LinkedIn! -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Saturday, August 09, 2014 9:54 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Need help on SQLite In-Memory Mode Try this: SQLiteConnection connection = new SQLiteConnection( "FullUri=file::memory:?cache=shared;"); -- Joe Mistachkin ___ 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] 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 <d...@sqlite.org> 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 <d...@sqlite.org> wrote: > > > > > > > > > On Mon, Jun 30, 2014 at 12:14 PM, Nick Eubank <nickeub...@gmail.com> > > wrote: > > > >> Sorry, I wasn't clear: I have 64 bit R and Windows, but since there are > no > >> 64 bit binaries for SQLite I started with 32 bit SQLite. > >> > >> So now I'm wondering if I could fix this memory problem by installing > the > >> 64-bit version of SQLite. > >> > > > > Probably so, yes. We are working on providing a 64-bit DLL on the > > download site now. > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?
Sorry, I wasn't clear: I have 64 bit R and Windows, but since there are no 64 bit binaries for SQLite I started with 32 bit SQLite. So now I'm wondering if I could fix this memory problem by installing the 64-bit version of SQLite. (I know that this wouldn't fix the problem in 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 <slav...@bigfraud.org> wrote: > > On 30 Jun 2014, at 4:58pm, Nick Eubank <nickeub...@gmail.com > <javascript:;>> wrote: > > > 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. > > You are misunderstanding the problem. There is no bug to be fixed. It is > in the nature of 32-bit Windows that no process has access to more than > 4Gig of memory. In your case, with your version of Windows, it's 2Gig. > Only Microsoft can fix this and they did that by releasing a 64-bit > version of Windows. > > So you need a 64-bit version of R, 64-bit ODBC drivers (which means 64-bit > version of Office if you have it), and a 64-bit version of Windows. > > > Also: any advice on getting a 64bit installation for someone who doesn't > > really know how to compile C? > > I don't know much about that, but this page might be relevant: > > <http://www.ch-werner.de/sqliteodbc/> > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org <javascript:;> > 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] 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, June 30, 2014, Cory Nelson <phro...@gmail.com> wrote: > Without special handling, any 32-bit Windows process is limited to a 2GB > user address space. Due to fragmentation during allocation, you'll never > reach a full 2GB. > > > On Mon, Jun 30, 2014 at 10:31 AM, Nick Eubank <nickeub...@gmail.com > <javascript:;>> wrote: > > > Hi All, > > > > I'm a social scientist wrestling with SQLite in Windows 8 (through R > using > > the RSQLite library) for some data manipulation and querying. > > > > No matter what I do to cache_size (or R's memory settings), SQLite never > > seems to be using more than about ~1900 mb of RAM. Is that a result 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 much! > > > > Nick > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org <javascript:;> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Cory Nelson > http://int64.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org <javascript:;> > 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] Is 32bit SQLite limited to 1900mb RAM in windows?
Hi All, I'm a social scientist wrestling with SQLite in Windows 8 (through R using the RSQLite library) for some data manipulation and querying. No matter what I do to cache_size (or R's memory settings), SQLite never seems to be using more than about ~1900 mb of RAM. Is that a result 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 much! Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite for single user data manipulation
Hello 'Liters! I'd like to move to SQLite from Postgres, but have two quick questions. I'm a social scientist looking to manipulate a large dataset (5 billion transactions, 700gb). I do not need multiple connections, and will only ever run one query at a time. I started in Postgres, but discovered that in Windows one cannot increase some of the per-query RAM memory caps above 2gb (I know -- I would love to switch operating systems, but it's beyond my control). So I'm thinking of moving to SQLite. Before I make the move, I was hoping you kind people could answer two quick questions for me: -- am I going to have problems using all 16gb of ram on my Windows 8 machine for data manipulations if I switch to SQLite? Or will SQLite set me free? -- Is there any reason I should NOT use SQLite for manipulation of large datasets like this (for example, pulling out unique pairs of transaction participants, averages across users, etc.)? All the literature I can find talks about SQL database choices for people setting up databases that will be queried by lots of people, and I just can't find any input for people like me who just want a data manipulation tool 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?
Hi guys,
So I just started using System.Data.Sqlite with entity framework 6
(downloaded the latest System.Data.Sqlite from Nuget, version 1.0.91.0)
After some configuration and code, I found out that I can read from the
database but somehow write is not working.
Here's my code:
using (var context = new InternalDbContext())
{
var demo = context.DemoEntities.Where(d => d.ID ==
1).FirstOrDefault();
demo.Name = "TestTest";
context.DemoEntities.Add(new Demo { Name = "Test" });
context.SaveChanges();
}
Basically after SaveChanges, nothing was updated in the DB. However I can
read fro the DB with the data I manually populated via SQlite admin tool.
Here's my DB schema:
Table name :Demo
Field: ID - Integer Primary Key AutoIncrement
Field: Name - VARCHAR(256)
Here's my classes
public class InternalDbContext : DbContext
{
public DbSet DemoEntities { get; set; }
public InternalDbContext()
{
// Turn off the Migrations, (NOT a code first Db)
Database.SetInitializer(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Database does not pluralize table names
modelBuilder.Conventions.Remove();
}
}
[Table("Demo")]
public class Demo
{
public long ID { get; set; }
public string Name { get; set; }
}
App.config
If anyone 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
Hi, I'm finding that if I compile with either SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4, repeatedly executing a prepared SELECT statement is substantially slower. I have a silly benchmark app that populates a database with test data, creates some pertinent indexes, runs ANALYZE, then executes a particular SELECT statement against the database several thousand times. Executing all these SELECTs normally takes a few seconds, but it's 10x slower when SQLite is compiled with SQLITE_ENABLE_STAT4. Curious, I ran my test app under Visual Studio's profiler, and saw that a substantial amount of time is spent in calls to sqlite3Reprepare(). Reading the docs at http://www.sqlite.org/c3ref/prepare.html, I assume SQLlite is recompiling the statement because it thinks it can find a better query plan based on the parameters I'm binding to it. Unfortunately, the time needed for 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.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resolving Database locking issues in a multi-user environment
Ben Morris wrote: > Potentially we could have around fifty client applications all attempting > simultaneous writes > to a SQLite database stored on a file server. In that case, I would say quoting directly from the SqLite web page we've mentioned already should be sufficient to scare management (emphasis added): "If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, *performance will not be great*. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, *it might be possible for two or more client programs to modify the same part of the same database 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 <nick.s...@citysync.co.uk> wrote: > Richard Hipp wrote: > > Ben Morris <magospiet...@gmail.com> wrote: > >> If anyone could share their honest opinions of both my suggested > >> approach, and my colleague's, I would be very grateful. > > > > [snip] > > Using a separate SQLite database on each client to serve as a local > cache of the master database and then > > periodically synchronizing with the master is a very reasonable > > thing to > do in many situations. Such an > > approach can be very bandwidth efficient (if implemented correctly) > > and > it has the huge advantage that is > > allows for disconnected operation - it allows the clients to > > continue > functioning when the network goes down. > > [snip] > > I completely agree; this is what I do in our database application - > the clients have a local SqLite copy of a central MSSQL database, so > the clients can operate with the data when the network goes down > (which on some customers' sites is a fairly regular occurrence!). To > avoid having to rectify duplicate rows / primary key violations / etc > when down, we just mark the local database as 'read-only' when the > link to MSSQL goes down so no changes can be made to it 'offline'. > Whenever the link is up, we poll the MSSQL database for changes every 15 > seconds or so, so the clients' > SqLite copies are pretty much always in sync. It adds a bit of > network traffic doing this every 15 seconds, but data doesn't change > very often in our application so there's rarely anything more than a > few COUNT queries going on. Plus it means the client-side app can > usually just query the local SqLite database instead of talking over > the network to MSSQL all the time, which can dramatically speed things > up on slow networks. > > So I see no fundamental problem in using this kind of approach. But > as Richard Hipp says, it depends on what your system needs are. > Operating on an SqLite database from multiple networked clients > (especially when on a Windows network) with data that is changing a > lot is not advised when performance and concurrency are important > 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 > ___ 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] Resolving Database locking issues in a multi-user environment
Richard Hipp wrote: > Ben Morris <magospiet...@gmail.com> wrote: >> If anyone could share their honest opinions of both my suggested >> approach, and my colleague's, I would be very grateful. > > [snip] > Using a separate SQLite database on each client to serve as a local cache of > the master database and then > periodically synchronizing with the master is a very reasonable thing to do > in many situations. Such an > approach can be very bandwidth efficient (if implemented correctly) and it > has the huge advantage that is > allows for disconnected operation - it allows the clients to continue > functioning when the network goes down. > [snip] I completely agree; this is what I do in our database application - the clients have a local SqLite copy of a central MSSQL database, so the clients can operate with the data when the network goes down (which on some customers' sites is a fairly regular occurrence!). To avoid having to rectify duplicate rows / primary key violations / etc when down, we just mark the local database as 'read-only' when the link to MSSQL goes down so no changes can be made to it 'offline'. Whenever the link is up, we poll the MSSQL database for changes every 15 seconds or so, so the clients' SqLite copies are pretty much always in sync. It adds a bit of network traffic doing this every 15 seconds, but data doesn't change very often in our application so there's rarely anything more than a few COUNT queries going on. Plus it means the client-side app can usually just query the local SqLite database instead of talking over the network to MSSQL all the time, which can dramatically speed things up on slow networks. So I see no fundamental problem in using this kind of approach. But as Richard Hipp says, it depends on what your system needs are. Operating on an SqLite database from multiple networked clients (especially when on a Windows network) with data that is changing a lot is not advised when performance and concurrency are important 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
Hi Michael, Kreith, Jonas, Thanks for your response. I just tried to launch the binary manually, and it worked very fast. I didn't figure out by myself that i should've tried to launch the application outside of Visual Studio (I was thinking that in "Release" mode VS doesn't slow down the execution by debugging instruments). Sorry for raising a dust with all this. 2012/5/25 Black, Michael (IS) <michael.bla...@ngc.com>: > Usng your sqlite3.exe > CPU Time: user 2.156250 sys 2.078125 > > Using your sqlite3.console.exe > CPU Time: user 1.375000 sys 0.140625 > > I'm 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 > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Nick [eveningn...@gmail.com] > Sent: Thursday, May 24, 2012 5:49 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] SQLite SELECT performance problem > > > The sizes of the executable files are almost identical - there's a few > kilobytes difference. > I have attached the original (downloaded from sqlite.org) sqlite3.exe, > a compiled-by-myself sqlite3console.exe. And the source code. Also > there's import tables dump (import tables are also very similar for > both executables). I hope you can see the difference in the speed: > > http://dl.dropbox.com/u/74970714/Custom%20built%20SQLite3.exe%20Performance%20problem.rar > > There is also a database file. but it is quite large (71 Mb). You can > download it here, to be able to test the SQL query: > > http://dl.dropbox.com/u/74970714/database.sqlite > > Thanks to anyone who can help! > > 2012/5/25 Simon Slavin <slav...@bigfraud.org>: >> >> On 24 May 2012, at 11:13pm, Nick <eveningn...@gmail.com> wrote: >> >>> In my initial message I described some proof-of-concept that I've done. >>> >>> I downloaded sqlite3.exe (An SQLite command line tool) from the >>> SQLite's website. I executed my query and I had to wait 4 seconds for >>> it to complete. >>> >>> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them >>> altogether (and got again the command line tool, but now i've built it >>> by myself using Visual Studio) and executed the same query. It took 15 >>> seconds this time. >> >> I'm very sorry I missed that. I have now re-read your original post. >> >> Can you compare the size of the two executable files for us ? Are the >> various versions you're compiling (I understand you've tried several >> different compilation options) all definitely bigger than the one supplied >> on the SQLite site ? It might give us something to investigate. Also, I >> don't know how to do this under Windows, but do you have a way to check >> whether the versions made by Visual Studio address any DLLs or other >> libraries ? >> >> 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] SQLite SELECT performance problem
The sizes of the executable files are almost identical - there's a few kilobytes difference. I have attached the original (downloaded from sqlite.org) sqlite3.exe, a compiled-by-myself sqlite3console.exe. And the source code. Also there's import tables dump (import tables are also very similar for both executables). I hope you can see the difference in the speed: http://dl.dropbox.com/u/74970714/Custom%20built%20SQLite3.exe%20Performance%20problem.rar There is also a database file. but it is quite large (71 Mb). You can download it here, to be able to test the SQL query: http://dl.dropbox.com/u/74970714/database.sqlite Thanks to anyone who can help! 2012/5/25 Simon Slavin <slav...@bigfraud.org>: > > On 24 May 2012, at 11:13pm, Nick <eveningn...@gmail.com> wrote: > >> In my initial message I described some proof-of-concept that I've done. >> >> I downloaded sqlite3.exe (An SQLite command line tool) from the >> SQLite's website. I executed my query and I had to wait 4 seconds for >> it to complete. >> >> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them >> altogether (and got again the command line tool, but now i've built it >> by myself using Visual Studio) and executed the same query. It took 15 >> seconds this time. > > I'm very sorry I missed that. I have now re-read your original post. > > Can you compare the size of the two executable files for us ? Are the > various versions you're compiling (I understand you've tried several > different compilation options) all definitely bigger than the one supplied on > the SQLite site ? It might give us something to investigate. Also, I don't > know how to do this under Windows, but do you have a way to check whether the > versions made by Visual Studio address any DLLs or other libraries ? > > 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] SQLite SELECT performance problem
I tried defining "SQLITE_TEMP_STORE 2" as well. Unfortunately it doesn't influence the speed much, in my case... 2012/5/25 Jonas Malaco Filho <jonasmalacofi...@gmail.com>: > Why TEMP_STORE=1 (file by default) and not TEMP_STORE=2 (memory by default)? > > *Jonas Malaco Filho* > > 2012/5/24 Simon Slavin <slav...@bigfraud.org> > >> >> On 24 May 2012, at 8:59pm, Nick <eveningn...@gmail.com> 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? >> >> I'm wondering whether the speed increase is related to figuring out the >> parameters in the command. Purely out of interest, and not because I know >> it's faster, have you tried executing the query using sqlite3_exec() rather >> than _prepare(), _step(), _finalize() ? Don't do a lot of reworking of >> your code unless it turns out to be faster. >> >> 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] SQLite SELECT performance problem
In my initial message I described some proof-of-concept that I've done. I downloaded sqlite3.exe (An SQLite command line tool) from the SQLite's website. I executed my query and I had to wait 4 seconds for it to complete. Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them altogether (and got again the command line tool, but now i've built it by myself using Visual Studio) and executed the same query. It took 15 seconds this time. I can't understand why.. I set the code optimization level to "max" in the Visual Studio's Settings. I also executed 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 <slav...@bigfraud.org>: > > On 24 May 2012, at 8:59pm, Nick <eveningn...@gmail.com> 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? > > I'm wondering whether the speed increase is related to figuring out the > parameters in the command. Purely out of interest, and not because I know > it's faster, have you tried executing the query using sqlite3_exec() rather > than _prepare(), _step(), _finalize() ? Don't do a lot of reworking of your > code unless it turns out to be faster. > > 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] 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 <d...@sqlite.org>: > On Thu, May 24, 2012 at 3:59 PM, Nick <eveningn...@gmail.com> wrote: > >> >> Any way I could make my C program execute this query as fast as the >> prebuilt command line tool does it? >> > > > Have you tried compiling with the -DSQLITE_THREADSAFE=0 option? > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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. Then it returns the average value for each of these
groups.
The query looks like this:
SELECT strftime('%s',Min(Stamp)) AS DateTimeStamp,
AVG(P) AS MeasuredValue,
((100 * (strftime('%s', [Stamp]) - 1334580095)) /
(1336504574 - 1334580095)) AS SubIntervalNumber
FROM LogValues
WHERE ((DeviceID=1) AND (Stamp >= datetime(1334580095, 'unixepoch')) AND
(Stamp <= datetime(1336504574, 'unixepoch')))
GROUP BY ((100 * (strftime('%s', [Stamp]) - 1334580095)) /
(1336504574 - 1334580095)) ORDER BY MIN(Stamp)
The numbers in this request are substituted by my application with
some values. I don't know if i can optimize this request more (if
anyone could help me to do so, i'd really appreciate)..
This SQL query can be executed using an SQLite command line shell
(sqlite3.exe). On my Intel Core i5 machine it takes 4 seconds to
complete (there are 10 records in the database that are being
processed).
Now, if i write a C program, using sqlite.h C interface, I am waiting
for 14 seconds for exactly the same query to complete. This C program
"waits" during these 14 seconds on the first sqlite3_step() function
call (any following sqlite3_step() calls are executed immediately).
>From the Sqlite download page I have downloaded SQLite command line
shell's source code and build it using Visual Studio 2008. I ran it
and executed the query. Again 14 seconds.
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?
I am running Windows 64 bit. The prebuilt tool is an x86 process. It
also does not seem to be multicore optimized - in a Task Manager,
during query execution, I can see only one core busy, for both
built-by-mine and prebuilt SQLite shells.
I have tried different Visual Studio's optimization options, tried to
match "Pragma compile_options;" output by defining preprocessor
directives in sqlite3.c file to output generated by downloaded
sqlite3.exe file. To no avail.
Any way I could make my C program execute this query as fast as the
prebuilt command line tool does it?
Thanks much!
___
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
-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- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: 11 April 2012 16:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode > After sqlite3_close() returns SQLITE_BUSY, you can use > sqlite3_next_stmt() to loop through those statements that SQLite thinks are > unfinalized. Then use sqlite3_sql() to identify each. > > The results might reveal something. Found the problem. Was indeed in my code, deep inside my own wrapper function. There's no Unicode equivalent of sqlite3_exec(), so in unicode, it has to go through the prepare/step/finalize steps. It wasn't finalizing the stmt (as that bit of code in my wrapper is usually only called if the caller wants data back, so assumes the caller will finalize the stmt at a later time). Whoops! Thanks for the help anyway guys! 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
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: 11 April 2012 16:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode > After sqlite3_close() returns SQLITE_BUSY, you can use > sqlite3_next_stmt() to loop through those statements that SQLite thinks are > unfinalized. Then use sqlite3_sql() to identify each. > > The results might reveal something. Ok, did this. It tells me the SQL command that is unfinalized is: "PRAGMA journal_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 occurs in Unicode build... 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
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: 11 April 2012 16:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode > > I'll try your suggestion of setting it to what it currently is (it *should* > > be DELETE) and see what it does. > > After sqlite3_close() returns SQLITE_BUSY, you can use > sqlite3_next_stmt() to loop through those statements that SQLite thinks are > unfinalized. Then use sqlite3_sql() to identify 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
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: 10 April 2012 19:04 To: General Discussion of SQLite Database Subject: 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). > > Are you by any chance having a technical problem with the PRAGMA command > itself ? For instance, suppose the command was encoded in UTF-16 and SQLite > was expecting ASCII. Figure out what the default > value should be (e.g. use > the shell tool to do a "PRAGMA journal_mode;") then change your normal app to > set the mode to that value. See if it gives you the same problem. I'm not getting the same problem with other PRAGMA commands (quick_check(1) and synchronous=NORMAL), so I don't know why journal_mode would be any different. If I leave this one PRAGMA out, everything is fine. If I include it, the DB always fails to close. But only in Unicode. I'll try your suggestion of setting 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 2012 13:34 To: General Discussion of SQLite Database (sqlite-users@sqlite.org) Subject: [sqlite] Unicode problem when setting PRAGMA journal_mode Hi all, Our windows application uses sqlite, and we've had no problems with it in our existing builds, which use the multibyte character set. We are now converting our codebase to Unicode. SqLite is quite happily running with our Unicode builds, creating SqLite databases in UTF-16LE encoding. We have come across one very odd thing, though. If I run the query "PRAGMA journal_mode = NORMAL" (actually, setting it to ANY of the value values, including "OFF" does the same thing), everything runs fine until the code closes the database, at which point the database returns SQLITE_BUSY, with the error "unable to close due to unfinalised statements". This happens even if the only query I run on the database is that pragma command. I can run other pragmas fine (e.g. "PRAGMA synchronous = NORMAL") and all SQL queries are fine, and the database will close happily; it's just this journal_mode pragma that's causing this odd behaviour, and ONLY in the Unicode build of our code - the multibyte build doesn't show this problem. This happens whether the database exists already [in UTF-16LE mode], or whether it creates 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unicode problem when setting PRAGMA journal_mode
Hi all, Our windows application uses sqlite, and we've had no problems with it in our existing builds, which use the multibyte character set. We are now converting our codebase to Unicode. SqLite is quite happily running with our Unicode builds, creating SqLite databases in UTF-16LE encoding. We have come across one very odd thing, though. If I run the query "PRAGMA journal_mode = NORMAL" (actually, setting it to ANY of the value values, including "OFF" does the same thing), everything runs fine until the code closes the database, at which point the database returns SQLITE_BUSY, with the error "unable to close due to unfinalised statements". This happens even if the only query I run on the database is that pragma command. I can run other pragmas fine (e.g. "PRAGMA synchronous = NORMAL") and all SQL queries are fine, and the database will close happily; it's just this journal_mode pragma that's causing this odd behaviour, and ONLY in the Unicode build of our code - the multibyte build doesn't show this problem. This happens whether the database exists already [in UTF-16LE mode], or whether it creates 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
-Original Message-
> I am trying to compile the latest sqlite 3.7.11 amalgamation C file within a
> Visual Studio 2008 Express C++ project.
> sqlite3.c is correctly set as to be compiled as C code but I am unable to
> find out a way to fix some compilation errors:
>
> Compiling...
> sqlite3.c
>..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':' before
>'!'
>..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';'
>..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}'
>..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct
>'LikeOp'
>..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have
>struct/union type
>..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!'
>..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have
>struct/union type
>..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!'
>..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of this
>type as an expression
> ..\Sources\sqlite3.c(8133) : see declaration of 'ExprList'
>..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have
>struct/union type
>..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few
>arguments for call
>..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!'
>..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have
>struct/union type
>..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few
>arguments 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 and it builds fine. Can you check what you have for the LikeOp
structure definition? Mine looks like this, and starts on line 107829:
struct LikeOp {
Token eOperator; /* "like" or "glob" or "regexp" */
int not; /* True if the NOT keyword is present */
};
Thanks,
Nick.
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] storing and comparing dates in sqlite
It's your choice, really. You could store it as TEXT, in some standardised format (e.g. XML dateTime format: -mm-ddThh:mm:ss. plus a timezone offset if you wanted) or your own format, and query it back as text, or you could store it in UNIX integer time (seconds since unix epoch (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 18:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] storing and comparing dates in sqlite can you point me in the right direction? aka. what data type am i using to store the date in my sqlite database? is TEXT correct? how do I do a select on it? thanks. Stephan Beal-3 wrote: > > On Mon, Jan 23, 2012 at 6:51 PM, dotolee <woo_ju...@yahoo.com> wrote: > >> >> i'm new to sqlite... and relatively new to php. just wondering what >> the best way is to store and compare dates. >> > > For any given 10 developers you'll likely hear 11 opinions on this topic. > IMO Unix timestamps are the most portable form out there. "Portable" > meaning, in this context, the ability to work with them (more or less > easily) in a wide variety of contexts. > > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/storing-and-comparing-dates-in-sqlite-tp33189769p33189862.html Sent from the SQLite mailing list archive at Nabble.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] EXT :Re: Poor performance with nested query in outer join
"Black, Michael (IS)" <michael.bla...@ngc.com> writes: > Why do you have a subselectwhat are you doing there that you can't > do in the "on" clause? In the "real" example I'm joining with a view: create view v as select * from b where ... select * from a left natural 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 the view. 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
Simon Slavin <slav...@bigfraud.org> writes: > I understand that SQLite handles VIEWs as if you had defined and saved > a SELECT statement. So if you JOIN with a VIEW, does SQLite handle it > as as JOIN or a sub-SELECT ? It's just the same as if you JOIN with a sub-SELECT (the query for the view), as far 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
"Black, Michael (IS)" <michael.bla...@ngc.com> writes: > Natural joins are generally considered to be evil. Too many columns > in common can be bad. > > If you just spell it out it works as expected > > sqlite> explain query plan select * from a left join b where a.id=1 and > b.id=a.id; > 0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 > rows) > 0|1|1|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?) > (~1 rows) Thanks, but this is a red herring: it makes no difference whether you use natural join or an explicit join here (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 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
Simon Slavin <slav...@bigfraud.org> 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 express this as just > a JOIN you'll find that the optimizer works as expected. The reason > it can't work here is because it can work across JOINs but not > SELECTs. This does the trick, of course. The problem is that I am really joining with a view, i.e. select * from a left natural join some_view where id = 1; so there is no way to avoid a subquery if I want to use the view. I suppose I will just give up on using a view here, then, and write my query from scratch instead. Thanks anyway! Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Poor performance with nested query in outer join
Hi list, I have the following schema: CREATE TABLE a(id int primary key); CREATE TABLE b(id int primary key); I want to find information about a particular id, and my query boils down to something like select * from a left natural join (select * from b) where id = 1; (in the real code, the inner query has a where-clause). I would expect SQLite to just look up 1 in the indexes for a.id and b.id. Unfortunately, it does a full table scan: sqlite> explain query plan ...> select * from a left natural join (select * from b) where id = 1; 1|0|0|SCAN TABLE b (~100 rows) 0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 rows) 0|1|1|SCAN SUBQUERY 1 (~10 rows) However, if I add "where id = 1" to the inner query I get the plan I want: sqlite> explain query plan ...> select * from a left natural join (select * from b where id = 1) ...> where id = 1; 1|0|0|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?) (~1 rows) 0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 rows) 0|1|1|SCAN SUBQUERY 1 (~1 rows) I also get a nice plan if I use an inner 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 expecting too much from the query optimiser? 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 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 it was quoted. For example, in C you don't expect: x = "123E45678942"; ... to go through any sort of numeric conversion. Now I know this isn't C, but the "let's see if we can turn a string into a number, and take two to three seconds to do so" is not right, IMHO. ___ 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 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
On 17/10/2011, at 9:55 PM, Dan Kennedy wrote: > Did you download the binary from the website or build it yourself? > If the latter, which compiler are you using? And what level of > optimization is enabled? I initially observed the problem with version 3.7.7.1, as embedded in my application using Visual C++ version 6.0. I have "maximum speed" optimization selected. When a user reported the problem, and I confirmed it, I downloaded the latest command-line utility from the SQLite site (version 3.7.8), as follows: http://www.sqlite.org/sqlite-shell-win32-x86-3070800.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 above, changing the column type from STRING to TEXT also fixes it. However isn't over 2 seconds a bit much for evaluating a number, regardless of the column type? - Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very slow processing of some SELECT statements
Hello, Running under Windows XP, using sqlite3.exe version: 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 As follows: sqlite3 foo.db The following SQL: DROP TABLE IF EXISTS rooms; DROP TABLE IF EXISTS exits; CREATE TABLE IF NOT EXISTS rooms ( roomidINTEGER PRIMARY KEY AUTOINCREMENT, uid TEXT NOT NULL -- unique room ID ); CREATE TABLE IF NOT EXISTS exits ( exitid INTEGER PRIMARY KEY AUTOINCREMENT, fromuid STRING NOT NULL -- exit from which room (in rooms table) ); CREATE INDEX IF NOT EXISTS fromuid_index ON exits (fromuid); SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC'; Takes over 2 seconds to execute (in particular, the SELECT statement). Two changes make it much faster. Either: * Delete the CREATE INDEX line or * Change the select statement to: SELECT * FROM exits WHERE fromuid = 'x2E515665758C87202B281C7FC'; I'm not sure what is going on, but it appears that somewhere internally SQLite3 is trying to calculate the very large number 2e515665758 (2 times 10 to the power 515665758). And somehow the index is influencing this behaviour. Trying under Mac OS/X (Lion) does not appear to exhibit this problem. The string I am searching for is a hex hash string, generated by hashing various other things (not shown here). Occasionally it would appear, the hash "looks like" a decimal number 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.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 attached, then ANALYZE fails, perhaps for obvious reasons... Is there a way to ATTACH and explicitly specify a readonly flag? I'm not doing this yet, but what about the other way around - readonly main db, with attached read/write databases? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3
On 30 Aug 2011, at 16:53, Simon Slavin wrote: > > Most PRAGMAs, including others which modify engine behaviour like > recursive_triggers, are not stored in the database but have to be restated > every time you open the database file. > Ok, thanks, If that is the case for this 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.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 1073741823. Is anyone else affected by this? Environment: Ubuntu Linux 10.04 SQLite shell 3.7.6.3 downloaded from SQlite.org The following is copy-n-paste from the command line rm page_count_test.db rm: cannot remove `page_count_test.db': No such file or directory ./sqlite3 page_count_test.db SQLite version 3.7.6.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" PRAGMA encoding = "UTF-8"; PRAGMA legacy_file_format = off; PRAGMA auto_vacuum = 0; PRAGMA page_size = 4096; PRAGMA max_page_count = 12800; 12800 sqlite> PRAGMA journal_mode = WAL; wal sqlite> PRAGMA user_version = 20; sqlite> sqlite> sqlite> PRAGMA page_size; 4096 sqlite> PRAGMA max_page_count; 12800 sqlite> .quit ./sqlite3 page_count_test.db SQLite version 3.7.6.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA page_size; 4096 sqlite> PRAGMA max_page_count; 1073741823 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Making data and tables persistent
Darren Duncan wrote: > >> I am using sqlite3 primarily from c++, everything is working fine, > >> except when I switch off my computer I loose all data, is there any > >> setting I need to do to make the data and table object persistent in the .db file? > > > > Are you correctly closing your connection to the database before your application quits ? > > > > Does a file with the correct name exist on your disk ? Does it have zero length ? > > For that matter, maybe hinted from the second point here, are you using a regular file-based database or a MEMORY one? -- Darren Duncan ___ And are you using autocommit, or are you manually beginning and ending transactions? If you have a number of uncommitted transactions prior to closing the database (or a power failure / app crash / OS 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.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-users-boun...@sqlite.org] On Behalf Of dawemail2006-sqliterea...@yahoo.co.uk Sent: 22 May 2011 12:29 To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite Files Hello, Can you please tell me how to open and read SQlite files. I have a Firefox browser which uses a Read It Later add-on. I would like to open this file and view its contents with a view of deleting some of it. I hope you can help me and other users of this add-on in Firefox which tends to bloated. There is a method of deleting one entry at a time but that is very time consuming. It would be much better if a range of dates for instance could be deleted in one go. Regards, Tony Wilkins ___ 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] Transaction speed too slow?
Thanks for the suggestion and link. It seems that noatime has already been set
for both partition types on my test setup. I turned atime back on and re-ran
the tests.
The results are within error margins and therefore arguably the same as
previous.
I'd be interested in the results if others 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 every 63ms. My
> understanding is that the hard drive in the netbook is 15ms access time so
> you're seeing 4 accesses per transaction with the way you're doing this test
> (write caching off, full sync).
>
>
>
> When you write a record you have to update last access times for example.
>
>
>
>
>
> Might be interesting to turn off last access updates and see if it changes
> your results much.
>
>
>
> http://www.cyberciti.biz/faq/linux-noatime-ext3-ext4-fstab-configuration/
>
>
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> ____
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Nick [maill...@css-uk.net]
> Sent: Sunday, May 08, 2011 3:52 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Transaction speed too slow?
>
>
> From http://www.sqlite.org/faq.html#q19 it says "A transaction normally
> requires two complete rotations of the disk platter, which on a 7200RPM disk
> drive limits you to about 60 transactions per second."
>
> Using Linux/Ubuntu 10.04 on an otherwise idle Atom powered Nettop with a
> 5400RPM disk drive I was expecting circa 45 transactions per second. However
> using the basic python script below I seem to be getting a lot slower
> results, namely:
>
> ext3 partition: 16 transactions per second
> jfs partition: 9 transactions per second
> :memory: 15798 transaction per second
>
> Does anyone have an explanation for this?
>
> The script used is below. I'd be grateful if people could confirm whether it
> is just my hardware or a common result
>
> FULL RESULTS:
>
> nick@Haribo:~$ sudo hdparm -W 0 /dev/sda
>
> /dev/sda:
> setting drive write-caching to 0 (off)
> write-caching = 0 (off)
> nick@Haribo:~$ python write-transactions-1.py
> Run Number: 1, Location: :memory:
> 0:00:00.000108
> 0:00:00.58
> 0:00:00.57
> 0:00:00.57
> 0:00:00.56
> 0:00:00.56
> 0:00:00.57
> 0:00:00.57
> 0:00:00.57
> 0:00:00.56
> Time Avg: 0.62
> Trans/sec Avg: 16155
> Run Number: 2, Location: write-transactions-1.db
> 0:00:00.099678
> 0:00:00.121630
> 0:00:00.110672
> 0:00:00.099599
> 0:00:00.110782
> 0:00:00.099542
> 0:00:00.121776
> 0:00:00.099599
> 0:00:00.121794
> 0:00:00.099624
> Time Avg: 0.108470
> Trans/sec Avg: 9
>
> ---
>
> nick@Haribo:~$ sudo hdparm -W 1 /dev/sda
>
> /dev/sda:
> setting drive write-caching to 1 (on)
> write-caching = 1 (on)
> nick@Haribo:~$ python write-transactions-1.py
> Run Number: 1, Location: :memory:
> 0:00:00.000113
> 0:00:00.57
> 0:00:00.56
> 0:00:00.56
> 0:00:00.56
> 0:00:00.57
> 0:00:00.57
> 0:00:00.56
> 0:00:00.56
> 0:00:00.56
> Time Avg: 0.62
> Trans/sec Avg: 16129
> Run Number: 2, Location: write-transactions-1.db
> 0:00:00.001438
> 0:00:00.000898
> 0:00:00.000989
> 0:00:00.000984
> 0:00:00.000982
> 0:00:00.001945
> 0:00:00.001059
> 0:00:00.001169
> 0:00:00.000914
> 0:00:00.001063
> Time Avg: 0.001144
> Trans/sec Avg: 874
>
>
> --
>
> SCRIPT
>
> # Test Benchmark for Transactions speed per second using built in SQLite
> import sqlite3
> import datetime
>
> contype = [":memory:", "write-transactions-1.db"]
>
> for runidx, runtype in enumerate(contype):
>
># Heading
>print "Run Number: %d, Location: %s" % (runidx + 1, runtype)
>
>con = sqlite3.connect(runtype)
>con.isolation_level = None
>con.execute("PRAGMA synchronous = FULL")
>times = []
>
># Create the table
>con.execute("drop table if exists person")
>con.execute("create table person(firstname, lastname)")
>
># Measure 10 points
>for run in range(10):
>
># BEGIN transaction
>con.execute
