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

mysqldump restore does not properly populate mysql.gtid_slave_pos table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.13
    • Fix Version/s: None
    • Component/s: Backup
    • Labels:
      None
    • Environment:
      Ubuntu 20

      Description

      I have two MariaDB instances (named A and B). I want to set up replication between A and B.
      To do this, I perform a Mysqldump backup on A with the following parameters --opt --single-transaction --flush-privileges --routines --events --all-databases --master-data --gtid
      On instance B, i restore my backup and start replica.

      Everything seems correct.
      The global variable gtid_slave_pos is populated.

      show variables like "gtid_slave_pos";
      -----------------------+

      Variable_name Value

      -----------------------+

      gtid_slave_pos 0-1-905

      -----------------------+

      The status of the replica is correct.
      mysql:root:(none)>* show replica status \G;*
      _ Slave_IO_State: Waiting for master to send event
      Master_Host: 192.168.2.191
      Master_User: replication
      Master_Port: 3306
      Connect_Retry: 60
      Master_Log_File: myserver01_logbin.000003
      Read_Master_Log_Pos: 475
      Relay_Log_File: myserver01_relaylog.000002
      Relay_Log_Pos: 684
      Relay_Master_Log_File: myserver01_logbin.000003
      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
      Replicate_Do_DB:
      Replicate_Ignore_DB:
      Replicate_Do_Table:
      Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
      Last_Errno: 0
      Last_Error:
      Skip_Counter: 0
      Exec_Master_Log_Pos: 475
      Relay_Log_Space: 993
      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: 0
      Master_SSL_Verify_Server_Cert: No
      Last_IO_Errno: 0
      Last_IO_Error:
      Last_SQL_Errno: 0
      Last_SQL_Error:
      Replicate_Ignore_Server_Ids:
      Master_Server_Id: 1
      Master_SSL_Crl:
      Master_SSL_Crlpath:
      Using_Gtid: Slave_Pos
      Gtid_IO_Pos: 0-1-905
      Replicate_Do_Domain_Ids:
      Replicate_Ignore_Domain_Ids:
      Parallel_Mode: optimistic
      SQL_Delay: 0
      SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
      Slave_DDL_Groups: 0
      Slave_Non_Transactional_Groups: 0
      Slave_Transactional_Groups: 0_

      However, the mysql.gtid_slave_pos table is empty.
      If I stop and restart my instance B, the replication fails. The replication is restarted with an incorrect GTID.

      show variables like "gtid_slave_pos";
      _---------------------+

      Variable_name Value

      ---------------------+

      gtid_slave_pos 0-1-5

      ---------------------+_
      The mysql.gtid_slave_pos table is populated with inconsistent values.

      select * from mysql.gtid_slave_pos;
      _--------------------------------+

      domain_id sub_id server_id seq_no

      --------------------------------+

      0 1 1 1
      0 2 1 2
      0 3 1 3
      0 4 1 4
      0 5 1 5

      --------------------------------+_

      My analysis:
      Normally the mysql.gtid_slave_pos table is populated when you set the gtid_slave_pos variable. In the header of my dump file,i find the command to feed the gtid_slave_pos variable. But at the end of the file I find the orders to recreate the table mysql.gtid_slave_pos. What should happen when I restore my file first the gtid_slave_pos variable is populated as well as the mysql.gtid_slave_pos table. Then the mysql.gtid_slave_pos table is recreated having lost the original gtid.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            pilafosse LAFOSSE
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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