Should read selects on one mysql session cause inserts on another to fail?
am 15.01.2004 15:26:54 von Ashok VadekarI'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
#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");
}
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org