[sqlite] Segmentation fault in 3.22 eval.c under PRAGMA empty_result_callbacks=1

[petern]

sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4

sqlite> .load eval.so
sqlite> PRAGMA empty_result_callbacks=1;
sqlite> SELECT eval('SELECT 1 WHERE 0');
... Segmentation fault

If a new protection line 35:"if(!argv) return 0;" or equivalent is being
added to 'eval.c', please also consider the enhancement of implementing an
optional 3rd argument eg. eval(X,Y,Z) where Z is the row separator.
eval(X[,Y[,Z]]) with  both optional Y column separator and optional Z row
separator will make eval() more general and useful in all situations.

Thanks.
Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Static sqlite3 library for Linux

[petern]

Petros, FYI.  gcc also has several different switches for object ouput:
eg. "gcc -c -static  ..."

It might help to investigate these options during steps to compile sqlite.c
and your main program to avoid dynamic/static symbol conflicts.

   -static
   On systems that support dynamic linking, this prevents linking
with the shared libraries.  On other systems, this option has no effect.

   -shared
   Produce a shared object which can then be linked with other
objects to form an executable.  Not all systems support this option.  For
predictable results,
   you must also specify the same set of options used for
compilation (-fpic, -fPIC, or model suboptions) when you specify this
linker option.[1]

   -shared-libgcc
   -static-libgcc
   On systems that provide libgcc as a shared library, these
options force the use of either the shared or static version,
respectively.  If no shared version
   of libgcc was built when the compiler was configured, these
options have no effect.

   There are several situations in which an application should use
the shared libgcc instead of the static version.  The most common of these
is when the
   application wishes to throw and catch exceptions across
different shared libraries.  In that case, each of the libraries as well as
the application itself
   should use the shared libgcc.

   Therefore, the G++ and GCJ drivers automatically add
-shared-libgcc whenever you build a shared library or a main executable,
because C++ and Java programs
   typically use exceptions, so this is the right thing to do.

--
I have a side question for you, if you can answer.
How did Omilia implement their speaker independent speech recognition
corpus?  Was it licensed from elsewhere or developed in house?

Peter



On Wed, Feb 14, 2018 at 8:27 AM, Petros Marinos  wrote:

> Thank you Arjen and Simon for your answers, really helpful!
>
> While there was progress by following the two commands noted in Arjen’s
> answer and creating the libsqlite.a file, I stumbled upon the following
> errors:
>
> [LD] astdb2sqlite3.o db1-ast/libdb1.a -> astdb2sqlite3
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_sigaction':
> (.text+0x89f0): multiple definition of `__libc_sigaction'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/
> libc.a(sigaction.o):(.text+0x20): first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_fsync':
> (.text+0x8100): multiple definition of `__libc_fsync'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fsync.o):(.text+0x0):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__connect_nocancel':
> (.text+0x7dc9): multiple definition of `__connect_nocancel'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(connect.o):(.text+0x9):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_fcntl':
> (.text+0x7c40): multiple definition of `__libc_fcntl'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fcntl.o):(.text+0xa0):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__fsync_nocancel':
> (.text+0x8109): multiple definition of `__fsync_nocancel'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fsync.o):(.text+0x9):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `_IO_funlockfile':
> (.text+0x8990): multiple definition of `_IO_funlockfile'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/
> libc.a(funlockfile.o):(.text+0x0): first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_nanosleep':
> (.text+0x8220): multiple definition of `__libc_nanosleep'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/
> libc.a(nanosleep.o):(.text+0x0): first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__read':
> (.text+0x7ae0): multiple definition of `__libc_read'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(read.o):(.text+0x0):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__open_nocancel':
> (.text+0x8289): multiple definition of `__open_nocancel'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(open.o):(.text+0x9):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__lseek_nocancel':
> (.text+0x8169): multiple definition of 

Re: [sqlite] Static sqlite3 library for Linux

[Petros Marinos]

Thank you Arjen and Simon for your answers, really helpful!

While there was progress by following the two commands noted in Arjen’s answer 
and creating the libsqlite.a file, I stumbled upon the following errors:

[LD] astdb2sqlite3.o db1-ast/libdb1.a -> astdb2sqlite3
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__libc_sigaction':
(.text+0x89f0): multiple definition of `__libc_sigaction'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(sigaction.o):(.text+0x20):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__libc_fsync':
(.text+0x8100): multiple definition of `__libc_fsync'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fsync.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__connect_nocancel':
(.text+0x7dc9): multiple definition of `__connect_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(connect.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__libc_fcntl':
(.text+0x7c40): multiple definition of `__libc_fcntl'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fcntl.o):(.text+0xa0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__fsync_nocancel':
(.text+0x8109): multiple definition of `__fsync_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fsync.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `_IO_funlockfile':
(.text+0x8990): multiple definition of `_IO_funlockfile'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(funlockfile.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__libc_nanosleep':
(.text+0x8220): multiple definition of `__libc_nanosleep'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(nanosleep.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__read':
(.text+0x7ae0): multiple definition of `__libc_read'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(read.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__open_nocancel':
(.text+0x8289): multiple definition of `__open_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(open.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__lseek_nocancel':
(.text+0x8169): multiple definition of `__lseek_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(llseek.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__read_nocancel':
(.text+0x7ae9): multiple definition of `__read_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(read.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `send':
(.text+0x7f90): multiple definition of `__libc_send'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(send.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__libc_close':
(.text+0x7b40): multiple definition of `__libc_close'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(close.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__open':
(.text+0x8280): multiple definition of `__libc_open'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(open.o):(.text+0x0):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__write_nocancel':
(.text+0x7a89): multiple definition of `__write_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(write.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__close_nocancel':
(.text+0x7b49): multiple definition of `__close_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(close.o):(.text+0x9):
 first defined here
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
 In function `__fcntl_nocancel':
(.text+0x7ba0): multiple definition of `__fcntl_nocancel'
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fcntl.o):(.text+0x0):
 first 

Re: [sqlite] Static sqlite3 library for Linux.

[Simon Slavin]

On 13 Feb 2018, at 10:55am, Petros Marinos  wrote:

> Is there any chance that I can find a Linux flavour static sqlite3 library, 
> or do I have to build from source?
> And if I have to build from source, is it recommended to use the 
> sqlite-autoconf or the sqlite-src pkg? Or does the amalgamation file could be 
> of any use, so as to avoid compilation etc?

It is recommended, in all possible cases, that you include the amalgamation 
source code (one .c file and one .h file) in your project.  Make sure your 
compiler understands that a .c file is C source code, not C++ source code.  The 
object code size of sqlite.c is very small and should not bulk up your program 
by much.

SQLite (talking about the API rather than the stand-alone tools) has no 
dependencies.  Therefore if you include the source code in your project rather 
than using SQLite as a library, you are adding no dependencies to your project. 
 This is important since a /lot/ of projects include SQLite and you can easily 
run into version disputes if you depend on a peculiarity of a certain version 
of SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Static sqlite3 library for Linux

[Arjen Markus]

If you use the amalgamated source, there is only one file to be compiled. That 
can be as simple as:

gcc -c sqlite3.c

ar r libsqlite3.a sqlite3.o



Any further dependencies are up to the linker. No need to worry about that.



Regards,



Arjen


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf
> Of Petros Marinos
> Sent: Wednesday, February 14, 2018 3:30 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Static sqlite3 library for Linux
>
> Greetings dears!
>
> While I am trying to build a static library of another tool which is 
> dependent to sqlite,
> I need to use an sqlite3 static library as well. Still, in my Centos 7.4 
> system, I
> cannot find one. The error is shown below:
>
> /bin/ld: cannot find -lsqlite3
> collect2: error: ld returned 1 exit status
> make[1]: *** [astdb2sqlite3] Error 1
> make: *** [utils] Error 2
>
> When I look for one with:
>
> yum provides */libsqlite*.a
>
> I find the Windows version:
>
> /usr/x86_64-w64-mingw32/sys-root/mingw/lib/libsqlite3.a
>
> Which of course is useless:
>
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/.../.../.../.../lib64/libsqlite3.a: 
> error adding
> symbols: File format not recognized
> collect2: error: ld returned 1 exit status
> make[1]: *** [astdb2sqlite3] Error 1
> make: *** [utils] Error 2
>
> Is there any chance that I can find a Linux flavour static sqlite3 library, 
> or do I have
> to build from source?
>
> And if I have to build from source, is it recommended to use the 
> sqlite-autoconf or
> the sqlite-src pkg? Or does the amalgamation file could be of any use, so as 
> to
> avoid compilation etc?
>
> PS: One final relevant question, does the resulting libsqlite3.a file (when 
> built from
> source), includes any other libraries like libc.a, libthread.a etc? Is there 
> a way to
> avoid this?
>
> Thank you in advance for your time!
>
> Best Regards,
> Petros
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited. 
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Static sqlite3 library for Linux

[Petros Marinos]

Greetings dears!

While I am trying to build a static library of another tool which is dependent 
to sqlite, I need to use an sqlite3 static library as well. Still, in my Centos 
7.4 system, I cannot find one. The error is shown below:

/bin/ld: cannot find -lsqlite3
collect2: error: ld returned 1 exit status
make[1]: *** [astdb2sqlite3] Error 1
make: *** [utils] Error 2

When I look for one with:

yum provides */libsqlite*.a

I find the Windows version:

/usr/x86_64-w64-mingw32/sys-root/mingw/lib/libsqlite3.a

Which of course is useless:

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/…/…/…/…/lib64/libsqlite3.a: error adding 
symbols: File format not recognized
collect2: error: ld returned 1 exit status
make[1]: *** [astdb2sqlite3] Error 1
make: *** [utils] Error 2

Is there any chance that I can find a Linux flavour static sqlite3 library, or 
do I have to build from source?

And if I have to build from source, is it recommended to use the 
sqlite-autoconf or the sqlite-src pkg? Or does the amalgamation file could be 
of any use, so as to avoid compilation etc?

PS: One final relevant question, does the resulting libsqlite3.a file (when 
built from source), includes any other libraries like libc.a, libthread.a etc? 
Is there a way to avoid this?

Thank you in advance for your time!

Best Regards,
Petros
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need some tips on using FTS5 with SQLite

[Wout Mertens]

I too am interested in this answer, I still have to start using fts5.

What would be interesting is to see the `EXPLAIN QUERY PLAN [query]` for
each of your queries, so as to see what causes the slowness.

On Thu, Feb 8, 2018, 7:14 PM John Found,  wrote:

>
> I am using FTS5 for pretty complex search in my application, but recently,
> trying to make it even more complex I faced some problems that are more
> general than only FTS5.
>
> I have a forum engine where are several tables for the threads, for the
> posts, for the users etc. At first I want to be able to search in the posts
> text, but moreover, this search have to be limiter to some subset of the
> posts, for example in the posts of a particular thread or posts of some
> user. Also, there are cases where free-text search is not actually
> necessary, for example when I am searching for all posts from a particular
> user.
>
> At first, I tried to create a FTS5 table, containing only the text data
> that need to be searched and then to access it by queries of the type:
>
> select
>   some,
>   fields
> from
>   fts
>   left join posts p on p.id = fts.rowid
>   left join threads t on t.id = p.threadid
>   left join users u on u.id = p.userid
> where
>   fts match ?1 and u.nick = ?2 and t.id = ?3
> order by ORDER
>
> Such queries are pretty fast when there is only fts match directive in the
> where clause.
> But any additional condition added ruins the performance, especially if
> the fts match returns big amount of matches.
>
> Additional problem is the order by clause. If the ORDER BY term is "rank"
> everything works great, but changing it to
> other field (for example the post time in order to get first most recent
> posts) causes huge slow down of the query.
>
> My second attempt was to sacrifice space for speed and to put all
> searchable data in the fts table - post text, the thread titles and the
> usernames. This way, building complex fts queries kind of:
>
>(content: ?1 OR caption: ?2) AND thread: ?3 AND user: ?4
>
> I can leave only the fts query in the WHERE clause. This way, the search
> is pretty fast, but the huge problem remains
> the ORDER BY clause. Again everything works fine with "rank", but attempts
> to use any other field for sorting, causes
> huge probems: slow downs up to tens of seconds (usual search time is few
> milliseconds) and out of memory errors.
>
> Such problems with this second approach are even more serious than on the
> first approach. i.e. with the second approach everything works fine and
> quick with "rank" order by, and very, very slow and with errors, on any
> other "order by" option.
>
> So, he main question follows:
>
> What is the right way to design such complex search systems, based on FTS?
> How to properly approach the sorting of the search results in order to not
> have so big slowdowns and out of memory errors.
>
> Any tips are highly welcome!
>
> Regards
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> 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

[sqlite] Strange behavior with wal_checkpoint PRAGMA

[Joshua Watt]

Hello,

I observed the following behavior when using the wal_checkpoint PRAGMA
in TRUNCATE mode:
 
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> create table test ( key INTEGER PRIMARY KEY, val INTEGER );
sqlite> insert into test values ( 5, 6 );
sqlite> insert into test values ( 6, 6 );
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
0|0|0
 
According to the documentation, the second and third values should
correspond to the number of pages in the write-ahead log and the number
of pages that have been moved back to the database file.  This works as
expected in the other modes, but in TRUNCATE mode the second and third
return values are always zero.
 
I believe this is because walCheckpoint() triggers a call to
walRestartHdr() if the mode is SQLITE_CHEKPOINT_TRUNCATE.  Once
walCheckpoint() returns, sqlite3WalCheckpoint() attempts to extract the
values from the already restarted wal header.

Has anyone else expirenced this issue?

Thanks,
Joshua Watt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Static sqlite3 library for Linux.

[Petros Marinos]

Greetings!

While I am trying to build a static library of another tool which is dependent 
to sqlite, I need to use an sqlite3 static library as well. Still, in my Centos 
7.4 system, I cannot find one. The error is shown below:

/bin/ld: cannot find -lsqlite3
collect2: error: ld returned 1 exit status
make[1]: *** [astdb2sqlite3] Error 1
make: *** [utils] Error 2

When I look for one with:
yum provides */libsqlite*.a

I find the Windows version:
/usr/x86_64-w64-mingw32/sys-root/mingw/lib/libsqlite3.a

Which of course is useless:
/usr/lib/gcc/x86_64-redhat-linux/4.8.5/…/…/…/…/lib64/libsqlite3.a: error adding 
symbols: File format not recognized
collect2: error: ld returned 1 exit status
make[1]: *** [astdb2sqlite3] Error 1
make: *** [utils] Error 2

Is there any chance that I can find a Linux flavour static sqlite3 library, or 
do I have to build from source?
And if I have to build from source, is it recommended to use the 
sqlite-autoconf or the sqlite-src pkg? Or does the amalgamation file could be 
of any use, so as to avoid compilation etc?

PS: One final relevant question, does the resulting libsqlite3.a file (when 
built from source), includes any other libraries like libc.a, libthread.a etc? 
Is there a way to avoid this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about threadsafe

[Simon Slavin]

On 14 Feb 2018, at 6:19am, Nick  wrote:

> 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? 

Yes.  You have two threads writing to the same file.  Naturally this cannot be 
allowed, so one thread results in SQLITE_BUSY.  The thread which gets that 
resilt is the thread which tries to obtain a write lock second.

You should not be setting both a busy handler and a timeout.  The idea is to 
use one or the other.  SQLite's own busy handler is very good at doin g the 
right thing (it features exponential backoff) so most of the time you just set 
a long timeout and don't bother writing your own busy handler.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Simon Slavin]

On 14 Feb 2018, at 7:50am, Dominique Devienne  wrote:

> Thanks. That's interesting. But then, why use it in this context?
> Why DRH wants to purposely bypass the index in this case?
> How is that relevant to testing tuple / row-values comparisons? --DD

In the original example, column a is the PRIMARY KEY.  SQLite automatically 
makes an index for the PRIMARY KEY.  So SQLIte might use that index if there's 
a WHERE clause on a.  In the case of the ticket DRH wants to avoid this, since 
the bug shows up only when an index is not used.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about threadsafe

[R Smith]

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?


It's almost like you are not reading the posts by Keith - which leads me 
to believe you might have messages channeled to spam for some reason, 
it's worth checking the spam folder.


To answer the questions: YES, it is expected.

To elaborate:
A - When starting a writing transaction from ANY thread, then ANY other 
write attempt (threaded or otherwise) will find the DB/Table(s) locked, 
that's by design and an absolute necessity in any RDBMS.


B - Should this happen, sqlite /CAN/ use a default busy handler that 
does nothing more than waits a bit, then see if the in-progress lock is 
lifted before continuing. Rinse and repeat until busy timeout time is 
exhausted.  It is important to note that this busy handler ONLY 
functions if there is an allowed time-out set on the connection. (See 
pragma timeout, or it can be set via the api too).


C - You can provide a busy-handler of your own making that will fire 
whenever an SQLITE_BUSY signal is received, regardless of timeouts. You 
can specify via this handler if the operation should wait, cancel, etc.



Usually setting the internal busy timeout is enough, and with large DBs 
running huge transactions, a very long timeout is common (sometimes in 
the order of minutes) - you will need to study your DB use case and 
decide what a good time is to give up on waiting.  If you /DO/ expect a 
lot of timeout waiting on an interactive user-based system, probably 
best to indicate to the user and not look like you died - perhaps even 
prompt them to find if they would like to wait for xxx to finish or 
cancel - in which case a combination of setting a busy handler and also 
a progress handler will be best.


More information on all the above is easily found in the sqlite site's 
search - if you do decide which to do and have any difficulty with a 
specific method, or fail to find information - please post again.

Good luck!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users