TL&DR: changing super_read_only to ON also sets read_only to ON and setting read_only to OFF changes super_read_only to OFF.
That surprise is that changing super_read_only to ON also sets read_only to ON and setting read_only to OFF changes super_read_only to OFF). It is documented in the manual under the read_only entry, and the super_read_only entry refers to read_only for the details. Thinking about it, this makes sense:
- when disabling read_only, you probably also want to disable super_read_only,
- and when enabling super_read_only, you probably also want to enable read_only.
The problem arrises when you do not know this (or you forgot about it). It was my case a few days ago and it causes more problems than it should have. My redemption is writing this post which hopefully will prevent a few people from doing the same mistake.
So, when run on a server that is not read-only, undoing this:
- SET GLOBAL super_read_only = ON;
is not this:
- SET GLOBAL super_read_only = OFF;
- SET GLOBAL read_only = OFF;
And if you run the wrong command, the reason why writes are not succeeding is not that established connections are read-only, it is because you run to wrong command (read_only and super_read_only are only global variables, they do not have a session scope). So do not lose time killing all connections (I lost time on this).
The behaviour of this feature is IMHO clearly part of the ugly (or dark) side of MySQL. It is part of the things that make sense when you know about them, but which are also traps for people that are not familiar with the details. Considering the history of MySQL, I do not think we can do much better here (and explaining why is too long for this post), so I do not know how to improve this.