diff -urp 5.1-9680-orig/include/my_base.h 5.1-9680/include/my_base.h --- 5.1-9680-orig/include/my_base.h 2006-01-18 15:23:17.000000000 +0200 +++ 5.1-9680/include/my_base.h 2006-01-19 16:06:01.830797968 +0200 @@ -362,8 +362,11 @@ enum ha_base_keytype { 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) diff -urp 5.1-9680-orig/mysql-test/r/innodb.result 5.1-9680/mysql-test/r/innodb.result --- 5.1-9680-orig/mysql-test/r/innodb.result 2006-01-18 15:23:47.000000000 +0200 +++ 5.1-9680/mysql-test/r/innodb.result 2006-01-19 15:47:19.587404896 +0200 @@ -2777,3 +2777,21 @@ e varchar(255) character set utf8, 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; diff -urp 5.1-9680-orig/mysql-test/t/innodb.test 5.1-9680/mysql-test/t/innodb.test --- 5.1-9680-orig/mysql-test/t/innodb.test 2006-01-18 15:24:18.000000000 +0200 +++ 5.1-9680/mysql-test/t/innodb.test 2006-01-19 15:45:37.228965744 +0200 @@ -1741,3 +1741,32 @@ create table t1 (a varchar(255) characte 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; diff -urp 5.1-9680-orig/sql/ha_innodb.cc 5.1-9680/sql/ha_innodb.cc --- 5.1-9680-orig/sql/ha_innodb.cc 2006-01-18 15:25:03.000000000 +0200 +++ 5.1-9680/sql/ha_innodb.cc 2006-01-19 16:06:32.395151480 +0200 @@ -464,6 +464,10 @@ convert_error_code_to_mysql( 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); diff -urp 5.1-9680-orig/sql/handler.cc 5.1-9680/sql/handler.cc --- 5.1-9680-orig/sql/handler.cc 2006-01-18 15:25:12.000000000 +0200 +++ 5.1-9680/sql/handler.cc 2006-01-19 16:05:17.641515760 +0200 @@ -358,6 +358,7 @@ static int ha_init_errors(void) 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); @@ -1862,6 +1863,29 @@ void handler::print_error(int error, myf 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; @@ -1997,8 +2021,9 @@ uint handler::get_dup_key(int error) { 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); } diff -urp 5.1-9680-orig/sql/share/errmsg.txt 5.1-9680/sql/share/errmsg.txt --- 5.1-9680-orig/sql/share/errmsg.txt 2006-01-18 15:26:00.000000000 +0200 +++ 5.1-9680/sql/share/errmsg.txt 2006-01-19 11:16:57.000000000 +0200 @@ -5794,3 +5794,5 @@ ER_EVENT_DATA_TOO_LONG ER_DROP_INDEX_FK eng "Cannot drop index '%-.64s': needed in a foreign key constraint" ger "Kann Index '%-.64s' nicht löschen: wird für einen einen Fremdschlüssel benötigt" +ER_FOREIGN_DUPLICATE_KEY 23000 S1009 + eng "Upholding foreign key constraints for table '%.64s', entry '%-.64s', key %d would lead to a duplicate entry" diff -urp 5.1-9680-orig/storage/innobase/include/db0err.h 5.1-9680/storage/innobase/include/db0err.h --- 5.1-9680-orig/storage/innobase/include/db0err.h 2006-01-18 15:27:09.000000000 +0200 +++ 5.1-9680/storage/innobase/include/db0err.h 2006-01-18 16:27:22.000000000 +0200 @@ -57,6 +57,10 @@ Created 5/24/1996 Heikki Tuuri 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 diff -urp 5.1-9680-orig/storage/innobase/row/row0ins.c 5.1-9680/storage/innobase/row/row0ins.c --- 5.1-9680-orig/storage/innobase/row/row0ins.c 2006-01-18 15:27:21.000000000 +0200 +++ 5.1-9680/storage/innobase/row/row0ins.c 2006-01-19 16:03:54.284188000 +0200 @@ -1376,6 +1376,21 @@ run_again: 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; } diff -urp 5.1-9680-orig/storage/innobase/row/row0mysql.c 5.1-9680/storage/innobase/row/row0mysql.c --- 5.1-9680-orig/storage/innobase/row/row0mysql.c 2006-01-18 15:27:22.000000000 +0200 +++ 5.1-9680/storage/innobase/row/row0mysql.c 2006-01-18 16:35:14.000000000 +0200 @@ -473,8 +473,9 @@ handle_new_error: 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 */