Bug #45636 allow alter table to record changes to binary log when using rbr

• Submitted: 21 Jun 2009 10:24
• Reporter: Mikiya Okuno
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S4 (Feature request)
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[21 Jun 2009 10:24] Mikiya Okuno

Description:
If table mismatch happens between a master and a slave, replication may fail due to unique constraints mismatch or similar reasons. Currently, we should ignore errors or reload tables from the master to the slave using mysqldump in such a situation.

Once we have RBR, we can sync a table on the master and the slave like below:

mysql> set session binlog_format=row;
mysql> create table tmp_tbl like orig_tbl;
mysql> insert into tmp_tbl select * from orig_tbl;
mysql> commit;
mysql> drop table orig_tbl;
mysql> rename table tmp_tbl to orig_tbl;

This procedure comes in handy. But there's a problem that we cannot lock tables during the operation because of DDLs. So, we should stop loads from application when the procedure is performed.

Since ALTER TABLE copies all row data from the original table to an internal temporary table during alteration, we may sync a table on the master and the slave using ALTER TABLE if it can record changes to the binlog during alteration.

If there's such a functionality, we can sync a table on the master and the slave like this:

mysql> set session binlog_format=row;
mysql> ALTER TABLE tbl ENGINE=InnoDB;

How to repeat:
n/a

Suggested fix:
Introduce a new logging mode like this:

mysql> SET SESSION binlog_format=row_extended;

or

Add a new option to ALTER TABLE like this:

mysql> ALTER DUMP TABLE ...;

or

Add a new system variable to control the alter table behavior like this:

mysql> SET SESSION log_rows_during_alteration=1;