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: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.1.1 alpha | OS: | Linux (Redhat 9) |
Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[19 May 2004 1:34]
Darryl Rodden
[20 May 2004 17:42]
Dmitry Lenev
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).
[20 May 2004 17:48]
Darryl Rodden
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.
[20 May 2004 18:06]
Darryl Rodden
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));
[25 May 2004 14:07]
Dmitry Lenev
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!
[25 May 2004 23:19]
Darryl Rodden
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); }
[29 May 2004 10:34]
Dmitry Lenev
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!