MySQL multi-threading

[deadtrickster]

Hey guys, I spent this whole nigth banging my head trying to understand source of creepy connections errors. They were so creepy that I started to think about multitheading issues :-). And here we go

http://dev.mysql.com/doc/refman/5.0/en/mysql-library-init.html

In a nonmulti-threaded environment, the call to mysql_library_init() may be omitted, because
mysql_init() will invoke it automatically as necessary. However, mysql_library_init() is not thread-safe
in a multi-threaded environment, and thus neither is mysql_init(), which calls mysql_library_init(). You
must either call mysql_library_init() prior to spawning any threads, or else use a mutex to protect the
call, whether you invoke mysql_library_init() or indirectly through mysql_init(). Do this prior to any other 
client library call.

Test case is simple (from memory):

(loop for i from 0 to 9 do
  (sb-thread:make-thread (lambda () (clsql:with-database ("" "" "" "") :database-type :mysql))))

that is it. If lucky you can see something like

2013 :MESSAGE "Lost connection to MySQL server during query"

or maybe even

Error 2013 / Lost connection to MySQL server at 'reading initial communication packet', system error: 4

Right now as a hot fix I wrapped database-connect with mutex.

However I like mysql_library_init way more. What you think? I'm happy to create pull request with fix but before I would like to hear what you think is the best.

[UnwashedMeme]

I came across this bug years ago; I don't recall much other than thinking at the time that it wasn't a critical issue (for my use case at least). It looks like I even tried to leave some warnings behind about the issue https://github.com/UnwashedMeme/clsql/blob/master/doc/threading-warnings.txt

---

Yes, patches are welcome. I can't promise a fast turnaround but if you keep the patches small and concise then there's a better chance we can process them quickly (ever).

Another user tried to help out with threading stuff a while back; but his giant patches didn't apply cleanly and I'm ashamed to say I've never worked through them: https://github.com/UnwashedMeme/clsql/tree/jtk-threads

I just updated the master branch; this actually moves backward slightly but directly matches Kevin M Rosenberg's official master: http://git.b9.com/?p=clsql.git.

I just updated the development branch; this includes all the patches that @bobbysmith007 has been working on, notably #4 and some build issues on newer Ubuntu. Base your work here. Once we get that in we'll work on getting this into a proper CLSQL release.

[deadtrickster]

Update on topic: Old (or more precise - actual versions (at least on Ubuntu)) libmysql(client) versions completely ignore SIGINT existence. This was fixed in upstream from 5.6. Using recent (they renamed as mysqlconnector/c 6.* ) versions reduced number of creepy 2013 :MESSAGE "Lost connection to MySQL server during query" errors greatly.

Threading problems still there I'm planning to address this (hopefully soon) in PR.