bk commit into 5.1 tree (aivanov:1.2134) BUG#9680

[Alex Ivanov Notebook]

Below is the list of changes that have just been committed into a local
5.1 repository of alexi. When alexi 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
  1.2134 06/02/09 20:43:10 aivanov@stripped +9 -0
  Fixed BUG#9680: Wrong error from cascading update
    Applied 9680.patch (by Osku Salerma)

  storage/innobase/row/row0mysql.c
    1.121 06/02/09 20:43:04 aivanov@stripped +3 -2
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  storage/innobase/row/row0ins.c
    1.76 06/02/09 20:43:04 aivanov@stripped +15 -0
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  storage/innobase/include/db0err.h
    1.12 06/02/09 20:43:04 aivanov@stripped +4 -0
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  sql/share/errmsg.txt
    1.80 06/02/09 20:43:04 aivanov@stripped +2 -0
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  sql/handler.cc
    1.214 06/02/09 20:43:04 aivanov@stripped +27 -2
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  sql/ha_innodb.cc
    1.255 06/02/09 20:43:04 aivanov@stripped +4 -0
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  mysql-test/t/innodb.test
    1.127 06/02/09 20:43:04 aivanov@stripped +29 -0
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  mysql-test/r/innodb.result
    1.158 06/02/09 20:43:04 aivanov@stripped +18 -0
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

  include/my_base.h
    1.83 06/02/09 20:43:04 aivanov@stripped +4 -1
    Fixed BUG#9680: Wrong error from cascading update
      Applied 9680.patch (by Osku Salerma)

# 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:	aivanov
# Host:	mysqld.creware.com
# Root:	/home/alexi/innodb/mysql-5.1-9680

--- 1.82/include/my_base.h	2006-02-02 16:48:03 +03:00
+++ 1.83/include/my_base.h	2006-02-09 20:43:04 +03:00
@@ -367,8 +367,11 @@
                                           given value */
 #define HA_ERR_RBR_LOGGING_FAILED 161  /* Row-based binlogging of row failed */
 #define HA_ERR_DROP_INDEX_FK      162  /* Index needed in foreign key constr. */
+#define HA_ERR_FOREIGN_DUPLICATE_KEY 163 /* Upholding foreign key constraints
+                                            would lead to a duplicate key
+                                            error in some other table. */
 
-#define HA_ERR_LAST               162  /* Copy last error no */
+#define HA_ERR_LAST               163  /* Copy last error no */
 
 /* Add error numbers before HA_ERR_LAST and change it accordingly. */
 #define HA_ERR_ERRORS            (HA_ERR_LAST - HA_ERR_FIRST + 1)

--- 1.213/sql/handler.cc	2006-01-20 00:40:51 +03:00
+++ 1.214/sql/handler.cc	2006-02-09 20:43:04 +03:00
@@ -358,6 +358,7 @@
   SETMSG(HA_ERR_TABLE_EXIST,            ER(ER_TABLE_EXISTS_ERROR));
   SETMSG(HA_ERR_NO_CONNECTION,          "Could not connect to storage engine");
   SETMSG(HA_ERR_TABLE_DEF_CHANGED,      ER(ER_TABLE_DEF_CHANGED));
+  SETMSG(HA_ERR_FOREIGN_DUPLICATE_KEY,  "FK constraint would lead to duplicate key");
 
   /* Register the error messages for use with my_error(). */
   return my_error_register(errmsgs, HA_ERR_FIRST, HA_ERR_LAST);
@@ -1868,6 +1869,29 @@
     textno=ER_DUP_KEY;
     break;
   }
+  case HA_ERR_FOREIGN_DUPLICATE_KEY:
+  {
+    uint key_nr= get_dup_key(error);
+    if ((int) key_nr >= 0)
+    {
+      /* Write the key in the error message */
+      char key[MAX_KEY_LENGTH];
+      String str(key,sizeof(key),system_charset_info);
+      /* Table is opened and defined at this point */
+      key_unpack(&str,table,(uint) key_nr);
+      uint max_length= MYSQL_ERRMSG_SIZE-(uint) strlen(ER(ER_FOREIGN_DUPLICATE_KEY));
+      if (str.length() >= max_length)
+      {
+        str.length(max_length-4);
+        str.append(STRING_WITH_LEN("..."));
+      }
+      my_error(ER_FOREIGN_DUPLICATE_KEY, MYF(0), table_share->table_name.str,
+        str.c_ptr(), key_nr+1);
+      DBUG_VOID_RETURN;
+    }
+    textno= ER_DUP_KEY;
+    break;
+  }
   case HA_ERR_NULL_IN_SPATIAL:
     textno= ER_UNKNOWN_ERROR;
     break;
@@ -2003,8 +2027,9 @@
 {
   DBUG_ENTER("handler::get_dup_key");
   table->file->errkey  = (uint) -1;
-  if (error == HA_ERR_FOUND_DUPP_KEY || error == HA_ERR_FOUND_DUPP_UNIQUE ||
-      error == HA_ERR_NULL_IN_SPATIAL || error == HA_ERR_DROP_INDEX_FK)
+  if (error == HA_ERR_FOUND_DUPP_KEY || error == HA_ERR_FOREIGN_DUPLICATE_KEY ||
+      error == HA_ERR_FOUND_DUPP_UNIQUE || error == HA_ERR_NULL_IN_SPATIAL ||
+      error == HA_ERR_DROP_INDEX_FK)
     info(HA_STATUS_ERRKEY | HA_STATUS_NO_LOCK);
   DBUG_RETURN(table->file->errkey);
 }

