bk commit into 5.0 tree (antony:1.2493) BUG#25511

[antony]

Below is the list of changes that have just been committed into a local
5.0 repository of antony. When antony does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-06-21 01:57:36-07:00, antony@stripped +4 -0
  Bug#25511
    "Federated INSERT failures"
    Federated does not correctly handle "INSERT ... ON DUPLICATE KEY UPDATE"
    If it is in effect, we must permit mysqld to retry the insert operation
    by translating the error code returned. We also check if the local
    primary key definition is adequate to identify a specific row on the
    remote server by its primary key.
    This patch builds on Bug29019

  mysql-test/r/federated.result@stripped, 2007-06-21 01:57:27-07:00, antony@stripped +16 -0
    test for bug 25511

  mysql-test/t/federated.test@stripped, 2007-06-21 01:57:27-07:00, antony@stripped +30 -0
    test for bug 25511

  sql/ha_federated.cc@stripped, 2007-06-21 01:57:28-07:00, antony@stripped +76 -11
    bug25511
      If a "ON DUPLICATE KEY UPDATE" is in effect while writing a row,
      we need to check if the remote server has a compatible primary
      key and allow mysqld to reattempt the operation as required.

  sql/ha_federated.h@stripped, 2007-06-21 01:57:28-07:00, antony@stripped +2 -0
    bug25511
      new member variables:
        retrieve_primary_key, remote_primary_key_checked
      new method:
        perform_primary_key_check()

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	antony
# Host:	ppcg5.local
# Root:	/private/Network/Servers/anubis.xiphis.org/home/antony/work/p2-bug25511.2

--- 1.41/mysql-test/r/federated.result	2007-06-20 17:09:13 -07:00
+++ 1.42/mysql-test/r/federated.result	2007-06-21 01:57:27 -07:00
@@ -1867,6 +1867,22 @@
 3	Curly
 drop table federated.t1;
 drop table federated.t1;
