bk commit into 5.0 tree (osku:1.1986) BUG#3443

[Osku Salerma]

Below is the list of changes that have just been committed into a local
5.0 repository of osku. When osku 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.1986 05/09/14 10:42:29 osku@127.(none) +13 -0
  Fix bug #3443, better foreign key error messsages.

  sql/sql_table.cc
    1.270 05/09/14 10:42:25 osku@127.(none) +2 -2
    Adapt to changes in foreign key error messages.

  sql/sql_parse.cc
    1.481 05/09/14 10:42:25 osku@127.(none) +2 -0
    Clear THD::detailed_error in mysql_execute_command.

  sql/sql_class.h
    1.263 05/09/14 10:42:25 osku@127.(none) +6 -0
    Add THD::detailed_error.

  sql/sql_class.cc
    1.207 05/09/14 10:42:25 osku@127.(none) +1 -0
    Initialize THD::detailed_error.

  sql/share/errmsg.txt
    1.43 05/09/14 10:42:25 osku@127.(none) +4 -0
    Add ER_ROW_IS_REFERENCED_2 and ER_NO_REFERENCED_ROW_2.

  sql/handler.cc
    1.186 05/09/14 10:42:25 osku@127.(none) +6 -6
    Add special case code in print_error for HA_ERR_ROW_IS_REFERENCED and
    HA_ERR_NO_REFERENCED_ROW.
    
    Change SETMSG to point to new error messages.

  sql/ha_innodb.cc
    1.256 05/09/14 10:42:25 osku@127.(none) +16 -1
    Add innobase_set_detailed_error.

  mysql-test/t/innodb.test
    1.107 05/09/14 10:42:25 osku@127.(none) +39 -3
    Add new tests, adapt existing ones whose error codes were changed.

  mysql-test/r/innodb.result
    1.132 05/09/14 10:42:25 osku@127.(none) +26 -3
    Add new tests, adapt existing error messages that were changed.

  innobase/row/row0ins.c
    1.70 05/09/14 10:42:25 osku@127.(none) +59 -4
    Add row_ins_set_detailed function and call it when needed.
    
    Adapt to changes in dict_print_info_on_foreign_key_in_create_format.

  innobase/include/dict0dict.h
    1.35 05/09/14 10:42:25 osku@127.(none) +4 -3
    Add 'add_newline' parameter to dict_print_info_on_foreign_key_in_create_format.

  innobase/dict/dict0dict.c
    1.67 05/09/14 10:42:25 osku@127.(none) +17 -6
    Add 'add_newline' parameter to dict_print_info_on_foreign_key_in_create_format.

  innobase/include/ha_innodb_callbacks.h
    1.1 05/09/14 10:31:22 osku@127.(none) +24 -0

  innobase/include/ha_innodb_callbacks.h
    1.0 05/09/14 10:31:22 osku@127.(none) +0 -0
    BitKeeper file /home/osku/mysql/5.0/fkem/innobase/include/ha_innodb_callbacks.h

# 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:	osku
# Host:	127.(none)
# Root:	/home/osku/mysql/5.0/fkem

--- 1.185/sql/handler.cc	2005-08-25 09:37:10 +03:00
+++ 1.186/sql/handler.cc	2005-09-14 10:42:25 +03:00
@@ -310,8 +310,8 @@
   SETMSG(HA_ERR_READ_ONLY_TRANSACTION,  ER(ER_READ_ONLY_TRANSACTION));
   SETMSG(HA_ERR_LOCK_DEADLOCK,          ER(ER_LOCK_DEADLOCK));
   SETMSG(HA_ERR_CANNOT_ADD_FOREIGN,     ER(ER_CANNOT_ADD_FOREIGN));
-  SETMSG(HA_ERR_NO_REFERENCED_ROW,      ER(ER_NO_REFERENCED_ROW));
-  SETMSG(HA_ERR_ROW_IS_REFERENCED,      ER(ER_ROW_IS_REFERENCED));
+  SETMSG(HA_ERR_NO_REFERENCED_ROW,      ER(ER_NO_REFERENCED_ROW_2));
+  SETMSG(HA_ERR_ROW_IS_REFERENCED,      ER(ER_ROW_IS_REFERENCED_2));
   SETMSG(HA_ERR_NO_SAVEPOINT,           "No savepoint with that name");
   SETMSG(HA_ERR_NON_UNIQUE_BLOCK_SIZE,  "Non unique key block size");
   SETMSG(HA_ERR_NO_SUCH_TABLE,          "No such table: '%.64s'");
