WL#6799: Super-read-only that also blocks SUPER users

Affects: Server-5.7   —   Status: Complete

When bringing servers offline for move, performance of the procedure can be improved by extending READ_ONLY to also block users with SUPER privileges.

This introduces a new option, super_read_only, which supplements read_only. When super_read_only is set to ON, read_only should also be set to ON. When read_only is set to OFF, super_read_only should also be set to OFF. read_only can be set to ON while super_read_only is set to OFF, but not the reverse.

When super_read_only is ON, SUPER users should be blocked from modifying changes in the same way non-SUPER users are affected by read_only being enabled.

Dependent Bugs

  • BUG#75188: WHEN PROMOTING SECONDARY TO PRIMARY, OLD PRIMARY CONTAIN EXTRA TRANSACTIONS

User Documentation

FR 1 : Add new server variable, super_read_only.

FR 1.1 : Valid values will be standard boolean.

FR 1.2 : Variable has global scope only.

FR 1.3 : Users with SUPER privilege may update the value dynamically.

FR 1.4 : Variable may be set using command-line option or option file, using any standard boolean variable syntax.

FR2 : When super_read_only is set to ON, read_only will also be set to ON.

FR3 : When read_only is set to OFF, super_read_only will also be set to OFF.

FR4 : When super_read_only is set to ON, operations prohibited by read_only (for non-SUPER users) will not be permitted by users with SUPER privilege.

FR5 : Existing error message text will be modified to indicate whether --read-only or --super-read-only triggered the error.

FR6 : The state where super_read_only = ON and read_only = OFF will be disallowed at all times.

FR7 : Changing the global super_read_only state will have immediate effect - existing sessions will be restricted (or enabled) without reconnecting.

MySQL Server code has existing checks to prohibit certain operations while in read-only mode. These checks include exceptions when the user had SUPER privilege. The logic used in each of these checks will be modified to exclude operations only when the SUPER privilege is present and super_read_only is OFF.

The following files will be modified:

sql/handler.cc: sql/lock.cc: sql/sql_parse.cc: sql/sql_trigger.cc: sql/sql_update.cc: sql/sql_transaction.cc:

* Modify read-only check to exclude SUPER users only when super-read-only is OFF.
* Modify error code argument to indicate whether --read-only or --super-read-only triggered the error.

sql/mysqld.cc: sql/mysqld.h:

* Add super_read_only and opt_super_read_only boolean variables.

sql/sys_vars.cc:

* Add super_read_only variable
 ** Add fix_super_read_only() method
* Modify fix_read_only() method to synchronize super_read_only state when read_only set to OFF.