summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorJan Lindström <jan.lindstrom@mariadb.com>2015-07-29 05:58:45 +0300
committerJan Lindström <jan.lindstrom@mariadb.com>2015-07-29 05:58:45 +0300
commit7abb21d1fde04e6141c86b95605ae690011771c3 (patch)
tree0be23a1687c2aa65f528f75a650e796cd673506b
parente40bc659335f7f8b69427ed2d215c34c045a5ed7 (diff)
downloadmariadb-git-7abb21d1fde04e6141c86b95605ae690011771c3.tar.gz
MDEV-8524: Improve error messaging when there is duplicate key or foreign key namesbb-5.5-inno
Added better error message that will be printed when foreign key constraint name in create table is not unique in database.
-rw-r--r--mysql-test/suite/innodb/r/innodb-fk-warnings.result20
-rw-r--r--mysql-test/suite/innodb/t/innodb-fk-warnings.test29
-rw-r--r--storage/innobase/dict/dict0crea.c117
-rw-r--r--storage/innobase/handler/ha_innodb.cc26
-rw-r--r--storage/innobase/include/ha_prototypes.h9
-rw-r--r--storage/xtradb/dict/dict0crea.c117
-rw-r--r--storage/xtradb/handler/ha_innodb.cc25
-rw-r--r--storage/xtradb/include/ha_prototypes.h9
8 files changed, 350 insertions, 2 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
new file mode 100644
index 00000000000..c64cb3024b5
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
@@ -0,0 +1,20 @@
+CREATE TABLE t1 (
+id int(11) NOT NULL PRIMARY KEY,
+a int(11) NOT NULL,
+b int(11) NOT NULL,
+c int not null,
+CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+id int(11) NOT NULL PRIMARY KEY,
+a int(11) NOT NULL,
+b int(11) NOT NULL,
+c int not null,
+CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ERROR HY000: Can't create table 'test.t2' (errno: 121)
+show warnings;
+Level Code Message
+Warning 121 InnoDB: foreign key constraint name `test/test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`).
+Error 1005 Can't create table 'test.t2' (errno: 121)
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb-fk-warnings.test b/mysql-test/suite/innodb/t/innodb-fk-warnings.test
new file mode 100644
index 00000000000..e12a0ecf820
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-fk-warnings.test
@@ -0,0 +1,29 @@
+--source include/have_innodb.inc
+
+#
+# MDEV-8524: Improve error messaging when there is duplicate key or foreign key names
+#
+CREATE TABLE t1 (
+ id int(11) NOT NULL PRIMARY KEY,
+ a int(11) NOT NULL,
+ b int(11) NOT NULL,
+ c int not null,
+ CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+#
+# Below create table fails because constraint name test
+# is reserved for above table.
+#
+--error 1005
+CREATE TABLE t2 (
+ id int(11) NOT NULL PRIMARY KEY,
+ a int(11) NOT NULL,
+ b int(11) NOT NULL,
+ c int not null,
+ CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+show warnings;
+
+drop table t1;
diff --git a/storage/innobase/dict/dict0crea.c b/storage/innobase/dict/dict0crea.c
index ac8a1eac03c..7a52aff3ec3 100644
--- a/storage/innobase/dict/dict0crea.c
+++ b/storage/innobase/dict/dict0crea.c
@@ -1420,6 +1420,91 @@ dict_create_add_foreign_field_to_dictionary(
}
/********************************************************************//**
+Construct foreign key constraint defintion from data dictionary information.
+*/
+static
+char*
+dict_foreign_def_get(
+ dict_foreign_t* foreign,/*!< in: foreign */
+ trx_t* trx) /*!< in: trx */
+{
+ char* fk_def = mem_heap_alloc(foreign->heap, 4*1024);
+ const char* tbname;
+ char tablebuf[MAX_TABLE_NAME_LEN + 1] = "";
+ int i;
+
+ tbname = dict_remove_db_name(foreign->id);
+ innobase_convert_name(tablebuf, MAX_TABLE_NAME_LEN,
+ tbname, strlen(tbname), trx->mysql_thd, FALSE);
+
+ 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 ");
+
+ innobase_convert_name(tablebuf, MAX_TABLE_NAME_LEN,
+ foreign->referenced_table_name,
+ strlen(foreign->referenced_table_name),
+ trx->mysql_thd, TRUE);
+
+ strcat(fk_def, tablebuf);
+ strcat(fk_def, " (");
+
+ for(i = 0; i < foreign->n_fields; i++) {
+ char buf[MAX_TABLE_NAME_LEN + 1] = "";
+ innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+ foreign->referenced_col_names[i],
+ strlen(foreign->referenced_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 *)")");
+
+ 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 */
+{
+ *field = mem_heap_alloc(foreign->heap, MAX_TABLE_NAME_LEN+1);
+ *field2 = mem_heap_alloc(foreign->heap, MAX_TABLE_NAME_LEN+1);
+
+ innobase_convert_name(*field, MAX_TABLE_NAME_LEN,
+ foreign->foreign_col_names[col_no],
+ strlen(foreign->foreign_col_names[col_no]),
+ trx->mysql_thd, FALSE);
+
+ innobase_convert_name(*field, MAX_TABLE_NAME_LEN,
+ foreign->referenced_col_names[col_no],
+ strlen(foreign->referenced_col_names[col_no]),
+ trx->mysql_thd, FALSE);
+}
+
+/********************************************************************//**
Add a single foreign key definition to the data dictionary tables in the
database. We also generate names to constraints that were not named by the
user. A generated constraint has a name of the format
@@ -1501,6 +1586,22 @@ dict_create_add_foreign_to_dictionary(
if (error != DB_SUCCESS) {
+ if (error == DB_DUPLICATE_KEY) {
+ char buf[MAX_TABLE_NAME_LEN + 1] = "";
+ char* fk_def;
+
+ innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+ foreign->id, strlen(foreign->id), trx->mysql_thd, FALSE);
+
+ fk_def = dict_foreign_def_get(foreign, trx);
+
+ ib_push_warning(trx, error, (const char *)"InnoDB: foreign key constraint name %s "
+ "already exists on data dictionary."
+ " Foreign key constraint names need to be unique in database."
+ " Error in foreign key definition: %s.",
+ buf, fk_def);
+ }
+
return(error);
}
@@ -1509,6 +1610,20 @@ dict_create_add_foreign_to_dictionary(
i, table, foreign, trx);
if (error != DB_SUCCESS) {
+ char buf[MAX_TABLE_NAME_LEN + 1] = "";
+ char* field=NULL;
+ char* field2=NULL;
+ char* fk_def;
+
+ innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+ foreign->id, strlen(foreign->id), trx->mysql_thd, FALSE);
+ fk_def = dict_foreign_def_get(foreign, trx);
+ dict_foreign_def_get_fields(foreign, trx, &field, &field2, i);
+
+ ib_push_warning(trx, error,
+ (const char *)"InnoDB: Error adding foreign key constraint name %s fields %s or %s to the dictionary."
+ " Error in foreign key definition: %s.",
+ buf, i+1, fk_def);
return(error);
}
@@ -1593,7 +1708,7 @@ dict_create_add_foreigns_to_dictionary(
foreign = UT_LIST_GET_NEXT(foreign_list, foreign)) {
error = dict_create_add_foreign_to_dictionary(&number, table,
- foreign, trx);
+ foreign, trx);
if (error != DB_SUCCESS) {
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index c97b5bfadc2..2bdbdf7a19f 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -12304,3 +12304,29 @@ ib_warn_row_too_big(const dict_table_t* table)
" ROW_FORMAT=COMPRESSED ": ""
, prefix ? DICT_MAX_FIXED_COL_LEN : 0);
}
+
+/********************************************************************//**
+Helper function to push warnings from InnoDB internals to SQL-layer. */
+extern "C" UNIV_INTERN
+void
+ib_push_warning(
+ trx_t* trx, /*!< in: trx */
+ ulint error, /*!< in: error code to push as warning */
+ const char *format,/*!< in: warning message */
+ ...)
+{
+ va_list args;
+ THD *thd = (THD *)trx->mysql_thd;
+ char *buf;
+#define MAX_BUF_SIZE 4*1024
+
+ va_start(args, format);
+ buf = (char *)my_malloc(MAX_BUF_SIZE, MYF(MY_WME));
+ vsprintf(buf,format, args);
+
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ convert_error_code_to_mysql(error, 0, thd),
+ buf);
+ my_free(buf);
+ va_end(args);
+}
diff --git a/storage/innobase/include/ha_prototypes.h b/storage/innobase/include/ha_prototypes.h
index 3859b45e8d1..a69d3d126a0 100644
--- a/storage/innobase/include/ha_prototypes.h
+++ b/storage/innobase/include/ha_prototypes.h
@@ -326,5 +326,14 @@ innobase_convert_to_filename_charset(
const char* from, /* in: identifier to convert */
ulint len); /* in: length of 'to', in bytes */
+/********************************************************************//**
+Helper function to push warnings from InnoDB internals to SQL-layer. */
+UNIV_INTERN
+void
+ib_push_warning(
+ trx_t* trx, /*!< in: trx */
+ ulint error, /*!< in: error code to push as warning */
+ const char *format,/*!< in: warning message */
+ ...);
#endif
diff --git a/storage/xtradb/dict/dict0crea.c b/storage/xtradb/dict/dict0crea.c
index c88979b20f9..71a00ca8d0f 100644
--- a/storage/xtradb/dict/dict0crea.c
+++ b/storage/xtradb/dict/dict0crea.c
@@ -1627,6 +1627,91 @@ dict_create_add_foreign_field_to_dictionary(
}
/********************************************************************//**
+Construct foreign key constraint defintion from data dictionary information.
+*/
+static
+char*
+dict_foreign_def_get(
+ dict_foreign_t* foreign,/*!< in: foreign */
+ trx_t* trx) /*!< in: trx */
+{
+ char* fk_def = mem_heap_alloc(foreign->heap, 4*1024);
+ const char* tbname;
+ char tablebuf[MAX_TABLE_NAME_LEN + 1] = "";
+ int i;
+
+ tbname = dict_remove_db_name(foreign->id);
+ innobase_convert_name(tablebuf, MAX_TABLE_NAME_LEN,
+ tbname, strlen(tbname), trx->mysql_thd, FALSE);
+
+ 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 ");
+
+ innobase_convert_name(tablebuf, MAX_TABLE_NAME_LEN,
+ foreign->referenced_table_name,
+ strlen(foreign->referenced_table_name),
+ trx->mysql_thd, TRUE);
+
+ strcat(fk_def, tablebuf);
+ strcat(fk_def, " (");
+
+ for(i = 0; i < foreign->n_fields; i++) {
+ char buf[MAX_TABLE_NAME_LEN + 1] = "";
+ innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+ foreign->referenced_col_names[i],
+ strlen(foreign->referenced_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 *)")");
+
+ 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 */
+{
+ *field = mem_heap_alloc(foreign->heap, MAX_TABLE_NAME_LEN+1);
+ *field2 = mem_heap_alloc(foreign->heap, MAX_TABLE_NAME_LEN+1);
+
+ innobase_convert_name(*field, MAX_TABLE_NAME_LEN,
+ foreign->foreign_col_names[col_no],
+ strlen(foreign->foreign_col_names[col_no]),
+ trx->mysql_thd, FALSE);
+
+ innobase_convert_name(*field, MAX_TABLE_NAME_LEN,
+ foreign->referenced_col_names[col_no],
+ strlen(foreign->referenced_col_names[col_no]),
+ trx->mysql_thd, FALSE);
+}
+
+/********************************************************************//**
Add a single foreign key definition to the data dictionary tables in the
database. We also generate names to constraints that were not named by the
user. A generated constraint has a name of the format
@@ -1708,6 +1793,22 @@ dict_create_add_foreign_to_dictionary(
if (error != DB_SUCCESS) {
+ if (error == DB_DUPLICATE_KEY) {
+ char buf[MAX_TABLE_NAME_LEN + 1] = "";
+ char* fk_def;
+
+ innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+ foreign->id, strlen(foreign->id), trx->mysql_thd, FALSE);
+
+ fk_def = dict_foreign_def_get(foreign, trx);
+
+ ib_push_warning(trx, error, (const char *)"InnoDB: foreign key constraint name %s "
+ "already exists on data dictionary."
+ " Foreign key constraint names need to be unique in database."
+ " Error in foreign key definition: %s.",
+ buf, fk_def);
+ }
+
return(error);
}
@@ -1716,6 +1817,20 @@ dict_create_add_foreign_to_dictionary(
i, table, foreign, trx);
if (error != DB_SUCCESS) {
+ char buf[MAX_TABLE_NAME_LEN + 1] = "";
+ char* field=NULL;
+ char* field2=NULL;
+ char* fk_def;
+
+ innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
+ foreign->id, strlen(foreign->id), trx->mysql_thd, FALSE);
+ fk_def = dict_foreign_def_get(foreign, trx);
+ dict_foreign_def_get_fields(foreign, trx, &field, &field2, i);
+
+ ib_push_warning(trx, error,
+ (const char *)"InnoDB: Error adding foreign key constraint name %s fields %s or %s to the dictionary."
+ " Error in foreign key definition: %s.",
+ buf, i+1, fk_def);
return(error);
}
@@ -1800,7 +1915,7 @@ dict_create_add_foreigns_to_dictionary(
foreign = UT_LIST_GET_NEXT(foreign_list, foreign)) {
error = dict_create_add_foreign_to_dictionary(&number, table,
- foreign, trx);
+ foreign, trx);
if (error != DB_SUCCESS) {
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index fa0081da436..6c4e9eedd7d 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -14107,3 +14107,28 @@ ha_innobase::idx_cond_push(
DBUG_RETURN(NULL);
}
+/********************************************************************//**
+Helper function to push warnings from InnoDB internals to SQL-layer. */
+extern "C" UNIV_INTERN
+void
+ib_push_warning(
+ trx_t* trx, /*!< in: trx */
+ ulint error, /*!< in: error code to push as warning */
+ const char *format,/*!< in: warning message */
+ ...)
+{
+ va_list args;
+ THD *thd = (THD *)trx->mysql_thd;
+ char *buf;
+#define MAX_BUF_SIZE 4*1024
+
+ va_start(args, format);
+ buf = (char *)my_malloc(MAX_BUF_SIZE, MYF(MY_WME));
+ vsprintf(buf,format, args);
+
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ convert_error_code_to_mysql(error, 0, thd),
+ buf);
+ my_free(buf);
+ va_end(args);
+}
diff --git a/storage/xtradb/include/ha_prototypes.h b/storage/xtradb/include/ha_prototypes.h
index ec2ba77f784..ad0abb88ff6 100644
--- a/storage/xtradb/include/ha_prototypes.h
+++ b/storage/xtradb/include/ha_prototypes.h
@@ -353,5 +353,14 @@ innobase_convert_to_filename_charset(
const char* from, /* in: identifier to convert */
ulint len); /* in: length of 'to', in bytes */
+/********************************************************************//**
+Helper function to push warnings from InnoDB internals to SQL-layer. */
+UNIV_INTERN
+void
+ib_push_warning(
+ trx_t* trx, /*!< in: trx */
+ ulint error, /*!< in: error code to push as warning */
+ const char *format,/*!< in: warning message */
+ ...);
#endif