@@ -1695,11 +1695,11 @@
     textno=ER_CANNOT_ADD_FOREIGN;
     break;
   case HA_ERR_ROW_IS_REFERENCED:
-    textno=ER_ROW_IS_REFERENCED;
-    break;
+    my_error(ER_ROW_IS_REFERENCED_2, MYF(0), current_thd->detailed_error);
+    DBUG_VOID_RETURN;
   case HA_ERR_NO_REFERENCED_ROW:
-    textno=ER_NO_REFERENCED_ROW;
-    break;
+    my_error(ER_NO_REFERENCED_ROW_2, MYF(0), current_thd->detailed_error);
+    DBUG_VOID_RETURN;
   case HA_ERR_TABLE_DEF_CHANGED:
     textno=ER_TABLE_DEF_CHANGED;
     break;

--- 1.206/sql/sql_class.cc	2005-09-03 02:13:10 +03:00
+++ 1.207/sql/sql_class.cc	2005-09-14 10:42:25 +03:00
@@ -210,6 +210,7 @@
   db_charset= global_system_variables.collation_database;
   bzero(ha_data, sizeof(ha_data));
   mysys_var=0;
+  detailed_error[0] = '\0';
   binlog_evt_union.do_union= FALSE;
 #ifndef DBUG_OFF
   dbug_sentry=THD_SENTRY_MAGIC;

--- 1.262/sql/sql_class.h	2005-09-02 16:21:08 +03:00
+++ 1.263/sql/sql_class.h	2005-09-14 10:42:25 +03:00
@@ -1174,6 +1174,12 @@
   thr_lock_type update_lock_default;
   delayed_insert *di;
 
+  /* Table handlers can store detailed error messages in this that are
+     added to the normal error message. That needs special-case code in
+     handler::print_error for all error codes that want to do this. It's
+     valid for this to be empty. */
+  char detailed_error[ERRMSGSIZE + 20];
+
   /* <> 0 if we are inside of trigger or stored function. */
   uint in_sub_stmt;
 

--- 1.480/sql/sql_parse.cc	2005-09-07 10:33:21 +03:00
+++ 1.481/sql/sql_parse.cc	2005-09-14 10:42:25 +03:00
@@ -2299,7 +2299,9 @@
   SELECT_LEX_UNIT *unit= &lex->unit;
   /* Saved variable value */
   DBUG_ENTER("mysql_execute_command");
+
   thd->net.no_send_error= 0;
+  thd->detailed_error[0] = '\0';
 
   /*
     In many cases first table of main SELECT_LEX have special meaning =>

--- 1.269/sql/sql_table.cc	2005-09-01 10:05:39 +03:00
+++ 1.270/sql/sql_table.cc	2005-09-14 10:42:25 +03:00
@@ -309,7 +309,7 @@
       my_printf_error(ER_BAD_TABLE_ERROR, ER(ER_BAD_TABLE_ERROR), MYF(0),
                       wrong_tables.c_ptr());
     else
-      my_message(ER_ROW_IS_REFERENCED, ER(ER_ROW_IS_REFERENCED), MYF(0));
+      my_error(ER_ROW_IS_REFERENCED_2, MYF(0), thd->detailed_error);
     error= 1;
   }
 
@@ -3476,7 +3476,7 @@
   if (lower_case_table_names)
     my_casedn_str(files_charset_info, tmp_name);
   if (new_db_type != old_db_type && !table->file->can_switch_engines()) {
-    my_error(ER_ROW_IS_REFERENCED, MYF(0));
+    my_error(ER_ROW_IS_REFERENCED_2, MYF(0), "");
     goto err;
   }
   create_info->db_type=new_db_type;

--- 1.42/sql/share/errmsg.txt	2005-09-03 02:25:41 +03:00
+++ 1.43/sql/share/errmsg.txt	2005-09-14 10:42:25 +03:00
@@ -5403,3 +5403,7 @@
         eng "The definition of table '%-.64s' prevents operation %s on table '%-.64s'."
 ER_PS_NO_RECURSION
         eng "The prepared statement contains a stored routine call that refers to that
same statement. It's not allowed to execute a prepared statement in such a recursive
manner"
+ER_ROW_IS_REFERENCED_2 23000
+	eng "Cannot delete or update a parent row: a foreign key constraint fails (%.192s)"
+ER_NO_REFERENCED_ROW_2 23000
+	eng "Cannot add or update a child row: a foreign key constraint fails (%.192s)"

--- 1.66/innobase/dict/dict0dict.c	2005-09-06 14:38:17 +03:00
+++ 1.67/innobase/dict/dict0dict.c	2005-09-14 10:42:25 +03:00
@@ -2189,7 +2189,7 @@
 	dict_foreign_error_report_low(file, fk->foreign_table_name);
 	fputs(msg, file);
 	fputs(" Constraint:\n", file);
-	dict_print_info_on_foreign_key_in_create_format(file, NULL, fk);
+	dict_print_info_on_foreign_key_in_create_format(file, NULL, fk, TRUE);
 	if (fk->foreign_index) {
 		fputs("\nThe index in the foreign key in table is ", file);
 		ut_print_name(file, NULL, fk->foreign_index->name);
@@ -4330,9 +4330,10 @@
 void
 dict_print_info_on_foreign_key_in_create_format(
 /*============================================*/
