Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




25.2.15. How to Make a Threaded Client

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.

To avoid aborting the program when a connection terminates, MySQL blocks SIGPIPE on the first call to mysql_server_init(), mysql_init(), or mysql_connect(). If you want to use your own SIGPIPE handler, you should first call mysql_server_init() and then install your handler.

In the older binaries we distribute on our Web site (, 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.

To get a threaded client where you can interrupt the client from other threads and set timeouts when talking with the MySQL server, you should use the -lmysys, -lmystrings, and -ldbug libraries and the net_serv.o code that the server uses.

If you don't need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r) and use this. See Section 25.2, “MySQL C API”. In this case, you don't have to worry about the net_serv.o object file or the other MySQL libraries.

When using a threaded client and you want to use timeouts and interrupts, you can make great use of the routines in the thr_alarm.c file. If you are using routines from the mysys library, the only thing you must remember is to call my_init() first! See Section 25.2.11, “C API Threaded Function Descriptions”.

All functions except mysql_real_connect() are by default thread-safe. The following notes describe how to compile a thread-safe client library and use it in a thread-safe manner. (The notes below for mysql_real_connect() actually apply to mysql_connect() as well, but because mysql_connect() is deprecated, you should be using mysql_real_connect() anyway.)

To make mysql_real_connect() thread-safe, you must recompile the client library with this command:

shell> ./configure --enable-thread-safe-client

This creates a thread-safe client library libmysqlclient_r. (Assuming that your OS has a thread-safe gethostbyname_r() function.) This library 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.

  • Many threads can access different result sets that are retrieved with mysql_store_result().

  • 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.

  • 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 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() or mysql_connect(), 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.

To get things to work smoothly you have to do the following:

  1. Call my_init() at the start of your program if it calls any other MySQL function before calling mysql_real_connect().

  2. Call mysql_thread_init() in the thread handler before calling any MySQL function.

  3. In the thread, call mysql_thread_end() before calling pthread_exit(). This frees the memory used by MySQL thread-specific variables.

You may get some errors because of undefined symbols when linking your client with libmysqlclient_r. In most cases this is because you haven't included the thread libraries on the link/compile line.

  Published under the terms of the GNU General Public License Design by Interspire