MySQL Bugs: #80474: Foreign key error

Bug #80474	Foreign key error
Submitted:	23 Feb 2016 10:02	Modified:	19 Jul 2018 7:37
Reporter:	qinglin zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.XX	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	error, foreign key

Description:
MySQL Replication introduce parallel replication in 5.6, To make slave work correctly, it use Table_map_log_event::m_flags to record foreign key Info, the step like bellow:
1) If table is referred by other table, the header of Table_map_log_event record TM_REFERRED_FK_DB_F just after table_id, according Table_map_log_event::write_data_header

2) We read log event from relay log, m_flags is restore from 1)

3) In parallel replication mode, it mark this trx should be played in sequence, and call wait_for_workers_to_finish.

4) Generate Table_map_log_event at slave side.

However, Table_map_log_event generated at slave side don't record m_flags correctly, as a result, slave->slave replication will generate foreign key error.

How to repeat:
source include/master-slave.inc;
source include/have_binlog_format_row.inc;

--connection master
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

--sync_slave_with_master
stop slave;
set global slave_parallel_workers=8;
start slave;

--connection master
insert into parent values(1)

--source include/rpl_end.inc

gdb --args mysqlbinlog -vvv slave-binlog.000002
b Table_map_log_event::Table_map_log_event

Watch m_flags and you will find the m_flags is marked by TM_REFERRED_FK_DB_F.

Suggested fix:
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ int Table_map_log_event::do_apply_event(Relay_log_info const *rli)
     my_casedn_str(system_charset_info, tname_mem);
   }
 
+  /* bugfix for foreign key */
+  if (mts_number_dbs() == OVER_MAX_DBS_IN_EVENT_MTS)
+  {
+    thd->clear_binlog_accessed_db_names();
+    thd->add_to_binlog_accessed_dbs("");
+  }

Repeat:

-- source include/have_binlog_format_row.inc
-- let $rpl_topology= 1->2->3
-- source include/rpl_init.inc

-- connection server_1
-- source include/have_innodb.inc
-- connection server_2
-- source include/have_binlog_format_row.inc
-- source include/have_innodb.inc
-- connection server_3
-- source include/have_binlog_format_row.inc
-- source include/have_innodb.inc

-- connection server_3
set @old_debug= @@global.debug;
set @old_slave_parallel_workers= @@global.slave_parallel_workers;

set @@global.debug="+d,rpl_foreign_key_parent";
stop slave;
set  global slave_parallel_workers= 8;
start slave;

-- connection server_1
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;
insert into parent values(1);
insert into child values(1, 1);
drop table child, parent;
--sleep 1

--connection server_3
stop slave;
set @@global.debug=@old_debug;
set @@global.slave_parallel_workers= @old_slave_parallel_workers;
start slave;

--source include/rpl_end.inc

--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -12259,6 +12259,13 @@ int Table_map_log_event::do_apply_event(Relay_log_info const *rli)

    /* call from mysql_client_binlog_statement() will not set rli->mi */
   rpl_filter= rli->info_thd->slave_thread ? thd->rpl_filter : global_rpl_filter;
   /* rewrite rules changed the database */
@@ -12348,6 +12355,13 @@ int Table_map_log_event::do_apply_event(Relay_log_info const *rli)
     my_free(memory);
   }

+  DBUG_EXECUTE_IF("rpl_foreign_key_parent",
+                  if (strcmp("parent", m_tblnam)== 0)
+                  {
+                    sql_print_information("in parent arm sleep");
+                    my_sleep(1000000);
+                  });
+  
   DBUG_RETURN(tblmap_status == SAME_ID_MAPPING_DIFFERENT_TABLE);
 }

Hi,

Thanks for both the report and contribution!

all best
Bogdan

does it fixed?