diff --git a/mysql-test/suite/rpl/r/bug80821.result b/mysql-test/suite/rpl/r/bug80821.result new file mode 100644 index 00000000000..ec12ce4e0c3 --- /dev/null +++ b/mysql-test/suite/rpl/r/bug80821.result @@ -0,0 +1,37 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +CREATE TABLE t0 ( +f0 INT PRIMARY KEY +) ENGINE=innodb; +CREATE TABLE t1 ( +f1 INT PRIMARY KEY, +f0 INTEGER, +FOREIGN KEY (f0) +REFERENCES t0(f0) +ON DELETE CASCADE +) ENGINE=innodb; +INSERT INTO t0 VALUES (0); +INSERT INTO t0 VALUES (1); +INSERT INTO t1 VALUES (0, 0); +INSERT INTO t1 VALUES (1, 0); +DELETE t0a.*, t1.* FROM t0 AS t0a, t1 WHERE t0a.f0 = 0 AND t1.f1 = 0; +SELECT COUNT(*) = 1 FROM t0; +COUNT(*) = 1 +1 +SELECT COUNT(*) = 0 FROM t1; +COUNT(*) = 0 +1 +include/sync_slave_sql_with_master.inc +SELECT COUNT(*) = 1 FROM t0; +COUNT(*) = 1 +1 +SELECT COUNT(*) = 0 FROM t1; +COUNT(*) = 0 +1 +[connection master] +DROP TABLE t1; +DROP TABLE t0; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/bug80821.test b/mysql-test/suite/rpl/t/bug80821.test new file mode 100644 index 00000000000..3ffd0828cf5 --- /dev/null +++ b/mysql-test/suite/rpl/t/bug80821.test @@ -0,0 +1,37 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +CREATE TABLE t0 ( +f0 INT PRIMARY KEY +) ENGINE=innodb; + +CREATE TABLE t1 ( +f1 INT PRIMARY KEY, +f0 INTEGER, +FOREIGN KEY (f0) +REFERENCES t0(f0) +ON DELETE CASCADE +) ENGINE=innodb; + +INSERT INTO t0 VALUES (0); +INSERT INTO t0 VALUES (1); +INSERT INTO t1 VALUES (0, 0); +INSERT INTO t1 VALUES (1, 0); + +# Should delete both t1 values, and the 0 value from t0 +DELETE t0a.*, t1.* FROM t0 AS t0a, t1 WHERE t0a.f0 = 0 AND t1.f1 = 0; + +SELECT COUNT(*) = 1 FROM t0; +SELECT COUNT(*) = 0 FROM t1; + +--source include/sync_slave_sql_with_master.inc + +SELECT COUNT(*) = 1 FROM t0; +SELECT COUNT(*) = 0 FROM t1; + +--source include/rpl_connection_master.inc + +DROP TABLE t1; +DROP TABLE t0; + +--source include/rpl_end.inc diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index a8f25749e99..d1f05b36856 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -835,6 +835,53 @@ bool Query_result_delete::prepare(THD *thd, List &, SELECT_LEX_UNIT *u) { DBUG_RETURN(false); } +/** + Test that the two strings are equal, accoding to the lower_case_table_names + setting. + + @param a A table or database name + @param b A table or database name + @retval bool True if the two names are equal +*/ +static bool db_or_table_name_equals(const char *a, const char *b) { + return lower_case_table_names ? my_strcasecmp(files_charset_info, a, b) == 0 + : strcmp(a, b) == 0; +} + +/** + Test that the subject table (of DELETE) has a cascade foreign key + parent present in the query. + + @param thd thread handle + @param table table to be checked (must be updatable base table) + @param table_list List of tables to check against + + @retval bool True if cascade parent found. +*/ +static bool has_cascade_dependency(THD *thd, const TABLE_LIST &table, + TABLE_LIST *table_list) { + DBUG_ASSERT(&table == const_cast(table).updatable_base_table()); + + List fk_table_list; + List_iterator fk_table_list_it(fk_table_list); + + table.table->file->get_cascade_foreign_key_table_list(thd, &fk_table_list); + + st_handler_tablename *tbl_name; + while ((tbl_name = fk_table_list_it++)) { + for (TABLE_LIST *curr = table_list; curr; curr = curr->next_local) { + const bool same_table_name = + db_or_table_name_equals(curr->table_name, tbl_name->tablename); + const bool same_db_name = db_or_table_name_equals(curr->db, tbl_name->db); + if (same_table_name && same_db_name) { + return true; + } + } + } + + return false; +} + /** Optimize for deletion from one or more tables in a multi-table DELETE @@ -842,7 +889,6 @@ bool Query_result_delete::prepare(THD *thd, List &, SELECT_LEX_UNIT *u) { Calculate which tables can be deleted from immediately and which tables must be delayed. Create objects for handling of delayed deletes. */ - bool Query_result_delete::optimize() { DBUG_ENTER("Query_result_delete::optimize"); @@ -868,7 +914,9 @@ bool Query_result_delete::optimize() { for (TABLE_LIST *tr = select->leaf_tables; tr; tr = tr->next_leaf) { if (!tr->updating) continue; delete_table_map |= tr->map(); - if (delete_while_scanning && unique_table(tr, join->tables_list, false)) { + if (delete_while_scanning && + (unique_table(tr, join->tables_list, false) || + has_cascade_dependency(thd, *tr, join->tables_list))) { /* If the table being deleted from is also referenced in the query, defer delete so that the delete doesn't interfer with reading of this