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

17.6.15.2. How to Write 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.

Note

Beginning with version 4.0.6, MySQL blocks SIGPIPE on the first call to mysql_server_init(), mysql_init(), or mysql_connect(). This is done to avoid aborting the program when a connection terminates. If you want to use your own SIGPIPE handler, you should first call mysql_server_init() and then install your handler. As of MySQL 4.1.10, use mysql_library_init() instead of mysql_server_init().

Before MySQL 4.0, binary client libraries that we provided other than those for Windows were not normally compiled with the thread-safe option. Current binary distributions should have both a normal client library, libmysqlclient, and a thread-safe library, libmysqlclient_r. For threaded clients, link against the latter library. If “undefined symbol” errors occur, in most cases this is because you have not included the thread libraries on the link/compile command.

The thread-safe client library, libmysqlclient_r, 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.

  • mysql_ping() does not attempt a reconnection if the connection is down. It returns an error instead.

You need to know the following if you have a thread that did not create the connection to the MySQL database but is calling MySQL functions:

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 avoid problems, 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(), it 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().


User Comments

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 ) {}

Posted by Bob Smith on April 13 2011 6:08am[Delete] [Edit]

Thanks Lefteris for the example code.

If you have fewer DB connections than threads, you will get a segmentation fault. Update the following code:

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

to this:

for (i = 0; i < THREADS; ++i)
{
int db_slot = i %CONPOOL;
pthread_create(&pthread[i], NULL, db_pthread, (void *)db_slot);
}

This will allocate DB connections in a 'round-robin' fashion to the threads.

Also, I think mysql_library_init() is required before thread creation and mysql_library_end() somewhere at the end (for cleanup).

Add your own comment.