Bug #90824 mysql_secure_installation.cc expires all passwords including internal ones
Submitted: 10 May 2018 13:50 Modified: 8 Jun 2019 12:49
Reporter: root@ kaba1ah.org Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:5.7.22 OS:FreeBSD (11.1-RELEASE-p8)
Assigned to: CPU Architecture:x86
Tags: freebsd, GROUP, master, replication

[10 May 2018 13:50] root@ kaba1ah.org
Description:
$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

Initially observed with the mysql57-server-5.7.22 package from the FreeBSD port collection, but the same issue is present with 5.7.22 FreeBSD binaries available under https://dev.mysql.com/downloads/mysql/5.7.html#downloads.

An attempt to setup multi-master group replication (between three nodes). I can't bootstrap the primary node for some reason. 

The same procedure works perfectly fine on Debian.

Logs are not very helpful, so if you can't reproduce it I'd appreciate assistance in getting more verbose logs.

Many thanks.

How to repeat:
- install 5.7.22 FreeBSD binaries from https://dev.mysql.com/downloads/mysql/5.7.html#downloads on a FreeBSD 11.1-RELEASE server.

- modify my.cnf as per below:

[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"

- bootstrap the primary node:

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)

err logs are provided below:

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'
[6 May 2019 12:08] MySQL Verification Team
Hi,

I was not able to reproduce this on my local setup. Is there any firewall or sebsd/flask/mac setup on those servers?

thanks
Bogdan
[8 May 2019 14:08] root@ kaba1ah.org
Hi,

No, there is nothing special with regards to firewall/sebsd/flask/mac/etc.

Did you try with FreeBSD 11.1-RELEASE-p8 and 5.7.22 or something newer? I can retest in case the latter.
[9 May 2019 15:47] MySQL Verification Team
Hi,

I tried latest 12.0 from freebsd.org inside ESXi

if you can retest I'd appreciate it.

thanks
[12 May 2019 11:40] root@ kaba1ah.org
Hi,

I assume you've tried MySQL 8 on FreeBSD 12. I confirm that the group replication works fine on FreeBSD 12.0-RELEASE with MySQL 8.0.15 installed from ports.

I do get the same error as reported initially with MySQL 5.7.26 installed from ports under FreeBSD 12.0-RELEASE.

The same server and the same sequence was used to bootstrap the primary node.

Could you please try to reproduce it with 5.7.26?
[12 May 2019 17:25] MySQL Verification Team
Hi,

Yes, I tried "all latest versions" (both mysql and freebsd).
I'll retry with latest 5.7

all best
Bogdan
[13 May 2019 18:05] MySQL Verification Team
Verified 5.7 on FreeBSD 12 does not work properly. Thanks for your report

all best
Bogdan
[24 May 2019 17:15] MySQL Verification Team
Hi,
We are having issues reproducing this with mysql binaries (from dev.mysql.com). I did reproduce with "pkg install mysql57-server mysql57-client"

Also, this is part of automated testing so it's almost impossible for this type of bug to go trough with our own binaries. Did you manage to reproduce this with 

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-freebsd12-x86_64.tar.gz

thanks
Bogdan
[5 Jun 2019 11:47] Nuno Carvalho
Hi,

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;

Best regards,
Nuno Carvalho
[8 Jun 2019 12:49] root@ kaba1ah.org
Many thanks Nuno.

I confirm that the workaround works.