How thread-safe is mysql_real_connect()?

--=_alternative 00579CBC85257093_=
Content-Type: text/plain; charset="US-ASCII"

(please excuse the double post but I wanted to reach the two audiences I
thought could help the best)

This is a question about the interpreting the documentation in the manual
for the C API.
I searched the list archives (all lists) going back 365 days for the terms
(unquoted): "mysql_real_connect thread" (I also looked for
alternatives:"mysql_real_connect threaded", "mysql_real_connect multi
threaded", etc.). I searched on Google Groups for: mysql_real_connect
thread and found a few interesting hits. However, I am still not 100%
clear on how to interpret some of the information on this page:
http://dev.mysql.com/doc/mysql/en/threaded-clients.html

I do a lot of MySQL administration and development using mostly the CLI
and a few other tools but I am writing a multithreaded client to automate
certain background processing and I need a bit of advice. According to the
page in question the function mysql_real_connect() is not "thread-safe".
Does that simply mean that I cannot call that function from more than one
thread at a time or does that mean that the connection created by one call
to the function will be visible to the other threads or what? Just how not
"thread-safe" is it?

Each thread will have it's own MYSQL structure and I will need to use two
different connections per thread at the same time (am I going to need a
separate call to mysql_init() for each connection?). I know how to wrap
all of my calls to mysql_real_connect() in a critical section or protect
them with a mutex if that's all I need to do . If it's not that simple and
I do need to compile and link against another library (as the page
suggests - sort of) can someone help me to configure my Microsoft Visual
C++ .NET (v7) to do it? I said "sort-of" because the page also says that
the binary distributions (which I am working with ) already contain the
threadsafe library so I wonder if I need to rebuild anything or not. How
can I tell?

I am an experienced but not well-seasoned C++ developer (not using c# for
this). I know the language and can write and debug code just fine (I can
make stand-alone apps and DLLs all day); it's just that some of the
complier/linker options and settings that confound me and I am having
trouble translating the advice on the page into specifics I can work with
for my environment.

I know I probably left out some simple pieces of information, just let me
know and I will respond ASAP. Please remember to CC: both lists on all
responses.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




--=_alternative 00579CBC85257093_=--

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. =20

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(....);

=20
}

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

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



mysql_thread_end();
} =20




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

-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Friday, October 07, 2005 9:01 AM
To: win32@lists.mysql.com; mysql@lists.mysql.com
Subject: How thread-safe is mysql_real_connect()?

(please excuse the double post but I wanted to reach the two audiences I

thought could help the best)

