summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/insert.result38
-rw-r--r--mysql-test/t/insert.test41
-rw-r--r--sql/handler.cc27
-rw-r--r--sql/handler.h10
-rw-r--r--sql/sql_insert.cc12
-rw-r--r--sql/sql_update.cc26
6 files changed, 141 insertions, 13 deletions
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index 655303be7f4..1aa22349593 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -686,3 +686,41 @@ ERROR 42000: Column 'a' specified twice
INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
ERROR 42000: Column 'a' specified twice
DROP TABLE t1;
+#
+# BUG#22037930: INSERT IGNORE FAILS TO IGNORE
+# FOREIGN KEY CONSTRAINT
+# Setup.
+CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1))
+ENGINE=INNODB;
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0);
+# Without fix, an error is reported.
+INSERT IGNORE INTO t2 VALUES(1);
+Warnings:
+Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
+UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0;
+Warnings:
+Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
+UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0;
+Warnings:
+Warning 1451 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
+# Test for multi update.
+UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3;
+Warnings:
+Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
+UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3;
+Warnings:
+Warning 1451 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
+# Reports an error since IGNORE is not used.
+INSERT INTO t2 VALUES(1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t2 SET fld2=20 WHERE fld2=0;
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t1 SET fld1=20 WHERE fld1=0;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3;
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+DROP TABLE t2, t1;
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 2bf543511ac..ea89872200c 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -550,3 +550,44 @@ INSERT IGNORE t1 (a, a) SELECT 1,1;
INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
DROP TABLE t1;
+
+
+--echo #
+--echo # BUG#22037930: INSERT IGNORE FAILS TO IGNORE
+--echo # FOREIGN KEY CONSTRAINT
+
+--echo # Setup.
+CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1))
+ENGINE=INNODB;
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0);
+
+--echo # Without fix, an error is reported.
+--enable_warnings
+INSERT IGNORE INTO t2 VALUES(1);
+UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0;
+UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0;
+
+--echo # Test for multi update.
+UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3;
+UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3;
+--disable_warnings
+
+--echo # Reports an error since IGNORE is not used.
+--error ER_NO_REFERENCED_ROW_2
+INSERT INTO t2 VALUES(1);
+
+--error ER_NO_REFERENCED_ROW_2
+UPDATE t2 SET fld2=20 WHERE fld2=0;
+
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET fld1=20 WHERE fld1=0;
+
+--error ER_NO_REFERENCED_ROW_2
+UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3;
+
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3;
+
+DROP TABLE t2, t1;
diff --git a/sql/handler.cc b/sql/handler.cc
index 6307e95a194..9d57cba73dc 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -5452,3 +5452,28 @@ fl_create_iterator(enum handler_iterator_type type,
}
}
#endif /*TRANS_LOG_MGM_EXAMPLE_CODE*/
+
+
+/**
+ Report a warning for FK constraint violation.
+
+ @param thd Thread handle.
+ @param table table on which the operation is performed.
+ @param error handler error number.
+*/
+void warn_fk_constraint_violation(THD *thd,TABLE *table, int error)
+{
+ String str;
+ switch(error) {
+ case HA_ERR_ROW_IS_REFERENCED:
+ table->file->get_error_message(error, &str);
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_ROW_IS_REFERENCED_2, str.c_ptr_safe());
+ break;
+ case HA_ERR_NO_REFERENCED_ROW:
+ table->file->get_error_message(error, &str);
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_NO_REFERENCED_ROW_2, str.c_ptr_safe());
+ break;
+ }
+}
diff --git a/sql/handler.h b/sql/handler.h
index 17306fe7dd4..29b6a86c030 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -2,7 +2,7 @@
#define HANDLER_INCLUDED
/*
- Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+ Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
@@ -303,6 +303,7 @@
/* Flags for method is_fatal_error */
#define HA_CHECK_DUP_KEY 1
#define HA_CHECK_DUP_UNIQUE 2
+#define HA_CHECK_FK_ERROR 4
#define HA_CHECK_DUP (HA_CHECK_DUP_KEY + HA_CHECK_DUP_UNIQUE)
enum legacy_db_type
@@ -1485,7 +1486,10 @@ public:
if (!error ||
((flags & HA_CHECK_DUP_KEY) &&
(error == HA_ERR_FOUND_DUPP_KEY ||
- error == HA_ERR_FOUND_DUPP_UNIQUE)))
+ error == HA_ERR_FOUND_DUPP_UNIQUE)) ||
+ ((flags & HA_CHECK_FK_ERROR) &&
+ (error == HA_ERR_ROW_IS_REFERENCED ||
+ error == HA_ERR_NO_REFERENCED_ROW)))
return FALSE;
return TRUE;
}
@@ -2362,4 +2366,6 @@ inline const char *table_case_name(HA_CREATE_INFO *info, const char *name)
return ((lower_case_table_names == 2 && info->alias) ? info->alias : name);
}
+void warn_fk_constraint_violation(THD *thd, TABLE *table, int error);
+
#endif /* HANDLER_INCLUDED */
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 7bfc7b083ac..a267108c847 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1,5 +1,5 @@
/*
- Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
+ Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -1522,7 +1522,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
else
table->file->insert_id_for_cur_row= insert_id_for_cur_row;
bool is_duplicate_key_error;
- if (table->file->is_fatal_error(error, HA_CHECK_DUP))
+ if (table->file->is_fatal_error(error, HA_CHECK_DUP | HA_CHECK_FK_ERROR))
goto err;
is_duplicate_key_error= table->file->is_fatal_error(error, 0);
if (!is_duplicate_key_error)
@@ -1620,7 +1620,8 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
error != HA_ERR_RECORD_IS_THE_SAME)
{
if (info->ignore &&
- !table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
+ !table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
+ HA_CHECK_FK_ERROR))
{
goto ok_or_after_trg_err;
}
@@ -1733,7 +1734,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
{
DEBUG_SYNC(thd, "write_row_noreplace");
if (!info->ignore ||
- table->file->is_fatal_error(error, HA_CHECK_DUP))
+ table->file->is_fatal_error(error, HA_CHECK_DUP | HA_CHECK_FK_ERROR))
goto err;
table->file->restore_auto_increment(prev_insert_id);
goto ok_or_after_trg_err;
@@ -1751,6 +1752,9 @@ ok_or_after_trg_err:
my_safe_afree(key,table->s->max_unique_length,MAX_KEY_LENGTH);
if (!table->file->has_transactions())
thd->transaction.stmt.modified_non_trans_table= TRUE;
+ if (info->ignore &&
+ !table->file->is_fatal_error(error, HA_CHECK_FK_ERROR))
+ warn_fk_constraint_violation(thd, table, error);
DBUG_RETURN(trg_error);
err:
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index a29d474fbfc..64d1b3e49dc 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -735,7 +735,8 @@ int mysql_update(THD *thd,
error= 0;
}
else if (!ignore ||
- table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
+ table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
+ HA_CHECK_FK_ERROR))
{
/*
If (ignore && error is ignorable) we don't have to
@@ -743,7 +744,8 @@ int mysql_update(THD *thd,
*/
myf flags= 0;
- if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
+ if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
+ HA_CHECK_FK_ERROR))
flags|= ME_FATALERROR; /* Other handler errors are fatal */
prepare_record_for_error_message(error, table);
@@ -751,6 +753,9 @@ int mysql_update(THD *thd,
error= 1;
break;
}
+ else if (ignore && !table->file->is_fatal_error(error,
+ HA_CHECK_FK_ERROR))
+ warn_fk_constraint_violation(thd, table, error);
}
if (table->triggers &&
@@ -1883,7 +1888,8 @@ bool multi_update::send_data(List<Item> &not_used_values)
{
updated--;
if (!ignore ||
- table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
+ table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
+ HA_CHECK_FK_ERROR))
{
/*
If (ignore && error == is ignorable) we don't have to
@@ -1891,13 +1897,17 @@ bool multi_update::send_data(List<Item> &not_used_values)
*/
myf flags= 0;
- if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
+ if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
+ HA_CHECK_FK_ERROR))
flags|= ME_FATALERROR; /* Other handler errors are fatal */
prepare_record_for_error_message(error, table);
table->file->print_error(error,MYF(flags));
DBUG_RETURN(1);
}
+ else if (ignore && !table->file->is_fatal_error(error,
+ HA_CHECK_FK_ERROR))
+ warn_fk_constraint_violation(thd, table, error);
}
else
{
@@ -2138,8 +2148,12 @@ int multi_update::do_updates()
local_error != HA_ERR_RECORD_IS_THE_SAME)
{
if (!ignore ||
- table->file->is_fatal_error(local_error, HA_CHECK_DUP_KEY))
+ table->file->is_fatal_error(local_error, HA_CHECK_DUP_KEY |
+ HA_CHECK_FK_ERROR))
goto err;
+ else if (ignore && !table->file->is_fatal_error(local_error,
+ HA_CHECK_FK_ERROR))
+ warn_fk_constraint_violation(thd, table, local_error);
}
if (local_error != HA_ERR_RECORD_IS_THE_SAME)
updated++;
View Lorry Controller Status