Bug #29019 REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work
Submitted: 11 Jun 2007 14:53 Modified: 7 Jul 2007 19:01
Reporter: Antony Curtis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.0/5.1 OS:Any
Assigned to: Antony Curtis

[11 Jun 2007 14:53] Antony Curtis
Description:
When using INSERT IGNORE, REPLACE or UPDATE IGNORE, duplicate key errors are not peroperly ignored and with REPLACE, the existing row is not replaced.

How to repeat:
simply use INSERT IGNORE  or REPLACE or UPDATE IGNORE with a Federated table

Suggested fix:
Apply the following patch:

===== storage/federated/ha_federated.cc 1.101 vs edited =====
--- 1.101/storage/federated/ha_federated.cc	2007-06-11 06:52:17 -07:00
+++ edited/storage/federated/ha_federated.cc	2007-06-11 06:51:17 -07:00
@@ -1839,7 +1839,13 @@
   /*
     start both our field and field values strings
   */
-  insert_string.append(STRING_WITH_LEN("INSERT INTO `"));
+  if (!ignore_duplicates)
+    insert_string.append(STRING_WITH_LEN("INSERT INTO `"));
+  else
+  if (replace_duplicates)
+    insert_string.append(STRING_WITH_LEN("REPLACE INTO `"));
+  else
+    insert_string.append(STRING_WITH_LEN("INSERT IGNORE INTO `"));
   insert_string.append(share->table_name, share->table_name_length);
   insert_string.append('`');
   insert_string.append(STRING_WITH_LEN(" ("));
@@ -2053,7 +2059,10 @@
   update_string.length(0);
   where_string.length(0);
 
-  update_string.append(STRING_WITH_LEN("UPDATE `"));
+  if (!ignore_duplicates)
+    update_string.append(STRING_WITH_LEN("UPDATE `"));
+  else
+    update_string.append(STRING_WITH_LEN("UPDATE IGNORE `"));
   update_string.append(share->table_name);
   update_string.append(STRING_WITH_LEN("` SET "));
 
@@ -2767,6 +2776,45 @@
                             mysql_errno(mysql), mysql_error(mysql)));
   my_error(error_code, MYF(0), error_buffer);
   DBUG_RETURN(error_code);
+}
+
+
+/**
+  @brief Handles extra signals from MySQL server
+  @param operation Signal
+ */
+int ha_federated::extra(ha_extra_function operation)
+{
+  DBUG_ENTER("ha_federated::extra");
+  switch (operation) {
+  case HA_EXTRA_IGNORE_DUP_KEY:
+    ignore_duplicates= TRUE;
+	break;
+  case HA_EXTRA_WRITE_CAN_REPLACE:
+    replace_duplicates= TRUE;
+	break;
+  case HA_EXTRA_WRITE_CANNOT_REPLACE:
+    replace_duplicates= FALSE;
+    break;
+  case HA_EXTRA_NO_IGNORE_DUP_KEY:
+    reset();
+    break;
+  default:
+    /* do nothing */
+    DBUG_PRINT("info",("unhandled operation: %d", (uint) operation));
+  }
+  DBUG_RETURN(0);
+}
+
+
+/**
+  @brief Resets the storage engine to a 'newly opened' state.
+ */
+int ha_federated::reset()
+{
+  ignore_duplicates= FALSE;
+  replace_duplicates= FALSE;
+  return 0;
 }
 
 
===== storage/federated/ha_federated.h 1.46 vs edited =====
--- 1.46/storage/federated/ha_federated.h	2007-06-11 06:52:17 -07:00
+++ edited/storage/federated/ha_federated.h	2007-06-11 06:51:52 -07:00
@@ -88,6 +88,7 @@
   MYSQL_ROW_OFFSET current_position;  // Current position used by ::position()
   int remote_error_number;
   char remote_error_buf[FEDERATED_QUERY_BUFFER_SIZE];
+  bool ignore_duplicates, replace_duplicates;
 
 private:
   /*
@@ -216,6 +217,8 @@
   int rnd_pos(uchar *buf, uchar *pos);                            //required
   void position(const uchar *record);                            //required
   int info(uint);                                              //required
+  int extra(ha_extra_function operation);
+  int reset();
 
   void update_auto_increment(void);
   int repair(THD* thd, HA_CHECK_OPT* check_opt);
[11 Jun 2007 19:03] Miguel Solorzano
Thank you for the bug report.
[19 Jun 2007 23:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29138

ChangeSet@1.2492, 2007-06-19 16:58:27-07:00, antony@ppcg5.local +4 -0
  Bug#29019
    "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work"
    Federated does not record neccessary HA_EXTRA flags in order to
    support REPLACE/INSERT IGNORE/UPDATE IGNORE.
    Implement ::extra() to capture flags neccessary for functionality.
  New function append_ident() to better escape identifiers.
[20 Jun 2007 22:40] Brian Aker
Fix reuse of length variable and its good.
[21 Jun 2007 0:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29229

ChangeSet@1.2492, 2007-06-20 17:09:16-07:00, antony@ppcg5.local +4 -0
  Bug#29019
    "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work"
    Federated does not record neccessary HA_EXTRA flags in order to
    support REPLACE/INSERT IGNORE/UPDATE IGNORE.
    Implement ::extra() to capture flags neccessary for functionality.
  New function append_ident() to better escape identifiers.
[21 Jun 2007 18:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29330

ChangeSet@1.2492, 2007-06-21 11:20:24-07:00, antony@ppcg5.local +4 -0
  Bug#29019
    "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work"
    Federated does not record neccessary HA_EXTRA flags in order to
    support REPLACE/INSERT IGNORE/UPDATE IGNORE.
    Implement ::extra() to capture flags neccessary for functionality.
  New function append_ident() to better escape identifiers.
[28 Jun 2007 7:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29805

ChangeSet@1.2504, 2007-06-28 00:23:14-07:00, antony@ppcg5.local +4 -0
  Bug#29019
    "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work"
    Federated does not record neccessary HA_EXTRA flags in order to
    support REPLACE/INSERT IGNORE/UPDATE IGNORE.
    Implement ::extra() to capture flags neccessary for functionality.
  New function append_ident() to better escape identifiers consistantly.
[2 Jul 2007 16:14] Antony Curtis
queued to engines tree
[7 Jul 2007 16:34] Bugs System
Pushed into 5.1.21-beta
[7 Jul 2007 16:35] Bugs System
Pushed into 5.0.46
[7 Jul 2007 19:01] Paul Dubois
Noted in 5.0.46, 5.1.21 changelogs.

REPLACE, INSERT IGNORE, and UPDATE IGNORE did not work for FEDERATED 
tables.