Skip to content

Commit b27fa60

Browse files
author
Nisha Gopalakrishnan
committed
Merge branch 'mysql-5.5' into mysql-5.6
2 parents 58c6600 + d9c541c commit b27fa60

6 files changed

Lines changed: 139 additions & 12 deletions

File tree

‎mysql-test/r/insert.result‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -712,3 +712,41 @@ ERROR 42000: Column 'a' specified twice
712712
INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
713713
ERROR 42000: Column 'a' specified twice
714714
DROP TABLE t1;
715+
#
716+
# BUG#22037930: INSERT IGNORE FAILS TO IGNORE
717+
# FOREIGN KEY CONSTRAINT
718+
# Setup.
719+
CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB;
720+
CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1))
721+
ENGINE=INNODB;
722+
INSERT INTO t1 VALUES(0);
723+
INSERT INTO t2 VALUES(0);
724+
# Without fix, an error is reported.
725+
INSERT IGNORE INTO t2 VALUES(1);
726+
Warnings:
727+
Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
728+
UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0;
729+
Warnings:
730+
Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
731+
UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0;
732+
Warnings:
733+
Warning 1451 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
734+
# Test for multi update.
735+
UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3;
736+
Warnings:
737+
Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
738+
UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3;
739+
Warnings:
740+
Warning 1451 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
741+
# Reports an error since IGNORE is not used.
742+
INSERT INTO t2 VALUES(1);
743+
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`))
744+
UPDATE t2 SET fld2=20 WHERE fld2=0;
745+
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`))
746+
UPDATE t1 SET fld1=20 WHERE fld1=0;
747+
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`))
748+
UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3;
749+
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`))
750+
UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3;
751+
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`))
752+
DROP TABLE t2, t1;

‎mysql-test/t/insert.test‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -550,3 +550,44 @@ INSERT IGNORE t1 (a, a) SELECT 1,1;
550550
INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
551551

552552
DROP TABLE t1;
553+
554+
555+
--echo #
556+
--echo # BUG#22037930: INSERT IGNORE FAILS TO IGNORE
557+
--echo # FOREIGN KEY CONSTRAINT
558+
559+
--echo # Setup.
560+
CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB;
561+
CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1))
562+
ENGINE=INNODB;
563+
INSERT INTO t1 VALUES(0);
564+
INSERT INTO t2 VALUES(0);
565+
566+
--echo # Without fix, an error is reported.
567+
--enable_warnings
568+
INSERT IGNORE INTO t2 VALUES(1);
569+
UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0;
570+
UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0;
571+
572+
--echo # Test for multi update.
573+
UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3;
574+
UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3;
575+
--disable_warnings
576+
577+
--echo # Reports an error since IGNORE is not used.
578+
--error ER_NO_REFERENCED_ROW_2
579+
INSERT INTO t2 VALUES(1);
580+
581+
--error ER_NO_REFERENCED_ROW_2
582+
UPDATE t2 SET fld2=20 WHERE fld2=0;
583+
584+
--error ER_ROW_IS_REFERENCED_2
585+
UPDATE t1 SET fld1=20 WHERE fld1=0;
586+
587+
--error ER_NO_REFERENCED_ROW_2
588+
UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3;
589+
590+
--error ER_ROW_IS_REFERENCED_2
591+
UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3;
592+
593+
DROP TABLE t2, t1;

‎sql/handler.cc‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7613,3 +7613,28 @@ fl_create_iterator(enum handler_iterator_type type,
76137613
}
76147614
}
76157615
#endif /*TRANS_LOG_MGM_EXAMPLE_CODE*/
7616+
7617+
7618+
/**
7619+
Report a warning for FK constraint violation.
7620+
7621+
@param thd Thread handle.
7622+
@param table table on which the operation is performed.
7623+
@param error handler error number.
7624+
*/
7625+
void warn_fk_constraint_violation(THD *thd,TABLE *table, int error)
7626+
{
7627+
String str;
7628+
switch(error) {
7629+
case HA_ERR_ROW_IS_REFERENCED:
7630+
table->file->get_error_message(error, &str);
7631+
push_warning(thd, Sql_condition::WARN_LEVEL_WARN,
7632+
ER_ROW_IS_REFERENCED_2, str.c_ptr_safe());
7633+
break;
7634+
case HA_ERR_NO_REFERENCED_ROW:
7635+
table->file->get_error_message(error, &str);
7636+
push_warning(thd, Sql_condition::WARN_LEVEL_WARN,
7637+
ER_NO_REFERENCED_ROW_2, str.c_ptr_safe());
7638+
break;
7639+
}
7640+
}

‎sql/handler.h‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
#define HANDLER_INCLUDED
33

44
/*
5-
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
5+
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
66
77
This program is free software; you can redistribute it and/or
88
modify it under the terms of the GNU General Public License
@@ -369,6 +369,7 @@ static const uint MYSQL_START_TRANS_OPT_READ_WRITE = 4;
369369
/* Flags for method is_fatal_error */
370370
#define HA_CHECK_DUP_KEY 1
371371
#define HA_CHECK_DUP_UNIQUE 2
372+
#define HA_CHECK_FK_ERROR 4
372373
#define HA_CHECK_DUP (HA_CHECK_DUP_KEY + HA_CHECK_DUP_UNIQUE)
373374

374375
enum legacy_db_type
@@ -2147,7 +2148,10 @@ class handler :public Sql_alloc
21472148
if (!error ||
21482149
((flags & HA_CHECK_DUP_KEY) &&
21492150
(error == HA_ERR_FOUND_DUPP_KEY ||
2150-
error == HA_ERR_FOUND_DUPP_UNIQUE)))
2151+
error == HA_ERR_FOUND_DUPP_UNIQUE)) ||
2152+
((flags & HA_CHECK_FK_ERROR) &&
2153+
(error == HA_ERR_ROW_IS_REFERENCED ||
2154+
error == HA_ERR_NO_REFERENCED_ROW)))
21512155
return FALSE;
21522156
return TRUE;
21532157
}
@@ -3488,5 +3492,6 @@ inline const char *table_case_name(HA_CREATE_INFO *info, const char *name)
34883492

34893493
void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag);
34903494
void print_keydup_error(TABLE *table, KEY *key, myf errflag);
3495+
void warn_fk_constraint_violation(THD *thd, TABLE *table, int error);
34913496

34923497
#endif /* HANDLER_INCLUDED */

‎sql/sql_insert.cc‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
2+
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
33
44
This program is free software; you can redistribute it and/or modify
55
it under the terms of the GNU General Public License as published by
@@ -1676,7 +1676,7 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, COPY_INFO *update)
16761676
else
16771677
table->file->insert_id_for_cur_row= insert_id_for_cur_row;
16781678
bool is_duplicate_key_error;
1679-
if (table->file->is_fatal_error(error, HA_CHECK_DUP))
1679+
if (table->file->is_fatal_error(error, HA_CHECK_DUP | HA_CHECK_FK_ERROR))
16801680
goto err;
16811681
is_duplicate_key_error= table->file->is_fatal_error(error, 0);
16821682
if (!is_duplicate_key_error)
@@ -1814,7 +1814,8 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, COPY_INFO *update)
18141814
error != HA_ERR_RECORD_IS_THE_SAME)
18151815
{
18161816
if (ignore_errors &&
1817-
!table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
1817+
!table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
1818+
HA_CHECK_FK_ERROR))
18181819
{
18191820
goto ok_or_after_trg_err;
18201821
}
@@ -1922,7 +1923,7 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, COPY_INFO *update)
19221923
{
19231924
DEBUG_SYNC(thd, "write_row_noreplace");
19241925
if (!ignore_errors ||
1925-
table->file->is_fatal_error(error, HA_CHECK_DUP))
1926+
table->file->is_fatal_error(error, HA_CHECK_DUP | HA_CHECK_FK_ERROR))
19261927
goto err;
19271928
table->file->restore_auto_increment(prev_insert_id);
19281929
goto ok_or_after_trg_err;
@@ -1940,6 +1941,9 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, COPY_INFO *update)
19401941
my_safe_afree(key,table->s->max_unique_length,MAX_KEY_LENGTH);
19411942
if (!table->file->has_transactions())
19421943
thd->transaction.stmt.mark_modified_non_trans_table();
1944+
if (ignore_errors &&
1945+
!table->file->is_fatal_error(error, HA_CHECK_FK_ERROR))
1946+
warn_fk_constraint_violation(thd, table, error);
19431947
DBUG_RETURN(trg_error);
19441948

19451949
err:

‎sql/sql_update.cc‎

Lines changed: 20 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
22
33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License as published by
@@ -826,21 +826,26 @@ int mysql_update(THD *thd,
826826
error= 0;
827827
}
828828
else if (!ignore ||
829-
table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
829+
table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
830+
HA_CHECK_FK_ERROR))
830831
{
831832
/*
832833
If (ignore && error is ignorable) we don't have to
833834
do anything; otherwise...
834835
*/
835836
myf flags= 0;
836837

837-
if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
838+
if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
839+
HA_CHECK_FK_ERROR))
838840
flags|= ME_FATALERROR; /* Other handler errors are fatal */
839841

840842
table->file->print_error(error,MYF(flags));
841843
error= 1;
842844
break;
843845
}
846+
else if (ignore && !table->file->is_fatal_error(error,
847+
HA_CHECK_FK_ERROR))
848+
warn_fk_constraint_violation(thd, table, error);
844849
}
845850

846851
if (table->triggers &&
@@ -2073,20 +2078,25 @@ bool multi_update::send_data(List<Item> &not_used_values)
20732078
{
20742079
updated--;
20752080
if (!ignore ||
2076-
table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
2081+
table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
2082+
HA_CHECK_FK_ERROR))
20772083
{
20782084
/*
20792085
If (ignore && error == is ignorable) we don't have to
20802086
do anything; otherwise...
20812087
*/
20822088
myf flags= 0;
20832089

2084-
if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
2090+
if (table->file->is_fatal_error(error, HA_CHECK_DUP_KEY |
2091+
HA_CHECK_FK_ERROR))
20852092
flags|= ME_FATALERROR; /* Other handler errors are fatal */
20862093

20872094
table->file->print_error(error,MYF(flags));
20882095
DBUG_RETURN(1);
20892096
}
2097+
else if (ignore && !table->file->is_fatal_error(error,
2098+
HA_CHECK_FK_ERROR))
2099+
warn_fk_constraint_violation(thd, table, error);
20902100
}
20912101
else
20922102
{
@@ -2365,8 +2375,12 @@ int multi_update::do_updates()
23652375
else if (local_error == HA_ERR_RECORD_IS_THE_SAME)
23662376
local_error= 0;
23672377
else if (!ignore ||
2368-
table->file->is_fatal_error(local_error, HA_CHECK_DUP_KEY))
2378+
table->file->is_fatal_error(local_error, HA_CHECK_DUP_KEY |
2379+
HA_CHECK_FK_ERROR))
23692380
goto err;
2381+
else if (ignore && !table->file->is_fatal_error(local_error,
2382+
HA_CHECK_FK_ERROR))
2383+
warn_fk_constraint_violation(thd, table, local_error);
23702384
else
23712385
local_error= 0;
23722386
}

0 commit comments

Comments
 (0)