MySQL Bugs: #84733: Cannot Start GR with super_read

Bug #84733	Cannot Start GR with super_read_only=1
Submitted:	31 Jan 2017 9:15	Modified:	1 Apr 2017 14:50
Reporter:	Kenny Gryp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It is not possible to start Group Replication with 'super_read_only=1`:

...

2017-01-31T09:04:19.021545Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.17-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2017-01-31T09:04:19.118628Z 3 [ERROR] Plugin group_replication reported: 'The internal plugin query 'CREATE USER IF NOT EXISTS _gr_user@localhost IDENTIFIED WITH mysql_native_password AS '*7CF5CA9067EC647187EB99FCC27548FBE4839AE3' ACCOUNT LOCK;' resulted in failure. errno: 1290'
2017-01-31T09:04:19.119309Z 3 [ERROR] Plugin group_replication reported: 'It was not possible to create the group replication user usedby the plugin for internal operations.'

The code that gets executed I believe is in https://github.com/mysql/mysql-server/blob/23032807537d8dd8ee4ec1c4d40f0633cd4e12f9/rapid/...

How to repeat:
use this my.cnf:

[mysqld]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log=gr-3-relay-bin
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
group_replication_group_name="da7aba5e-dead-da7a-ba55-da7aba5e57ab"
group_replication_start_on_boot=on
super_read_only=1
group_replication_local_address= "gr-3:24901"
group_replication_group_seeds= "gr-1:24901,gr-2:24901,gr-3:24901"
group_replication_bootstrap_group= off

start mysql, get error in errorlog

Suggested fix:
We should have a way that GR can run these GRANT/CREATE USER statements while 'super_read_only' is in effect. 

Unfortunately, the fix is not that easy as 'super_read_only' is a global variable only (and you can debate this yes).

However, due to bug/feature https://bugs.mysql.com/bug.php?id=84728 I still want to avoid split brain from happening and therefore I want to have 'super_read_only=1' enabled in my.cnf, otherwise there are no guarantees.

Hello Kenny Gryp,

Thank you for the report and feedback!

Thanks,
Umesh

Posted by developer:
 
Hi Kenny,

Thank you for evaluating Group Replication, your (and all community) feedback is important!

The actual issue is on Group Replication plugin install, that operation does create a user for plugin internal operations, though since server is on super_read_only mode plugin initialization is not able to create that user and the install fails.

Best regards,
Nuno Carvalho

Posted by developer:
 
Noted in 8.0.2 changelog.

Group Replication failed to start if the super_read_only system
variable was enabled.

This will also be available on 5.7.19