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

[patch] gtid replication broken during upgrade to debian 10.0.16

    XMLWordPrintable

    Details

      Description

      after upgrading two servers from 10.0.15 to 10.0.16 replicating off each other in gtid replication mode Current_Pos their slave status was as follows:

      server greeneggs:

      MariaDB [mysql]> show slave status\G
      *************************** 1. row ***************************
                     Slave_IO_State: 
                        Master_Host: 192.168.138.168
                        Master_User: replica
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mariadb-bin.010731
                Read_Master_Log_Pos: 25836657
                     Relay_Log_File: mysqld-relay-bin.000053
                      Relay_Log_Pos: 4
              Relay_Master_Log_File: mariadb-bin.010731
                   Slave_IO_Running: No
                  Slave_SQL_Running: Yes
                    Replicate_Do_DB: 
                Replicate_Ignore_DB: 
                 Replicate_Do_Table: 
             Replicate_Ignore_Table: 
            Replicate_Wild_Do_Table: 
        Replicate_Wild_Ignore_Table: peoplesforum.cache%
                         Last_Errno: 0
                         Last_Error: 
                       Skip_Counter: 0
                Exec_Master_Log_Pos: 25836657
                    Relay_Log_Space: 546
                    Until_Condition: None
                     Until_Log_File: 
                      Until_Log_Pos: 0
                 Master_SSL_Allowed: No
                 Master_SSL_CA_File: 
                 Master_SSL_CA_Path: 
                    Master_SSL_Cert: 
                  Master_SSL_Cipher: 
                     Master_SSL_Key: 
              Seconds_Behind_Master: NULL
      Master_SSL_Verify_Server_Cert: No
                      Last_IO_Errno: 1236
                      Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-12302-1263736, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra errorneous transactions'
                     Last_SQL_Errno: 0
                     Last_SQL_Error: 
        Replicate_Ignore_Server_Ids: 
                   Master_Server_Id: 12301
                     Master_SSL_Crl: 
                 Master_SSL_Crlpath: 
                         Using_Gtid: Current_Pos
                        Gtid_IO_Pos: 13-12302-15747,0-12302-1263736,39-12302-34591,106-12302-476,33-12302-190678,49-12302-1664631,43-12302-7199375,15-12302-69257,37-12302-39157,104-12302-4,47-12302-29257,98-12302-4377,57-12302-48207,55-12302-1620,35-12302-356,17-12302-26913,29-12302-455091,67-12302-2829328
      1 row in set (0.00 sec)

      server catinthehat:

      MariaDB [(none)]> show slave status\G
      *************************** 1. row ***************************
                     Slave_IO_State: 
                        Master_Host: 192.168.149.5
                        Master_User: replica
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mariadb-bin.008477
                Read_Master_Log_Pos: 26139596
                     Relay_Log_File: mysqld-relay-bin.000001
                      Relay_Log_Pos: 4
              Relay_Master_Log_File: mariadb-bin.008477
                   Slave_IO_Running: No
                  Slave_SQL_Running: Yes
                    Replicate_Do_DB: 
                Replicate_Ignore_DB: 
                 Replicate_Do_Table: 
             Replicate_Ignore_Table: 
            Replicate_Wild_Do_Table: 
        Replicate_Wild_Ignore_Table: peoplesforum.cache%
                         Last_Errno: 0
                         Last_Error: 
                       Skip_Counter: 0
                Exec_Master_Log_Pos: 26139596
                    Relay_Log_Space: 546
                    Until_Condition: None
                     Until_Log_File: 
                      Until_Log_Pos: 0
                 Master_SSL_Allowed: No
                 Master_SSL_CA_File: 
                 Master_SSL_CA_Path: 
                    Master_SSL_Cert: 
                  Master_SSL_Cipher: 
                     Master_SSL_Key: 
              Seconds_Behind_Master: NULL
      Master_SSL_Verify_Server_Cert: No
                      Last_IO_Errno: 1236
                      Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-12301-1263736, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra errorneous transactions'
                     Last_SQL_Errno: 0
                     Last_SQL_Error: 
        Replicate_Ignore_Server_Ids: 
                   Master_Server_Id: 12302
                     Master_SSL_Crl: 
                 Master_SSL_Crlpath: 
                         Using_Gtid: Current_Pos
                        Gtid_IO_Pos: 13-12302-15747,0-12301-1263736,39-12302-34591,106-12302-476,33-12302-190678,49-12302-1664631,43-12302-7199362,15-12302-69257,37-12302-39157,104-12302-4,47-12302-29257,98-12302-4377,57-12302-48207,55-12302-1620,35-12302-356,17-12302-26913,29-12302-455087,67-12302-2829328

      greeneggs:

      no changes to domain_id 0 in this file after the 26139596 position.
       
      root@greeneggs:/home/danblack# mysqlbinlog --base64-output=DECODE-ROWS --start-position=26139596     /var/lib/mysql/mariadb-bin.008477 | more         
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 26139596
      #150127 22:09:33 server id 12302  end_log_pos 26139634  GTID 29-12302-455088
      /*!100001 SET @@session.gtid_domain_id=29*//*!*/;
      /*!100001 SET @@session.server_id=12302*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=455088*//*!*/;
      BEGIN
      /*!*/;
      # at 26139634
      #150127 22:09:33 server id 12302  end_log_pos 26139798  Query   thread_id=4077092       exec_time=0     error_code=0
      use `lentzwiki`/*!*/;
      SET TIMESTAMP=1422396573/*!*/;
      SET @@session.pseudo_thread_id=4077092/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=0/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
      /*!\C latin1 *//*!*/;
      SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
      UPDATE /*  157.55.39.103 */ `page` SET page_counter = page_counter + 1 WHERE page_id = 1385
      /*!*/;
      ....
      #150127 22:09:40 server id 12302  end_log_pos 26147317  Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
       
      root@greeneggs:/home/danblack# mysqlbinlog --base64-output=DECODE-ROWS --verbose      /var/lib/mysql/mariadb-bin.008478 | more                        
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:10:04 server id 12302  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:10:04 at startup
      ROLLBACK/*!*/;
      # at 248
      #150127 22:10:04 server id 12302  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12302-1263625,
      # 0-12301-1263643,
      # 55-12302-1620,
      # 106-12302-476,
      # 49-12302-1664631,
      # 17-12302-26913,
      # 43-12302-7199375,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 39-12302-34591,
      # 67-12302-2829328,
      # 35-12302-356,
      # 29-12302-455091,
      # 33-12302-190678]
      # at 575
      #150127 22:10:04 server id 12302  end_log_pos 616       Binlog checkpoint mariadb-bin.008478
      # at 616
      #150127 22:10:04 server id 12302  end_log_pos 654       GTID 0-12302-1263644
      /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
      /*!100001 SET @@session.server_id=12302*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=1263644*//*!*/;
      # at 654
      #150127 22:10:04 server id 12302  end_log_pos 2418      Query   thread_id=2     exec_time=0     error_code=0
      use `mysql`/*!*/;
      SET TIMESTAMP=1422396604/*!*/;
      SET @@session.pseudo_thread_id=2/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=0/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
      /*!\C latin1 *//*!*/;
      SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
      CREATE TABLE IF NOT EXISTS db ( ...
      /*!*/;
      # at 2418
      #150127 22:10:04 server id 12302  end_log_pos 2456      GTID 0-12302-1263645
      /*!100001 SET @@session.gtid_seq_no=1263645*//*!*/;
      # at 2456
      #150127 22:10:04 server id 12302  end_log_pos 4116      Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396604/*!*/;
      CREATE TABLE IF NOT EXISTS host (  Host....
      .....
      # at 65671
      #150127 22:10:04 server id 12302  end_log_pos 65709     GTID 0-12302-1263733
      /*!100001 SET @@session.gtid_seq_no=1263733*//*!*/;
      # at 65709
      #150127 22:10:04 server id 12302  end_log_pos 65861     Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396604/*!*/;
      DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_proxies_priv` /* generated by server */
      /*!*/;
      # at 65861
      #150127 22:10:04 server id 12302  end_log_pos 65880     Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
      # mysqlbinlog --base64-output=DECODE-ROWS --verbose      /var/lib/mysql/mariadb-bin.008479 | more
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:10:07 server id 12302  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:10:07 at startup
      ROLLBACK/*!*/;
      # at 248
      #150127 22:10:07 server id 12302  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12301-1263643,
      # 0-12302-1263733,
      # 39-12302-34591,
      # 106-12302-476,
      # 33-12302-190678,
      # 49-12302-1664631,
      # 43-12302-7199375,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 55-12302-1620,
      # 35-12302-356,
      # 67-12302-2829328,
      # 29-12302-455091,
      # 17-12302-26913]
      # at 575
      #150127 22:10:07 server id 12302  end_log_pos 616       Binlog checkpoint mariadb-bin.008479
      # at 616
      #150127 22:10:07 server id 12302  end_log_pos 635       Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
       
      mariadb-bin.008480
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:10:09 server id 12302  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:10:09 at startup
      ROLLBACK/*!*/;
      # at 248
      #150127 22:10:09 server id 12302  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12301-1263643,
      # 0-12302-1263733,
      # 55-12302-1620,
      # 106-12302-476,
      # 17-12302-26913,
      # 33-12302-190678,
      # 43-12302-7199375,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 39-12302-34591,
      # 67-12302-2829328,
      # 35-12302-356,
      # 29-12302-455091,
      # 49-12302-1664631]
      # at 575
      #150127 22:10:09 server id 12302  end_log_pos 616       Binlog checkpoint mariadb-bin.008480
      # at 616
      #150127 22:10:09 server id 12302  end_log_pos 654       GTID 0-12302-1263734
      /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
      /*!100001 SET @@session.server_id=12302*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=1263734*//*!*/;
      # at 654
      #150127 22:10:09 server id 12302  end_log_pos 835       Query   thread_id=2     exec_time=0     error_code=0
      use `mysql`/*!*/;
      SET TIMESTAMP=1422396609/*!*/;
      SET @@session.pseudo_thread_id=2/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=1073741824/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
      /*!\C latin1 *//*!*/;
      SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
       ALTER TABLE user CHANGE Password Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL;
      /*!*/;
      # at 835
      #150127 22:10:09 server id 12302  end_log_pos 854       Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
      # mysqlbinlog --base64-output=DECODE-ROWS --verbose      /var/lib/mysql/mariadb-bin.008482 | more 
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:10:14 server id 12302  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:10:14 at startup
      ROLLBACK/*!*/;
      # at 248
      #150127 22:10:14 server id 12302  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12301-1263643,
      # 0-12302-1263735,
      # 55-12302-1620,
      # 106-12302-476,
      # 33-12302-190678,
      # 49-12302-1664631,
      # 43-12302-7199375,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 39-12302-34591,
      # 67-12302-2829328,
      # 35-12302-356,
      # 29-12302-455091,
      # 17-12302-26913]
      # at 575
      #150127 22:10:14 server id 12302  end_log_pos 616       Binlog checkpoint mariadb-bin.008482
      # at 616
      #150127 22:10:14 server id 12302  end_log_pos 654       GTID 0-12302-1263736
      /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
      /*!100001 SET @@session.server_id=12302*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=1263736*//*!*/;
      # at 654
      #150127 22:10:14 server id 12302  end_log_pos 964       Query   thread_id=2     exec_time=0     error_code=0
      use `mysql`/*!*/;
      SET TIMESTAMP=1422396614/*!*/;
      SET @@session.pseudo_thread_id=2/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=1073741824/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
      /*!\C latin1 *//*!*/;
      SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
       CREATE TABLE IF NOT EXISTS plugin (name char(64) COLLATE utf8_bin NOT NULL DEFAULT '',    dl char(128) COLLATE utf8_bin NOT NULL DEFAULT '',    PRIMARY KEY (name)) ENGINE=MyISAM DEFAULT CHAR
      SET=utf8 COLLATE=utf8_bin COMMENT='MySQL plugins';
      /*!*/;
      # at 964
      #150127 22:10:14 server id 12302  end_log_pos 983       Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
      # mysqlbinlog --base64-output=DECODE-ROWS --verbose      /var/lib/mysql/mariadb-bin.008482 | more 
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:10:14 server id 12302  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:10:14 at startup
      ROLLBACK/*!*/;
      # at 248
      #150127 22:10:14 server id 12302  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12301-1263643,
      # 0-12302-1263735,
      # 55-12302-1620,
      # 106-12302-476,
      # 33-12302-190678,
      # 49-12302-1664631,
      # 43-12302-7199375,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 39-12302-34591,
      # 67-12302-2829328,
      # 35-12302-356,
      # 29-12302-455091,
      # 17-12302-26913]
      # at 575
      #150127 22:10:14 server id 12302  end_log_pos 616       Binlog checkpoint mariadb-bin.008482
      # at 616
      #150127 22:10:14 server id 12302  end_log_pos 654       GTID 0-12302-1263736
      /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
      /*!100001 SET @@session.server_id=12302*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=1263736*//*!*/;
      # at 654
      #150127 22:10:14 server id 12302  end_log_pos 964       Query   thread_id=2     exec_time=0     error_code=0
      use `mysql`/*!*/;
      SET TIMESTAMP=1422396614/*!*/;
      SET @@session.pseudo_thread_id=2/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=1073741824/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
      /*!\C latin1 *//*!*/;
      SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
       CREATE TABLE IF NOT EXISTS plugin (name char(64) COLLATE utf8_bin NOT NULL DEFAULT '',    dl char(128) COLLATE utf8_bin NOT NULL DEFAULT '',    PRIMARY KEY (name)) ENGINE=MyISAM DEFAULT CHAR
      SET=utf8 COLLATE=utf8_bin COMMENT='MySQL plugins';
      /*!*/;
      # at 964
      #150127 22:10:14 server id 12302  end_log_pos 983       Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
       
      mariadb-bin.008483 (empty)
       
      # mysqlbinlog --base64-output=DECODE-ROWS --verbose      /var/lib/mysql/mariadb-bin.008484 | more 
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:10:18 server id 12302  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:10:18
      # Warning: this binlog is either in use or was not closed properly.
      # at 248
      #150127 22:10:18 server id 12302  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12301-1263643,
      # 0-12302-1263736,
      # 39-12302-34591,
      # 106-12302-476,
      # 17-12302-26913,
      # 33-12302-190678,
      # 43-12302-7199375,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 55-12302-1620,
      # 35-12302-356,
      # 67-12302-2829328,
      # 29-12302-455091,
      # 49-12302-1664631]
      # at 575
      #150127 22:10:18 server id 12302  end_log_pos 616       Binlog checkpoint mariadb-bin.008483
      # at 616
      #700101  0:00:00 server id 12302  end_log_pos 657       Binlog checkpoint mariadb-bin.008484
      # at 657
      #150127 22:10:39 server id 12302  end_log_pos 695       GTID 29-12302-455092
      /*!100001 SET @@session.gtid_domain_id=29*//*!*/;
      /*!100001 SET @@session.server_id=12302*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=455092*//*!*/;
      BEGIN
      /*!*/;
      # at 695
      #150127 22:10:39 server id 12302  end_log_pos 858       Query   thread_id=70    exec_time=0     error_code=0
      use `lentzwiki`/*!*/;
      SET TIMESTAMP=1422396639/*!*/;
      SET @@session.pseudo_thread_id=70/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=0/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;

      catinthehat:

      root@catinthehat:/home/danblack# mysqlbinlog  --start-position=25836657 /var/lib/mysql/mariadb-bin.010731       
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 18:33:46 server id 12301  end_log_pos 248       Start: binlog v 4, server v 10.0.15-MariaDB-1~wheezy-log created 150127 18:33:46
      BINLOG '
      CtrHVA8NMAAA9AAAAPgAAAAAAAQAMTAuMC4xNS1NYXJpYURCLTF+d2hlZXp5LWxvZwAAAAAAAAAA
      AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3AAEGggAAAAICAgCAAAACgoKAAAAAAAA
      AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAEEwQA1Ak15Q==
      '/*!*/;
      # at 25836657
      #150127 22:09:33 server id 12301  end_log_pos 25836676  Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
       
       
      # mysqlbinlog  /var/lib/mysql/mariadb-bin.010732 | more
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
      /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      DELIMITER /*!*/;
      # at 4
      #150127 22:09:42 server id 12301  end_log_pos 248       Start: binlog v 4, server v 10.0.16-MariaDB-1~wheezy-log created 150127 22:09:42 at startup
      ROLLBACK/*!*/;
      BINLOG '
      pgzIVA8NMAAA9AAAAPgAAAAAAAQAMTAuMC4xNi1NYXJpYURCLTF+d2hlZXp5LWxvZwAAAAAAAAAA
      AAAAAAAAAAAAAAAAAACmDMhUEzgNAAgAEgAEBAQEEgAA3AAEGggAAAAICAgCAAAACgoKAAAAAAAA
      AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAEEwQAnGHsRQ==
      '/*!*/;
      # at 248
      #150127 22:09:42 server id 12301  end_log_pos 575       Gtid list [13-12302-15747,
      # 0-12302-1263625,
      # 0-12301-1263643,
      # 55-12302-1617,
      # 106-12302-476,
      # 17-12302-26913,
      # 49-12302-1664631,
      # 43-12302-7199362,
      # 15-12302-69257,
      # 37-12302-39157,
      # 104-12302-4,
      # 47-12302-29257,
      # 98-12302-4377,
      # 57-12302-48207,
      # 39-12302-34591,
      # 67-12302-2829328,
      # 35-12302-356,
      # 29-12302-455087,
      # 33-12302-190678]
      # at 575
      #150127 22:09:42 server id 12301  end_log_pos 616       Binlog checkpoint mariadb-bin.010732
      # at 616
      #150127 22:09:42 server id 12301  end_log_pos 654       GTID 0-12301-1263644
      /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
      /*!100001 SET @@session.server_id=12301*//*!*/;
      /*!100001 SET @@session.gtid_seq_no=1263644*//*!*/;
      # at 654
      #150127 22:09:42 server id 12301  end_log_pos 2418      Query   thread_id=2     exec_time=0     error_code=0
      use `mysql`/*!*/;
      SET TIMESTAMP=1422396582/*!*/;
      SET @@session.pseudo_thread_id=2/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=0/*!*/;
      SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
      /*!\C latin1 *//*!*/;
      SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
      CREATE TABLE IF NOT EXISTS db ( 
      ....
      # at 2418
      #150127 22:09:42 server id 12301  end_log_pos 2456      GTID 0-12301-1263645
      /*!100001 SET @@session.gtid_seq_no=1263645*//*!*/;
      # at 2456
      #150127 22:09:42 server id 12301  end_log_pos 4116      Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396582/*!*/;
      CREATE TABLE IF NOT EXISTS host 
      ...
      # at 64324
      #150127 22:09:42 server id 12301  end_log_pos 64501     Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396582/*!*/;
      CREATE TABLE performance_schema.session_account_connect_attrs  LIKE performance_schema.session_connect_attrs
      /*!*/;
      # at 64501
      #150127 22:09:42 server id 12301  end_log_pos 64539     GTID 0-12301-1263728
      /*!100001 SET @@session.gtid_seq_no=1263728*//*!*/;
      # at 64539
      #150127 22:09:42 server id 12301  end_log_pos 64650     Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396582/*!*/;
      CREATE TEMPORARY TABLE tmp_user LIKE user;
      /*!*/;
      # at 64650
      #150127 22:09:42 server id 12301  end_log_pos 64688     GTID 0-12301-1263729
      /*!100001 SET @@session.gtid_seq_no=1263729*//*!*/;
      BEGIN
      /*!*/;
      # at 64688
      #150127 22:09:42 server id 12301  end_log_pos 64960     Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396582/*!*/;
      INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','
      ','N', 'N');
      /*!*/;
      # at 64960
      #150127 22:09:42 server id 12301  end_log_pos 65035     Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396582/*!*/;
      COMMIT
      /*!*/;
      # at 65035
      ...
      #150127 22:09:42 server id 12301  end_log_pos 65709     GTID 0-12301-1263733
      /*!100001 SET @@session.gtid_seq_no=1263733*//*!*/;
      # at 65709
      #150127 22:09:42 server id 12301  end_log_pos 65861     Query   thread_id=2     exec_time=0     error_code=0
      SET TIMESTAMP=1422396582/*!*/;
      DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_proxies_priv` /* generated by server */
      /*!*/;
      # at 65861
      #150127 22:09:42 server id 12301  end_log_pos 65880     Stop
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

      Looks like the mysql_install_db pushed statements to the binlogs of both servers and confused the gtid positions. Can these be not written to binlog (like the mysql_update)?

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: