I'm trying to evaluate mySQL for a commercial use. The first thing I want to do is use prepared statements for a large number of inserts. At the same time, I will have other threads reading from the database.

In my initial testing, when I use a mysql session to do a select on the table that the inserting session is using, it causes the inserting session to fail with a Duplicate key error. The inserting session sequentially inserts incrementing key values, so it can't really be a duplicate insert.

To recreate this behaviour using the code below, compile it using the compile line at the top. Run it (providing a valid user and password and an iteration count). set the iteration count large enough that you can switch windows to run the read query in another window before the binary finishes.

In mysql session, type: connect test select max(id) from test; Repeat the select statement until the binary spits out a failure message.

It appears that the mysql server is 1) allowing the read query to impact the execution of the insert, and 2) producing an error message that is not particularly indicative of the reason for the failure. 3) causing persistent damage to the inserting session such that subsequent inserts also fail.

Has anyone run into similar issues? If so, any suggestions?

Source for inserting into table (to end of message): // gcc s.c -L /usr/local/mysql/lib -lmysqlclient -lsocket -lnsl -lm

#include <stdio.h> #include "/usr/local/mysql/include/mysql.h"

#define STRING_SIZE 50

#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test" #define CREATE_SAMPLE_TABLE "CREATE TABLE test(id INT KEY,\ info char(16),\ lastchange TIMESTAMP)"// TYPE=InnoDB" #define INSERT_SAMPLE "INSERT INTO test(id,info) VALUES(?,?)"

main (int argc, char **argv) { MYSQL_STMT *stmt; MYSQL_BIND bind[2]; my_ulonglong affected_rows; int param_count; int int_data, int2_data; char str_data[STRING_SIZE]; unsigned long str_length; my_bool is_null; int rc, count; char *pwd,*usr;

MYSQL *mysql;

if (argc < 4) { fprintf (stderr, "Usage: %s user pwd iterations\n", argv[0]); exit (1); } usr = argv[1]; pwd = argv[2]; count = atoi (argv[3]);

mysql = mysql_init (NULL); mysql_options (mysql, MYSQL_READ_DEFAULT_GROUP, "test"); if (!mysql_real_connect (mysql, "localhost", usr, pwd, "test", 0, NULL, 0)) { fprintf (stderr, "Failed to connect, error: %s\n", mysql_error (mysql)); exit (0); }

if (mysql_query (mysql, DROP_SAMPLE_TABLE)) { fprintf (stderr, " DROP TABLE failed\n"); fprintf (stderr, " %s\n", mysql_error (mysql)); exit (0); }

if (mysql_query (mysql, CREATE_SAMPLE_TABLE)) { fprintf (stderr, " CREATE TABLE failed\n"); fprintf (stderr, " %s\n", mysql_error (mysql)); exit (0); }

/* Prepare an INSERT query with 2 parameters */ /* (the TIMESTAMP column is not named; it will */ /* be set to the current date and time) */ stmt = mysql_prepare (mysql, INSERT_SAMPLE, strlen (INSERT_SAMPLE)); if (!stmt) { fprintf (stderr, " mysql_prepare(), INSERT failed\n"); fprintf (stderr, " %s\n", mysql_error (mysql)); exit (0); } fprintf (stdout, " prepare, INSERT successful\n");

/* Get the parameter count from the statement */ param_count = mysql_param_count (stmt); fprintf (stdout, " total parameters in INSERT: %d\n", param_count);

if (param_count != 2) /* validate parameter count */ { fprintf (stderr, " invalid parameter count returned by MySQL\n"); exit (0); }

/* Bind the data for all 2 parameters */

/* INTEGER PARAM */ /* This is a number type, so there is no need to specify buffer_length */ bind[0].buffer_type = MYSQL_TYPE_LONG; bind[0].buffer = (char *) &int_data; bind[0].is_null = 0; bind[0].length = 0;

/* STRING PARAM */ bind[1].buffer_type = MYSQL_TYPE_STRING; bind[1].buffer = (char *) &str_data; bind[1].is_null = 0; bind[1].length = &str_length;

/* Bind the buffers */ if (mysql_bind_param (stmt, bind)) { fprintf (stderr, " mysql_bind_param() failed\n"); fprintf (stderr, " %s\n", mysql_stmt_error (stmt)); exit (0); }

if (mysql_autocommit (mysql, 0)) { fprintf (stderr, " mysql_autocommit() failed\n"); exit (0); } for (int_data = 1; int_data < count; int_data++) { int2_data = -int_data; sprintf (str_data, "%08x%08x", int_data, int_data); str_length = strlen (str_data);

/* Execute the INSERT statement - 1 */ if (rc = mysql_execute (stmt)) { fprintf (stderr, " mysql_execute(), failed with %d\n", rc); fprintf (stderr, " %s\n", mysql_stmt_error (stmt)); //exit(0); //Try to commit what is already in place, and see if we can continue if (mysql_commit (mysql)) { fprintf (stderr, " mysql_commit() failed\n"); exit (0); } } else { /* Get the total number of affected rows */ affected_rows = mysql_stmt_affected_rows (stmt); //fprintf(stdout, "[row %d] total affected rows(insert 1): %ld\n",
int_data,(int)affected_rows);

if (affected_rows != 1) /* validate affected rows */ { fprintf (stderr, " invalid affected rows by MySQL (1 != %d) on entry %d\n", (int) affected_rows, int_data); //exit(0); } } // Periodically commit: if (!(int_data & 127) && mysql_commit (mysql)) { fprintf (stderr, " mysql_commit() failed\n"); exit (0); } } // Commit last set of inserts: if (mysql_commit (mysql)) { fprintf (stderr, " mysql_commit() failed\n"); exit (0); }

/* Close the statement */ if (mysql_stmt_close (stmt)) { fprintf (stderr, " failed while closing the statement\n"); fprintf (stderr, " %s\n", mysql_stmt_error (stmt)); exit (0); }

puts ("done"); }