[Zope] ZMySQLDA-2.0.9b1 released

[Andy Dustman]

On Fri, 2002-02-01 at 09:12, Matthew T. Kromer wrote:
> Harald Koschinski wrote:
> 
> > MySQLdb-0.9.2a2 is not compiling :-((
> >
> > intranet:/usr/local/opt/Zope/products/MySQL-python-0.9.2a2 # python 
> > setup.py build
> > [...]
> > /usr/i486-suse-linux/bin/ld: cannot find -lmysqlclient_r
> > collect2: ld returned 1 exit status
> > error: command 'gcc' failed with exit status 1
> >
> > No Problems with 0.9.1 on the same mashine
> >
> But the 0.9.1 you built is a time bomb waiting to blow up on you.  If 
> you don't link with the reentrant mysqlclient libraries your python 
> adapater isn't thread safe.
> 
> I expect you can edit the Setup.py file and change the "use threaded 
> client libraries" switch to use the non-threaded library.  If you do 
> that and use it in Zope though, you are asking for crashes.

The funny thing is, the "non-thread-safe" libraries are not all that
unsafe. I've done a lot of testing (outside of Zope) of the
non-thread-safe libraries in a threaded environment, and they seem to
work fine. The docs currently say [my comments in brackets]:

http://www.mysql.com/doc/T/h/Threaded_clients.html

     The client library is almost thread safe. The biggest problem is
    that the subroutines in `net.c'  that read from sockets are not
    interrupt safe. This was done with the thought that you might want
    to have your own alarm that can break a long read to a server. If
    you install interrupt handlers for the SIGPIPE interrupt, the socket
    handling should be thread safe. [My own experiment indicates that
    Zope seems to ignore the SIGPIPE.]
    
    In the older binaries we distribute on our web site
    (http://www.mysql.com/ ), the client libraries are not normally
    compiled with the thread-safe option (the Windows binaries are by
    default compiled to be thread safe). Newer binary distributions
    should have both a normal and a thread-safe client library.
    
    ...
    
    If you don't need interrupts or timeouts, you can just compile a
    thread safe client library (mysqlclient_r) and use this. See section
    8.4 MySQL C API. In this case you don't have to worry about the
    net_serv.o object file or the other MySQL libraries.
    
    ...
    
    All functions except mysql_real_connect() are by default thread
    safe. ... This library [libmysqlclient_r] is thread safe per
    connection. You can let two threads share the same connection with
    the following caveats:
    
        * Two threads can't send a query to the MySQL server at the same
    time on the same connection. In particular, you have to ensure that
    between a mysql_query() and mysql_store_result() no other thread is
    using the same connection. [ZMySQLDA-2.0.9b1 enforces this. Previous
    versions do not, but it is not clear whether or not the Zope DA code
    either allows or prevents simultaneous use of a connection object by
    multiple threads.]
        * Many threads can access different result sets that are
    retrieved with mysql_store_result(). [This is what is used.]
        * If you use mysql_use_result , you have to ensure that no other
    thread is using the same connection until the result set is closed.
    However, it really is best for threaded clients that share the same
    connection to use mysql_store_result().
        * If you want to use multiple threads on the same connection,
    you must have a mutex lock around your mysql_query() and
    mysql_store_result() call combination. Once mysql_store_result() is
    ready, the lock can be released and other threads may query the same
    connection. [As stated above, 2.0.9b1 now does this, just in case
    Zope is allowing simultaneous access.]
        * If you program with POSIX threads, you can use
    pthread_mutex_lock() and pthread_mutex_unlock() to establish and
    release a mutex lock.
    
    You need to know the following if you have a thread that is calling
    MySQL functions which did not create the connection to the MySQL
    database: 
    
    When you call mysql_init() or mysql_connect(), MySQL will create 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() or mysql_connect() , the thread will not have the
    necessary thread specific variables in place and you are likely to
    end up with a core dump sooner or later. [_mysql.connect() obeys
    this] 

So as near as I can tell, even using the non-thread-safe library with
Zope *ought* to be safe, so long as multiple threads are not using a
connection at the same time. The most likely explanation for the crashes
people occasionally see is that there is nothing preventing Zope from
simultanous multiple access of the database connection object. If that's
true, ZMySQLDA 2.0.9 ought to fix it. But then I would expect crashes
more often than what actually seems to happen.

-- 
Andy Dustman         PGP: 0x930B8AB6
    @       .net     http://dustman.net/andy
You can have my keys when you pry them from my dead, cold neurons.