Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-485

Statistics: Row-based replication aborts on some DDL statements if statistics is enabled

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 10.0.0
    • None
    • None

    Description

      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

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.