-	FILE*		file,	/* in: file where to print */
-	trx_t*		trx,	/* in: transaction */
-	dict_foreign_t*	foreign)/* in: foreign key constraint */
+	FILE*		file,		/* in: file where to print */
+	trx_t*		trx,		/* in: transaction */
+	dict_foreign_t*	foreign,	/* in: foreign key constraint */
+	ibool		add_newline)	/* in: whether to add a newline */
 {
 	const char*	stripped_id;
 	ulint	i;
@@ -4345,7 +4346,17 @@
 		stripped_id = foreign->id;
 	}
 
-	fputs(",\n  CONSTRAINT ", file);
+	putc(',', file);
+	
+	if (add_newline)
+	{
+		/* SHOW CREATE TABLE wants constraints each printed nicely
+		on its own line, while error messages want no newlines
+		inserted. */
+		fputs("\n ", file);
+	}
+	
+	fputs(" CONSTRAINT ", file);
 	ut_print_name(file, trx, stripped_id);
 	fputs(" FOREIGN KEY (", file);
 
@@ -4447,7 +4458,7 @@
 	while (foreign != NULL) {
 		if (create_table_format) {
 			dict_print_info_on_foreign_key_in_create_format(
-						file, trx, foreign);
+						file, trx, foreign, TRUE);
 		} else {
 			ulint	i;
 			fputs("; (", file);

--- 1.34/innobase/include/dict0dict.h	2005-09-06 14:38:17 +03:00
+++ 1.35/innobase/include/dict0dict.h	2005-09-14 10:42:25 +03:00
@@ -375,9 +375,10 @@
 void
 dict_print_info_on_foreign_key_in_create_format(
 /*============================================*/
-	FILE*		file,	/* in: file where to print */
-	trx_t*		trx,	/* in: transaction */
-	dict_foreign_t*	foreign);/* in: foreign key constraint */
+	FILE*		file,		/* in: file where to print */
+	trx_t*		trx,		/* in: transaction */
+	dict_foreign_t*	foreign,	/* in: foreign key constraint */
+	ibool		add_newline);	/* in: whether to add a newline */
 /************************************************************************
 Displays the names of the index and the table. */
 void

--- 1.69/innobase/row/row0ins.c	2005-08-12 11:29:56 +03:00
+++ 1.70/innobase/row/row0ins.c	2005-09-14 10:42:25 +03:00
@@ -28,6 +28,7 @@
 #include "eval0eval.h"
 #include "data0data.h"
 #include "usr0sess.h"
+#include "ha_innodb_callbacks.h"
 
 #define	ROW_INS_PREV	1
 #define	ROW_INS_NEXT	2
@@ -579,6 +580,52 @@
 }
 
 /*************************************************************************
+Set detailed error message associated with foreign key errors for
+the current thread. */
+static
+void
+row_ins_set_detailed(
+/*=======================*/
+	trx_t*		trx,		/* in: transaction */
+	dict_foreign_t*	foreign)	/* in: foreign key constraint */
+{
+		
+	long	flen;
+	char*	str;
+	FILE*	tf = os_file_create_tmpfile();
+
+	ut_a(tf);
+		
+	ut_print_name(tf, trx, foreign->foreign_table_name);
+	dict_print_info_on_foreign_key_in_create_format(tf, trx,
+		foreign, FALSE);
+
+	flen = ftell(tf);
+	if (flen == -1) {
+		fclose(tf);
+		
+		fprintf(stderr,
+"InnoDB: ftell failed in row_ins_foreign_report_add_err.\n");
+
+		return;
+	}
+
+	/* allocate buffer for the string, and read the contents of the
+	temporary file */
+
+	str = ut_malloc(flen + 1);
+	rewind(tf);
+	flen = (long)fread(str, 1, flen, tf);
+	str[flen] = '\0';
+
+	fclose(tf);
+
+	innobase_set_detailed_error(trx, str);
+
+	ut_free(str);
+}
+
+/*************************************************************************
 Reports a foreign key error associated with an update or a delete of a
 parent table index entry. */
 static
@@ -598,6 +645,8 @@
 	FILE*	ef	= dict_foreign_err_file;
 	trx_t*	trx	= thr_get_trx(thr);
 
+	row_ins_set_detailed(trx, foreign);
+
 	mutex_enter(&dict_foreign_err_mutex);
 	rewind(ef);
 	ut_print_timestamp(ef);
@@ -607,7 +656,8 @@
 	fputs("Foreign key constraint fails for table ", ef);
 	ut_print_name(ef, trx, foreign->foreign_table_name);
 	fputs(":\n", ef);
-	dict_print_info_on_foreign_key_in_create_format(ef, trx, foreign);
+	dict_print_info_on_foreign_key_in_create_format(ef, trx, foreign,
+		TRUE);
 	putc('\n', ef);
 	fputs(errstr, ef);
 	fputs(" in parent table, in index ", ef);
@@ -648,7 +698,9 @@
 					child table */
 {
 	FILE*	ef	= dict_foreign_err_file;
-
+	
+	row_ins_set_detailed(trx, foreign);
+	
 	mutex_enter(&dict_foreign_err_mutex);
 	rewind(ef);
 	ut_print_timestamp(ef);
@@ -657,7 +709,8 @@
 	fputs("Foreign key constraint fails for table ", ef);
 	ut_print_name(ef, trx, foreign->foreign_table_name);
 	fputs(":\n", ef);
-	dict_print_info_on_foreign_key_in_create_format(ef, trx, foreign);
+	dict_print_info_on_foreign_key_in_create_format(ef, trx, foreign,
+		TRUE);
 	fputs("\nTrying to add in child table, in index ", ef);
 	ut_print_name(ef, trx, foreign->foreign_index->name);
 	if (entry) {
@@ -1223,6 +1276,8 @@
 
 	if (check_table == NULL || check_table->ibd_file_missing) {
 		if (check_ref) {
+			row_ins_set_detailed(trx, foreign);
+			
 			FILE*	ef = dict_foreign_err_file;
 			mutex_enter(&dict_foreign_err_mutex);
 			rewind(ef);
@@ -1233,7 +1288,7 @@
 			ut_print_name(ef, trx, foreign->foreign_table_name);
 			fputs(":\n", ef);
 			dict_print_info_on_foreign_key_in_create_format(ef,
-					trx, foreign);
+					trx, foreign, TRUE);
 			fputs("\nTrying to add to index ", ef);
 			ut_print_name(ef, trx, foreign->foreign_index->name);
 			fputs(" tuple:\n", ef);

--- 1.131/mysql-test/r/innodb.result	2005-09-06 16:08:02 +03:00
+++ 1.132/mysql-test/r/innodb.result	2005-09-14 10:42:25 +03:00
@@ -1378,9 +1378,9 @@
 create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint
`t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
 insert into `t3`values ( 1 ) ;
 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
(`test/t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
 update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7  where t1.id =1 and t2.id = t1.id and t3.id
= t2.id;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
(`test/t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
 update t3 set  t3.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
 ERROR 42S22: Unknown column 't1.id' in 'where clause'
 drop table t3,t2,t1;
@@ -1392,7 +1392,7 @@
 insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
 (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
 delete from t1 where id=0;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
(`test/t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t1` (`id`) ON DELETE
CASCADE)
 delete from t1 where id=15;
 delete from t1 where id=0;
 drop table t1;
@@ -2539,3 +2539,26 @@
 ) ENGINE=InnoDB;
 Got one of the listed errors
 DROP TABLE t1;
+CREATE TABLE t1
+(
+id INT PRIMARY KEY
+) ENGINE=InnoDB;
+CREATE TABLE t2
+(
+v INT,
+CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(2);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`,
CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1);
+DELETE FROM t1 WHERE id = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
(`test/t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
+DROP TABLE t1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails ()
+SET FOREIGN_KEY_CHECKS=0;
+DROP TABLE t1;
+SET FOREIGN_KEY_CHECKS=1;
+INSERT INTO t2 VALUES(3);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`,
CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
+DROP TABLE t2;

--- 1.106/mysql-test/t/innodb.test	2005-09-06 16:08:02 +03:00
+++ 1.107/mysql-test/t/innodb.test	2005-09-14 10:42:25 +03:00
@@ -978,9 +978,9 @@
 insert into `t2`values ( 1 ) ;
 create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint
`t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
 insert into `t3`values ( 1 ) ;
---error 1217
+--error 1445
 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
---error 1217
+--error 1445
 update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7  where t1.id =1 and t2.id = t1.id and t3.id
= t2.id;
 --error 1054
 update t3 set  t3.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
@@ -996,7 +996,7 @@
 	foreign key(pid) references t1(id) on delete cascade) engine=innodb;
 insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
 	(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
--- error 1217
+-- error 1445
 delete from t1 where id=0;
 delete from t1 where id=15;
 delete from t1 where id=0;
@@ -1466,3 +1466,39 @@
  FOREIGN KEY (b) REFERENCES test.t1(id)
 ) ENGINE=InnoDB;
 DROP TABLE t1;
+
+#
+# Test improved foreign key error messages (bug #3443)
+#
+
+CREATE TABLE t1
+(
+ id INT PRIMARY KEY
+) ENGINE=InnoDB;
+
+CREATE TABLE t2
+(
+ v INT,
+ CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
+) ENGINE=InnoDB;
+
+--error 1446
+INSERT INTO t2 VALUES(2);
+
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1);
+
+--error 1445
+DELETE FROM t1 WHERE id = 1;
+
+--error 1445
+DROP TABLE t1;
+
+SET FOREIGN_KEY_CHECKS=0;
+DROP TABLE t1;
+SET FOREIGN_KEY_CHECKS=1;
+
+--error 1446
+INSERT INTO t2 VALUES(3);
+
+DROP TABLE t2;

--- 1.255/sql/ha_innodb.cc	2005-09-06 14:38:17 +03:00
+++ 1.256/sql/ha_innodb.cc	2005-09-14 10:42:25 +03:00
@@ -134,6 +134,7 @@
 #include "../innobase/include/fil0fil.h"
 #include "../innobase/include/trx0xa.h"
 }
+#include "../innobase/include/ha_innodb_callbacks.h"
 
 #define HA_INNOBASE_ROWS_IN_TABLE 10000 /* to get optimization right */
 #define HA_INNOBASE_RANGE_COUNT	  100
@@ -483,7 +484,7 @@
 
         } else if (error == (int) DB_CANNOT_DROP_CONSTRAINT) {
 
-    		return(HA_ERR_ROW_IS_REFERENCED); /* TODO: This is a bit
+		return(HA_ERR_ROW_IS_REFERENCED); /* TODO: This is a bit
 						misleading, a new MySQL error
 						code should be introduced */
         } else if (error == (int) DB_COL_APPEARS_TWICE_IN_INDEX) {
@@ -737,6 +738,20 @@
 		my_close(fd, MYF(MY_WME));
 	}
 	return(fd2);
+}
+
+/**********************************************************************
+Set detailed error message for the transaction's thread. */
+extern "C"
+void
+innobase_set_detailed_error(
+/*================*/
+	trx_t*	trx,	/* in: transaction handle */
+	char*	msg)	/* in: detailed error message */
+{
+	THD* thd = (THD*)(trx->mysql_thd);
+
+	strmake(thd->detailed_error, msg, sizeof(thd->detailed_error) - 1);
 }
 
 /*************************************************************************
--- New file ---
+++ innobase/include/ha_innodb_callbacks.h	05/09/14 10:31:22
/******************************************************
Callback prototypes for InnoDB table handler.
*******************************************************/

#ifndef HA_INNODB_CALLBACKS_H
#define HA_INNODB_CALLBACKS_H

#ifdef __cplusplus
extern "C" {
#endif

/**********************************************************************
Set detailed error message for the transaction's thread. */
void
innobase_set_detailed_error(
/*================*/
	trx_t*	trx,	/* in: transaction handle */
	char*	msg);	/* in: detailed error message */

#ifdef __cplusplus
}
#endif

#endif