|
While ANALYZE TABLE doesn't cause row binlog events on mysql.*stat tables anymore, some other statements still do; it can cause replication failure even in the best setup, when both master and slave have the same value of use_stat_tables; and even more so if the variable is set on the session level.
Test case 1
Here both master and slave have the same use_stat_tables; but it doesn't help, because DROP TABLE is written into the binlog first, and then row events on mysql.*stat tables:
# Run as
|
# perl mysql-test-run.pl --mysqld=--use-stat-tables=preferably <testname>
|
|
--source include/master-slave.inc
|
--source include/have_binlog_format_row.inc
|
|
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
|
ANALYZE TABLE t1;
|
DROP TABLE t1;
|
|
--sync_slave_with_master
|
|
|
...
|
slave-relay-bin.000002 711 Query 1 530 use `test`; DROP TABLE `t1` /* generated by server */
|
slave-relay-bin.000002 815 Query 1 598 BEGIN
|
slave-relay-bin.000002 883 Table_map 1 654 table_id: 1 (mysql.table_stat)
|
slave-relay-bin.000002 939 Table_map 1 728 table_id: 2 (mysql.column_stat)
|
slave-relay-bin.000002 1013 Table_map 1 790 table_id: 3 (mysql.index_stat)
|
slave-relay-bin.000002 1075 Delete_rows 1 830 table_id: 2
|
slave-relay-bin.000002 1115 Delete_rows 1 876 table_id: 1 flags: STMT_END_F
|
slave-relay-bin.000002 1161 Query 1 945 COMMIT
|
So, slave SQL thread fails with
|
Could not execute Delete_rows event on table mysql.column_stat; Can't find record in 'column_stat', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 830
|
Test case 2
In this slightly different scenario, if we enable statistics globally for both master and slave, replication works all right, apparently because statement and row events are written in the reverse order comparing to Test case 1; but if use_stat_tables is enabled on master on the session level, it again causes replication abort, since slave didn't get the memo and didn't write anything to the stat tables, so it has nothing to remove:
# Run as
|
# perl mysql-test-run.pl <test name>
|
|
--source include/master-slave.inc
|
--source include/have_binlog_format_row.inc
|
|
SET use_stat_tables = PREFERABLY;
|
|
CREATE TABLE t1 ( a INT, b INT );
|
ANALYZE TABLE t1;
|
ALTER TABLE t1 DROP COLUMN b;
|
|
--sync_slave_with_master
|
slave-relay-bin.000002 718 Query 1 501 BEGIN
|
slave-relay-bin.000002 786 Table_map 1 575 table_id: 34 (mysql.column_stat)
|
slave-relay-bin.000002 860 Delete_rows 1 615 table_id: 34 flags: STMT_END_F
|
slave-relay-bin.000002 900 Query 1 684 COMMIT
|
slave-relay-bin.000002 969 Query 1 775 use `test`; ALTER TABLE t1 DROP COLUMN b
|
Slave SQL thread aborts with
Could not execute Delete_rows event on table mysql.column_stat; Can't find record in 'column_stat', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 615
|
Test case 3
Here is yet another case which I noticed to write row events on stat tables into the binary log; I'm adding it in case this problem requires case-by-case approach (there might be more, though):
# Run as
|
# perl mysql-test-run.pl <test name>
|
|
--source include/have_partition.inc
|
--source include/have_binlog_format_row.inc
|
|
SET use_stat_tables = PREFERABLY;
|
|
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM PARTITION BY HASH(a) PARTITIONS 2;
|
ALTER TABLE t1 ANALYZE PARTITION p1;
|
SHOW BINLOG EVENTS;
|
|
master-bin.000001 245 Query 1 381 use `test`; CREATE TABLE t1 ( a INT ) ENGINE=MyISAM PARTITION BY HASH(a) PARTITIONS 2
|
master-bin.000001 381 Query 1 449 BEGIN
|
master-bin.000001 449 Table_map 1 505 table_id: 43 (mysql.table_stat)
|
master-bin.000001 505 Table_map 1 579 table_id: 34 (mysql.column_stat)
|
master-bin.000001 579 Table_map 1 641 table_id: 40 (mysql.index_stat)
|
master-bin.000001 641 Write_rows 1 687 table_id: 43
|
master-bin.000001 687 Write_rows 1 727 table_id: 34 flags: STMT_END_F
|
master-bin.000001 727 Query 1 796 COMMIT
|
master-bin.000001 796 Query 1 894 use `test`; ALTER TABLE t1 ANALYZE PARTITION p1
|
bzr version-info:
revision-id: igor@askmonty.org-20120823182226-tv99ic3pbk5wela9
|
date: 2012-08-23 11:22:26 -0700
|
build-date: 2012-08-24 17:40:34 +0400
|
revno: 3363
|
|