Description:
MySQL Document states:
== In MySQL 8.0, when binlog_format is set to ROW or MIXED, statements that exclusively use temporary tables are not logged on the master, and therefore the temporary tables are not replicated. Statements that involve a mix of temporary and nontemporary tables are logged on the master only for the operations on nontemporary tables, and the operations on temporary tables are not logged. This means that there are never any temporary tables on the slave to be lost in the event of an unplanned shutdown by the slave.
[+] https://dev.mysql.com/doc/refman/8.0/en/replication-features-temptables.html
== Temporary tables are not replicated when using row-based or mixed format because there is no need.
[+] https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-rbr-usage.html#repl...
Noticed an edge case where the binary logs has these statements which cause the replication to fail, since other statements might not have been logged
How to repeat:
== Binary log format set to 'MIXED'.
== Create a simple function that can be invoked later.
CREATE DEFINER=`root`@`%` FUNCTION `replica_test_function`() RETURNS int(11)
BEGIN RETURN 1;
END
== Create a temporary table.
create temporary table t5(id int, id1 int);
== Create a Procedure that inserts value into this temporary table, and also calls the Function that was created in first step.
Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `Test_PROC_For_Replica`(counter1 int)
BEGIN
INSERT INTO t5
select counter1, replica_test_function() counter2;
select * from t5;
END
== When the procedure is called, the statements are written to Binary logs.
== OUTPUT:
mysql> call Test_PROC_For_Replica(200);
mysql> show binlog events in 'mysql-bin-changelog.090172';
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| mysql-bin-changelog.090172 | 4 | Format_desc | 872357350 | 124 | Server ver: 8.0.16, Binlog ver: 4 |
| mysql-bin-changelog.090172 | 124 | Previous_gtids | 872357350 | 155 | |
| mysql-bin-changelog.090172 | 155 | Anonymous_Gtid | 872357350 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.090172 | 234 | Query | 872357350 | 313 | BEGIN |
| mysql-bin-changelog.090172 | 313 | Query | 872357350 | 501 | INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1591099888518) ON DUPLICATE KEY UPDATE value = 1591099888518 |
| mysql-bin-changelog.090172 | 501 | Xid | 872357350 | 532 | COMMIT /* xid=10025 */ |
| mysql-bin-changelog.090172 | 532 | Anonymous_Gtid | 872357350 | 611 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.090172 | 611 | Query | 872357350 | 688 | BEGIN |
| mysql-bin-changelog.090172 | 688 | Query | 872357350 | 843 | use `bltest`; INSERT INTO t5
select NAME_CONST('counter1',200), replica_test_function() counter2 |
| mysql-bin-changelog.090172 | 843 | Query | 872357350 | 921 | COMMIT |
| mysql-bin-changelog.090172 | 921 | Rotate | 872357350 | 978 | mysql-bin-changelog.090173;pos=4 |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)
As we can see, the insert statement is logged.
Suggested fix:
Using binlog format as 'ROW' ensures that it is not logged in the binary logs.
