Since few days am doing a benchmark of mysql 4.1 and 5.1 on different hardware using sysbench so that I can test MySQL along with threads, cpu and IO related. But most of the time, when I mix the OLTP test with mysql to have writes; then the test bails when there is a duplicate key entry:

1
2
3

Threads started!
ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry 'XXXXX' for key X
FATAL: database error, exiting...

as I run with multiple threads using a shell script; which runs for about 12-16 hrs…so I did not wanted some tests to bail in middle…as having a duplicate key for the updates is fine … so applied a simple patch by having extra argument –mysql-ignore-duplicates, which makes the test to continue even on the duplicate key errors … another option is to reissue the query with different numbers, but thought this is enough for base testing.

Here is the patch file for mysql-ignore-duplicates and can also be downloaded from here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- sysbench-0.5.0/sysbench/drivers/mysql/drv_mysql.c   2006-10-10 12:22:51.000000000 -0700
+++ venu-patch/sysbench/drivers/mysql/drv_mysql.c       2007-12-22 02:30:36.492383000 -0800
@@ -65,6 +65,7 @@
    SB_ARG_TYPE_STRING, "auto"},
   {"mysql-ssl", "use SSL connections, if available in the client library", SB_ARG_TYPE_FLAG, "off"},
   {"myisam-max-rows", "max-rows parameter for MyISAM tables", SB_ARG_TYPE_INT, "1000000"},
+  {"mysql-ignore-duplicates", "Ignore the duplicate key errors", SB_ARG_TYPE_FLAG, "off"},
 
   {NULL, NULL, SB_ARG_TYPE_NULL, NULL}
 };
@@ -87,6 +88,7 @@
   unsigned int       myisam_max_rows;
   mysql_drv_trx_t    engine_trx;
   unsigned int       use_ssl;
+  unsigned int       ignore_duplicates;
 } mysql_drv_args_t;
 
 #ifdef HAVE_PS
@@ -232,6 +234,10 @@
   args.db = sb_get_value_string("mysql-db");
   args.myisam_max_rows = sb_get_value_int("myisam-max-rows");
   args.use_ssl = sb_get_value_flag("mysql-ssl");
+  args.ignore_duplicates = sb_get_value_flag("mysql-ignore-duplicates");
+
+  if (args.ignore_duplicates)
+       log_text(LOG_ALERT, "WARNING: Duplicate Key errors will be ignored");
 
   use_ps = 0;
 #ifdef HAVE_PS
@@ -640,6 +646,12 @@
       if (rc == ER_LOCK_DEADLOCK || rc == ER_LOCK_WAIT_TIMEOUT ||
           rc == ER_CHECKREAD)
         return SB_DB_ERROR_DEADLOCK;
+         if (args.ignore_duplicates && rc == ER_DUP_ENTRY) {
+            log_text(LOG_ALERT, "WARNING: Ignoring the duplicate error on execute mysql_stmt_execute(): Err%d %s",
+                    mysql_errno(con->ptr),
+                    mysql_error(con->ptr));
+            return SB_DB_ERROR_NONE;
+         }
       log_text(LOG_ALERT, "failed to execute mysql_stmt_execute(): Err%d %s",
                mysql_errno(con->ptr),
                mysql_error(con->ptr));