Description:
When triggers are used, the binary log includes Table_map events for all tables, even those that are not actually modified.
Splitting the trigger into two separate ones does not help eliminate the extra Table_map events from the binary log.
In practice, this results in a significant overhead of up to 17% in binary log size.
The replica also has to acquire and release unnecessary MDL locks, which slows down replication.
binlog_format = ROW
binlog_row_image = FULL
binlog_row_metadata = MINIMAL
How to repeat:
DROP TABLE IF EXISTS binlog_test;
CREATE TABLE IF NOT EXISTS binlog_test (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
val double,
name_1 varchar(50),
name_2 varchar(50),
name_3 varchar(50)
);
DROP TABLE IF EXISTS binlog_test_val_change_log;
CREATE TABLE IF NOT EXISTS binlog_test_val_change_log (
id_ai int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
id int UNSIGNED,
dt datetime,
dx double,
is_trigger tinyint UNSIGNED
);
DROP TABLE IF EXISTS binlog_test_name_change_log;
CREATE TABLE IF NOT EXISTS binlog_test_name_change_log (
id_ai int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
id int UNSIGNED,
name_1 varchar(50),
name_2 varchar(50),
name_3 varchar(50)
);
DELIMITER $$
CREATE TRIGGER binlog_test AFTER UPDATE ON binlog_test
FOR EACH ROW
BEGIN
IF old.val != new.val THEN
INSERT INTO binlog_test_val_change_log ( id, dt, dx, is_trigger )
VALUES (old.id, NOW(), (new.val - old.val), 1);
END IF;
IF (COALESCE(old.name_1, '') != COALESCE(new.name_1, '') OR COALESCE(old.name_2, '') != COALESCE(new.name_2, '') OR COALESCE(old.name_2, '') != COALESCE(new.name_2, '') ) THEN
INSERT IGNORE INTO binlog_test_name_change_log (id, name_1, name_2, name_3)
VALUES (old.id, old.name_1, old.name_2, old.name_3);
END IF;
END
$$
DELIMITER ;
INSERT INTO binlog_test (val, name_1, name_2, name_3)
VALUES (0.0, 'a', 'a', 'a');
INSERT INTO binlog_test (val, name_1, name_2, name_3)
VALUES (0.0, 'b', 'b', 'b');
SELECT * FROM binlog_test;
UPDATE binlog_test bt SET
bt.val = bt.val + 0.5
WHERE bt.id = 1
LIMIT 1;
UPDATE binlog_test bt SET
bt.val = bt.val + 0.7
WHERE bt.id = 1
LIMIT 1;
UPDATE binlog_test bt SET
bt.val = bt.val + 0.12
WHERE bt.id = 2
LIMIT 1;
UPDATE binlog_test bt SET
bt.name_1 = 'aaa'
WHERE bt.id = 1
LIMIT 1;
UPDATE binlog_test bt SET
bt.name_1 = 'bbb'
WHERE bt.id = 2
LIMIT 1;
SELECT * FROM binlog_test;
#1 1,2 aaa a a
#2 0,12 bbb b b
SELECT * FROM binlog_test_val_change_log;
#1 1 2026-07-27 01:42:03 0,5 1
#2 1 2026-07-27 01:42:03 0,7 1
#3 2 2026-07-27 01:42:24 0,12 1
SELECT * FROM binlog_test_name_change_log;
#1 2 b b b
#2 1 a a a
SELECT @@global.binlog_format, @@global.binlog_row_image, @@global.binlog_row_metadata;
#ROW FULL MINIMAL
SHOW BINARY LOGS;
#binlog.000001 10687 No
SHOW BINLOG EVENTS IN 'binlog.000001';
/*
binlog.000001 7454 Anonymous_Gtid 1 7533 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000001 7533 Query 1 7638 BEGIN
binlog.000001 7638 Table_map 1 7732 table_id: 103 (db_name.binlog_test)
binlog.000001 7732 Table_map 1 7841 table_id: 104 (db_name.binlog_test_name_change_log)
binlog.000001 7841 Table_map 1 7940 table_id: 105 (db_name.binlog_test_val_change_log)
binlog.000001 7940 Update_rows 1 8014 table_id: 103
binlog.000001 8014 Write_rows 1 8072 table_id: 105 flags: STMT_END_F
binlog.000001 8072 Xid 1 8103 COMMIT /* xid=53 */
*/
Suggested fix:
sql/binlog.cc
int THD::binlog_write_table_map(TABLE *table, bool is_transactional, bool binlog_rows_query)
eleminate unused mapping