This is a question about the interpreting the documentation in the
manual=20
for the C API.
I searched the list archives (all lists) going back 365 days for the
terms=20
(unquoted): "mysql_real_connect thread" (I also looked for=20
alternatives:"mysql_real_connect threaded", "mysql_real_connect multi=20
threaded", etc.). I searched on Google Groups for: mysql_real_connect=20
thread and found a few interesting hits. However, I am still not 100%=20
clear on how to interpret some of the information on this page:=20
http://dev.mysql.com/doc/mysql/en/threaded-clients.html

I do a lot of MySQL administration and development using mostly the CLI=20
and a few other tools but I am writing a multithreaded client to
automate=20
certain background processing and I need a bit of advice. According to
the=20
page in question the function mysql_real_connect() is not "thread-safe".

Does that simply mean that I cannot call that function from more than
one=20
thread at a time or does that mean that the connection created by one
call=20
to the function will be visible to the other threads or what? Just how
not=20
"thread-safe" is it?=20

Each thread will have it's own MYSQL structure and I will need to use
two=20
different connections per thread at the same time (am I going to need a=20
separate call to mysql_init() for each connection?). I know how to wrap=20
all of my calls to mysql_real_connect() in a critical section or
protect=20
them with a mutex if that's all I need to do . If it's not that simple
and=20
I do need to compile and link against another library (as the page=20
suggests - sort of) can someone help me to configure my Microsoft Visual

C++ .NET (v7) to do it? I said "sort-of" because the page also says that

the binary distributions (which I am working with ) already contain the=20
threadsafe library so I wonder if I need to rebuild anything or not.
How=20
can I tell?

I am an experienced but not well-seasoned C++ developer (not using c#
for=20
this). I know the language and can write and debug code just fine (I can

make stand-alone apps and DLLs all day); it's just that some of the=20
complier/linker options and settings that confound me and I am having=20
trouble translating the advice on the page into specifics I can work
with=20
for my environment.=20

I know I probably left out some simple pieces of information, just let
me=20
know and I will respond ASAP. Please remember to CC: both lists on all=20
responses.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: How thread-safe is mysql_real_connect()?

------_=_NextPart_001_01C5CE76.223FDA85
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Wow, thats good to know. Thanks Jeremiah. It is a little strange that =
the documentation doesn't mention that this behavior is different under =
windows and leads one to believe that calling mysql_thread_init/end is =
still neccesary. =20
=20
John

________________________________

From: SGreen@unimin.com [mailto:SGreen@unimin.com]
Sent: Tue 10/11/2005 6:52 AM
To: Jeremiah Gowdy
Cc: John McCaskey; mysql@lists.mysql.com; win32@lists.mysql.com
Subject: Re: How thread-safe is mysql_real_connect()?




"Jeremiah Gowdy" wrote on 10/11/2005 03:08:40 AM:

> The Windows DLL is thread safe. You do not have to call my_init()=20
> and my_thread_init() because Windows DLLs receive events when they=20
> are attached to a new process and when they are attached to a new=20
> thread in a process. This is one of the nicer features of Windows=20
> shared libraries. Other than that, you don't have to do anything=20
> special. I am a heavy user of libmysql under Win32. You simply=20
> mysql_init() your MYSQL struct, and then mysql_real_connect() and=20
> you're ready to mysql_query().
>=20
> You should not call my_init() or my_thread_init() as the previous=20
> poster suggested. This could result in memory leaks.
>=20
>=20
> From libmysql/dll.c
>=20
> BOOL APIENTRY LibMain(HANDLE hInst,DWORD ul_reason_being_called,
> LPVOID lpReserved)
> {
> switch (ul_reason_being_called) {
> case DLL_PROCESS_ATTACH: /* case of libentry call in win 3.x */
> if (!inited++)
> {
> s_hModule=3DhInst;
> libmysql_init();
> main_thread=3DGetCurrentThreadId();
> }
> break;
> case DLL_THREAD_ATTACH:
> threads++;
> my_thread_init();
> break;
> case DLL_PROCESS_DETACH: /* case of wep call in win 3.x */
> if (!--inited) /* Safety */
> {
> /* my_thread_init() */ /* This may give extra safety */
> my_end(0);
> }
> break;
> case DLL_THREAD_DETACH:
> /* Main thread will free by my_end() */
> threads--;
> if (main_thread !=3D GetCurrentThreadId())
> my_thread_end();
> break;
> default:
> break;
> } /* switch */
> return TRUE;
> UNREFERENCED_PARAMETER(lpReserved);
> } /* LibMain */
>=20
> ----- Original Message -----=20
> From: "John McCaskey"
> To: ; ; =

> Sent: Friday, October 07, 2005 10:31 AM
> Subject: RE: How thread-safe is mysql_real_connect()?
>=20
>=20
> Sean,
>=20
> First let me thank you for all the great posts and info I've seen you
> put on this list for others.
>=20
> 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.
>=20
> 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. =20
>=20
> 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.
>=20
> Here is some pseudo code of what you need to do:
>=20
> int main(int argc, char **argv) {
>=20
> if(!mysql_thread_safe()) {
> fprintf(stderr, "Not Thread safe!!!");
> return 1;
> }
>=20
> my_init();
>=20
> // your regular init code
>=20
> // create the threads that will use mysql
> CreateThread(....);
>=20
> =20
> }
>=20
> void *mysql_thread(void *arg) {
> mysql_thread_init();
>=20
>=20
> //regular mysql code and whatever else here
> //use mysql_real_connect and mysql_real_query=20
> //and whatever without worrying about thread safety
>=20
>=20
>=20
> mysql_thread_end();
> } =20
>=20
>=20
>=20
>=20
> John A. McCaskey
> Software Development Engineer
> Klir Technologies, Inc.
> johnm@klir.com
> 206.902.2027
>=20
> -----Original Message-----
> From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
> Sent: Friday, October 07, 2005 9:01 AM
> To: win32@lists.mysql.com; mysql@lists.mysql.com
> Subject: How thread-safe is mysql_real_connect()?
>=20

>=20
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>=20

Thank you very much!!=20

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

------_=_NextPart_001_01C5CE76.223FDA85--