Skip navigation links
21.10 MySQL PHP API »
Section Navigation      [Toggle]

21.9.16.2. 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_library_init(), mysql_init(), or mysql_connect(). If you want to use your own SIGPIPE handler, you should first call mysql_library_init() and then install your handler.

Current binary distributions should have both a normal and a thread-safe client library.

To create 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 net_serv.o code that the server uses and the -lmysys, -lmystrings, and -ldbug libraries.

If you don't need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r) and use it. 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 21.9.8, “C API Threaded Function Descriptions”.

In all cases, be sure to initialize the client library by calling mysql_library_init() before calling any other MySQL functions. When you are done with the library, call mysql_library_end().

mysql_real_connect() is not thread-safe by default. 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() also apply to the older mysql_connect() routine as well, although mysql_connect() is deprecated and should no longer be used.)

To make mysql_real_connect() thread-safe, you must configure your MySQL distribution with this command: 

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

Then recompile the distribution to create a thread-safe client library, libmysqlclient_r. (Assuming that your operating system 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 calls to 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 must 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 pair of mysql_query() and mysql_store_result() calls. Once mysql_store_result() is ready, the lock can be released and other threads may query the same connection.

  • If you use 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(), 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(), 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 must do the following:

  1. Call mysql_library_init() before any other MySQL functions. It is not thread-safe, so call it before threads are created, or protect the call with a mutex.

  2. Arrange for mysql_thread_init() to be called early in the thread handler before calling any MySQL function. If you call mysql_init(), they will call mysql_thread_init() for you.

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

The preceding notes regarding mysql_init() also apply to mysql_connect(), which calls mysql_init().

If “undefined symbol” errors occur when linking your client with libmysqlclient_r, in most cases this is because you haven't included the thread libraries on the link/compile command.


User Comments

Posted by Matthias Urlichs on May 14 2004 5:22pm[Delete] [Edit]

Sharing one connection between threads doesn't make much sense anyway. *Passing* it, yes, for instance by way of a connection pool. But *sharing*? MySQL connections are not resource intense: just open a second one.

Posted by Season Sze on March 16 2005 5:27pm[Delete] [Edit]

In fact, I found plenty of connections of mysql client doesn't make the database faster at all. Try to use as less as u can in your program

Posted by Lefteris Tsintjelis on October 7 2005 10:48am[Delete] [Edit]

#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include <mysql.h>

// Just a small exmaple of multithreading, MUST link with -lpthreads -lmysqlclient_r
// Note: Optimal # of threads and connections pool is the # of CPUs BUT,
// that depends a lot on how fast you expect the answer to your queries

#define QPERTHR 500
#define THREADS 2
#define CONPOOL (THREADS)

typedef struct db_donfig {
char host[16];
char user[16];
char pass[16];
char name[16];
unsigned int port;
char *socket;
} db_config;

typedef struct db_mutex {
MYSQL *db;
pthread_mutex_t lock;
} db_mutex;

db_mutex dbm[CONPOOL];

void *db_pthread(void *arg);
static void db_die(char *fmt, ...);
MYSQL *db_connect(MYSQL *db, db_config *dbc);
void db_disconnect(MYSQL *db);
long db_query(MYSQL *db, pthread_mutex_t *lock, const char *query);

int main(int argc, char **argv) {
int i;
pthread_t pthread[THREADS];
db_config dbc;

strcpy(dbc.host,"");
strcpy(dbc.user,"");
strcpy(dbc.pass,"");
strcpy(dbc.name,"");
dbc.port = 3306;
dbc.socket = NULL;

if (!mysql_thread_safe())
fprintf(stderr, "Thread Safe OFF\n");
else
fprintf(stderr, "Thread Safe ON\n");
fprintf(stdout, "DB Connections: %d, Threads: %d, Queries per Thread: %d, Total Queries: %d\n",\
CONPOOL, THREADS, QPERTHR, THREADS * QPERTHR);

// pre initialize connections and locks
for (i = 0; i < CONPOOL; ++i) {
dbm[i].db = db_connect(dbm[i].db, &dbc);
pthread_mutex_init(&dbm[i].lock, NULL);
}

// pthread_setconcurrency(THREADS);
// fire up the threads
for (i = 0; i < THREADS; ++i)
pthread_create(&pthread[i], NULL, db_pthread, (void *)i);

// wait for threads to finish
for (i = 0; i < THREADS; ++i)
pthread_join(pthread[i], NULL);

for (i = 0; i < CONPOOL; ++i) {
pthread_mutex_destroy(&dbm[i].lock);
db_disconnect(dbm[i].db);
}

exit(EXIT_SUCCESS);
}

void *db_pthread(void *arg) {
int i = (int) arg, j, cancelstate;

// Always a good idea to disable thread cancel state or
// unexpected crashes may occur in case of database failures
pthread_setcancelstate(PTHREAD_CANCEL_DISABLE,&cancelstate);
if ((mysql_thread_init() != 0))
db_die("mysql_thread_init failed: %s", mysql_error(dbm[i].db));
for (j = 0; j < QPERTHR; ++j)
db_query(dbm[i].db, &(dbm[i].lock), "show status");
mysql_thread_end();
pthread_setcancelstate(PTHREAD_CANCEL_ENABLE,&cancelstate);
pthread_exit((void *)0);
}

static void db_die(char *fmt, ...) {
int i;

va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
(void)putc('\n', stderr);
for (i = 0; i < CONPOOL; ++i) {
pthread_mutex_destroy(&dbm[i].lock);
db_disconnect(dbm[i].db);
}
exit(EXIT_FAILURE);
}

MYSQL *db_connect(MYSQL *db, db_config *dbc) {
if (!(db = mysql_init(db)))
db_die("mysql_init failed: %s", mysql_error(db));
else {
if (!mysql_real_connect(db, dbc->host, dbc->user, dbc->pass, dbc->name, dbc->port, dbc->socket, 0))
db_die("mysql_real_connect failed: %s", mysql_error(db));
}
return (db);
}

void db_disconnect(MYSQL *db) {
if (db)
mysql_close(db);
}

long db_query(MYSQL *db, pthread_mutex_t *lock, const char *query) {
long ret;

// lock must be called before mysql_query
pthread_mutex_lock(lock);
ret = mysql_query(db, query);
// if query failed, exit with db error
if (ret != 0) {
// Get rid of the lock first
pthread_mutex_unlock(lock);
db_die("mysql_query failed: %s", mysql_error(db));
}
// if query succeeded
else {
MYSQL_RES *res;

res = mysql_store_result(db);
// Get rid of the lock ASAP, only safe after mysql_store_result
pthread_mutex_unlock(lock);
// if there are rows
if (res) {
MYSQL_ROW row, end_row;
unsigned int num_fields;

num_fields = mysql_num_fields(res);
// count total rows * fields and return the value, if SELECT
while ( (row = mysql_fetch_row(res)) )
for (end_row = row + num_fields; row < end_row; ++row)
++ret;
mysql_free_result(res);
}
// if there are no rows, should there be any ?
else {
// if query was not a SELECT, return with affected rows
if (mysql_field_count(db) == 0)
ret = mysql_affected_rows(db);
// there should be data, exit with db error
else
db_die("mysql_store_result failed: %s", mysql_error(db));
}
}
return (ret);
}

Posted by [name withheld] on February 24 2006 8:37pm[Delete] [Edit]

Thank you Lefteris; the example helps a lot!

At the end of db_thread, shouldn't you call:

pthread_setcancelstate(cancelstate,0);

to restore it, instead of

pthread_setcancelstate(PTHREAD_CANCEL_ENABLE, &cancelstate);

Thanks
npj

Posted by [name withheld] on February 24 2006 8:55pm[Delete] [Edit]

Also,

You need to initialize the db_mutex::db fields to null. It's likely the compiler would do this automatically since your dbm array is statically allocated, but this practice could lead to confusing bugs in an example.

Posted by Alexander Petrossian (PAF) on November 21 2006 12:40pm[Delete] [Edit]

If you need multithreaded client and standard
MYSQL_OPT_CONNECT_TIMEOUT / MYSQL_OPT_READ_TIMEOUT / MYSQL_OPT_WRITE_TIMEOUT
settings do not work for you, you really need to go the hard way.

Regretfully, this chapter is pretty vague about this point.
After spending ~3 working days:

1. download and compile mysql sources
#define add --with-debug if you need mysql calls logging and debugging
./configure --enable-thread-safe-client --with-unix-socket-path=/var/lib/mysql/mysql.sock
make

2. link your application against all these
net_serv.o -lmysys -lmystrings -ldbug -lmysqlclient_r

3. in all threads do not forget about mysql_thread_init/mysql_thread_end, I suggest using the MySQLThreadData C++ sugar (below).

4. at start of your application do this magic

int main(int argc, char *argv[]) {
// http://dev.mysql.com/doc/refman/4.1/en/threaded-clients.html
// plus they don't tell you about init_thr_alarm and THR_SERVER_ALARM handling
if (mysql_server_init(0, NULL, NULL)) {
mmErr("mysql_server_init failed\n");
exit(-1);
}
MySQLThreadData sqlThreadData; // sequence like in thr_alarm.c:signal_hand
init_thr_alarm(1000); // this is max number of threads in your app todo:find out what happends if we exceed that?
{
// we are not using mysql signal thread, so install and unblock handler
my_sigset(THR_SERVER_ALARM, process_alarm);

sigset_t s; sigemptyset(&s);
sigaddset(&s, THR_SERVER_ALARM);
pthread_sigmask(SIG_UNBLOCK, &s, NULL);
}


class MySQLThreadData {
public:
MySQLThreadData() { mysql_thread_init(); }
~MySQLThreadData() { mysql_thread_end(); }
};

5. if you need deeper understanding about internals of mysql client, use --with-debug when configuring mysql, and use
MYSQL_DEBUG=d:t command-to-start-your-application
to instruct mysql library to print debug info to stderr.
more on debugging here:
http://dev.mysql.com/doc/refman/5.0/en/the-dbug-package.html

Posted by Alexander Petrossian (PAF) on November 21 2006 12:42pm[Delete] [Edit]

forgot to mention that you also need tricky includes, to fetch all appropriate internals:

// be extra careful, this should be mysql config.h, not your local!
#undef PACKAGE_VERSION
#include <config.h>
#include <my_global.h>
#include <my_dbug.h>
#include <my_pthread.h>
#include <thr_alarm.h>
#include <mysql.h>

Posted by Alexander Petrossian (PAF) on November 30 2006 9:36am[Delete] [Edit]

Yet also forgot to mention fake mysqld.cpp that is also needed .

#include <mysql.h>

uint test_flags;
ulong bytes_sent;
ulong bytes_received;
ulong net_big_packet_count;
void query_cache_insert(NET *, const char *, ulong ) {}

Add your own comment.