----- Original Message ----- From: "John McCaskey" <joh...@klir.com> To: <SGr...@unimin.com>; <win...@lists.mysql.com>; <mys...@lists.mysql.com> Sent: Friday, October 07, 2005 10:31 AM Subject: RE: How thread-safe is mysql_real_connect()?

Sean,

First let me thank you for all the great posts and info I've seen you put on this list for others.

I've been working in C with MySQL in a very multithreaded environment for several years and think I can explain the thread safety issues clearly. Rather than try to respond point by point to your question I'm going to give a summary and if that doesn't help please respond again and I'll answer specific questions.

First, mysql is in fact pretty much threadsafe when using the _r library. You definitely do need to use the _r library and not the normal one as the SIGPIPE discussion applies to both, the non _r library has additional safety issues surrounding mysql_real_connect() and should not be used. On windows you don't really need to do anything here I believe because "the Windows binaries are by default compiled to be thread-safe." (from http://dev.mysql.com/doc/mysql/en/threaded-clients.html). To validate this in your client code you should in the main() function close to startup use mysql_thread_safe() to verify your linked in version is thread safe.

The next thing you need to do is initialize mysql globally before creating any threads that will use it. Simply call my_init(); in your main thread. After this you can go ahead and create any threads. In the threads you create you need to call mysql_thread_init(); and when you end the thread mysql_thread_end(); in between these calls you can just use mysql as normal and the mysql_real_connect function will be thread safe, you do not need to perform any locking of your own to make only one call at a time or anything along those lines.

Here is some pseudo code of what you need to do:

int main(int argc, char **argv) {

if(!mysql_thread_safe()) { fprintf(stderr, "Not Thread safe!!!"); return 1; }

my_init();

// your regular init code

// create the threads that will use mysql CreateThread(....);

}

void *mysql_thread(void *arg) { mysql_thread_init();

//regular mysql code and whatever else here //use mysql_real_connect and mysql_real_query //and whatever without worrying about thread safety

mysql_thread_end(); }

John A. McCaskey Software Development Engineer Klir Technologies, Inc. joh...@klir.com 206.902.2027

<snip>

Thank you very much!!

Shawn Green Database Administrator Unimin Corporation - Spruce Pine