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

CHANGE MASTER TO doesn't work with prepared statements

    XMLWordPrintable

Details

    Description

      CHANGE MASTER TO doesn't actually change the master when using prepared statements. This is problematic for users who use orchestration frameworks that rely on prepared statements.

      As an example, we can first set up a master:

      MariaDB [(none)]> CHANGE MASTER TO master_host='orig_master', master_user='repl', master_password='password', master_log_file='localhost-bin.000002', master_log_pos=330;
      Query OK, 0 rows affected (0.05 sec)
       
      MariaDB [(none)]> SHOW SLAVE STATUS\G
      *************************** 1. row ***************************
                     Slave_IO_State: 
                        Master_Host: orig_master
                        Master_User: repl
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: localhost-bin.000002
                Read_Master_Log_Pos: 330
                     Relay_Log_File: localhost-relay-bin.000001
                      Relay_Log_Pos: 4
              Relay_Master_Log_File: localhost-bin.000002
                   Slave_IO_Running: No
                  Slave_SQL_Running: No
                    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: 330
                    Relay_Log_Space: 248
                    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: 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: No
                        Gtid_IO_Pos: 
      1 row in set (0.00 sec)

      Then let's try to change it via a prepared statement:

      MariaDB [(none)]> SET @s := "CHANGE MASTER TO master_host='new_master', master_user='repl', master_password='password', master_log_file='localhost-bin.000002', master_log_pos=330";
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> PREPARE st FROM @s;
      Query OK, 0 rows affected (0.00 sec)
      Statement prepared
       
      MariaDB [(none)]> EXECUTE st;
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [(none)]> DEALLOCATE PREPARE st;
      Query OK, 0 rows affected (0.00 sec)

      We don't get any errors, so it sounds like things are OK, but the master hasn't actually been changed:

      MariaDB [(none)]> SHOW SLAVE STATUS\G
      *************************** 1. row ***************************
                     Slave_IO_State: 
                        Master_Host: orig_master
                        Master_User: repl
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: localhost-bin.000002
                Read_Master_Log_Pos: 330
                     Relay_Log_File: localhost-relay-bin.000001
                      Relay_Log_Pos: 4
              Relay_Master_Log_File: localhost-bin.000002
                   Slave_IO_Running: No
                  Slave_SQL_Running: No
                    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: 330
                    Relay_Log_Space: 248
                    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: 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: No
                        Gtid_IO_Pos: 
      1 row in set (0.00 sec)

      The wrong change goes into the error log as well:

      150226  9:21:48 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='orig_master', master_port='3306', master_log_file='localhost-bin.000002', master_log_pos='330'. New state master_host='orig_master', master_port='3306', master_log_file='localhost-bin.000002', master_log_pos='330'.

      Attachments

        Activity

          People

            nirbhay_c Nirbhay Choubey (Inactive)
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            8 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.