[sqlite] System.Data.SQLite and spellfix under VB.net
Make sure that you have all the necessary .h files in the same directory as the spellfix.c source code (the #include's use " " rather than < >, which means look in the current directory, not the include path). Use the following command line in the directory containing source (.c) and header (.h) files: gcc -mdll -s spellfix.c -o spellfix.dll Notes: -mthreads is not necessary. static lib-gcc is not necessary. This will generate only dependancies on KERNEL32 and the MSVCRT.DLL subsystem runtime. If you want to use a specific (common) runtime, then you need to link that runtime. The long form might be required for some modules: gcc -mdll -mthreads -s -DSQLITE_API=__declspec(dllexport) -Wl,-Bstatic -o -static-libgcc plus any special platform runtimes you want to use in preference to the subsystem runtime (such as msvcrt90 etc). --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of sonypsx >Sent: Thursday, 12 March, 2015 11:56 >To: 'General Discussion of SQLite Database' >Subject: Re: [sqlite] System.Data.SQLite and spellfix under VB.net > >Hello, > >may i ask again ... >Can somebody help to successfully compile spellfix.c for windows? >Errors see below! > >Cheers >sonypsx > >-Urspr?ngliche Nachricht- >Von: sqlite-users-bounces at mailinglists.sqlite.org >[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von >sonypsx >Gesendet: Mittwoch, 04. M?rz 2015 19:54 >An: 'General Discussion of SQLite Database' >Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net > >Hello Joe, > >ok i'll tried: > >gcc -s -O4 -I /path/to/sqlite/headers/ -shared -o spellfix.dll spellfix.c > >and got this error: > >c:\MinGW\bin>gcc -s -O4 -I c:\Sqlite\src\ -shared -o spellfix.dll >c:\Sqlite\ext \misc\spellfix.c In file included from >c:\Sqlite\ext\misc\spellfix.c:17:0: >c:\Sqlite\src/sqlite3ext.h:20:21: fatal error: sqlite3.h: No such file or >direct ory #include "sqlite3.h" > ^ >compilation terminated. > >c:\MinGW\bin> > >If I look into sqlite3ext.h i see the reference to the sqlite3.h file >which >does not exist in the whole sqlite source (zip) which I downloaded from >http://www.sqlite.org/src/info/e693e11d1b926597 > > >Can you help me please? > >Best regards >sonypsx > >-Urspr?ngliche Nachricht- >Von: sqlite-users-bounces at mailinglists.sqlite.org >[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Joe >Mistachkin >Gesendet: Montag, 02. M?rz 2015 22:37 >An: 'General Discussion of SQLite Database' >Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net > > >sonypsx wrote: >> >> could some please post a sample how to use the spellfix module with >> System.Data.SQLite under VB.net? >> > >The first step would be to compile the spellfix extension as a loadable >module, as documented here: > > https://www.sqlite.org/loadext.html > >Next, you can load it using the SQLiteConnection.LoadExtension method. > >Finally, you should be able to follow along with the normal spellfix >docs, >here: > > https://www.sqlite.org/spellfix1.html > >-- >Joe Mistachkin > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fix rowid's of fts table
Rael Bauer wrote: > Now I have restructured the normal table (i.e. delete column) using the > algorithm mentioned here a couple of times (insert into normal select > (columns) from normal_OLD, etc..) > > This "resets" the rowid's of the normal table. If you rely on the rowid values, you should declare this column as INTEGER PRIMARY KEY to prevent such changes. Regards, Clemens
[sqlite] fix rowid's of fts table
Hi, I have a normal table, and a fts table that are joined by rowid's. E.g. when I add a row to the normal table, I add some values into the fts table as well, and set the rowid based on the normal table rowid (as recommended). Now I have restructured the normal table (i.e. delete column) using the algorithm mentioned here a couple of times (insert into normal select (columns) from normal_OLD, etc..) This "resets" the rowid's of the normal table. E.g. if the table started with rowid's like: 1 2 6 7 10 after restructuring they will be: 1 2 3 4 5 So the fts table is now out of sync with the normal table. Do i have to completely re-populate the fts table or is there a simpler way to "reset" the fts rowids? (If I tried to just reset the rowid's on the "main" fts table I imagine this would not work, since there are the aux fts tables as well..) Thanks Rael
[sqlite] fix rowid's of fts table
On 12 March 2015 at 20:20, Rael Bauer wrote: > > Hi, > . . . > Now I have restructured the normal table (i.e. delete column) using the > algorithm mentioned here a couple of times (insert into normal select > (columns) from normal_OLD, etc..) > > This "resets" the rowid's of the normal table. > E.g. if the table started with rowid's like: > 1 > 2 > 6 > 7 > 10 > > after restructuring they will be: > 1 > 2 > 3 > 4 > 5 > > So the fts table is now out of sync with the normal table. Do i have to > completely re-populate the fts table or is there a simpler way to > "reset" the fts rowids? Propagate the rowids from the old table when populating the new table: INSERT INTO nTable( rowid, data1, ... ) SELECT rowid, data1, ... FROM oTable; > > (If I tried to just reset the rowid's on the "main" fts table I imagine > this would not work, since there are the aux fts tables as well..) > > Thanks > Rael Regards, Simon
[sqlite] Releasing a read (SHARED) lock
On 3/12/15, Barry wrote: > On 13 March 2015 at 01:21, Dinu Marina wrote: > >> You should be also aware of a more common pitfall: unclosed result sets. >> Any lock is held until you read PAST the last row or you call >> stmt_finalize >> (or the equivalent abstraction in your DBAL). Always close select >> statements. > > > Hi Dinu, > > Am I correct in my understanding then that it is not enough to call > sqlite3_reset, I must call sqlite3_finalize after I am done with a > statement? > One or the other. Call sqlite3_reset() if you intend to reuse prepared statement (run it again). Call sqlite3_finalize() to destroy the prepared statement and release all of its resources back to the system to be reused. Either is sufficient to release any locks held. -- D. Richard Hipp drh at sqlite.org
[sqlite] Releasing a read (SHARED) lock
There is also a more obscure side-effect exhibited by sqlite only: if you issue consecutive unfinalized select statements, it never releases the write lock (although they should be atomic), but it does trip the deadlock mechanism; any writer in the wait will receive SQLITE_BUSY at the very moment the second read statement is issued, regardless of the busy_timeout value. This happens in non-WAL mode. The usual culprit are one-record results, like SELECT COUNT(*)... usually the client expects one row and reads one row, but you need to either try to read 2 rows or explicitly free the cursor. Do note (for evangelism sake) that DBs with random-access cursors do not free the read lock even when you read past the last record, because you could decide to rewind. So always free result sets.
[sqlite] Releasing a read (SHARED) lock
You should be also aware of a more common pitfall: unclosed result sets. Any lock is held until you read PAST the last row or you call stmt_finalize (or the equivalent abstraction in your DBAL). Always close select statements. On Mar 12, 2015 11:40 AM, "R.Smith" wrote: > > > On 2015-03-12 04:38 AM, Barry wrote: > >> Hello everybody, >> >> I have a situation where two processes are accessing the same SQLite >> database. One process only reads from the database, one process reads and >> writes. >> >> These processes keep a single database connection open for the lifetime of >> the process. >> >> It seems to me that once the reader process accesses the database (after >> it >> performs its first SELECT statement), it maintains a lock on the database >> until the connection is closed (when the program is exited). This prevents >> the writer process from updating the database. >> > > Hi Barry, > > This is the usual situation when one of your transactions in the "reading > only" database does not finalize. i.e. you started a transaction there and > did not end it with either "END TRANSACTION" or "COMMIT" or "ROLLBACK". > > In the non-WAL DB it will simply hold the lock preventing changes. In the > WAL DB it will hold the lock for its own view of the data but let the other > writer write.. however it won't see the changes for itself. > > This is very common actually. Just find every transaction you start > (explicitly or implicitly) and make sure you end it and when you end it, > see what the return value is from SQLite and whether it reports any error. > > HTH! > Ryan > > > >> I tried changing to WAL. This made the writer process able to commit its >> changes, but now the reader does not see any modifications made to the >> database until it is restarted (It seems to see a snapshot of the DB at >> the >> time of its first read). >> >> I am using prepared statements: On opening the DB, I create all my >> prepared >> statements. When I need to execute a statement, I bind to the statement, >> call sqlite3_step (possibly multiple times), then call sqlite3_reset. I do >> not finalise the statements until the program closes. >> >> In order to simulate 'save' behaviour, the writer process always holds a >> transaction open. When the user chooses 'save', the current transaction is >> committed and a new transaction is begun. (I understand that the reader >> will not see any changes in the uncommitted transaction, but is not seeing >> any committed transactions either). >> >> I have checked quite thoroughly through my code and cannot find any >> instances of statements executed without a sqlite3_reset quickly >> following. >> >> Is this intended behaviour - that once a connection has performed a read, >> it maintains its lock on the database for its lifetime? Is SQLite smart >> enough to know that the pages it holds in cache of the reader are invalid >> after the writer has made changes to the DB on disk? >> >> If this is not the intended behaviour - is there a way I can find out >> which >> statements are causing the lock to be held open? Or can I force SQLite to >> discard its cache? >> >> Any help would be appreciated. >> >> Regards, >> >> Barry Smith >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Memory leak?
On 2015-03-12 06:21 PM, Matthias Schmitt wrote: > Hello, > > I know, I am touching a hot iron, when claiming that sqlite might have a > memory leak. But I am trying to debug memory leaks related to sqlite now > since 4 days and I am running against walls. Hi Matthias, Firstly, there are no hot irons here, it either works as designed, or it doesn't. If it does work correctly, we will gladly show you the way. if it doesn't, then we will all be glad it came to light and await the fix eagerly - you are very welcome to post the concern. It is a little suspect that millions of applications around the world gets compiled and memory-leak-checked daily with sqlite and your's is the only one finding a leak - BUT, it is not impossible at all. > Here is my problem: > > - I am writing an iPhone app using iOS 8.2, Xcode 4.2, sqlite 3.8.8.3. > > - The memory leak appears in the iPhone simulator more often, than on an > original iPhone device, but it appears always. > > - The leak appears with totally different type of queries. It might be, that > a leak appears in a specific query in one debug session and in another debug > session, it doesn?t (which makes it difficult to debug and drives me totally > nuts). For me it seems to be related to a timing/threading issue. This is why > it makes not much sense to offer some code here. > > - When debugging inside sqlite the leak is always related to the same code > line, independent from the type of database command (select, insert, delete). > > - It appears always with the memory allocation in sqlite.c line 16872 in > function sqlite3MemMalloc > Here is the line: void *p = SQLITE_MALLOC( nByte ); > > - The leaked memory has always a size of nByte = 4344 bytes. > > - Here is a typical call stack up to the leaked code: This is a bit weird, how do you know the memory is leaked when you are still in the functions dealing with it? The definition of a memory leak (simplified for our purposes) is where some memory is acquired into processes repeatedly with the needed memory already existing and not released so that perpetual memory-adding will eventually eat up all the available resources. To be more succinct: It's an accumulation of allocations over time. Lots of times memory is allocated once and kept, or cached, or used by shared resources or many other things that might speed up processes. If the memory is only ever released when the object or only program structure that can use it is released/freed/disposed, then it is still ok and not a leak. If memory is retained even after the destruction of it's keeper, then it may also be regarded as a "leak", even without accumulation. I cannot see from anything you said that the memory survives the death of the object, nor that it accumulates in any way. It may well still be an unnecessary allocation, but it can't possibly be a "Leak" (unless I am misreading your information). All that said, you may well ask "What is this allocation, and should it happen?" (Which a dev might answer) - but is it hurting your program or the use thereof? (That would be a real cause for concern).
[sqlite] System.Data.SQLite and spellfix under VB.net
Hello, may i ask again ... Can somebody help to successfully compile spellfix.c for windows? Errors see below! Cheers sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von sonypsx Gesendet: Mittwoch, 04. M?rz 2015 19:54 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net Hello Joe, ok i'll tried: gcc -s -O4 -I /path/to/sqlite/headers/ -shared -o spellfix.dll spellfix.c and got this error: c:\MinGW\bin>gcc -s -O4 -I c:\Sqlite\src\ -shared -o spellfix.dll c:\Sqlite\ext \misc\spellfix.c In file included from c:\Sqlite\ext\misc\spellfix.c:17:0: c:\Sqlite\src/sqlite3ext.h:20:21: fatal error: sqlite3.h: No such file or direct ory #include "sqlite3.h" ^ compilation terminated. c:\MinGW\bin> If I look into sqlite3ext.h i see the reference to the sqlite3.h file which does not exist in the whole sqlite source (zip) which I downloaded from http://www.sqlite.org/src/info/e693e11d1b926597 Can you help me please? Best regards sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Joe Mistachkin Gesendet: Montag, 02. M?rz 2015 22:37 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net sonypsx wrote: > > could some please post a sample how to use the spellfix module with > System.Data.SQLite under VB.net? > The first step would be to compile the spellfix extension as a loadable module, as documented here: https://www.sqlite.org/loadext.html Next, you can load it using the SQLiteConnection.LoadExtension method. Finally, you should be able to follow along with the normal spellfix docs, here: https://www.sqlite.org/spellfix1.html -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimization Opportunity?
>> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > >I was under the impression that the opposite is true, but I wasn't sure >about that. The primary key is only a covering index if you are only accessing fields comprising the primary key, or if the primary key is the rowid (in which case the primary key is the rowid of the entry in the table -- in which case a scan of the primary key index is the same as a scan of the table) or if the table was declared as without_rowid. >> From the secunsary indexes only a part oh the key is used. >> Note there is not much use on adding PK as second column in the >> additional indexes. It is there anyway a a pointer to the row. >You're right, that index doesn't make much sense; in my real application >it looks different, what I was showing here was just an example (one that >was not very well thought of, obviously). No. The rowid is contained in the index anyway and that is the primary key if and only if the table is declared such that the rowid is the primary key. Alternate keys (such as declare by any other method) are *NOT* included in any other index. If your primary key is declared on columns, then the rowid is what is stored in the index in addition to the named columns, in order to locate the table row. Unless of course your table is declared without rowid in which case the primary key is the declared key, not the rowid, and the declared primary key is stored in every index to allow you to located the corresponding table row. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why.
[sqlite] System.Data.SQLite and spellfix under VB.net
The zip should contain 4 files shell.c sqlite3.c sqlite3.h sqlite3ext.h sqlite-amalgamation-3080803.zip -Urspr?ngliche Nachricht- Von: sonypsx [mailto:sonypsx at gmx.net] Gesendet: Donnerstag, 12. M?rz 2015 18:56 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net Hello, may i ask again ... Can somebody help to successfully compile spellfix.c for windows? Errors see below! Cheers sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von sonypsx Gesendet: Mittwoch, 04. M?rz 2015 19:54 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net Hello Joe, ok i'll tried: gcc -s -O4 -I /path/to/sqlite/headers/ -shared -o spellfix.dll spellfix.c and got this error: c:\MinGW\bin>gcc -s -O4 -I c:\Sqlite\src\ -shared -o spellfix.dll c:\Sqlite\ext \misc\spellfix.c In file included from c:\Sqlite\ext\misc\spellfix.c:17:0: c:\Sqlite\src/sqlite3ext.h:20:21: fatal error: sqlite3.h: No such file or direct ory #include "sqlite3.h" ^ compilation terminated. c:\MinGW\bin> If I look into sqlite3ext.h i see the reference to the sqlite3.h file which does not exist in the whole sqlite source (zip) which I downloaded from http://www.sqlite.org/src/info/e693e11d1b926597 Can you help me please? Best regards sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Joe Mistachkin Gesendet: Montag, 02. M?rz 2015 22:37 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net sonypsx wrote: > > could some please post a sample how to use the spellfix module with > System.Data.SQLite under VB.net? > The first step would be to compile the spellfix extension as a loadable module, as documented here: https://www.sqlite.org/loadext.html Next, you can load it using the SQLiteConnection.LoadExtension method. Finally, you should be able to follow along with the normal spellfix docs, here: https://www.sqlite.org/spellfix1.html -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Memory leak?
Hello, I know, I am touching a hot iron, when claiming that sqlite might have a memory leak. But I am trying to debug memory leaks related to sqlite now since 4 days and I am running against walls. Here is my problem: - I am writing an iPhone app using iOS 8.2, Xcode 4.2, sqlite 3.8.8.3. - The memory leak appears in the iPhone simulator more often, than on an original iPhone device, but it appears always. - The leak appears with totally different type of queries. It might be, that a leak appears in a specific query in one debug session and in another debug session, it doesn?t (which makes it difficult to debug and drives me totally nuts). For me it seems to be related to a timing/threading issue. This is why it makes not much sense to offer some code here. - When debugging inside sqlite the leak is always related to the same code line, independent from the type of database command (select, insert, delete). - It appears always with the memory allocation in sqlite.c line 16872 in function sqlite3MemMalloc Here is the line: void *p = SQLITE_MALLOC( nByte ); - The leaked memory has always a size of nByte = 4344 bytes. - Here is a typical call stack up to the leaked code: 0 libsystem_malloc.dylib malloc_zone_malloc 1 MYAPP sqlite3MemMalloc sqlite3.c:16872 2 MYAPP mallocWithAlarm sqlite3.c:20510 3 MYAPP sqlite3Malloc sqlite3.c:20541 4 MYAPP pcache1Alloc sqlite3.c:39749 5 MYAPP pcache1AllocPage sqlite3.c:39833 6 MYAPP pcache1FetchStage2 sqlite3.c:40266 7 MYAPP pcache1Fetch sqlite3.c:40367 8 MYAPP sqlite3PcacheFetch sqlite3.c:39119 9 MYAPP sqlite3PagerAcquire sqlite3.c:46573 10 MYAPP btreeGetPage sqlite3.c:54380 11 MYAPP getAndInitPage sqlite3.c:54435 12 MYAPP moveToRoot sqlite3.c:57233 13 MYAPP sqlite3BtreeMovetoUnpacked sqlite3.c:57465 14 MYAPP sqlite3VdbeExec sqlite3.c:74055 15 MYAPP sqlite3Step sqlite3.c:68839 16 MYAPP sqlite3_step sqlite3.c:68905 17 MYAPP -[FMResultSet nextWithError:] FMResultSet.m:155 18 MYAPP -[FMResultSet next] FMResultSet.m:150 19 MYAPP __40-[MYAPPAppDelegate loadPreferences]_block_invoke MYAPPAppDelegate.m:323 20 MYAPP __30-[FMDatabaseQueue inDatabase:]_block_invoke FMDatabaseQueue.m:150 21 libdispatch.dylib _dispatch_client_callout 22 libdispatch.dylib _dispatch_barrier_sync_f_invoke 23 MYAPP -[FMDatabaseQueue inDatabase:] FMDatabaseQueue.m:147 24 MYAPP -[MYAPPAppDelegate loadPreferences] MYAPPAppDelegate.m:321 25 MYAPP -[MYAPPAppDelegate application:didFinishLaunchingWithOptions:] MYAPPAppDelegate.m:157 26 UIKit -[UIApplication _handleDelegateCallbacksWithOptions:isSuspended:restoreState:] 27 UIKit -[UIApplication _callInitializationDelegatesForMainScene:transitionContext:] 28 UIKit -[UIApplication _runWithMainScene:transitionContext:completion:] 29 UIKit -[UIApplication workspaceDidEndTransaction:] 30 FrontBoardServices __31-[FBSSerialQueue performAsync:]_block_invoke 31 CoreFoundation __CFRUNLOOP_IS_CALLING_OUT_TO_A_BLOCK__ 32 CoreFoundation __CFRunLoopDoBlocks 33 CoreFoundation __CFRunLoopRun 34 CoreFoundation CFRunLoopRunSpecific 35 UIKit -[UIApplication _run] 36 UIKit UIApplicationMain 37 MYAPP main main.m:16 38 libdyld.dylib start Does anybody have an idea how to debug something like this? Any help would be highly appreciated. Best regards Matthias Schmitt magic moving pixel s.a. 23, Avenue Grande-Duchesse Charlotte L-3441 Dudelange Luxembourg Phone: +352 54 75 75 http://www.mmp.lu
[sqlite] UPDATE Problem
Thanks to all for explaining my confusion. The printf solution seems like the best way to handle this since I don't need to worry about how many decimal places are in the number.
[sqlite] Memory leak?
On 12 Mar 2015, at 4:21pm, Matthias Schmitt wrote: > - The memory leak appears in the iPhone simulator more often, than on an > original iPhone device, but it appears always. > > - The leak appears with totally different type of queries. It might be, that > a leak appears in a specific query in one debug session and in another debug > session, it doesn?t (which makes it difficult to debug and drives me totally > nuts). For me it seems to be related to a timing/threading issue. This is why > it makes not much sense to offer some code here. > > - When debugging inside sqlite the leak is always related to the same code > line, independent from the type of database command (select, insert, delete). Is it, by any chance, always the first SQL command executed after doing sqlite3_open() ? > - It appears always with the memory allocation in sqlite.c line 16872 in > function sqlite3MemMalloc > Here is the line: void *p = SQLITE_MALLOC( nByte ); > > - The leaked memory has always a size of nByte = 4344 bytes. Is there any chance you can look at this memory and see what appears there ? Do the extra 4344 bytes get included in the number shown by "sqlite3_memory_used()" ? You could try using that function after sqlite_close() and see what it shows. Is the memory released at any later time ? When you use _close() on the connection, for example ? Or, if you can track it, when SQLite calls "sqlite3_db_release_memory()" internally ? Simon.
[sqlite] PRAGMA Synchronous safety
I dump the output to the log file so when a user sends me a log after the diagnosis reported a ?repaired? damage, I see one or more entries like: ?row 2481 missing from index idx_settings_sndidmnun? I will see if I can collect more log files in the coming months.
[sqlite] PRAGMA Synchronous safety
On 12 Mar 2015, at 2:51pm, Mario M. Westphal wrote: > I dump the output to the log file so when a user sends me a log after the > diagnosis reported a ?repaired? damage, I see one or more entries like: > > ?row 2481 missing from index idx_settings_sndidmnun? > > I will see if I can collect more log files in the coming months. They will not help much. We do need to see what is wrong with the database file, and they will tell us that. But we also need to know the circumstances the corruption occurred in. A big part of that is which command caused the corruption. So we need to spot the corruption immediately it occurs, not three or four commands later. In an earlier post you stated that corruption occurs even in your little configuration database. This should be small enough that you can run "PRAGMA integrity_check" on it whenever you want. So would it be possible to run that command each time you open the config database and after any change to it ? That would give us a perfect way to find out which commands were causing your problems. Simon.
[sqlite] PRAGMA Synchronous safety
One thing to add: I was sometimes successful to remote-repair a corrupted database by telling the user how to use sqlite3.exe and calling REINDEX. After learning that, I added this to my diagnosis routine so if integrity_check() returns something that?s not ?ok?, my application runs a REINDEX on the database and then runs integrity_check again. These cases are now automatically repaired and usually go unnoticed by the user. I tried to find a reproducible case where indices get broken or out of sync with the pages, but was unable to.
[sqlite] Memory leak?
On 3/12/15, Matthias Schmitt wrote: > Hello, > > I know, I am touching a hot iron, when claiming that sqlite might have a > memory leak. But I am trying to debug memory leaks related to sqlite now > since 4 days and I am running against walls. > Here is my problem: > > - I am writing an iPhone app using iOS 8.2, Xcode 4.2, sqlite 3.8.8.3. > > - The memory leak appears in the iPhone simulator more often, than on an > original iPhone device, but it appears always. Let's start with the basics: How do you know that the memory was in fact leaked and is not instead simply being held for reuse? > > - The leak appears with totally different type of queries. It might be, that > a leak appears in a specific query in one debug session and in another debug > session, it doesn?t (which makes it difficult to debug and drives me totally > nuts). For me it seems to be related to a timing/threading issue. This is > why it makes not much sense to offer some code here. > > - When debugging inside sqlite the leak is always related to the same code > line, independent from the type of database command (select, insert, > delete). > > - It appears always with the memory allocation in sqlite.c line 16872 in > function sqlite3MemMalloc > Here is the line: void *p = SQLITE_MALLOC( nByte ); > > - The leaked memory has always a size of nByte = 4344 bytes. > > - Here is a typical call stack up to the leaked code: > > 0 libsystem_malloc.dylib malloc_zone_malloc > 1 MYAPP sqlite3MemMalloc sqlite3.c:16872 > 2 MYAPP mallocWithAlarm sqlite3.c:20510 > 3 MYAPP sqlite3Malloc sqlite3.c:20541 > 4 MYAPP pcache1Alloc sqlite3.c:39749 > 5 MYAPP pcache1AllocPage sqlite3.c:39833 > 6 MYAPP pcache1FetchStage2 sqlite3.c:40266 > 7 MYAPP pcache1Fetch sqlite3.c:40367 > 8 MYAPP sqlite3PcacheFetch sqlite3.c:39119 > 9 MYAPP sqlite3PagerAcquire sqlite3.c:46573 > 10 MYAPP btreeGetPage sqlite3.c:54380 > 11 MYAPP getAndInitPage sqlite3.c:54435 > 12 MYAPP moveToRoot sqlite3.c:57233 > 13 MYAPP sqlite3BtreeMovetoUnpacked sqlite3.c:57465 > 14 MYAPP sqlite3VdbeExec sqlite3.c:74055 > 15 MYAPP sqlite3Step sqlite3.c:68839 > 16 MYAPP sqlite3_step sqlite3.c:68905 > 17 MYAPP -[FMResultSet nextWithError:] FMResultSet.m:155 > 18 MYAPP -[FMResultSet next] FMResultSet.m:150 > 19 MYAPP __40-[MYAPPAppDelegate loadPreferences]_block_invoke > MYAPPAppDelegate.m:323 > 20 MYAPP __30-[FMDatabaseQueue inDatabase:]_block_invoke > FMDatabaseQueue.m:150 > 21 libdispatch.dylib _dispatch_client_callout > 22 libdispatch.dylib _dispatch_barrier_sync_f_invoke > 23 MYAPP -[FMDatabaseQueue inDatabase:] FMDatabaseQueue.m:147 > 24 MYAPP -[MYAPPAppDelegate loadPreferences] MYAPPAppDelegate.m:321 > 25 MYAPP -[MYAPPAppDelegate application:didFinishLaunchingWithOptions:] > MYAPPAppDelegate.m:157 > 26 UIKit -[UIApplication > _handleDelegateCallbacksWithOptions:isSuspended:restoreState:] > 27 UIKit -[UIApplication > _callInitializationDelegatesForMainScene:transitionContext:] > 28 UIKit -[UIApplication _runWithMainScene:transitionContext:completion:] > 29 UIKit -[UIApplication workspaceDidEndTransaction:] > 30 FrontBoardServices __31-[FBSSerialQueue performAsync:]_block_invoke > 31 CoreFoundation __CFRUNLOOP_IS_CALLING_OUT_TO_A_BLOCK__ > 32 CoreFoundation __CFRunLoopDoBlocks > 33 CoreFoundation __CFRunLoopRun > 34 CoreFoundation CFRunLoopRunSpecific > 35 UIKit -[UIApplication _run] > 36 UIKit UIApplicationMain > 37 MYAPP main main.m:16 > 38 libdyld.dylib start > > Does anybody have an idea how to debug something like this? Any help would > be highly appreciated. > > Best regards > > Matthias Schmitt > > magic moving pixel s.a. > 23, Avenue Grande-Duchesse Charlotte > L-3441 Dudelange > Luxembourg > Phone: +352 54 75 75 > http://www.mmp.lu > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Weird (slow) TEMP B-TREE ORDER BY
Hi Richard, Like said, we've already tried: CREATE TEMP TABLE x AS (...query without outer sort...); CREATE xind ON x (price); SELECT * FROM x ORDER BY 2 but it also takes 30 seconds; for some really esoteric reason, CREATE TABLE x AS ... takes all of those 30s, even though the execution plan looks identical to the simple select (the same 72 steps). This is what I would dare call "spooky", any explanation would be appreciated here too. Thanks
[sqlite] Releasing a read (SHARED) lock
On 2015-03-12 04:38 AM, Barry wrote: > Hello everybody, > > I have a situation where two processes are accessing the same SQLite > database. One process only reads from the database, one process reads and > writes. > > These processes keep a single database connection open for the lifetime of > the process. > > It seems to me that once the reader process accesses the database (after it > performs its first SELECT statement), it maintains a lock on the database > until the connection is closed (when the program is exited). This prevents > the writer process from updating the database. Hi Barry, This is the usual situation when one of your transactions in the "reading only" database does not finalize. i.e. you started a transaction there and did not end it with either "END TRANSACTION" or "COMMIT" or "ROLLBACK". In the non-WAL DB it will simply hold the lock preventing changes. In the WAL DB it will hold the lock for its own view of the data but let the other writer write.. however it won't see the changes for itself. This is very common actually. Just find every transaction you start (explicitly or implicitly) and make sure you end it and when you end it, see what the return value is from SQLite and whether it reports any error. HTH! Ryan > > I tried changing to WAL. This made the writer process able to commit its > changes, but now the reader does not see any modifications made to the > database until it is restarted (It seems to see a snapshot of the DB at the > time of its first read). > > I am using prepared statements: On opening the DB, I create all my prepared > statements. When I need to execute a statement, I bind to the statement, > call sqlite3_step (possibly multiple times), then call sqlite3_reset. I do > not finalise the statements until the program closes. > > In order to simulate 'save' behaviour, the writer process always holds a > transaction open. When the user chooses 'save', the current transaction is > committed and a new transaction is begun. (I understand that the reader > will not see any changes in the uncommitted transaction, but is not seeing > any committed transactions either). > > I have checked quite thoroughly through my code and cannot find any > instances of statements executed without a sqlite3_reset quickly following. > > Is this intended behaviour - that once a connection has performed a read, > it maintains its lock on the database for its lifetime? Is SQLite smart > enough to know that the pages it holds in cache of the reader are invalid > after the writer has made changes to the DB on disk? > > If this is not the intended behaviour - is there a way I can find out which > statements are causing the lock to be held open? Or can I force SQLite to > discard its cache? > > Any help would be appreciated. > > Regards, > > Barry Smith > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Releasing a read (SHARED) lock
Hello everybody, I have a situation where two processes are accessing the same SQLite database. One process only reads from the database, one process reads and writes. These processes keep a single database connection open for the lifetime of the process. It seems to me that once the reader process accesses the database (after it performs its first SELECT statement), it maintains a lock on the database until the connection is closed (when the program is exited). This prevents the writer process from updating the database. I tried changing to WAL. This made the writer process able to commit its changes, but now the reader does not see any modifications made to the database until it is restarted (It seems to see a snapshot of the DB at the time of its first read). I am using prepared statements: On opening the DB, I create all my prepared statements. When I need to execute a statement, I bind to the statement, call sqlite3_step (possibly multiple times), then call sqlite3_reset. I do not finalise the statements until the program closes. In order to simulate 'save' behaviour, the writer process always holds a transaction open. When the user chooses 'save', the current transaction is committed and a new transaction is begun. (I understand that the reader will not see any changes in the uncommitted transaction, but is not seeing any committed transactions either). I have checked quite thoroughly through my code and cannot find any instances of statements executed without a sqlite3_reset quickly following. Is this intended behaviour - that once a connection has performed a read, it maintains its lock on the database for its lifetime? Is SQLite smart enough to know that the pages it holds in cache of the reader are invalid after the writer has made changes to the DB on disk? If this is not the intended behaviour - is there a way I can find out which statements are causing the lock to be held open? Or can I force SQLite to discard its cache? Any help would be appreciated. Regards, Barry Smith
[sqlite] C++ ORM
On Mon, Mar 9, 2015 at 10:38 PM, Scott Robison wrote: > A co-worker who is working on a project is interested in finding out if > there is an effective ORM for C++ / SQLite. I've not used one so I'm > turning to the list to see if anyone has a recommendation. > > Note: He's okay using SQLite more or less directly if he needs to, as he > recognizes the lack of reflection in C++ might lead to a less manageable > ORM. Still, never hurts to ask. > I've been using SQLite from my C++ project with the sqlite3pp library. It is not an ORM and while it works OK for my use case, the transaction managements objects needs some work. I have only one transacion in my application so this is not an issue for me. https://github.com/iwongu/sqlite3pp -- Alejandro Santos
[sqlite] PRAGMA Synchronous safety
I have enabled the error callback and it logs everything except SQLITE_SCHEMA and SQLITE_OK. In the log files I have received so far, none of these were reported (I use special headers to identify SQLite errors because log files are often several hundred MB in size). I?m waiting for a log file which actually containers some SQLite error info before the disk image corrupted error is logged. I?ve asked the users who had the problem once to run the diagnostics more often so we catch problems faster. But often it?s just a ?Worked yesterday, today it does not start?. And then I get the log file and it shows ?disk image malformed? right after the database open procedure. It?s really hard to get more info about this, but I?ll try.
[sqlite] PRAGMA Synchronous safety
On 3/12/15, Mario M. Westphal wrote: > One thing to add: > > > > I was sometimes successful to remote-repair a corrupted database by telling > the user how to use sqlite3.exe and calling REINDEX. > > > > After learning that, I added this to my diagnosis routine so if > integrity_check() returns something that?s not ?ok?, my application runs a > REINDEX on the database and then runs integrity_check again. These cases are > now automatically repaired and usually go unnoticed by the user. I tried to > find a reproducible case where indices get broken or out of sync with the > pages, but was unable to. > I'm sure your users appreciate the automatic fixes. So that's probably the right thing to do. Nevertheless, this is really just masking a much deeper problem that needs to be rooted out. Can you change your application so that when an automatic repair does occur, it saves off a copy of the "PRAGMA integrity_check" output as well as other diagnostic information into a file, then asks the user to email that file to you? -- D. Richard Hipp drh at sqlite.org
[sqlite] PRAGMA Synchronous safety
> Why are you using shared cache, especially with WAL? Are your devices memory > constrained? I was under the impression that shared cache has performance benefits when used in a MT environment.
[sqlite] PRAGMA Synchronous safety
> If those databases are small then running "PRAGMA integrity_check" on them > should be very fast. My application does that. This is how users find out that their settings database has become corrupted. So far it is just reported and then the config database is restored from last working backup. I did not anticipate that this small database will ever become damaged, except in the case of blue-screens or power failures. For this a simple ?Config database is damaged, restoring from last backup?? message to the user seemed sufficient. No detailed logging is implemented at this time.
[sqlite] Libstringmetrics
oops,
sorry for wrong typo.
"Hi Milan," is the correct.
:)
Again,
A.
2015-03-12 9:08 GMT+01:00 Andrea Peri :
> Hi Lina,
>
> I tested your patch and it resolve the crash.
>
> I submit the patch in the master and generate a new dll for windows.
>
> Thx for patching.
>
> A.
>
>
> 2015-03-12 8:48 GMT+01:00 Andrea Peri :
>> Of course.
>> Thx for report and patch.
>>
>> However I will contact the original author of the simmetrics library
>> https://github.com/jokillsya/libsimmetrics
>> to report him this error and the patch.
>>
>> A.
>>
>>
>> 2015-03-12 2:12 GMT+01:00 Milan Roubal :
>>> Thank you very much for all feedback! the last example crashed also so I
>>> have tried with try and error to trace it down into the library and it looks
>>> like the problem are this 2 lines in file
>>> src/libsimmetrics/simmetrics/tokenizer.c
>>>
>>>tmp = calloc((init_len + qtype->qgram_len), sizeof(char));
>>>
>>> probably both lines should be changed to
>>>
>>> tmp = calloc((init_len + 2 * qtype->qgram_len), sizeof(char));
>>>
>>> @Andrea: can you verify and include that in library?
>>>
>>> However you last SQL example show interesting thing: calling 2 stringmetrics
>>> in one query result in values 100 and 36 in one order and 40 and 100 in
>>> opposite order. This is also not good :(
>>>
>>> sqlite> .load ./libstringmetrics.so
>>> select a.firstname, b.firstname, a.lastname, b.lastname,
>>> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
>>> first_dist,
>>> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
>>> last_dist
>>> from
>>> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
>>> ;
>>> sqlite>...>...>...>...>...>...>
>>> Milan|Milan|Roubal|RoubalRoubalRoubalRo|100.0|36.679382324
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
>>> last_dist,
>>> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
>>> first_dist
>>> from
>>> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
>>> ;
>>>...>...>...>...>...>...>
>>> Milan|Milan|Roubal|RoubalRoubalRoubalRo|40.0|100.0
>>>
>>>Thank you
>>>Best Regards
>>>Milan
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>>
>>
>> --
>> -
>> Andrea Peri
>> . . . . . . . . .
>> qwerty ?
>> -
>
>
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty ?
> -
--
-
Andrea Peri
. . . . . . . . .
qwerty ?
-
[sqlite] Libstringmetrics
Hi Lina,
I tested your patch and it resolve the crash.
I submit the patch in the master and generate a new dll for windows.
Thx for patching.
A.
2015-03-12 8:48 GMT+01:00 Andrea Peri :
> Of course.
> Thx for report and patch.
>
> However I will contact the original author of the simmetrics library
> https://github.com/jokillsya/libsimmetrics
> to report him this error and the patch.
>
> A.
>
>
> 2015-03-12 2:12 GMT+01:00 Milan Roubal :
>> Thank you very much for all feedback! the last example crashed also so I
>> have tried with try and error to trace it down into the library and it looks
>> like the problem are this 2 lines in file
>> src/libsimmetrics/simmetrics/tokenizer.c
>>
>>tmp = calloc((init_len + qtype->qgram_len), sizeof(char));
>>
>> probably both lines should be changed to
>>
>> tmp = calloc((init_len + 2 * qtype->qgram_len), sizeof(char));
>>
>> @Andrea: can you verify and include that in library?
>>
>> However you last SQL example show interesting thing: calling 2 stringmetrics
>> in one query result in values 100 and 36 in one order and 40 and 100 in
>> opposite order. This is also not good :(
>>
>> sqlite> .load ./libstringmetrics.so
>> select a.firstname, b.firstname, a.lastname, b.lastname,
>> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
>> first_dist,
>> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
>> last_dist
>> from
>> (select "Milan" as firstname, "Roubal" as lastname ) a,
>> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
>> ;
>> sqlite>...>...>...>...>...>...>
>> Milan|Milan|Roubal|RoubalRoubalRoubalRo|100.0|36.679382324
>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
>> last_dist,
>> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
>> first_dist
>> from
>> (select "Milan" as firstname, "Roubal" as lastname ) a,
>> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
>> ;
>>...>...>...>...>...>...>
>> Milan|Milan|Roubal|RoubalRoubalRoubalRo|40.0|100.0
>>
>>Thank you
>>Best Regards
>>Milan
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty ?
> -
--
-
Andrea Peri
. . . . . . . . .
qwerty ?
-
[sqlite] Libstringmetrics
Of course.
Thx for report and patch.
However I will contact the original author of the simmetrics library
https://github.com/jokillsya/libsimmetrics
to report him this error and the patch.
A.
2015-03-12 2:12 GMT+01:00 Milan Roubal :
> Thank you very much for all feedback! the last example crashed also so I
> have tried with try and error to trace it down into the library and it looks
> like the problem are this 2 lines in file
> src/libsimmetrics/simmetrics/tokenizer.c
>
>tmp = calloc((init_len + qtype->qgram_len), sizeof(char));
>
> probably both lines should be changed to
>
> tmp = calloc((init_len + 2 * qtype->qgram_len), sizeof(char));
>
> @Andrea: can you verify and include that in library?
>
> However you last SQL example show interesting thing: calling 2 stringmetrics
> in one query result in values 100 and 36 in one order and 40 and 100 in
> opposite order. This is also not good :(
>
> sqlite> .load ./libstringmetrics.so
> select a.firstname, b.firstname, a.lastname, b.lastname,
> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
> first_dist,
> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
> last_dist
> from
> (select "Milan" as firstname, "Roubal" as lastname ) a,
> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
> ;
> sqlite>...>...>...>...>...>...>
> Milan|Milan|Roubal|RoubalRoubalRoubalRo|100.0|36.679382324
> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
> last_dist,
> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
> first_dist
> from
> (select "Milan" as firstname, "Roubal" as lastname ) a,
> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
> ;
>...>...>...>...>...>...>
> Milan|Milan|Roubal|RoubalRoubalRoubalRo|40.0|100.0
>
>Thank you
>Best Regards
>Milan
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
-
Andrea Peri
. . . . . . . . .
qwerty ?
-
[sqlite] Libstringmetrics
Thank you very much for all feedback! the last example crashed also so I
have tried with try and error to trace it down into the library and it
looks like the problem are this 2 lines in file
src/libsimmetrics/simmetrics/tokenizer.c
tmp = calloc((init_len + qtype->qgram_len),
sizeof(char));
probably both lines should be changed to
tmp = calloc((init_len + 2 * qtype->qgram_len),
sizeof(char));
@Andrea: can you verify and include that in library?
However you last SQL example show interesting thing: calling 2
stringmetrics in one query result in values 100 and 36 in one order and
40 and 100 in opposite order. This is also not good :(
sqlite> .load ./libstringmetrics.so
select a.firstname, b.firstname, a.lastname, b.lastname,
stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist,
stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
last_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
;
sqlite>...>...>...>...>...>...>
Milan|Milan|Roubal|RoubalRoubalRoubalRo|100.0|36.679382324
sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
last_dist,
stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
;
...>...>...>...>...>...>
Milan|Milan|Roubal|RoubalRoubalRoubalRo|40.0|100.0
Thank you
Best Regards
Milan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE Problem
On 2015-03-12 01:27 AM, Peter Haworth wrote:
> I have a table, Transactions, with a column , Value, of type NUMERIC. The
> Value column is supposed to have 2 decimal places in all rows but some have
> only one.
SQLite has no formatting inherent to the column value, there is no such
thing as "supposed to have 2 zeroes" - who supposes this?
The only way you can rightfully expect a column value to be exactly a
certain length of zeroes (before or after the decimal point) is if the
column is typed as TEXT - which is what happens when you issue:
UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
i.e those values might no longer be stored as NUMERIC values but now are
strings and this might be why your next SELECT seemed to work. (The
interface of choice might still elect to show them via a numeric
interpretation).
Further to this, it is often the interface used which decides how to
represent number values where you do not explicitly define the format to
use.
To force a format, you need to specify it when querying. Example:
WITH TX(x) AS (
SELECT 10.1
UNION ALL
SELECT x+0.1 FROM TX WHERE x<20
)
SELECT printf('%0.2f',x), printf('%10.4f',x) FROM TX;
>
> To correct this I issued the following command:
>
> UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
>
> No errors on execution but nothing was changed.
>
> To help me figure out why that didn't work, I issued the following command:
>
> SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'
>
> This time, all the values with only 1 decimal place were listed with a
> trailing zero added.
>
> On the face of it, the WHERE statement works fine in a SELECT statement but
> does not find any rows in an UPDATE statement. Either that or the SET is
> not calculating the correct value.
>
> Is this a bug or am I missing something?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE Problem
Hi Peter, From https://www.sqlite.org/datatype3.html: "When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible"; basically any fraction you insert into a NUMERIC column with at most 15 digits will be converted do REAL (float). What you are looking for is DECIMAL data type, which is not supported by sqlite, and NUMERIC is not supposed to be a replacement, NUMERIC is only useful as an extension beyond the range of INTEGER or FLOAT for huge numbers or extreme precision, in which case it will behave as TEXT and it's the responsibility of the client app to convert back to some numeric representation. Do note however, that if you're dealing with only positive fractions, you can safely use TEXT to emulate DECIMAL, as long as all strings are the same length and right-aligned: " 100.50" < "999.00" On 12.03.2015 01:27, Peter Haworth wrote: > I have a table, Transactions, with a column , Value, of type NUMERIC. The > Value column is supposed to have 2 decimal places in all rows but some have > only one. > > To correct this I issued the following command: > > UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.' > > No errors on execution but nothing was changed. > > To help me figure out why that didn't work, I issued the following command: > > SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.' > > This time, all the values with only 1 decimal place were listed with a > trailing zero added. > > On the face of it, the WHERE statement works fine in a SELECT statement but > does not find any rows in an UPDATE statement. Either that or the SET is > not calculating the correct value. > > Is this a bug or am I missing something? > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Libstringmetrics
On 2015-03-12 00:33, Simon Slavin wrote:
>> On 11 Mar 2015, at 10:54pm, Milan Roubal wrote:
>>
>> .load ./libstringmetrics.so
>> select a.firstname, b.firstname, a.lastname, b.lastname,
>> stringmetrics("qgrams_distance","similarity",a.firstname,
>> b.firstname,"") first_dist,
>> stringmetrics("qgrams_distance","similarity",a.lastname,
>> b.lastname,"") last_dist
>> from
>> (select "Milan" as firstname, "Roubal" as lastname ) a,
>> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
>
> Do any of these crash ?
>
> SELECT stringmetrics("qgrams_distance","similarity","Milan",
> "Milan","");
>
pass
>
> SELECT stringmetrics("qgrams_distance","similarity","Roubal",
> "RoubalRoubalRoubalRo","");
>
crash
> select stringmetrics("qgrams_distance","similarity",a.firstname,
> b.firstname,"") first_dist
> from
> (select "Milan" as firstname, "Roubal" as lastname ) a,
> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b;
>
pass
>
> select stringmetrics("qgrams_distance","similarity",a.lastname,
> b.lastname,"") last_dist
> from
> (select "Milan" as firstname, "Roubal" as lastname ) a,
> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b;
>
crash
Thank you
Milan
[sqlite] Libstringmetrics
> On 11 Mar 2015, at 11:50pm, Milan Roubal wrote:
>
>> SELECT stringmetrics("qgrams_distance","similarity","Roubal",
>> "RoubalRoubalRoubalRo","");
> crash
Then that's the one to use for debugging. It is extremely simple and contains
no database access at all so now you know the problem has nothing to do with
your table setup or your data.
I suspect there's a bug in the stringmetrics library and you should be able to
approach the authors of it with that very simple way to reproduce your crash.
Erm ... it just occurred to me that SQLite uses single quotes for strings, not
double quotes. Do you get the same crash if you do
SELECT stringmetrics('qgrams_distance','similarity','Roubal',
'RoubalRoubalRoubalRo','');
? If so, the problem is in the library. If not, try using single quotes in
your original sourcecode.
Simon.
[sqlite] Libstringmetrics
Hi Milan,
thx for your patch.
SQLITE_STATIC --> SQLITE_TRANSIENT
I try to apply it for testing:
but seem nothing change .
However I leave it in the code to allow you to test.
I submit also a new dll.
Regards,
Andrea.
2015-03-11 16:07 GMT+01:00 Milan Roubal :
> Dear Andrea,
> thank you for the answer. The only idea I have so far is the line 452 in
> file wrapper_functions.c
> sqlite3_result_text(context, metrics, strlen(metrics)+1, SQLITE_STATIC);
> I would change that to
> sqlite3_result_text(context, metrics, strlen(metrics)+1, SQLITE_TRANSIENT);
> but I don't have compiler to verify if this idea makes sense.
> Thank you
> Best Regards
> Milan
>
> On 2015-03-11 00:15, aperi2007 wrote:
>>
>> Hi Milan,
>>
>> thx for your report.
>>
>> I try to see a check, and effectively I notice the problem is in the
>> qgrams_distance when used the metrics option.
>>
>> As reported in the readme, the stringmetrics extension is based on the
>> https://github.com/jokillsya/libsimmetrics
>> library.
>> The code on the metrics is integrally take from the sample of that
>> library.
>>
>> I guess the problem you report is due to a static allocation (or
>> similar effect) that is not appropriated when in the same query the
>> same function is called more than one time.
>>
>> Actually I dont know if the problem is in the sqlite side of my code
>> or in the original code of the symmetrics library.
>>
>> I try to resolve this firstly question when and if I find a few of time.
>>
>> However patches are welcomes.
>> :)
>>
>> Regards,
>>
>> Andrea Peri.
>>
>> Il 06/03/2015 17:00, Milan Roubal ha scritto:
>>>
>>> Dear all,
>>> I have some problems with https://github.com/aperi2007/libstringmetrics .
>>> For example:
>>> When I use "similarity" in qgrams_distance, I get good results. But when
>>> I use "metric", it works only if it is only once in the query. When there
>>> are 2 different usages, they somehow interfere together. Is this the right
>>> place where to report such problem?
>>>
sqlite3
>>>
>>> SQLite version 3.8.8.3 2015-02-25 13:29:11
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>>
>>> sqlite> select load_extension("libstringmetrics.dll");
>>>
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>>...> stringmetrics("qgrams_distance","similarity",a.firstname,
>>> b.firstname,"") first_dist,
>>>...> stringmetrics("qgrams_distance","similarity",a.lastname,
>>> b.lastname,"") last_dist
>>>...> from
>>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>>...> ;
>>> Milan|Milan|Roubal|roubal|100.0|62.5
>>>
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>>...> stringmetrics("qgrams_distance","metric",a.firstname,
>>> b.firstname,"") first_dist,
>>>...> stringmetrics("qgrams_distance","metric",a.lastname,
>>> b.lastname,"") last_dist
>>>...> from
>>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>>...> ;
>>> Milan|Milan|Roubal|roubal|6|6
>>>
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>>...> stringmetrics("qgrams_distance","metric",a.firstname,
>>> b.firstname,"") first_dist
>>>...> from
>>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>>...> ;
>>> Milan|Milan|Roubal|roubal|0
>>>
>>> Thank you
>>> Best Regards
>>> Milan
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
-
Andrea Peri
. . . . . . . . .
qwerty ?
-
[sqlite] Libstringmetrics
So I was able to identify the record doing the problem. I have tried to
find what is so special on that record and it is size 20 characters.
When I have this 3 examples with last name lenght 19, 20 and 21
characters only the size 20 crash.
.load ./libstringmetrics.so
select a.firstname, b.firstname, a.lastname, b.lastname,
stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist,
stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
last_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalR" as lastname ) b
;
.load ./libstringmetrics.so
select a.firstname, b.firstname, a.lastname, b.lastname,
stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist,
stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
last_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
;
.load ./libstringmetrics.so
select a.firstname, b.firstname, a.lastname, b.lastname,
stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist,
stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
last_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalRou" as lastname ) b
;
I got also once from 30 crashes this trace, maybe it will help to find
the problem.
*** Error in `/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3':
free(): invalid next size (fast): 0x01b53cf0 ***
=== Backtrace: =
/lib64/libc.so.6(+0x73d8f)[0x7ffa14020d8f]
/lib64/libc.so.6(+0x795ee)[0x7ffa140265ee]
/lib64/libc.so.6(+0x7a2c7)[0x7ffa140272c7]
./libstringmetrics.so(qgram_tokenize_to_utarray+0x2c0)[0x7ffa13b6b5e0]
./libstringmetrics.so(qgrams_distance_similarity_custom+0x27)[0x7ffa13b69257]
./libstringmetrics.so(qgrams_distance_similarity+0x37)[0x7ffa13b693b7]
./libstringmetrics.so(stringmetricsFunc+0xcfb)[0x7ffa13b62b9b]
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/libsqlite3.so.0(+0x6dc74)[0x7ffa14a11c74]
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/libsqlite3.so.0(sqlite3_step+0x28f)[0x7ffa14a163ff]
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3[0x406ee1]
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3[0x407fe3]
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3[0x40337b]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7ffa13fcebe5]
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3[0x403b69]
=== Memory map:
0040-0041 r-xp 00:27 50993
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3
0060f000-0061 r--p f000 00:27 50993
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3
0061-00612000 rw-p 0001 00:27 50993
/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3
01b3f000-01df2000 rw-p 00:00 0
[heap]
7ffa13644000-7ffa1365a000 r-xp 00:14 1355
/lib64/libgcc_s.so.1
7ffa1365a000-7ffa13859000 ---p 00016000 00:14 1355
/lib64/libgcc_s.so.1
7ffa13859000-7ffa1385a000 r--p 00015000 00:14 1355
/lib64/libgcc_s.so.1
7ffa1385a000-7ffa1385b000 rw-p 00016000 00:14 1355
/lib64/libgcc_s.so.1
7ffa1385b000-7ffa1395d000 r-xp 00:14 572971
/lib64/libm-2.18.so
7ffa1395d000-7ffa13b5c000 ---p 00102000 00:14 572971
/lib64/libm-2.18.so
7ffa13b5c000-7ffa13b5d000 r--p 00101000 00:14 572971
/lib64/libm-2.18.so
7ffa13b5d000-7ffa13b5e000 rw-p 00102000 00:14 572971
/lib64/libm-2.18.so
7ffa13b5e000-7ffa13b6f000 r-xp 00:27 52106
/home/milan/sqlite/libstringmetrics.so
7ffa13b6f000-7ffa13d6f000 ---p 00011000 00:27 52106
/home/milan/sqlite/libstringmetrics.so
7ffa13d6f000-7ffa13d7 r--p 00011000 00:27 52106
/home/milan/sqlite/libstringmetrics.so
7ffa13d7-7ffa13d71000 rw-p 00012000 00:27 52106
/home/milan/sqlite/libstringmetrics.so
7ffa13d71000-7ffa13d79000 rw-p 00:00 0
7ffa13d79000-7ffa13da4000 r-xp 00:14 1348
/lib64/libtinfo.so.5.9
7ffa13da4000-7ffa13fa3000 ---p 0002b000 00:14 1348
/lib64/libtinfo.so.5.9
7ffa13fa3000-7ffa13fa7000 r--p 0002a000 00:14 1348
/lib64/libtinfo.so.5.9
7ffa13fa7000-7ffa13fac000 rw-p 0002e000 00:14 1348
/lib64/libtinfo.so.5.9
7ffa13fac000-7ffa13fad000 rw-p 00:00 0
7ffa13fad000-7ffa14152000 r-xp 00:14 572963
/lib64/libc-2.18.so
7ffa14152000-7ffa14351000 ---p 001a5000 00:14 572963
/lib64/libc-2.18.so
7ffa14351000-7ffa14355000 r--p
[sqlite] Libstringmetrics
> On 11 Mar 2015, at 10:54pm, Milan Roubal wrote:
>
> .load ./libstringmetrics.so
> select a.firstname, b.firstname, a.lastname, b.lastname,
> stringmetrics("qgrams_distance","similarity",a.firstname, b.firstname,"")
> first_dist,
> stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
> last_dist
> from
> (select "Milan" as firstname, "Roubal" as lastname ) a,
> (select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b
Do any of these crash ?
SELECT stringmetrics("qgrams_distance","similarity","Milan", "Milan","");
SELECT stringmetrics("qgrams_distance","similarity","Roubal",
"RoubalRoubalRoubalRo","");
select stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b;
select stringmetrics("qgrams_distance","similarity",a.lastname, b.lastname,"")
last_dist
from
(select "Milan" as firstname, "Roubal" as lastname ) a,
(select "Milan" as firstname, "RoubalRoubalRoubalRo" as lastname ) b;
?
[sqlite] UPDATE Problem
I have a table, Transactions, with a column , Value, of type NUMERIC. The Value column is supposed to have 2 decimal places in all rows but some have only one. To correct this I issued the following command: UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.' No errors on execution but nothing was changed. To help me figure out why that didn't work, I issued the following command: SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.' This time, all the values with only 1 decimal place were listed with a trailing zero added. On the face of it, the WHERE statement works fine in a SELECT statement but does not find any rows in an UPDATE statement. Either that or the SET is not calculating the correct value. Is this a bug or am I missing something?
[sqlite] Libstringmetrics
Your SQL pass, so it is somewhere in the library. The problem is how to
find in the big data where exactly. I am now going one character by each
other, because it is probably only one specific record kind doing that.
I was able to compile the library under linux and running strace looks
like this:
lseek(3, 9742336, SEEK_SET) = 9742336
read(3,
"\r\0\0\0.\0x\0\3\357\3\333\3\313\3\273\3\250\3\223\3~\3k\3Y\3G\0033\3\31"...,
1024) = 1024
lseek(3, 9743360, SEEK_SET) = 9743360
read(3,
"\r\0\0\0)\0~\0\3\354\3\330\3\304\3\260\3\234\3\210\3t\3`\3L\0038\3$\3\20"...,
1024) = 1024
open("/dev/tty", O_RDWR|O_NOCTTY|O_NONBLOCK) = 4
writev(4, [{"*** Error in `", 14},
{"/home/milan/sqlite/sqlite-autoco"..., 56}, {"': ", 3}, {"free():
invalid next size (fast)", 32}, {": 0x", 4}, {"01eadc20", 16},
{" ***\n", 5}], 7*** Error in
`/home/milan/sqlite/sqlite-autoconf-3080803/.libs/sqlite3': free():
invalid next size (fast): 0x01eadc20 ***
) = 130
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)
= 0x7fe78ca59000
any hint how to fast find which record is doing that crash or even fix
for such crash is welcome :)
Milan
On 2015-03-11 19:30, Simon Slavin wrote:
>> On 11 Mar 2015, at 4:56pm, Milan Roubal wrote:
>>
>> I am also facing problem with sqlite closed by windows because of
>> crash. On same data this query crash:
>>
>> select load_extension("libstringmetrics.dll");
>> select a.vorname, b.vorname, a.nachname, b.nachname,
>> stringmetrics("qgrams_distance","similarity",a.nachname, b.nachname,
>> "") nach_dist
>> from r2 a, allUsers b
>> where
>> a.vorname like 'r%' and b.vorname like 'r%' and
>> round(nach_dist) between 50 and 100
>> ;
>>
>> The first query is able to pass on some small sample, but not on all
>> my data. I am trying to find what concrete string comparison is
>> crashing it, but it is slow with crashes. Is there any way how to
>> start logging the calls of the library to find the exact data that are
>> causing the crash?
>
> Try this command
>
> select a.vorname, b.vorname, a.nachname, b.nachname,
> 55 nach_dist
> from r2 a, allUsers b
> where
> a.vorname like 'r%' and b.vorname like 'r%' and
> round(nach_dist) between 50 and 100
>
> If it does not crash then the problem is in your stringmetrics library.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
