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'.
|
{"report":{"fcp":1653.0999994277954,"ttfb":250.79999923706055,"pageVisibility":"visible","entityId":50233,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"a7d98dfe-153c-4dde-bcc7-ab93d5132e16","navigationType":0,"readyForUser":1777.5,"redirectCount":0,"resourceLoadedEnd":2108.7999992370605,"resourceLoadedStart":291.8999996185303,"resourceTiming":[{"duration":471.80000019073486,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":291.8999996185303,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":291.8999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":763.6999998092651,"responseStart":0,"secureConnectionStart":0},{"duration":472,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":292.19999980926514,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":292.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":764.1999998092651,"responseStart":0,"secureConnectionStart":0},{"duration":482.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":292.3999996185303,"connectEnd":292.3999996185303,"connectStart":292.3999996185303,"domainLookupEnd":292.3999996185303,"domainLookupStart":292.3999996185303,"fetchStart":292.3999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":292.3999996185303,"responseEnd":774.5999994277954,"responseStart":774.5999994277954,"secureConnectionStart":292.3999996185303},{"duration":512.3000001907349,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":292.5999994277954,"connectEnd":292.5999994277954,"connectStart":292.5999994277954,"domainLookupEnd":292.5999994277954,"domainLookupStart":292.5999994277954,"fetchStart":292.5999994277954,"redirectEnd":0,"redirectStart":0,"requestStart":292.5999994277954,"responseEnd":804.8999996185303,"responseStart":804.8999996185303,"secureConnectionStart":292.5999994277954},{"duration":517.0999994277954,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":293.19999980926514,"connectEnd":293.19999980926514,"connectStart":293.19999980926514,"domainLookupEnd":293.19999980926514,"domainLookupStart":293.19999980926514,"fetchStart":293.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":293.19999980926514,"responseEnd":810.2999992370605,"responseStart":810.2999992370605,"secureConnectionStart":293.19999980926514},{"duration":518.8999996185303,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":293.5,"connectEnd":293.5,"connectStart":293.5,"domainLookupEnd":293.5,"domainLookupStart":293.5,"fetchStart":293.5,"redirectEnd":0,"redirectStart":0,"requestStart":293.5,"responseEnd":812.3999996185303,"responseStart":812.3999996185303,"secureConnectionStart":293.5},{"duration":520.0999994277954,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":293.69999980926514,"connectEnd":293.69999980926514,"connectStart":293.69999980926514,"domainLookupEnd":293.69999980926514,"domainLookupStart":293.69999980926514,"fetchStart":293.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":293.69999980926514,"responseEnd":813.7999992370605,"responseStart":813.6999998092651,"secureConnectionStart":293.69999980926514},{"duration":520.8000001907349,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":293.79999923706055,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":293.79999923706055,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":814.5999994277954,"responseStart":0,"secureConnectionStart":0},{"duration":520.3999996185303,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":294,"connectEnd":294,"connectStart":294,"domainLookupEnd":294,"domainLookupStart":294,"fetchStart":294,"redirectEnd":0,"redirectStart":0,"requestStart":294,"responseEnd":814.3999996185303,"responseStart":814.3999996185303,"secureConnectionStart":294},{"duration":527.6000003814697,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":294.3999996185303,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":294.3999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":822,"responseStart":0,"secureConnectionStart":0},{"duration":521.8999996185303,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":294.5,"connectEnd":294.5,"connectStart":294.5,"domainLookupEnd":294.5,"domainLookupStart":294.5,"fetchStart":294.5,"redirectEnd":0,"redirectStart":0,"requestStart":294.5,"responseEnd":816.3999996185303,"responseStart":816.3999996185303,"secureConnectionStart":294.5},{"duration":995.6999998092651,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":295.3999996185303,"connectEnd":295.3999996185303,"connectStart":295.3999996185303,"domainLookupEnd":295.3999996185303,"domainLookupStart":295.3999996185303,"fetchStart":295.3999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":295.3999996185303,"responseEnd":1291.0999994277954,"responseStart":1291.0999994277954,"secureConnectionStart":295.3999996185303},{"duration":1813.3999996185303,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":295.3999996185303,"connectEnd":295.3999996185303,"connectStart":295.3999996185303,"domainLookupEnd":295.3999996185303,"domainLookupStart":295.3999996185303,"fetchStart":295.3999996185303,"redirectEnd":0,"redirectStart":0,"requestStart":295.3999996185303,"responseEnd":2108.7999992370605,"responseStart":2108.7999992370605,"secureConnectionStart":295.3999996185303},{"duration":415.5999994277954,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1103.5,"connectEnd":1103.5,"connectStart":1103.5,"domainLookupEnd":1103.5,"domainLookupStart":1103.5,"fetchStart":1103.5,"redirectEnd":0,"redirectStart":0,"requestStart":1103.5,"responseEnd":1519.0999994277954,"responseStart":1519.0999994277954,"secureConnectionStart":1103.5}],"fetchStart":1,"domainLookupStart":1,"domainLookupEnd":1,"connectStart":1,"connectEnd":1,"requestStart":66,"responseStart":251,"responseEnd":288,"domLoading":289,"domInteractive":2131,"domContentLoadedEventStart":2131,"domContentLoadedEventEnd":2180,"domComplete":3646,"loadEventStart":3647,"loadEventEnd":3648,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2111.3999996185303},{"name":"bigPipe.sidebar-id.end","time":2112.2999992370605},{"name":"bigPipe.activity-panel-pipe-id.start","time":2112.3999996185303},{"name":"bigPipe.activity-panel-pipe-id.end","time":2115},{"name":"activityTabFullyLoaded","time":2190.199999809265}],"measures":[],"correlationId":"4674f9e6ac4fc7","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":99,"dbReadsTimeInMs":9,"dbConnsTimeInMs":17,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}