+create table federated.t1 (a int primary key, b int, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+create table federated.t1 (a int primary key, b int, c varchar(64))
+ENGINE=FEDERATED
+connection='mysql://root@stripped:12002/federated/t1';
+insert into federated.t1 values(1,3,"new") on duplicate key update b=b+100;
+select * from federated.t1;
+a	b	c
+1	102	original
+drop table federated.t1;
+create table federated.t1 (a int , b int primary key, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+insert into federated.t1 values(1,2,"new") on duplicate key update b=b+100;
+ERROR 23000: Can't write; duplicate key in table 't1'
+drop table federated.t1;
+drop table federated.t1;
 DROP TABLE IF EXISTS federated.t1;
 DROP DATABASE IF EXISTS federated;
 DROP TABLE IF EXISTS federated.t1;

--- 1.36/mysql-test/t/federated.test	2007-06-20 17:09:13 -07:00
+++ 1.37/mysql-test/t/federated.test	2007-06-21 01:57:27 -07:00
@@ -1602,4 +1602,34 @@
 connection slave;
 drop table federated.t1;
 
+
+#
+# Bug#25511 Federated INSERT failures
+#
+connection slave;
+create table federated.t1 (a int primary key, b int, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+connection master;
+eval create table federated.t1 (a int primary key, b int, c varchar(64))
+  ENGINE=FEDERATED
+  connection='mysql://root@stripped:$SLAVE_MYPORT/federated/t1';
+
+insert into federated.t1 values(1,3,"new") on duplicate key update b=b+100;
+select * from federated.t1;
+
+connection slave;
+drop table federated.t1;
+# if the primary key is not compatible, it doesn't work
+create table federated.t1 (a int , b int primary key, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+connection master;
+
+--error ER_DUP_KEY
+insert into federated.t1 values(1,2,"new") on duplicate key update b=b+100;
+
+drop table federated.t1;
+connection slave;
+drop table federated.t1;
+
+
 source include/federated_cleanup.inc;

--- 1.76/sql/ha_federated.cc	2007-06-20 17:09:13 -07:00
+++ 1.77/sql/ha_federated.cc	2007-06-21 01:57:28 -07:00
@@ -1614,7 +1614,7 @@
   */
   if (replace_duplicates)
     insert_string.append(STRING_WITH_LEN("REPLACE INTO "));
-  else if (ignore_duplicates)
+  else if (ignore_duplicates && !retrieve_primary_key)
     insert_string.append(STRING_WITH_LEN("INSERT IGNORE INTO "));
   else
     insert_string.append(STRING_WITH_LEN("INSERT INTO "));
@@ -1820,7 +1820,7 @@
   update_string.length(0);
   where_string.length(0);
 
-  if (ignore_duplicates)
+  if (ignore_duplicates && !retrieve_primary_key)
     update_string.append(STRING_WITH_LEN("UPDATE IGNORE "));
   else
     update_string.append(STRING_WITH_LEN("UPDATE "));
@@ -2428,7 +2428,6 @@
 {
   char error_buffer[FEDERATED_QUERY_BUFFER_SIZE];
   char status_buf[FEDERATED_QUERY_BUFFER_SIZE];
-  char escaped_table_name[FEDERATED_QUERY_BUFFER_SIZE];
   int error;
   uint error_code;
   MYSQL_RES *result= 0;
@@ -2442,14 +2441,7 @@
   {
     status_query_string.length(0);
     status_query_string.append(FEDERATED_INFO);
-    status_query_string.append(FEDERATED_SQUOTE);
-
-    escape_string_for_mysql(&my_charset_bin, (char *)escaped_table_name,
-                            sizeof(escaped_table_name),
-                            share->table_name,
-                            share->table_name_length);
-    status_query_string.append(escaped_table_name);
-    status_query_string.append(FEDERATED_SQUOTE);
+    append_ident(&status_query_string, share->table_name,
share->table_name_length, '\'');
 
     if (mysql_real_query(mysql, status_query_string.ptr(),
                          status_query_string.length()))
@@ -2498,6 +2490,9 @@
     */
     block_size= 4096;
   }
+  
+  if (flag & HA_STATUS_ERRKEY && perform_primary_key_check())
+    errkey= table->s->primary_key;
 
   if (result)
     mysql_free_result(result);
@@ -2535,9 +2530,14 @@
   case HA_EXTRA_WRITE_CANNOT_REPLACE:
     replace_duplicates= FALSE;
     break;
+  case HA_EXTRA_RETRIEVE_PRIMARY_KEY:
+    retrieve_primary_key= TRUE;
+    break;
   case HA_EXTRA_RESET:
     ignore_duplicates= FALSE;
     replace_duplicates= FALSE;
+    remote_primary_key_checked= FALSE;
+    retrieve_primary_key= FALSE;
     break;
   default:
     /* do nothing */
@@ -2547,6 +2547,68 @@
 }
 
 
+/**
+  @brief Check the remote table's Primary Key declarations for compatibility.
+  @return
+    @retval FALSE       Not compatible
+    @retval TRUE        Compatible
+  @note The remote table is compatible if all columns used for its primary
+        key are used in the local table's primary key declaration.
+*/
+
+bool ha_federated::perform_primary_key_check()
+{
+  char query_buf[FEDERATED_QUERY_BUFFER_SIZE];
+  MYSQL_RES *result= 0;
+  MYSQL_ROW row;
+  String query_string(query_buf, sizeof(query_buf), &my_charset_bin);
+  DBUG_ENTER("ha_federated::perform_primary_key_check");
+  if (remote_primary_key_checked || !retrieve_primary_key ||
+      table->s->primary_key == MAX_KEY)
+    DBUG_RETURN(remote_primary_key_checked);
+  
+  query_string.length(0);
+  query_string.append(STRING_WITH_LEN("SHOW COLUMNS FROM "));
+  append_ident(&query_string, share->table_name, share->table_name_length);
+    
+  if (mysql_real_query(mysql, query_string.ptr(), query_string.length()))
+    goto error;
+  if (!(result= mysql_store_result(mysql)))
+    goto error;  
+  
+  /*
+    we check that all columns which compose a primary key on the
+    remote server are at least part of our primary key.
+  */
+  
+  while ((row= mysql_fetch_row(result)))
+  {
+    Field **field;
+    
+    if (strcmp(row[3], "PRI"))
+      continue;
+
+    for (field= table->field; *field; field++)
+      if (!strcmp(row[0], (*field)->field_name))
+        break;
+
+    if (!*field || !((*field)->flags & PRI_KEY_FLAG))
+      goto error;   
+  }
+
+  remote_primary_key_checked= TRUE;
+  mysql_free_result(result);
+  DBUG_RETURN(TRUE);
+  
+error:
+  mysql_free_result(result);    
+  push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                      HA_FEDERATED_ERROR_WITH_REMOTE_SYSTEM,
+                      "PRIMARY KEY declaration mismatch");
+  DBUG_RETURN(FALSE);
+}
+
+
 /*
   Used to delete all rows in a table. Both for cases of truncate and
   for cases where the optimizer realizes that all rows will be
@@ -2677,6 +2739,9 @@
   DBUG_ENTER("ha_federated::stash_remote_error()");
   remote_error_number= mysql_errno(mysql);
   strmake(remote_error_buf, mysql_error(mysql), sizeof(remote_error_buf)-1);
+  if (remote_error_number == ER_DUP_ENTRY || 
+      remote_error_number == ER_DUP_KEY)
+    DBUG_RETURN(HA_ERR_FOUND_DUPP_KEY);
   DBUG_RETURN(HA_FEDERATED_ERROR_WITH_REMOTE_SYSTEM);
 }
 

--- 1.31/sql/ha_federated.h	2007-06-20 17:09:14 -07:00
+++ 1.32/sql/ha_federated.h	2007-06-21 01:57:28 -07:00
@@ -153,6 +153,7 @@
   MYSQL_ROW_OFFSET current_position;  // Current position used by ::position()
   int remote_error_number;
   char remote_error_buf[FEDERATED_QUERY_BUFFER_SIZE];
+  bool retrieve_primary_key, remote_primary_key_checked;
   bool ignore_duplicates, replace_duplicates;
 
 private:
@@ -167,6 +168,7 @@
                              const key_range *end_key,
                              bool records_in_range);
   int stash_remote_error();
+  bool perform_primary_key_check();
 
 public:
   ha_federated(TABLE *table_arg);