MySQL Bugs: #102260: init_file fails on an instance configured w/ SET PERSIST super_read

Bug #102260	init_file fails on an instance configured w/ SET PERSIST super_read_only = on
Submitted:	15 Jan 2021 16:47	Modified:	15 Jan 2021 19:30
Reporter:	Andrew Garner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When resetting a forgotten root password on a MySQL instance where super_read_only had been set via SET PERSIST, we found that the init-file failed to run.  In the error log w/ log-verbosity=3 we observe:

[Note] [MY-010137] [Server] Execution of init_file '/etc/mysql/init.sql' started.
[ERROR] [MY-000061] [Server] 1290  The MySQL server is running with the --super-read-only option so it cannot execute this statement.
[Note] [MY-010138] [Server] Execution of init_file '/etc/mysql/init.sql' ended.

In other environments, we sometimes use init-file to apply some config (e.g. repaving the root user password) even w/ super-read-only set.  However in those environment super-read-only is set via the my.cnf (or sometimes via the commandline) and init-file still works.

This seems to be a behavioral difference between how super_read_only is applied from the persisted variable cache and via other configuration sources.

In particular, InnoDB Cluster environments will always have SET PERSIST super_read_only set (or similar), which can also run this behavior.

How to repeat:
Initialize a new mysqld environment

Run SET PERSIST / PERSIST_ONLY super_read_only = on

Restart mysqld w/ init-file=some.sql

See that mysql fails to run the init-file:

[Note] [MY-010137] [Server] Execution of init_file '/etc/mysql/init.sql' started.
[ERROR] [MY-000061] [Server] 1290  The MySQL server is running with the --super-read-only option so it cannot execute this statement.
[Note] [MY-010138] [Server] Execution of init_file '/etc/mysql/init.sql' ended.

However, repeating with super-read-only in the my.cnf or in the command line options allows init-file to succeed.

Suggested fix:
There are situations where it is convenient to be able to apply an init-file even on an instance w/ super-read-only configured.

It would be nice if this could work even in the SET PERSIST case.

Hi Mr. Garner,

Thank you for your bug report.

However, this is not a bug.

This is a behaviour that is well described in our 8.0 Reference Manual, in many chapters, starting with 5.1.8.

If you would like to have a different behaviour, you can ask that this report is changed to be a feature request.

However, that feature request has to be well designed and in detailed. The change of behaviour should not break existing applications, in which case it would be considered.

Not a bug.

Fair enough.  It seems fair that "super-read-only" rejects all writes.

The surprising behavior is that my.cnf:super_read_only and --super-read-only DO NOT affect reject writes during "--init-file" execution, but "SET PERSIST" does.

That does not seem to be documented.  Maybe other ways super-read-only were implemented made this behavior accidentally work.