--- 1.79/sql/share/errmsg.txt	2006-02-02 23:25:37 +03:00
+++ 1.80/sql/share/errmsg.txt	2006-02-09 20:43:04 +03:00
@@ -5800,3 +5800,5 @@
         eng "You can't use usual read lock with log tables. Try READ LOCAL instead."
 ER_SP_WRONG_NAME 42000
 	eng "Incorrect routine name '%-.64s'"
+ER_FOREIGN_DUPLICATE_KEY 23000 S1009
+	eng "Upholding foreign key constraints for table '%.64s', entry '%-.64s', key %d would
lead to a duplicate entry"

--- 1.11/storage/innobase/include/db0err.h	2005-04-08 03:37:31 +04:00
+++ 1.12/storage/innobase/include/db0err.h	2006-02-09 20:43:04 +03:00
@@ -57,6 +57,10 @@
 					buffer pool (for big transactions,
 					InnoDB stores the lock structs in the
 					buffer pool) */
+#define DB_FOREIGN_DUPLICATE_KEY 46	/* foreign key constraints
+					activated by the operation would
+					lead to a duplicate key in some
+					table */
 
 /* The following are partial failure codes */
 #define DB_FAIL 		1000

--- 1.75/storage/innobase/row/row0ins.c	2006-02-02 01:04:02 +03:00
+++ 1.76/storage/innobase/row/row0ins.c	2006-02-09 20:43:04 +03:00
@@ -1376,6 +1376,21 @@
 						thr, foreign, &pcur, entry,
 									&mtr);
 					if (err != DB_SUCCESS) {
+						/* Since reporting a plain
+						"duplicate key" error
+						message to the user in
+						cases where a long CASCADE
+						operation would lead to a
+						duplicate key in some
+						other table is very
+						confusing, map duplicate
+						key errors resulting from
+						FK constraints to a
+						separate error code. */
+				    
+						if (err == DB_DUPLICATE_KEY) {
+						    err = DB_FOREIGN_DUPLICATE_KEY;
+						}
 
 						break;
 					}

--- 1.120/storage/innobase/row/row0mysql.c	2006-02-02 01:04:02 +03:00
+++ 1.121/storage/innobase/row/row0mysql.c	2006-02-09 20:43:04 +03:00
@@ -473,8 +473,9 @@
 	ut_a(err != DB_SUCCESS);
 	
 	trx->error_state = DB_SUCCESS;
-
-	if (err == DB_DUPLICATE_KEY) {
+	
+	if ((err == DB_DUPLICATE_KEY)
+		|| (err == DB_FOREIGN_DUPLICATE_KEY)) {
            	if (savept) {
 			/* Roll back the latest, possibly incomplete
 			insertion or update */

--- 1.157/mysql-test/r/innodb.result	2006-02-02 17:18:05 +03:00
+++ 1.158/mysql-test/r/innodb.result	2006-02-09 20:43:04 +03:00
@@ -2758,3 +2758,21 @@
 key (a,b,c,d,e)) engine=innodb;
 ERROR 42000: Specified key was too long; max key length is 3072 bytes
 End of 5.0 tests
+CREATE TABLE t1 (
+field1 varchar(8) NOT NULL DEFAULT '',
+field2 varchar(8) NOT NULL DEFAULT '',
+PRIMARY KEY  (field1, field2)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
+FOREIGN KEY (field1) REFERENCES t1 (field1)
+ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('old', 'somevalu');
+INSERT INTO t1 VALUES ('other', 'anyvalue');
+INSERT INTO t2 VALUES ('old');
+INSERT INTO t2 VALUES ('other');
+UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
+ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu',
key 1 would lead to a duplicate entry
+DROP TABLE t2;
+DROP TABLE t1;

--- 1.126/mysql-test/t/innodb.test	2006-02-02 17:18:18 +03:00
+++ 1.127/mysql-test/t/innodb.test	2006-02-09 20:43:04 +03:00
@@ -1715,3 +1715,32 @@
                  key (a,b,c,d,e)) engine=innodb;
 
 --echo End of 5.0 tests
+
+#
+# Test that cascading updates leading to duplicate keys give the correct
+# error message (bug #9680)
+#
+
+CREATE TABLE t1 (
+  field1 varchar(8) NOT NULL DEFAULT '',
+  field2 varchar(8) NOT NULL DEFAULT '',
+  PRIMARY KEY  (field1, field2)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+  field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
+  FOREIGN KEY (field1) REFERENCES t1 (field1)
+    ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES ('old', 'somevalu');
+INSERT INTO t1 VALUES ('other', 'anyvalue');
+
+INSERT INTO t2 VALUES ('old');
+INSERT INTO t2 VALUES ('other');
+
+--error ER_FOREIGN_DUPLICATE_KEY
+UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
+
+DROP TABLE t2;
+DROP TABLE t1;

--- 1.254/sql/ha_innodb.cc	2006-02-02 17:18:44 +03:00
+++ 1.255/sql/ha_innodb.cc	2006-02-09 20:43:04 +03:00
@@ -465,6 +465,10 @@
 
     		return(HA_ERR_FOUND_DUPP_KEY);
 
+  	} else if (error == (int) DB_FOREIGN_DUPLICATE_KEY) {
+
+		return(HA_ERR_FOREIGN_DUPLICATE_KEY);
+
  	} else if (error == (int) DB_RECORD_NOT_FOUND) {
 
     		return(HA_ERR_NO_ACTIVE_RECORD);