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

On 04/24/2012 01:31 AM, JTK wrote:
> Hello again,
>
> Apologies in advance if this is all completely wrong.
>
> I'm reading the mysql client manual for threads:
> http://dev.mysql.com/doc/refman/5.0/en/threaded-clients.html
>
> 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.
>
> I'm trying to compare the requirements to  code in clsql-6.0.1/db-mysql
>
> Because 6.0.1 isn't the latest, I also looked in the clsql git tree.
>
> Among other things, mysql documents say:  [** my notes in brackets **]
>
>   ====
> You need to know the following if you have a thread that did not create the connection to the MySQL database but is calling MySQL functions:
>
> [** isn't this what happens when a DATABASE is created, put into the pool, and picked up by another thread ? **]
>
> When you call mysql_init(), MySQL creates a thread-specific variable for the thread that is used by the debug
>   library (among other things).  If you call a MySQL function before the thread has called mysql_init(), the thread does not
>   have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later.  [** important?! **]
>   To avoid problems, you must do the following:
>
> 1 Call mysql_library_init() before any other MySQL functions. It is not thread-safe, so call it before threads are
>    created, or protect the call with a mutex.
>
> [** mysql_library_init is never used in clsql - its document says "Call this function to initialize the MySQL library before
>       you call any other MySQL function,     whether your application is a regular client program or uses the
>       embedded server. … In a nonmulti-threaded environment, the call to mysql_library_init()
>      may be omitted"  - so it looks like a problem that this is never defined in clsql, if used in a threaded environment **]
>
>
> 2  Arrange for mysql_thread_init() to be called early in the thread handler before calling any MySQL function. If you call mysql_init(), it will call mysql_thread_init() for you.
>
> [** mysql_thread_init is never called, or even defined.  If it must be called by each thread, when using a database connection not created in that thread, it
>       would be seem to be a bug in clsql.    If mysql_init were called in every thread (not just in database-connect)  the requirement would be satisfied **]
>
> 3. In the thread, call mysql_thread_end() before calling pthread_exit(). This frees the memory used by MySQL thread-specific variables.
>
> [** mysql_thread_end is undefined in clsql. Does this mean that when a Lisp thread quits, it leaks
>      memory (or would leak memory, had mysql_thread_init() been run)  ?  **]
>
> ====
>
> So unless I completely misunderstand the above mysql requirements, clsql doesn't do many of the things that are required
> to work in a threaded environment.    Not even if pooling isn't used, because of missing mysql_library_init().   And the lack of
> mysql_thread_end()  means that even non-pooled connections will leak.
>
> Do I understand that clsql should 1) call mysql_library_init() at some point.   2) call mysql_thread_init whenever a new thread uses
> mysql?  3) call mysql_thread_end whenever a thread exits, to clean up memory from item two?
>
> Or, alternatively, never use pools when threads are used,  always disconnect a db at end of use before exiting a thread,
> and change database-disconnect to run mysql_thread_end() to close off mysql_init()/mysql_thread_init()
> that occurred when database-connect was run.
>
> Or is my understanding of this just plain wrong, for some reason I don't get?


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.