The dark side of super_read_only

There is an interesting feature in MySQL 5.7+: the global variable super_read_only.  MySQL 5.6 and before only had the read_only global variable which is not preventing a user with SUPER privilege to write to the database.  With super_read_only, those users cannot write anymore.  But this feature comes with a surprise.

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;

but this:

• 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.

Some history about this feature: it was introduced by WebScaleSQL and back ported in Percona Server 5.6 (the Percona Server entry for super_read_only mentions this).