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

On 04/24/2012 05:46 PM, JTK wrote:
> On Apr 24, 2012, at 4:45 AM, Nathan Bird wrote:
>
>> On 04/24/2012 01:31 AM, JTK wrote:
>>> I'm wondering if I've found serious deficiencies in the way clsql/mysql handles
>>> threads, including missing per-thread init functions, and missing per-thread
>>> cleanups ……. [much deleted]
>> Nope, I think you're pretty much right.
>>
>> In practice we've used the current mysql backend on SBCL somewhat successfully-- it did run the queries and return the results as expected. But we did have some problems with threads that were running endlessly not being killable and problems interrupting other tasks. When we talked to some of the SBCL developers we found that sbcl uses sigpipe internally for some of that, and the mysql disables it/uses it otherways. In order to work around it we need to save the sbcl signal handler, init mysql then do some restore work...  we ended up moving that bit of code (we really didn't have much) out of lisp.
>
> 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.

http://ccl.clozure.com/irc-logs/lisp/2012-02/lisp-2012.02.22.txt
Look for the conversation starting at 20:03:32 between bobbysmith007 
(Russ Tyndall, a co-worker) and pkhuong (Paul Khuong, an SBCL developer).

Another note: when I ran the test suite yesterday before pushing I found 
the mysql suite had become a *lot* slower than last time I had run it. 
In between I upgraded my Ubuntu installation which brought a newer 
version of the mysql client library and server (5.5.22-0ubuntu1)...  I 
haven't investigated further yet.


> To address the issues with mysql, I think one needs to have a macro like
>
> (somelisp:launch-thread  ;; whatever the lisp thread launcher is
>   (lambda ()  ;; the thread lambda
>    (with-clsql-thread  ;; new macro to set up and shutdown thread-specific sql info
> 	(do-stuff-in-thread ….))))
>
> where the macro does necessary inits and UNWIND-PROTECTS the
> mysql thread-end functions, which are put into some kind of finalization
> cache local to the thread.  I think this is what I'll have to do.  Maybe define
> a thread-specific pool variable, so each thread has its own pool that gets shut down at end
> inside the unwind-protect. Then threads don't even know about each others'
> connections.
>
>
>
> Could I suggest that clsql needs a manual section on threading that
> explains what is and isn't safe?
>
> I think that a lot of people using sql with threads have to rediscover its quirks.
>
> Something like the following. Details on most back ends missing, but I suggest
> it is better to present it as an unknown rather than simply say
> that CLSQL is threadsafe without more elaboration.
>


Looks pretty good and mostly accurate from what I know-- I added a few 
notes where our experience indicates it works pretty well. Getting this 
into the official documentation (docbook manual) is a little bit time 
consuming so for the moment I've added a note in the BUGS file and added 
your text to doc/threading-warnings.txt.