MySQL Bugs: #3824: mysql_execute, mysql_insert

Bug #3824	mysql_execute, mysql_insert_id, and threads
Submitted:	19 May 2004 1:34	Modified:	29 May 2004 10:34
Reporter:	Darryl Rodden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1 alpha	OS:	Linux (Redhat 9)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I have a multi-threaded application.  Each thread has its own connection to the database (with autocommit turned off).  The app is working great on all normal (select, insert, update, delete, commit, rollback) operations.  

I have added new functionality to use a prepared statement to insert into a table that has an autoincrement column and a long blob.  Each thread has its own prepared insert statement (they are not sharing a single prepared statement).  After the insert, with mysql_execute(), I use mysql_insert_id() to get the value of the row just inserted.  However, when multiple threads are running, they sometimes get back a 0 (zero) result from mysql_insert_id(), even though mysql_execute() succeeded.  If I have one thread running, it works perfectly.  If I add a mutex lock around the mysql_execute() - mysql_insert_id() pair, multiple threads work perfectly.  When mysql_insert_id() returns zero, I check mysql_errno(), but it is also zero.  

Could there be some non-thread-safe code or data in the library?  Something about that pair of calls together?  The fact that a lock fixes it seems to point in that direction.

I do not have a full blown test program for you yet (I'm a little pressed for time, but am trying to put it together).  But here is a stripped down bit of code:

const char insertMsg[] = "INSERT INTO MsgBody (msg_body_id, msg_body) VALUES (0, ?)";

/* 
 * These are defined for each thread.  Each thread logs into the 
 * database and prepares its insert statement once when it first 
 * starts.  Note that autocommit is shutoff on each connection 
 * (don't know if that matters).  That is all successful.
 */ 
MYSQL      *myData;
MYSQL_STMT *insert_stmt;

/* 
 * this is the code that the thread executes each time to insert. 
 */ 

/* Bind the buffers */
unsigned char       buffer [10240];
unsigned long       length = sizeof(buffer);
unsigned long long  msg_id;
MYSQL_BIND          bind;

memset (&bind, 0, sizeof(bind));

/* body PARAM */
bind.buffer_type   = MYSQL_TYPE_LONG_BLOB;
bind.buffer        = &buffer;
bind.buffer_length = length;
bind.length        = &length;

if (mysql_bind_param (insert_stmt, &bind) != 0)
{
    printf ("mysql_stmt_bind_param() failed, errno=%d, error=%s\n", 
            mysql_stmt_errno(insert_stmt), mysql_stmt_error(insert_stmt));
    return (-1);
}

// add a mutex lock here and unlock below seems to fixes it

if (mysql_execute(insert_stmt) != 0)
{
    printf ("mysql_stmt_execute() failed, errno=%d, error=%s\n", 
            mysql_stmt_errno(insert_stmt), mysql_stmt_error(insert_stmt));
    return (-1);
}

msg_id = mysql_insert_id (myData);

// add a mutex unlock here

if (msg_id == 0)
{
    printf ("mysql_insert_id() failed, errno=%d, error=%s\n", 
            mysql_errno(myData), mysql_error(myData));
    return (-1);
}

return (0);  // success if we got this far

How to repeat:
see above description

Hi, Darryl!

Could you please provide complete program which reproduces behavior you have described ? From symptoms you have mentioned it sounds to me that your prepared statements share the same connection to MySQL. And it is not obvious from your code snippet is this true or not.

BTW starting from version 4.1.2 one could (and it is recommended) use mysql_stmt_insert_id() function for getting value of AUTO_INCREMENT column which was set during execution of prepared statement (I'll add documentation for this function ASAP).

As I mentioned in the original description, a mutex lock around the mysql_execute() and mysql_insert_id() gets me around the problem, but this is having an imapct on performance.  So I am bumping up the severity and prioity.

Hi Dimitri,

Each thread has its own connection.  This is created locally in each thread, as is the prepared statement.  I have verified this through the debugger and simply printing the addresses of them after they are created.  Here is what each thread does when it first starts (note I have removed all error handling, but believe me that I validate the success of each function before proceeding):

Thanks,
Darryl

MYSQL      *myData;
MYSQL_STMT *insert_stmt;

myData = mysql_init(NULL);
mysql_real_connect (myData, .....);
mysql_autocommit (myData, 0);
insert_stmt = mysql_prepare (myData, insertMsg, strlen(insertMsg));

Hi, Darryl!

Unfortunately I was not able to repeat behavior you have reported using my own small test program. I've tried both latest 4.1 bk snapshot and 4.1.1 version of MySQL. 

May be exact definitions of your tables would help...  But of course small program which will reproduce behaviour you have reported would be best thing of all...

Thank you for your interest in MySQL!

Hi Dmitri!

Here is a sample program.  I hope this helps.

Darryl

/*******************************************************************************
**       Module:  mysqltest.c
**
**  Description:  This is a multi-threaded test program from Darryl Rodden to 
**                MySql support.  It demonstrates a situation where 
**                mysql_isert_id() fails to return the autoincrement value of
**                a row just inserted with a prepared statement.  It returns 0
**                (zero) instead.  When this happens, the program prints a 
**                message.
**                Bug ticket ID# 3824
**
**
**  Environments Tested:
**      Client program is built with:
**          gcc 3.2.3 20030502 (Red Hat Linux 3.2.3-20)
**          libc.so.6 (GNU C Library stable release version 2.3.2)
**          4.1.1-alpha MySql client
**          gcc -c -Wall -g -I/usr/local/include -D_GNU_SOURCE -D_REENTRANT -D_THREAD_SAFE mysqltest.c
**          gcc -o mysqltest mysqltest.o -L/usr/local/lib -lpthread -lmysqlclient_r -lz
**      MySql server 4.1.1-alpha
**      Redhat 9, Redhat REL 3.0
**      Single- and Multi- CPU x86 servers
**
**
**  How to use:
**      1) modify the database login variables below to match your
**         database:
**           db_host, db_port, db_user, db_password
**           note: db_host and db_port can be supplied on the command line.
**
**      2) Compile and link with 4.1.1-alpha client library (be sure
**         to use mysqlclient_r).
**
**      3) Create the test table MsgBody:
**             CREATE TABLE MsgBody (
**                 msg_body_id bigint(19) unsigned NOT NULL auto_increment,
**                 msg_body longblob NOT NULL,
**                 PRIMARY KEY  (msg_body_id)
**             ) TYPE=InnoDB;
**
**      4) Program usage.  If you run the program with no parameters,
**         it will print the following usage:
**             usage: mysqltest -t # -n # [-h dbhost] [-p #]
**             where:
**                -t #       is the # of threads to create
**                -n #       is the # of records each thread will insert
**                -h dbhost  database ip address (or hostname)
**                -p #       is the database port #
**
**      5) Run the program with a single thread.  This is always successful.
**             ./mysqltest -t 1 -n 100
**
**      6) Run the program with a multiple threads.  
**             ./mysqltest -t 10 -n 100
**
**         The mysql_insert_id() will fail and return zero sometimes when 
**         two or more threads are running.  I say sometimes because it does 
**         not happen every time, but it **will** happen.  The more threads 
**         that are running, the more frequently the problem occurs.  When 
**         it does happen, a message like below is printed.  Note that the
**         messsage includes the mysql_errno() and mysql_error() for the 
**         connection, which are always 0/empty indicating no error.
**
**         thread 3064417200: record# 28, mysql_insert_id() returned 0, should be > 0
**              did it fail???, mysql_errno()=0, mysql_error()=
**
**      7) If you are feeling ambitious, uncomment the mutex locking in the 
**         worker_thread() function and rebuild.  This puts a mutex lock around 
**         the mysql_execute() and mysql_insert_id() calls.  Then run the 
**         multiple thread test again.  It always works successfully.
**             ./mysqltest -t 10 -n 100
**
**
**  Darryl Rodden
**  Senior Software Engineer
**  GoDaddy Software, Inc.
**  drodden@godaddy.com
**  (480) 505-8800 x166
**
*******************************************************************************/

#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <memory.h>
#include <errno.h>
#include <pthread.h>

#include <mysql.h>

//
// database login variables.  MODIFY to fit your database.
//
//static char db_host[128]        = "172.16.200.108";
//static char db_host[128]        = "127.0.0.1";
static unsigned int db_port     = 3306;
static char db_host[128]        = "";
static char const db_user[]     = "gomap";
static char const db_password[] = "gomap";
static char const db_database[] = "gomapproto";

#define MIN_NUM_RECORDS     10
#define MAX_NUM_RECORDS     1000
#define BUFFER_SIZE         10240;
static int num_records_to_insert = 0;

#define MIN_NUM_THREADS     1
#define MAX_NUM_THREADS     100
static int num_threads_to_create = 0;

//--------------------------------------------------------------------------
// define the strings that access the MySql database.
// --------------------------------------------------------------------------
//
// ----insert a new message into the body table
//
static const char insertMsgBlob[] = "INSERT INTO MsgBody (msg_body_id, msg_body) " \
                                    "VALUES (0, ?)";

//static pthread_mutex_t db_mutex = PTHREAD_MUTEX_INITIALIZER;

/*******************************************************************************
**     Function:  worker_thread
**
**  Description:  thread that will connect to the database, prepare an insert
**                statement, and insert one or more records into the table.
**                Any errors encountered are printed.
**
**   Parameters:
**
** Return Value:
**
*******************************************************************************/
static void *worker_thread ( void *arg )
{
    int                 ndx;
    unsigned long long  msg_id;
    unsigned long       buflen;
    char                *bufptr;
    pthread_t           thr_id;
    MYSQL               *myData;
    MYSQL_STMT          *body_insert;
    MYSQL_BIND          bind;

    thr_id = pthread_self();

    printf ("thread %lu: started\n", 
            thr_id);
    fflush (stdout);

    // I don't care what is in this buffer: nulls, whatever...it is a blob.
    buflen = BUFFER_SIZE;
    bufptr = malloc (buflen);

    if (bufptr == NULL)
    {
        printf("unable to malloc the blob buffer\n");
        pthread_exit (NULL);
    }

    myData = mysql_init(NULL);

    if (myData == NULL)
    {
        printf("mysql_init() failed\n");
        free (bufptr);
        pthread_exit (NULL);
    }

    if (mysql_real_connect (myData, db_host, db_user, 
                            db_password, db_database, db_port, NULL, 0) == NULL)
    {
        printf("mysql_real_connect() failed, errno=%d, error=%s\n",
               mysql_errno(myData), mysql_error(myData));
        free (bufptr);
        mysql_close (myData);
        pthread_exit (NULL);
    }

    if (mysql_autocommit (myData, 0) != 0)
    {
        printf("mysql_autocommit() failed, errno=%d, error=%s\n",
               mysql_errno(myData), mysql_error(myData));
        free (bufptr);
        mysql_close (myData);
        pthread_exit (NULL);
    }

    body_insert = mysql_prepare (myData, insertMsgBlob, strlen(insertMsgBlob));

    if (!body_insert)
    {
        printf ("mysql_prepare(insertMsgBlob) failed.  errno=%d, error=%s\n",
                mysql_errno(myData), mysql_error(myData));
        free (bufptr);
        mysql_close (myData);
        pthread_exit (NULL);
    }

    printf ("thread %lu: connected to the database...beginning the inserts\n", 
            thr_id);
    fflush (stdout);

    for (ndx = 0; ndx < num_records_to_insert; ndx++)
    {
        memset (&bind, 0, sizeof(bind));
        bind.buffer_type   = MYSQL_TYPE_LONG_BLOB;
        bind.buffer        = bufptr;
        bind.buffer_length = buflen;
        bind.length        = &buflen;

        if (mysql_bind_param (body_insert, &bind) != 0)
        {
            printf ("thread %lu: record# %d, mysql_stmt_bind_param() failed, errno=%d, error=%s\n", 
                    thr_id, ndx+1, mysql_stmt_errno(body_insert), mysql_stmt_error(body_insert));
            break;
        }

        //pthread_mutex_lock (&db_mutex);

        if (mysql_execute(body_insert) != 0)
        {
            printf ("thread %lu: record# %d, mysql_stmt_execute() failed, errno=%d, error=%s\n", 
                    thr_id, ndx+1, mysql_stmt_errno(body_insert), mysql_stmt_error(body_insert));
            //pthread_mutex_unlock (&db_mutex);
            mysql_rollback(myData);
            break;
        }

        msg_id = mysql_insert_id (myData);

        if (msg_id == 0)
        {
            printf ("thread %lu: record# %d, mysql_insert_id() returned 0, should be > 0\n" \
                    "     did it fail???, mysql_errno()=%d, mysql_error()=%s\n", 
                    thr_id, ndx+1,
                    mysql_errno(myData), mysql_error(myData));
            //pthread_mutex_unlock (&db_mutex);
            mysql_rollback(myData);
            break;
        }

        //pthread_mutex_unlock (&db_mutex);

        if (mysql_commit(myData) != 0)
        {
            printf ("thread %lu: record# %d, mysql_commit() failed, errno=%d, error=%s\n", 
                    thr_id, ndx+1, mysql_errno(myData), mysql_error(myData));
            mysql_rollback(myData);
            break;
        }
    } // for

    free (bufptr);
    mysql_close (myData);

    printf ("thread %lu: is exiting!\n", thr_id);
    fflush (stdout);

    pthread_exit (NULL);
} // worker_thread

/*******************************************************************************
**     Function:  
**
**  Description:  
**
**   Parameters:
**
** Return Value:
**
*******************************************************************************/
static void usage( void )
{
    fprintf (stderr, "\nusage: mysqltest -t # -n # [-h dbhost] [-p #]\n");
    fprintf (stderr, "where:\n");
    fprintf (stderr, "    -t #       is the # of threads to create\n");
    fprintf (stderr, "    -n #       is the # of records each thread will insert\n");
    fprintf (stderr, "    -h dbhost  database ip address (or hostname)\n");
    fprintf (stderr, "    -p #       is the database port #\n");
    exit( 1 );
}

/*******************************************************************************
**     Function:  
**
**  Description:  
**
**   Parameters:
**
** Return Value:
**
*******************************************************************************/
static void get_cmd_parms ( int argc, char *argv[] )
{
    int     cmd_line_opt;

    if (argc < 3)
    {
        usage();
    }

    while ((cmd_line_opt = getopt(argc, argv, "h:p:t:n:")) > 0) 
    {
        switch (cmd_line_opt)
        {
            case 'h': 
                strcpy (db_host, optarg);
                break;

            case 'p':
                db_port = atoi(optarg);
                break;

            case 't':
                num_threads_to_create = atoi(optarg);
                break;

            case 'n':
                num_records_to_insert = atoi(optarg);
                break;

            default: 
                usage();
        } // switch
    } // while

    if (db_host [0] == '\0')
    {
        printf ("\n\nMust supply the database host name/ip\n\n");
        usage ();
    }
        
    if (db_port == 0)
    {
        printf ("invalid database port # %s\n", optarg);
        usage ();
    }

    if ((num_threads_to_create < MIN_NUM_THREADS) || 
        (num_threads_to_create > MAX_NUM_THREADS))
    {
        printf ("\n\nthe number of threads must be %d to %d\n\n", 
                MIN_NUM_THREADS, MAX_NUM_THREADS);
        usage();
    }

    if ((num_records_to_insert < MIN_NUM_RECORDS) || 
        (num_records_to_insert > MAX_NUM_RECORDS))
    {
        printf ("\n\nthe number of records must be %d to %d\n\n", 
                MIN_NUM_RECORDS, MAX_NUM_RECORDS);
        usage ();
    }
}

/*******************************************************************************
**     Function:  
**
**  Description:  
**
**   Parameters:
**
** Return Value:
**
*******************************************************************************/
int main ( int argc, char *argv[] )
{
    int                 rc;
    int                 ndx;
    pthread_t           thr_ids [MAX_NUM_THREADS];
    pthread_attr_t      thr_attr;

    get_cmd_parms (argc, argv);

    //
    // create the worker threads in a joinable state.
    //
    printf ("main: creating threads...\n");
    fflush (stdout);

    pthread_attr_init (&thr_attr);

    for (ndx = 0; ndx < num_threads_to_create; ndx++)
    {
        if ((rc = pthread_create (&thr_ids[ndx], &thr_attr, worker_thread, NULL)) != 0) 
        {
            printf ("pthread_create() failed. rc=%d\n", rc);
            return (1);
        }
    }

    printf ("main: threads created...join()ing them...\n");
    fflush (stdout);

    for (ndx = 0; ndx < num_threads_to_create; ndx++)
    {
        if ((rc = pthread_join (thr_ids[ndx], NULL)) != 0) 
        {
            printf ("pthread_join(%lu) failed. rc=%d\n", thr_ids[ndx], rc);
        }
    }

    return (0);
}

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Hi, Darryl!

Thank you very much for your test case! I was able to repeat behaviour you have described with 4.1.1 version of MySQL. 
But after some digging in related code and since I was not able to repeat it with latest 4.1 snapshot I believe that this bug was fixed some time ago.
So I am closing this bug for now.

Feel free to reopen it in case if it turns out that this bug is still repeatable for you in 4.1.2 (which should be out really soon).

Thank you once again!