Bug 227806 - databases/mysql57-server: multi-master group replication doesn't seem to work
Summary: databases/mysql57-server: multi-master group replication doesn't seem to work
Status: New
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: amd64 Any
: --- Affects Some People
Assignee: Mahdi Mokhtari
URL: https://bugs.mysql.com/bug.php?id=90824
Keywords: needs-qa
Depends on:
Blocks:
 
Reported: 2018-04-27 14:17 UTC by Roman Serbski
Modified: 2019-06-08 13:00 UTC (History)
1 user (show)

See Also:
bugzilla: maintainer-feedback? (mmokhi)


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Roman Serbski 2018-04-27 14:17:58 UTC
$uname -a
FreeBSD srv-db-01 11.1-RELEASE-p8 FreeBSD 11.1-RELEASE-p8 #0 r330926: Wed Mar 14 13:59:52 CET 2018

$pkg info | grep mysql
mysql57-client-5.7.22_1        Multithreaded SQL database (client)
mysql57-server-5.7.22          Multithreaded SQL database (server)

I'm trying to setup multi-master group replication (between three nodes) and I can't seem to bootstrap the primary node. The same procedure works fine on Debian Stretch (with the same version of MySQL).

-=my.cnf=-

[mysqld]

gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

server_id = 10
bind-address = "192.168.58.36"

loose-group_replication_group_name = "d504d507-1217-11e8-8d30-005020a0d302"
loose-group_replication_ip_whitelist = "192.168.58.36,192.168.58.37,192.168.58.38"
loose-group_replication_group_seeds = "192.168.58.36:33006,192.168.58.37:33006,192.168.58.38:33006"

loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

report_host = "192.168.58.36"
loose-group_replication_local_address = "192.168.58.36:33006"

-=SQL=-

SET SQL_LOG_BIN=0;
CREATE USER 'replica'@'%' IDENTIFIED BY 'xxx' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='replica', MASTER_PASSWORD='xxx' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)

-= Logs =-

2018-04-27T09:44:10.6NZ mysqld_safe Logging to '/var/db/mysql/srv-db-01.err'.
2018-04-27T09:44:10.6NZ mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2018-04-27T09:44:10.899254Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-27T09:44:10.901715Z 0 [Note] /usr/local/libexec/mysqld (mysqld 5.7.22-log) starting as process 23378 ...
2018-04-27T09:44:10.906855Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-04-27T09:44:10.906990Z 0 [Note] InnoDB: Uses event mutexes
2018-04-27T09:44:10.907083Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-04-27T09:44:10.907170Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-04-27T09:44:10.907660Z 0 [Note] InnoDB: Number of pools: 1
2018-04-27T09:44:10.907873Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-04-27T09:44:10.909738Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-04-27T09:44:10.929881Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-04-27T09:44:10.946047Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-04-27T09:44:10.964472Z 0 [Warning] InnoDB: Resizing redo log from 2*16384 to 2*3072 pages, LSN=2598701
2018-04-27T09:44:11.085718Z 0 [Warning] InnoDB: Starting to delete and rewrite log files.
2018-04-27T09:44:11.110520Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-04-27T09:44:11.466069Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-04-27T09:44:11.840825Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-04-27T09:44:11.841082Z 0 [Warning] InnoDB: New log files created, LSN=2598701
2018-04-27T09:44:11.841594Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-04-27T09:44:11.841786Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-04-27T09:44:11.960047Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-04-27T09:44:11.961302Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-04-27T09:44:11.961391Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-04-27T09:44:11.961747Z 0 [Note] InnoDB: Waiting for purge to start
2018-04-27T09:44:12.012742Z 0 [Note] InnoDB: 5.7.22 started; log sequence number 2598692
2018-04-27T09:44:12.013421Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-04-27T09:44:12.013627Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/mysql/ib_buffer_pool
2018-04-27T09:44:12.026908Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180427 11:44:12
2018-04-27T09:44:12.089689Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2018-04-27T09:44:12.089851Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2018-04-27T09:44:12.090871Z 0 [Warning] CA certificate ca.pem is self signed.
2018-04-27T09:44:12.091055Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2018-04-27T09:44:12.092368Z 0 [Note] Server hostname (bind-address): '192.168.58.36'; port: 3306
2018-04-27T09:44:12.092477Z 0 [Note]   - '192.168.58.36' resolves to '192.168.58.36';
2018-04-27T09:44:12.092593Z 0 [Note] Server socket created on IP: '192.168.58.36'.
2018-04-27T09:44:12.128198Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=srv-db-01-relay-bin' to avoid this problem.
2018-04-27T09:44:12.197042Z 0 [Note] Event Scheduler: Loaded 0 events
2018-04-27T09:44:12.197333Z 0 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.7.22-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
2018-04-27T09:46:14.025321Z 3 [ERROR] Plugin group_replication reported: 'Can't read the server values for the read_only and super_read_only variables.'
2018-04-27T09:46:14.025456Z 3 [ERROR] Plugin group_replication reported: 'Can't read the server value for the super_read_only variable.'
2018-04-27T09:46:14.025468Z 3 [ERROR] Plugin group_replication reported: 'Could not enable the server read only mode and guarantee a safe recovery execution'
2018-04-27T09:46:14.025485Z 3 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2018-04-27T09:46:14.025497Z 3 [ERROR] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'
Comment 1 Roman Serbski 2019-06-08 13:00:10 UTC
This is handled by https://bugs.mysql.com/bug.php?id=90824

Quote from the bug activity log by Nuno Carvalho:

=============
The issue is that mysql_secure_installation is expiring all passwords, including the ones for internal users, which are used by plugins.

The workaround until this is fixed is execute the below statement after running mysql_secure_installation.

ALTER USER 'mysql.session'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
=============

Please let me know whether this bug #227806 should be closed.