Re: cannot reconcile mysql client library thread requirements with clsql code

On Wed 25 Apr 2012 06:14:34 PM EDT, JTK wrote:
>
>
> On Apr 25, 2012, at 5:22 AM, Nathan Bird wrote:
>
>>
>> On 04/24/2012 05:46 PM, JTK wrote:
>>>
>>>>
>>>>
>>>
>>>
>>> Thanks for the tips. Is there a reference to the the sigpipe fixes?
>>
>>
>> The best I've got for you is just the IRC chatlogs: we dropped it and 
>> took a different tack when we saw that rabbit hole getting much deeper.
>
>
>
> Thanks. I think I managed to understand the IRC thread, though it was 
> a bit difficult to follow.
>
> Reading this: 
> http://dev.mysql.com/doc/refman/5.0/en/threaded-clients.html the mysql 
> docs say:
>
> ====
> To avoid aborting the program when a connection terminates, MySQL 
> blocks SIGPIPE on the first call tomysql_library_init(), mysql_init(), 
> or mysql_connect().
> If you want to use your own SIGPIPE handler, you should first call 
> mysql_library_init() and then install your handler.
> ====
>
> So it seems that the whole signal problem could be fixed by having 
> something like this in the clsql C
> code for mysql, and calling it at the first use of connect, and never 
> calling it again. Or calling it upon load
> of the mysql back end.
>
>
> /* not tested */
> #include<signal.h>
>
> /* initialize clsql, and restore old SIGPIPE handler, returning 0 on 
> success, and 1,2,3 if failure
> took place at 1) getting old signal 2) initializing mysql and 3) 
> resetting old handler
>
> The errno returned by sigaction (cases 1,3) or msql_library_init() 
> (case 2) is returned in *nerror* */
>
> int clsql_mysql_init(int *nerror)
> {
> struct sigaction old_sigpipe_sigaction;
>
> /* save old SIGPIPE handler */
> if (sigaction(SIGPIPE, NULL,&old_sigpipe_sigaction)) {
> *nerror=errno;
> return 1;
> }
>
> /* call mysql_library_init(), which changes the handler */
> if (*nerror=mysql_library_init(0,NULL, NULL)) return 2;
>
> /* and restore the old handler */
> if (sigaction(SIGPIPE,&old_sigpipe_sigaction, NULL)) {
> *nerror=errno;
> return 3;
> }
>
> return 0;
> }
>
>
> And the per-thread problem could be called by having a threadwrapping 
> macro
> (with-clsql-thread ….) that will call mysql_init_thread() for the 
> first call to
> connect in this thread, and unwind-protecting a corresponding call to 
> mysql_thread_end().
> This (with-clsql-thread …) macro could also do stuff for the other 
> back-ends too, if they need it.
> And it could establish per-thread pooling, if desired, using 
> per-thread special variables.
>
> So unless I'm missing something, the solution is pretty simple. I'll 
> try to get around to testing
> it sometime soon.


I'm hardly an expert here but that's all looking pretty good--  A few 
thoughts:

* Mysql blocks sigpipe b/c mysql expects that signal to be raised when a 
connection terminates and is trying to prevent that from aborting the 
process. What effect is this going to have on SBCL (and any other lisp 
that is using sigpipe) to receive this signal?  My very fuzzy knowledge 
of SBCL here is that it has a queue of stuff that is done by that 
handler when it receives the signal-- empty queue means everything works?

* It looks like mysql_thread_init is called automatically from various 
places: http://dev.mysql.com/doc/refman/5.0/en/mysql-thread-init.html  I 
suspect it already has some logic for "This thread is already 
initialized." We don't need to worry about the initial mysql_connect 
(automically calls all the necessary inits); we might should be 
concerned about when a connection is then used in another thread because 
of pooling. Perhaps just call mysql_thead_init every time we return a 
connection from the pool?  (Yes I'm ignoring thread_end right now).

* I'm a bit nervous that the with-clsql-thread macro is going to be 
problematic to use. In some scenarios (e.g. a webapp) there is a 
threadpool created below 'user code' and there is not a convenient place 
lexically to place this macro. In that case the options are:
     * use this macro in an inner handler (i.e. a webrequest): is 
repeated calls to mysql_{thread_init,thread_end} take much time/resouces?
     * Some sort of thread exit callback -- it doesn't look like pthread 
has this (if so mysql would probably use it), SBCL doesn't appear to 
have one, and I don't know about other lisps. At that point we're 
introducing a hook in CLSQL that its users are expected to call from 
their threads or use the macro.
     * leaking memory at thread exit might not be problematic if threads 
last the lifetime of a process; e.g. fixed size threadpool or the lisp's 
main thread.


=== development notes ===
The C snippet you put above probably can be put into 
db-mysql/clsql_mysql.c and then arrange to call that from 
db-mysql/mysql-loader.lisp clsql-sys:database-type-load-foreign.

If you don't have a public git clsql repo to work on this in then 
forking my github clsql repo is a good option: 
https://github.com/UnwashedMeme/clsql   I can then pull patches from you 
there to help run test suite and push up to the main git.b9.com repo 
when ready.


Cheers,
Nathan Bird
_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql