Merge commit '96badb16afcf' into 10.0

Conflicts:
	client/mysql_upgrade.c
	mysql-test/r/func_misc.result
	mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
	mysql-test/suite/innodb/r/innodb-fk.result
	mysql-test/t/subselect_sj_mat.test
	sql/item.cc
	sql/item_func.cc
	sql/log.cc
	sql/log_event.cc
	sql/rpl_utility.cc
	sql/slave.cc
	sql/sql_class.cc
	sql/sql_class.h
	sql/sql_select.cc
	storage/innobase/dict/dict0crea.c
	storage/innobase/dict/dict0dict.c
	storage/innobase/handler/ha_innodb.cc
	storage/xtradb/dict/dict0crea.c
	storage/xtradb/dict/dict0dict.c
	storage/xtradb/handler/ha_innodb.cc
	vio/viosslfactories.c

3 files changed, 620 insertions, 77 deletions

diff --git a/storage/innobase/dict/dict0crea.cc b/storage/innobase/dict/dict0crea.cc
index 30523ff2af4..ed6eeb79a04 100644
--- a/storage/innobase/dict/dict0crea.cc
+++ b/storage/innobase/dict/dict0crea.cc
@@ -1562,12 +1562,111 @@ dict_create_add_foreign_field_to_dictionary(
 }
 
 /********************************************************************//**
+Construct foreign key constraint defintion from data dictionary information.
+*/
+UNIV_INTERN
+char*
+dict_foreign_def_get(
+/*=================*/
+	dict_foreign_t*	foreign,/*!< in: foreign */
+	trx_t*		trx)	/*!< in: trx */
+{
+	char* fk_def = (char *)mem_heap_alloc(foreign->heap, 4*1024);
+	const char* tbname;
+	char tablebuf[MAX_TABLE_NAME_LEN + 1] = "";
+	int i;
+	char* bufend;
+
+	tbname = dict_remove_db_name(foreign->id);
+	bufend = innobase_convert_name(tablebuf, MAX_TABLE_NAME_LEN,
+				tbname, strlen(tbname), trx->mysql_thd, FALSE);
+	tablebuf[bufend - tablebuf] = '\0';
+
+	sprintf(fk_def,
+		(char *)"CONSTRAINT %s FOREIGN KEY (", (char *)tablebuf);
+
+	for(i = 0; i < foreign->n_fields; i++) {
+		char	buf[MAX_TABLE_NAME_LEN + 1] = "";
+		innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+				foreign->foreign_col_names[i],
+				strlen(foreign->foreign_col_names[i]),
+				trx->mysql_thd, FALSE);
+		strcat(fk_def, buf);
+		if (i < foreign->n_fields-1) {
+			strcat(fk_def, (char *)",");
+		}
+	}
+
+	strcat(fk_def,(char *)") REFERENCES ");
+
+	bufend = innobase_convert_name(tablebuf, MAX_TABLE_NAME_LEN,
+	        	        foreign->referenced_table_name,
+			        strlen(foreign->referenced_table_name),
+			        trx->mysql_thd, TRUE);
+	tablebuf[bufend - tablebuf] = '\0';
+
+	strcat(fk_def, tablebuf);
+	strcat(fk_def, " (");
+
+	for(i = 0; i < foreign->n_fields; i++) {
+		char	buf[MAX_TABLE_NAME_LEN + 1] = "";
+		bufend = innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+				foreign->referenced_col_names[i],
+				strlen(foreign->referenced_col_names[i]),
+				trx->mysql_thd, FALSE);
+		buf[bufend - buf] = '\0';
+		strcat(fk_def, buf);
+		if (i < foreign->n_fields-1) {
+			strcat(fk_def, (char *)",");
+		}
+	}
+	strcat(fk_def, (char *)")");
+
+	return fk_def;
+}
+
+/********************************************************************//**
+Convert foreign key column names from data dictionary to SQL-layer.
+*/
+static
+void
+dict_foreign_def_get_fields(
+/*========================*/
+	dict_foreign_t*	foreign,/*!< in: foreign */
+	trx_t*		trx,	/*!< in: trx */
+	char**		field,  /*!< out: foreign column */
+	char**		field2, /*!< out: referenced column */
+	int		col_no) /*!< in: column number */
+{
+	char* bufend;
+	char* fieldbuf = (char *)mem_heap_alloc(foreign->heap, MAX_TABLE_NAME_LEN+1);
+	char* fieldbuf2 = (char *)mem_heap_alloc(foreign->heap, MAX_TABLE_NAME_LEN+1);
+
+	bufend = innobase_convert_name(fieldbuf, MAX_TABLE_NAME_LEN,
+			foreign->foreign_col_names[col_no],
+			strlen(foreign->foreign_col_names[col_no]),
+			trx->mysql_thd, FALSE);
+
+	fieldbuf[bufend - fieldbuf] = '\0';
+
+	bufend = innobase_convert_name(fieldbuf2, MAX_TABLE_NAME_LEN,
+			foreign->referenced_col_names[col_no],
+			strlen(foreign->referenced_col_names[col_no]),
+			trx->mysql_thd, FALSE);
+
+	fieldbuf2[bufend - fieldbuf2] = '\0';
+	*field = fieldbuf;
+	*field2 = fieldbuf2;
+}
+
+/********************************************************************//**
 Add a foreign key definition to the data dictionary tables.
 @return	error code or DB_SUCCESS */
 UNIV_INTERN
 dberr_t
 dict_create_add_foreign_to_dictionary(
 /*==================================*/
+	dict_table_t*		table,	/*!< in: table */
 	const char*		name,	/*!< in: table name */
 	const dict_foreign_t*	foreign,/*!< in: foreign key */
 	trx_t*			trx)	/*!< in/out: dictionary transaction */
@@ -1595,6 +1694,29 @@ dict_create_add_foreign_to_dictionary(
 
 	if (error != DB_SUCCESS) {
 
+		if (error == DB_DUPLICATE_KEY) {
+			char	buf[MAX_TABLE_NAME_LEN + 1] = "";
+			char	tablename[MAX_TABLE_NAME_LEN + 1] = "";
+			char*	fk_def;
+
+			innobase_convert_name(tablename, MAX_TABLE_NAME_LEN,
+				table->name, strlen(table->name),
+				trx->mysql_thd, TRUE);
+
+			innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+				foreign->id, strlen(foreign->id), trx->mysql_thd, FALSE);
+
+			fk_def = dict_foreign_def_get((dict_foreign_t*)foreign, trx);
+
+			ib_push_warning(trx, error,
+				"Create or Alter table %s with foreign key constraint"
+				" failed. Foreign key constraint %s"
+				" already exists on data dictionary."
+				" Foreign key constraint names need to be unique in database."
+				" Error in foreign key definition: %s.",
+				tablename, buf, fk_def);
+		}
+
 		return(error);
 	}
 
@@ -1603,6 +1725,26 @@ dict_create_add_foreign_to_dictionary(
 			i, name, foreign, trx);
 
 		if (error != DB_SUCCESS) {
+			char	buf[MAX_TABLE_NAME_LEN + 1] = "";
+			char	tablename[MAX_TABLE_NAME_LEN + 1] = "";
+			char*	field=NULL;
+			char*	field2=NULL;
+			char*	fk_def;
+
+			innobase_convert_name(tablename, MAX_TABLE_NAME_LEN,
+				table->name, strlen(table->name),
+				trx->mysql_thd, TRUE);
+			innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+				foreign->id, strlen(foreign->id), trx->mysql_thd, FALSE);
+			fk_def = dict_foreign_def_get((dict_foreign_t*)foreign, trx);
+			dict_foreign_def_get_fields((dict_foreign_t*)foreign, trx, &field, &field2, i);
+
+			ib_push_warning(trx, error,
+				"Create or Alter table %s with foreign key constraint"
+				" failed. Error adding foreign  key constraint name %s"
+				" fields %s or %s to the dictionary."
+				" Error in foreign key definition: %s.",
+				tablename, buf, i+1, fk_def);
 
 			return(error);
 		}
@@ -1649,7 +1791,7 @@ dict_create_add_foreigns_to_dictionary(
 		foreign = *it;
 		ut_ad(foreign->id != NULL);
 
-		error = dict_create_add_foreign_to_dictionary(table->name,
+		error = dict_create_add_foreign_to_dictionary((dict_table_t*)table, table->name,
 							      foreign, trx);
 
 		if (error != DB_SUCCESS) {
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index b866f44cc54..c7ffe071e99 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -3259,6 +3259,11 @@ dict_index_build_internal_fts(
 }
 /*====================== FOREIGN KEY PROCESSING ========================*/
 
+#define  DB_FOREIGN_KEY_IS_PREFIX_INDEX 200
+#define  DB_FOREIGN_KEY_COL_NOT_NULL    201
+#define  DB_FOREIGN_KEY_COLS_NOT_EQUAL  202
+#define  DB_FOREIGN_KEY_INDEX_NOT_FOUND 203
+
 /*********************************************************************//**
 Checks if a table is referenced by foreign keys.
 @return	TRUE if table is referenced by a foreign key */
@@ -3413,15 +3418,26 @@ dict_foreign_find_index(
 					/*!< in: whether to check
 					charsets.  only has an effect
 					if types_idx != NULL */
-	ulint			check_null)
+	ulint			check_null,
 					/*!< in: nonzero if none of
 					the columns must be declared
 					NOT NULL */
+	ulint*			error,	/*!< out: error code */
+	ulint*			err_col_no,
+					/*!< out: column number where
+					error happened */
+	dict_index_t**		err_index)
+			                /*!< out: index where error
+					happened */
 {
 	dict_index_t*	index;
 
 	ut_ad(mutex_own(&dict_sys->mutex));
 
+	if (error) {
+		*error = DB_FOREIGN_KEY_INDEX_NOT_FOUND;
+	}
+
 	index = dict_table_get_first_index(table);
 
 	while (index != NULL) {
@@ -3431,7 +3447,12 @@ dict_foreign_find_index(
 		    && dict_foreign_qualify_index(
 			    table, col_names, columns, n_cols,
 			    index, types_idx,
-			    check_charsets, check_null)) {
+			    check_charsets, check_null,
+			    error, err_col_no,err_index)) {
+			if (error) {
+				*error = DB_SUCCESS;
+			}
+
 			return(index);
 		}
 
@@ -3536,11 +3557,15 @@ dict_foreign_add_to_cache(
 	}
 
 	if (ref_table && !for_in_cache->referenced_table) {
+		ulint index_error;
+		ulint err_col;
+		dict_index_t *err_index=NULL;
+
 		index = dict_foreign_find_index(
 			ref_table, NULL,
 			for_in_cache->referenced_col_names,
 			for_in_cache->n_fields, for_in_cache->foreign_index,
-			check_charsets, false);
+			check_charsets, false, &index_error, &err_col, &err_index);
 
 		if (index == NULL
 		    && !(ignore_err & DICT_ERR_IGNORE_FK_NOKEY)) {
@@ -3572,6 +3597,10 @@ dict_foreign_add_to_cache(
 	}
 
 	if (for_table && !for_in_cache->foreign_table) {
+		ulint index_error;
+		ulint err_col;
+		dict_index_t *err_index=NULL;
+
 		index = dict_foreign_find_index(
 			for_table, col_names,
 			for_in_cache->foreign_col_names,
@@ -3579,7 +3608,8 @@ dict_foreign_add_to_cache(
 			for_in_cache->referenced_index, check_charsets,
 			for_in_cache->type
 			& (DICT_FOREIGN_ON_DELETE_SET_NULL
-			   | DICT_FOREIGN_ON_UPDATE_SET_NULL));
+				| DICT_FOREIGN_ON_UPDATE_SET_NULL),
+			&index_error, &err_col, &err_index);
 
 		if (index == NULL
 		    && !(ignore_err & DICT_ERR_IGNORE_FK_NOKEY)) {
@@ -4239,6 +4269,8 @@ static
 void
 dict_foreign_report_syntax_err(
 /*===========================*/
+	const char*     fmt,		/*!< in: syntax err msg */
+	const char*	oper,		/*!< in: operation */
 	const char*	name,		/*!< in: table name */
 	const char*	start_of_latest_foreign,
 					/*!< in: start of the foreign key clause
@@ -4251,12 +4283,102 @@ dict_foreign_report_syntax_err(
 
 	mutex_enter(&dict_foreign_err_mutex);
 	dict_foreign_error_report_low(ef, name);
-	fprintf(ef, "%s:\nSyntax error close to:\n%s\n",
-		start_of_latest_foreign, ptr);
+	fprintf(ef, fmt, oper, name, start_of_latest_foreign, ptr);
 	mutex_exit(&dict_foreign_err_mutex);
 }
 
 /*********************************************************************//**
+Push warning message to SQL-layer based on foreign key constraint
+index match error. */
+static
+void
+dict_foreign_push_index_error(
+/*==========================*/
+	trx_t*		trx,		/*!< in: trx */
+	const char*	operation,	/*!< in: operation create or alter
+					*/
+	const char*	create_name,	/*!< in: table name in create or
+					alter table */
+	const char*	latest_foreign,	/*!< in: start of latest foreign key
+					constraint name */
+	const char**	columns,	/*!< in: foreign key columns */
+	ulint		index_error,	/*!< in: error code */
+	ulint		err_col,	/*!< in: column where error happened
+					*/
+	dict_index_t*	err_index,	/*!< in: index where error happened
+					*/
+	dict_table_t*	table,		/*!< in: table */
+	FILE*		ef)		/*!< in: output stream */
+{
+	switch (index_error) {
+	case DB_FOREIGN_KEY_INDEX_NOT_FOUND: {
+		fprintf(ef,
+			"%s table '%s' with foreign key constraint"
+			" failed. There is no index in the referenced"
+			" table where the referenced columns appear"
+			" as the first columns. Error close to %s.\n",
+			operation, create_name, latest_foreign);
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table '%s' with foreign key constraint"
+			" failed. There is no index in the referenced"
+			" table where the referenced columns appear"
+			" as the first columns. Error close to %s.",
+			operation, create_name, latest_foreign);
+		break;
+	}
+	case DB_FOREIGN_KEY_IS_PREFIX_INDEX: {
+		fprintf(ef,
+			"%s table '%s' with foreign key constraint"
+			" failed. There is only prefix index in the referenced"
+			" table where the referenced columns appear"
+			" as the first columns. Error close to %s.\n",
+			operation, create_name, latest_foreign);
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table '%s' with foreign key constraint"
+			" failed. There is only prefix index in the referenced"
+			" table where the referenced columns appear"
+			" as the first columns. Error close to %s.",
+			operation, create_name, latest_foreign);
+		break;
+	}
+	case DB_FOREIGN_KEY_COL_NOT_NULL: {
+		fprintf(ef,
+			"%s table %s with foreign key constraint"
+			" failed. You have defined a SET NULL condition but "
+			"field %s on index is defined as NOT NULL close to %s\n",
+			operation, create_name, columns[err_col], latest_foreign);
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. You have defined a SET NULL condition but "
+			"field %s on index is defined as NOT NULL close to %s",
+			operation, create_name, columns[err_col], latest_foreign);
+		break;
+	}
+	case DB_FOREIGN_KEY_COLS_NOT_EQUAL: {
+		dict_field_t*	field;
+		const char*	col_name;
+		field = dict_index_get_nth_field(err_index, err_col);
+
+		col_name = dict_table_get_col_name(
+			table, dict_col_get_no(field->col));
+		fprintf(ef,
+			"%s table %s with foreign key constraint"
+			" failed. Field type or character set for column %s "
+			"does not mach referenced column %s close to %s\n",
+			operation, create_name, columns[err_col], col_name, latest_foreign);
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Field type or character set for column %s "
+			"does not mach referenced column %s close to %s",
+			operation, create_name, columns[err_col], col_name, latest_foreign);
+		break;
+	}
+	default:
+		ut_error;
+	}
+}
+
+/*********************************************************************//**
 Scans a table create SQL string and adds to the data dictionary the foreign
 key constraints declared in the string. This function should be called after
 the indexes for a table have been created. Each foreign key constraint must
@@ -4284,16 +4406,21 @@ dict_create_foreign_constraints_low(
 				DB_CANNOT_ADD_CONSTRAINT if any foreign
 				keys are found. */
 {
-	dict_table_t*	table;
-	dict_table_t*	referenced_table;
-	dict_table_t*	table_to_alter;
+	dict_table_t*	table			= NULL;
+	dict_table_t*	referenced_table	= NULL;
+	dict_table_t*	table_to_alter		= NULL;
+	dict_table_t*	table_to_create		= NULL;
 	ulint		highest_id_so_far	= 0;
 	ulint		number			= 1;
-	dict_index_t*	index;
-	dict_foreign_t*	foreign;
+	dict_index_t*	index			= NULL;
+	dict_foreign_t*	foreign			= NULL;
 	const char*	ptr			= sql_string;
 	const char*	start_of_latest_foreign	= sql_string;
+	const char*	start_of_latest_set     = NULL;
 	FILE*		ef			= dict_foreign_err_file;
+	ulint		index_error		= DB_SUCCESS;
+	dict_index_t*	err_index		= NULL;
+	ulint		err_col;
 	const char*	constraint_name;
 	ibool		success;
 	dberr_t		error;
@@ -4306,37 +4433,80 @@ dict_create_foreign_constraints_low(
 	ulint		n_on_updates;
 	const dict_col_t*columns[500];
 	const char*	column_names[500];
+	const char*	ref_column_names[500];
 	const char*	referenced_table_name;
 	dict_foreign_set	local_fk_set;
 	dict_foreign_set_free	local_fk_set_free(local_fk_set);
+	const char*	create_table_name;
+	const char*	orig;
+	char	create_name[MAX_TABLE_NAME_LEN + 1];
+	char	operation[8];
 
 	ut_ad(!srv_read_only_mode);
 	ut_ad(mutex_own(&(dict_sys->mutex)));
 
 	table = dict_table_get_low(name);
+	/* First check if we are actually doing an ALTER TABLE, and in that
+	case look for the table being altered */
+	orig = ptr;
+	ptr = dict_accept(cs, ptr, "ALTER", &success);
+
+	strcpy((char *)operation, success ? "Alter " : "Create ");
+
+	if (!success) {
+		orig = ptr;
+		ptr = dict_scan_to(ptr, "CREATE");
+		ptr = dict_scan_to(ptr, "TABLE");
+		ptr = dict_accept(cs, ptr, "TABLE", &success);
+
+		if (success) {
+			ptr = dict_scan_table_name(cs, ptr, &table_to_create, name,
+					&success, heap, &create_table_name);
+		}
+
+		if (success) {
+			char *bufend;
+			bufend = innobase_convert_name((char *)create_name, MAX_TABLE_NAME_LEN,
+					create_table_name, strlen(create_table_name),
+					trx->mysql_thd, TRUE);
+			create_name[bufend-create_name]='\0';
+			ptr = orig;
+		} else {
+			char *bufend;
+			ptr = orig;
+			bufend = innobase_convert_name((char *)create_name, MAX_TABLE_NAME_LEN,
+					name, strlen(name), trx->mysql_thd, TRUE);
+			create_name[bufend-create_name]='\0';
+		}
+
+		goto loop;
+	}
 
 	if (table == NULL) {
 		mutex_enter(&dict_foreign_err_mutex);
-		dict_foreign_error_report_low(ef, name);
-		fprintf(ef,
-			"Cannot find the table in the internal"
-			" data dictionary of InnoDB.\n"
-			"Create table statement:\n%s\n", sql_string);
+		dict_foreign_error_report_low(ef, create_name);
+		dict_foreign_error_report_low(ef, create_name);
+		fprintf(ef, "%s table %s with foreign key constraint"
+			" failed. Table %s not found from data dictionary."
+			" Error close to %s.\n",
+			operation, create_name, create_name, start_of_latest_foreign);
 		mutex_exit(&dict_foreign_err_mutex);
+		ib_push_warning(trx, DB_ERROR,
+			"%s table %s with foreign key constraint"
+			" failed. Table %s not found from data dictionary."
+			" Error close to %s.",
+			operation, create_name, create_name, start_of_latest_foreign);
 
 		return(DB_ERROR);
 	}
 
-	/* First check if we are actually doing an ALTER TABLE, and in that
-	case look for the table being altered */
-
-	ptr = dict_accept(cs, ptr, "ALTER", &success);
-
+	/* If not alter table jump to loop */
 	if (!success) {
 
 		goto loop;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "TABLE", &success);
 
 	if (!success) {
@@ -4346,13 +4516,40 @@ dict_create_foreign_constraints_low(
 
 	/* We are doing an ALTER TABLE: scan the table name we are altering */
 
+	orig = ptr;
 	ptr = dict_scan_table_name(cs, ptr, &table_to_alter, name,
 				   &success, heap, &referenced_table_name);
+
+	if (table_to_alter) {
+		char *bufend;
+		bufend = innobase_convert_name((char *)create_name, MAX_TABLE_NAME_LEN,
+				table_to_alter->name, strlen(table_to_alter->name),
+				trx->mysql_thd, TRUE);
+		create_name[bufend-create_name]='\0';
+	} else {
+		char *bufend;
+		bufend = innobase_convert_name((char *)create_name, MAX_TABLE_NAME_LEN,
+				referenced_table_name, strlen(referenced_table_name),
+				trx->mysql_thd, TRUE);
+		create_name[bufend-create_name]='\0';
+
+	}
+
 	if (!success) {
-		fprintf(stderr,
-			"InnoDB: Error: could not find"
-			" the table being ALTERED in:\n%s\n",
-			sql_string);
+		mutex_enter(&dict_foreign_err_mutex);
+		dict_foreign_error_report_low(ef, create_name);
+		fprintf(ef,
+			"%s table %s with foreign key constraint"
+			" failed. Table %s not found from data dictionary."
+			" Error close to %s.\n",
+			operation, create_name, create_name, orig);
+		mutex_exit(&dict_foreign_err_mutex);
+
+		ib_push_warning(trx, DB_ERROR,
+			"%s table %s with foreign key constraint"
+			" failed. Table %s not found from data dictionary."
+			" Error close to %s.",
+			operation, create_name, create_name, orig);
 
 		return(DB_ERROR);
 	}
@@ -4389,6 +4586,7 @@ loop:
 		of the constraint to system tables. */
 		ptr = ptr1;
 
+		orig = ptr;
 		ptr = dict_accept(cs, ptr, "CONSTRAINT", &success);
 
 		ut_a(success);
@@ -4419,6 +4617,19 @@ loop:
 		if so, immediately reject the command if the table is a
 		temporary one. For now, this kludge will work. */
 		if (reject_fks && !local_fk_set.empty()) {
+			mutex_enter(&dict_foreign_err_mutex);
+			dict_foreign_error_report_low(ef, create_name);
+			fprintf(ef, "%s table %s with foreign key constraint"
+				" failed. Temporary tables can't have foreign key constraints."
+				" Error close to %s.\n",
+				operation, create_name, start_of_latest_foreign);
+			mutex_exit(&dict_foreign_err_mutex);
+
+			ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+				"%s table %s with foreign key constraint"
+				" failed. Temporary tables can't have foreign key constraints."
+				" Error close to %s.",
+				operation, create_name, start_of_latest_foreign);
 
 			return(DB_CANNOT_ADD_CONSTRAINT);
 		}
@@ -4444,6 +4655,7 @@ loop:
 
 	start_of_latest_foreign = ptr;
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "FOREIGN", &success);
 
 	if (!success) {
@@ -4454,6 +4666,7 @@ loop:
 		goto loop;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "KEY", &success);
 
 	if (!success) {
@@ -4479,6 +4692,7 @@ loop:
                 }
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "(", &success);
 
 	if (!success) {
@@ -4488,8 +4702,17 @@ loop:
 		  ptr = dict_skip_word(cs, ptr, &success);
 		  if (!success) {
 			  dict_foreign_report_syntax_err(
-				  name, start_of_latest_foreign, ptr);
-                          return(DB_CANNOT_ADD_CONSTRAINT);
+				"%s table %s with foreign key constraint"
+				" failed. Foreign key constraint parse error in %s"
+				" close to %s.\n",
+				operation, create_name, start_of_latest_foreign, orig);
+
+			  ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+				"%s table %s with foreign key constraint"
+				" failed. Foreign key constraint parse error in %s"
+				" close to %s.",
+				operation, create_name, start_of_latest_foreign, orig);
+			  return(DB_CANNOT_ADD_CONSTRAINT);
                   }
 		}
                 else {
@@ -4516,15 +4739,26 @@ loop:
 	/* Scan the columns in the first list */
 col_loop1:
 	ut_a(i < (sizeof column_names) / sizeof *column_names);
+	orig = ptr;
 	ptr = dict_scan_col(cs, ptr, &success, table, columns + i,
 			    heap, column_names + i);
 	if (!success) {
 		mutex_enter(&dict_foreign_err_mutex);
-		dict_foreign_error_report_low(ef, name);
-		fprintf(ef, "%s:\nCannot resolve column name close to:\n%s\n",
-			start_of_latest_foreign, ptr);
+		dict_foreign_error_report_low(ef, create_name);
+		fprintf(ef,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, orig);
+
 		mutex_exit(&dict_foreign_err_mutex);
 
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, orig);
+
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
@@ -4536,11 +4770,22 @@ col_loop1:
 		goto col_loop1;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, ")", &success);
 
 	if (!success) {
 		dict_foreign_report_syntax_err(
-			name, start_of_latest_foreign, ptr);
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, orig);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, orig);
+
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
@@ -4551,27 +4796,41 @@ col_loop1:
 	set to 0 */
 
 	index = dict_foreign_find_index(
-		table, NULL, column_names, i, NULL, TRUE, FALSE);
+		table, NULL, column_names, i,
+		NULL, TRUE, FALSE, &index_error, &err_col, &err_index);
 
 	if (!index) {
 		mutex_enter(&dict_foreign_err_mutex);
-		dict_foreign_error_report_low(ef, name);
+		dict_foreign_error_report_low(ef, create_name);
 		fputs("There is no index in table ", ef);
-		ut_print_name(ef, NULL, TRUE, name);
+		ut_print_name(ef, NULL, TRUE, create_name);
 		fprintf(ef, " where the columns appear\n"
 			"as the first columns. Constraint:\n%s\n"
 			"See " REFMAN "innodb-foreign-key-constraints.html\n"
 			"for correct foreign key definition.\n",
 			start_of_latest_foreign);
-		mutex_exit(&dict_foreign_err_mutex);
+		dict_foreign_push_index_error(trx, operation, create_name, start_of_latest_foreign,
+			column_names, index_error, err_col, err_index, table, ef);
 
-		return(DB_CHILD_NO_INDEX);
+		mutex_exit(&dict_foreign_err_mutex);
+		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
+
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "REFERENCES", &success);
 
 	if (!success || !my_isspace(cs, *ptr)) {
 		dict_foreign_report_syntax_err(
-			name, start_of_latest_foreign, ptr);
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, orig);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, orig);
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
@@ -4640,21 +4899,46 @@ col_loop1:
 	checking of foreign key constraints! */
 
 	if (!success || (!referenced_table && trx->check_foreigns)) {
+		char	buf[MAX_TABLE_NAME_LEN + 1] = "";
+		char*	bufend;
+
+		bufend = innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+				referenced_table_name, strlen(referenced_table_name),
+				trx->mysql_thd, TRUE);
+		buf[bufend - buf] = '\0';
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint failed. Referenced table %s not found in the data dictionary "
+			"close to %s.",
+			operation, create_name, buf, start_of_latest_foreign);
 		mutex_enter(&dict_foreign_err_mutex);
-		dict_foreign_error_report_low(ef, name);
-		fprintf(ef, "%s:\nCannot resolve table name close to:\n"
-			"%s\n",
-			start_of_latest_foreign, ptr);
+		dict_foreign_error_report_low(ef, create_name);
+		fprintf(ef,
+			"%s table %s with foreign key constraint failed. Referenced table %s not found in the data dictionary "
+			"close to %s.\n",
+			operation, create_name, buf, start_of_latest_foreign);
+
 		mutex_exit(&dict_foreign_err_mutex);
 
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "(", &success);
 
 	if (!success) {
-		dict_foreign_report_syntax_err(name, start_of_latest_foreign,
-					       ptr);
+		dict_foreign_report_syntax_err(
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, orig);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, orig);
+
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
@@ -4662,34 +4946,54 @@ col_loop1:
 	i = 0;
 
 col_loop2:
+	orig = ptr;
 	ptr = dict_scan_col(cs, ptr, &success, referenced_table, columns + i,
-			    heap, column_names + i);
+			    heap, ref_column_names + i);
 	i++;
 
 	if (!success) {
 
 		mutex_enter(&dict_foreign_err_mutex);
-		dict_foreign_error_report_low(ef, name);
-		fprintf(ef, "%s:\nCannot resolve column name close to:\n"
-			"%s\n",
-			start_of_latest_foreign, ptr);
+		dict_foreign_error_report_low(ef, create_name);
+		fprintf(ef,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, orig);
 		mutex_exit(&dict_foreign_err_mutex);
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, orig);
 
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, ",", &success);
 
 	if (success) {
 		goto col_loop2;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, ")", &success);
 
 	if (!success || foreign->n_fields != i) {
 
-		dict_foreign_report_syntax_err(name, start_of_latest_foreign,
-					       ptr);
+		dict_foreign_report_syntax_err(
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s. Too few referenced columns.\n",
+			operation, create_name, start_of_latest_foreign, orig);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s. Too few referenced columns, you have %d when you should have %d.",
+			operation, create_name, start_of_latest_foreign, orig, i, foreign->n_fields);
+
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
@@ -4699,6 +5003,7 @@ col_loop2:
 scan_on_conditions:
 	/* Loop here as long as we can find ON ... conditions */
 
+	start_of_latest_set = ptr;
 	ptr = dict_accept(cs, ptr, "ON", &success);
 
 	if (!success) {
@@ -4706,15 +5011,27 @@ scan_on_conditions:
 		goto try_find_index;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "DELETE", &success);
 
 	if (!success) {
+		orig = ptr;
 		ptr = dict_accept(cs, ptr, "UPDATE", &success);
 
 		if (!success) {
 
 			dict_foreign_report_syntax_err(
-				name, start_of_latest_foreign, ptr);
+				"%s table %s with foreign key constraint"
+				" failed. Foreign key constraint parse error in %s"
+				" close to %s.\n",
+				operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
+			ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+				"%s table %s with foreign key constraint"
+				" failed. Foreign key constraint parse error in %s"
+				" close to %s.",
+				operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
 			return(DB_CANNOT_ADD_CONSTRAINT);
 		}
 
@@ -4725,12 +5042,14 @@ scan_on_conditions:
 		n_on_deletes++;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "RESTRICT", &success);
 
 	if (success) {
 		goto scan_on_conditions;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "CASCADE", &success);
 
 	if (success) {
@@ -4743,14 +5062,25 @@ scan_on_conditions:
 		goto scan_on_conditions;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "NO", &success);
 
 	if (success) {
+		orig = ptr;
 		ptr = dict_accept(cs, ptr, "ACTION", &success);
 
 		if (!success) {
 			dict_foreign_report_syntax_err(
-				name, start_of_latest_foreign, ptr);
+				"%s table %s with foreign key constraint"
+				" failed. Foreign key constraint parse error in %s"
+				" close to %s.\n",
+				operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
+			ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+				"%s table %s with foreign key constraint"
+				" failed. Foreign key constraint parse error in %s"
+				" close to %s.",
+				operation, create_name, start_of_latest_foreign, start_of_latest_set);
 
 			return(DB_CANNOT_ADD_CONSTRAINT);
 		}
@@ -4764,38 +5094,69 @@ scan_on_conditions:
 		goto scan_on_conditions;
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "SET", &success);
 
 	if (!success) {
-		dict_foreign_report_syntax_err(name, start_of_latest_foreign,
-					       ptr);
+		dict_foreign_report_syntax_err(
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, start_of_latest_set);
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
+	orig = ptr;
 	ptr = dict_accept(cs, ptr, "NULL", &success);
 
 	if (!success) {
-		dict_foreign_report_syntax_err(name, start_of_latest_foreign,
-					       ptr);
+		dict_foreign_report_syntax_err(
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.\n",
+			operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. Foreign key constraint parse error in %s"
+			" close to %s.",
+			operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
 	for (j = 0; j < foreign->n_fields; j++) {
 		if ((dict_index_get_nth_col(foreign->foreign_index, j)->prtype)
 		    & DATA_NOT_NULL) {
+			const dict_col_t*	col
+				= dict_index_get_nth_col(foreign->foreign_index, j);
+			const char* col_name = dict_table_get_col_name(foreign->foreign_index->table,
+				dict_col_get_no(col));
 
 			/* It is not sensible to define SET NULL
 			if the column is not allowed to be NULL! */
 
 			mutex_enter(&dict_foreign_err_mutex);
-			dict_foreign_error_report_low(ef, name);
-			fprintf(ef, "%s:\n"
-				"You have defined a SET NULL condition"
-				" though some of the\n"
-				"columns are defined as NOT NULL.\n",
-				start_of_latest_foreign);
+			dict_foreign_error_report_low(ef, create_name);
+			fprintf(ef,
+				"%s table %s with foreign key constraint"
+				" failed. You have defined a SET NULL condition but column %s is defined as NOT NULL"
+				" in %s close to %s.\n",
+				operation, create_name, col_name, start_of_latest_foreign, start_of_latest_set);
 			mutex_exit(&dict_foreign_err_mutex);
 
+			ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+				"%s table %s with foreign key constraint"
+				" failed. You have defined a SET NULL condition but column %s is defined as NOT NULL"
+				" in %s close to %s.",
+				operation, create_name, col_name, start_of_latest_foreign, start_of_latest_set);
+
 			return(DB_CANNOT_ADD_CONSTRAINT);
 		}
 	}
@@ -4813,11 +5174,20 @@ try_find_index:
 		/* It is an error to define more than 1 action */
 
 		mutex_enter(&dict_foreign_err_mutex);
-		dict_foreign_error_report_low(ef, name);
-		fprintf(ef, "%s:\n"
-			"You have twice an ON DELETE clause"
-			" or twice an ON UPDATE clause.\n",
-			start_of_latest_foreign);
+		dict_foreign_error_report_low(ef, create_name);
+		fprintf(ef,
+			"%s table %s with foreign key constraint"
+			" failed. You have more than one on delete or on update clause"
+			" in %s close to %s.\n",
+			operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
+		ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
+			"%s table %s with foreign key constraint"
+			" failed. You have more than one on delete or on update clause"
+			" in %s close to %s.",
+			operation, create_name, start_of_latest_foreign, start_of_latest_set);
+
+		dict_foreign_free(foreign);
 		mutex_exit(&dict_foreign_err_mutex);
 
 		return(DB_CANNOT_ADD_CONSTRAINT);
@@ -4829,12 +5199,12 @@ try_find_index:
 
 	if (referenced_table) {
 		index = dict_foreign_find_index(referenced_table, NULL,
-						column_names, i,
+						ref_column_names, i,
 						foreign->foreign_index,
-						TRUE, FALSE);
+			TRUE, FALSE, &index_error, &err_col, &err_index);
 		if (!index) {
 			mutex_enter(&dict_foreign_err_mutex);
-			dict_foreign_error_report_low(ef, name);
+			dict_foreign_error_report_low(ef, create_name);
 			fprintf(ef, "%s:\n"
 				"Cannot find an index in the"
 				" referenced table where the\n"
@@ -4852,9 +5222,13 @@ try_find_index:
 				"innodb-foreign-key-constraints.html\n"
 				"for correct foreign key definition.\n",
 				start_of_latest_foreign);
+
+			dict_foreign_push_index_error(trx, operation, create_name, start_of_latest_foreign,
+				column_names, index_error, err_col, err_index, referenced_table, ef);
+
 			mutex_exit(&dict_foreign_err_mutex);
 
-			return(DB_PARENT_NO_INDEX);
+			return(DB_CANNOT_ADD_CONSTRAINT);
 		}
 	} else {
 		ut_a(trx->check_foreigns == FALSE);
@@ -4873,11 +5247,12 @@ try_find_index:
 
 	for (i = 0; i < foreign->n_fields; i++) {
 		foreign->referenced_col_names[i]
-			= mem_heap_strdup(foreign->heap, column_names[i]);
+			= mem_heap_strdup(foreign->heap, ref_column_names[i]);
 	}
 
 	goto loop;
 }
+
 /**************************************************************************
 Determines whether a string starts with the specified keyword.
 @return	TRUE if str starts with keyword */
@@ -6029,7 +6404,8 @@ dict_foreign_replace_index(
 				foreign->foreign_table, col_names,
 				foreign->foreign_col_names,
 				foreign->n_fields, index,
-				/*check_charsets=*/TRUE, /*check_null=*/FALSE);
+				/*check_charsets=*/TRUE, /*check_null=*/FALSE,
+				NULL, NULL, NULL);
 			if (new_index) {
 				ut_ad(new_index->table == index->table);
 				ut_ad(!new_index->to_be_dropped);
@@ -6053,7 +6429,8 @@ dict_foreign_replace_index(
 				foreign->referenced_table, NULL,
 				foreign->referenced_col_names,
 				foreign->n_fields, index,
-				/*check_charsets=*/TRUE, /*check_null=*/FALSE);
+				/*check_charsets=*/TRUE, /*check_null=*/FALSE,
+				NULL, NULL, NULL);
 			/* There must exist an alternative index,
 			since this must have been checked earlier. */
 			if (new_index) {
@@ -6622,10 +6999,17 @@ dict_foreign_qualify_index(
 					/*!< in: whether to check
 					charsets.  only has an effect
 					if types_idx != NULL */
-	ulint			check_null)
+	ulint			check_null,
 					/*!< in: nonzero if none of
 					the columns must be declared
 					NOT NULL */
+	ulint*			error,	/*!< out: error code */
+	ulint*			err_col_no,
+					/*!< out: column number where
+					error happened */
+	dict_index_t**		err_index)
+					/*!< out: index where error
+					happened */
 {
 	if (dict_index_get_n_fields(index) < n_cols) {
 		return(false);
@@ -6642,11 +7026,21 @@ dict_foreign_qualify_index(
 		if (field->prefix_len != 0) {
 			/* We do not accept column prefix
 			indexes here */
+			if (error && err_col_no && err_index) {
+				*error = DB_FOREIGN_KEY_IS_PREFIX_INDEX;
+				*err_col_no = i;
+				*err_index = (dict_index_t*)index;
+			}
 			return(false);
 		}
 
 		if (check_null
 		    && (field->col->prtype & DATA_NOT_NULL)) {
+			if (error && err_col_no && err_index) {
+				*error = DB_FOREIGN_KEY_COL_NOT_NULL;
+				*err_col_no = i;
+				*err_index = (dict_index_t*)index;
+			}
 			return(false);
 		}
 
@@ -6662,6 +7056,12 @@ dict_foreign_qualify_index(
 			    dict_index_get_nth_col(index, i),
 			    dict_index_get_nth_col(types_idx, i),
 			    check_charsets)) {
+			if (error && err_col_no && err_index) {
+				*error = DB_FOREIGN_KEY_COLS_NOT_EQUAL;
+				*err_col_no = i;
+				*err_index = (dict_index_t*)index;
+			}
+
 			return(false);
 		}
 	}
diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc
index e830a8430b7..12f9516b938 100644
--- a/storage/innobase/dict/dict0mem.cc
+++ b/storage/innobase/dict/dict0mem.cc
@@ -417,7 +417,8 @@ dict_mem_table_col_rename_low(
 			dict_index_t* new_index = dict_foreign_find_index(
 				foreign->foreign_table, NULL,
 				foreign->foreign_col_names,
-				foreign->n_fields, NULL, true, false);
+				foreign->n_fields, NULL, true, false,
+				NULL, NULL, NULL);
 			/* There must be an equivalent index in this case. */
 			ut_ad(new_index != NULL);