After upgrade mysql-server from 5.6 to 5.7.10 something strange going on with multimaster replication.
When you add more then one channel replication tool not see second channel and server hangs on first channel.
This happens because mysql_upgrade tool have a bug. This bug create columns in table slave_master_info in wrong order.
CREATE TABLE `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin,
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin,
`Port` int(10) unsigned NOT NULL,
`Connect_retry` int(10) unsigned NOT NULL,
`Enabled_ssl` tinyint(1) NOT NULL,
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ssl_verify_server_cert` tinyint(1) NOT NULL,
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin,
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin,
`Retry_count` bigint(20) unsigned NOT NULL,
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin,
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin,
`Enabled_auto_position` tinyint(1) NOT NULL,
`Tls_version` text,
`Channel_name` char(64) NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information
And in code of mysql-server we see this
cat sql/rpl_mi.cc
....
/* line for channel */
LINE_FOR_CHANNEL= 24,
/* line for tls_version */
LINE_FOR_TLS_VERSION= 25,
.....
uint Master_info::get_channel_field_num()
{
uint channel_field= LINE_FOR_CHANNEL;
return channel_field;
}
....
if (to->prepare_info_for_write() ||
to->set_info((int) LINES_IN_MASTER_INFO) ||
to->set_info(master_log_name) ||
to->set_info((ulong) master_log_pos) ||
to->set_info(host) ||
to->set_info(user) ||
to->set_info(password) ||
to->set_info((int) port) ||
to->set_info((int) connect_retry) ||
to->set_info((int) ssl) ||
to->set_info(ssl_ca) ||
to->set_info(ssl_capath) ||
to->set_info(ssl_cert) ||
to->set_info(ssl_cipher) ||
to->set_info(ssl_key) ||
to->set_info((int) ssl_verify_server_cert) ||
to->set_info(heartbeat_period) ||
to->set_info(bind_addr) ||
to->set_info(ignore_server_ids) ||
to->set_info(master_uuid) ||
to->set_info(retry_count) ||
to->set_info(ssl_crl) ||
to->set_info(ssl_crlpath) ||
to->set_info((int) auto_position) ||
to->set_info(channel) ||
to->set_info(tls_version))
DBUG_RETURN(TRUE);
So when we add channel name they write in column ‘Tls_version’ and when Mysql read values for multi-master replication they see that:
SELECT * FROM slave_master_info;
| Number_of_lines | Master_log_name | Master_log_pos | Host | User_name | User_password | Port | Connect_retry | Enabled_ssl | Ssl_ca | Ssl_capath | Ssl_cert | Ssl_cipher | Ssl_key | Ssl_verify_server_cert | Heartbeat | Bind | Ignored_server_ids | Uuid | Retry_count | Ssl_crl | Ssl_crlpath | Enabled_auto_position | Tls_version | Channel_name |
.............
| 25 | s1-bin.000073 | 878486810 | 1.1.1.1 | repl1to7 | repl1to7 | 3306 | 60 | 0 | | | | | | 0 | 30 | | 0 | 1b1ead52-cf41-11e5-b8d8-14187727fa96 | 86400 | | | 0 | channel1 | |
..............
‘Channel_name’ = NULL, replication don’t work, when you want do stop slave – server hangs
So solution is pretty simple – change colunm order:
ALTER TABLE mysql.slave_master_info MODIFY COLUMN Tls_version TEXT AFTER Channel_name;
Restart Mysql daemon and enjoy with replication 🙂
ps:
https://bugs.launchpad.net/percona-server/+bug/1617715
https://bugs.mysql.com/bug.php?id=82384
https://github.com/mysql/mysql-server/blob/71f48ab393bce80a59e5a2e498cd1f46f6b43f9a/sql/rpl_mi.cc