WL#13369: Read only option for schema

Affects: Server-8.0   —   Status: Complete

This feature provides a mechanism to prohibit writes to entities in a schema. A new schema option 'read only' is introduced. It can be set in an ALTER SCHEMA statement, but not in CREATE SCHEMA statements, where its presence will cause a syntax error. To alter this option, the ALTER privilege is needed on the schema.

Use case

The purpose of this feature is to support migrating a schema and its tables from one MySQL instance to another MySQL instance.

Suppose you have 10 schemas within a single MySQL instance and one of the schemas grows 10x in size while the others stay relatively the same. In this case, it might be relevant to do an online migration to move the schema from one instance to another.

The migration will involve a logical dump/restore and then catch-up using binlogs. While the migration is occurring, the source schema is accessible for writes, but there is a point in time when all writes to the source schema must be stopped, and all read/write traffic switched to the new/destination instance.

At this point, a mechanism is needed to fence the original/source schema and prevent any writes from getting through. Otherwise, once the switch-over happens, any additional writes getting through will be lost.

ACL may provide the required support in some use cases, but:

  • Revoking global static privileges does not affect concurrent sessions, clients would need to reconnect to make the revoked privileges take effect. This would be disruptive to existing connections trying to access other schema objects.
  • Setting up user grants on a per-schema basis would make it possible to revoke schema-level privileges, hence affecting concurrent sessions. However, setting up privileges at this level of detail is a bit more complicated to deploy.
  • Using partial revokes would be a perfect match for this use case, however, partial revokes affect concurrent sessions in the same way as when changing global privileges: Clients need to reconnect for the changes to take effect.

Hence, a mechanism is needed that can prioritize revoking write access to the schema and overriding existing open transactions/connections.

Non functional requirements

NFR1
The new feature shall not have a performance impact exceeding 2% degradation.

Functional requirements

Some requirements refer to notes that are listed below the requirement section. The number of a note corresponds to the number of the requirement that refers to it.

FR1
A new schema option 'read only' is introduced, accepting values 0 and 1, similar to other options in the same category. The value will be stored persistently in the data dictionary along with the schema meta data.
FR2
Modifying the option in FR1 is done only by means of the ALTER SCHEMA statement as follows:
       ALTER SCHEMA <name> READ ONLY [=] {0, 1, DEFAULT};
Here, 'DEFAULT' is equivalent to '0'.
FR3
The option in FR1 shall not be possible to set in the CREATE SCHEMA statement, where its presence shall lead to an error, e.g. (note 3):
       mysql> CREATE SCHEMA s READ ONLY = 1;
       ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
       to your MySQL server version for the right syntax to use near 'read only = 1' at line 1
FR4
Any user with the ALTER privilege for the given schema can modify the option in FR1 for that schema (note 4).
FR5
The option in FR1 shall be part of the SHOW CREATE SCHEMA output if it is set to 1. However, since the option is not accepted in CREATE statements, we output the option enclosed in plain comment markers:
       mysql> show create schema test;
       +----------+-----------------------------------------------------------------------+
       | Database | Create Database                                                       |
       +----------+-----------------------------------------------------------------------+
       | test     | CREATE DATABASE `test` /*!40100 DEFAULT ... */ /* READ ONLY = 1    */ |
       +----------+-----------------------------------------------------------------------+
Since the option is ignored when part of a CREATE SCHEMA statement, this means that restoring dumps will still be possible. However, it also means that the read only property of a schema will be lost when restoring a dump.
FR6
The option in FR1 will not be visible in information_schema.schemata because this feature is added late in the life cycle of the product.
Instead, we will introduce a new non-standard information schema view 'SCHEMATA_EXTENSIONS' to make the information visible. The view may eventually be merged into the SCHEMATA view. The SCHEMATA_EXTENSIONS view will have the following columns:
  • CATALOG_NAME: The name of the catalog to which the schema belongs. This value is always def.
  • SCHEMA_NAME: The name of the schema.
  • OPTIONS: The read only option of the schema, if set, displayed as 'READ ONLY=1'. If not set, the option is not shown. See column definitions and example output below.
       mysql> desc information_schema.schemata_extensions;
       +--------------+--------------+------+-----+---------+-------+
       | Field        | Type         | Null | Key | Default | Extra |
       +--------------+--------------+------+-----+---------+-------+
       | CATALOG_NAME | varchar(64)  | NO   |     | NULL    |       |
       | SCHEMA_NAME  | varchar(64)  | NO   |     | NULL    |       |
       | OPTIONS      | varchar(256) | YES  |     | NULL    |       |
       +--------------+--------------+------+-----+---------+-------+

       mysql> select * from information_schema.schemata_extensions;
       +--------------+--------------------+-------------+
       | CATALOG_NAME | SCHEMA_NAME        | OPTIONS     |
       +--------------+--------------------+-------------+
       | def          | mysql              |             |
       | def          | information_schema |             |
       | def          | performance_schema |             |
       | def          | sys                |             |
       | def          | test               |             |
       | def          | mtr                |             |
       | def          | s                  | READ ONLY=1 |
       +--------------+--------------------+-------------+
FR7
The option in FR1 shall affect all users.
FR8
In a replication setting, a change to the option in FR1 on a master shall also have effect for a replication slave, and is therefore binlogged with the ALTER SCHEMA statement. For the relevant use case, we want to prohibit writes only on a single MySQL server. To achieve this, the user must turn off binlogging while the ALTER SCHEMA statement is executed.
FR9
Statements executed by the server as part of server initialization, restart, upgrade or replication shall not be subject to the read only checks.
FR10
Statements in an init-file shall not be subject to read-only checks.
FR11
It shall not be possible to set the option in FR1 for the system schemas mysql, information_schema and performance_schema.
FR12
The effect of setting the option in FR1 is that any write operation to any schema qualified entity being part of the schema is prohibited (with exceptions noted in FR8, FR9 and FR10). This includes DML operations (writing entity contents, e.g. table data) and DDL (writing meta data, e.g. changing a view). Thus, the option will have an effect on (see statements listed in note 12):
  • The schema itself
  • Events
  • Views
  • Triggers
  • Stored programs
  • Tables
An attempt to write shall be aborted with the following error message: 
       ERROR .... (HY000): Schema 's' is in read only mode...
FR13
The option in FR1 shall not have any affect on temporary tables. It is hence possible to create, alter, drop and write to temporary tables in a read-only schema.
FR14
An ALTER SCHEMA statement shall be blocked until all concurrent transactions that have already accessed an entity in the schema being altered have committed. A write transaction accessing an entity in a schema being altered in a concurrent ALTER SCHEMA shall be blocked until the ALTER SCHEMA has committed.
FR15
For a single ALTER SCHEMA statement, repeatedly setting options that conflict shall lead to an error. Repeatedly setting options that do not conflict shall not lead to an error. See examples in note 15.
FR16
ALTER SCHEMA statements addressing only the read only option (in a non-conflicting way, see above) shall always be permitted even if the schema is already in a read only state. Examples are listed in note 16.
FR17
ALTER SCHEMA statements addressing one or more schema options, not including the read only option, shall be subject to the current read only property of the schema. See example in note 17.
FR18
ALTER SCHEMA addressing several schema options, both the read only option and other options, shall be subject to the following behavior:
  • If the before image or after image schema read only state allows changes, then the statement is committed.
  • Otherwise - if neither the before image nor after image schema read only state allows changes, then the statement is rolled back.
Examples can be found in note 18.
FR19
Checking and enforcement of the option in FR1 shall be done a priori, i.e., before the execution of each relevant statement. Thus, the option will affect both transactional and non-transactional tables.
FR20
If the clone plugin is used to clone the data directory, locally or remotely, the schemas in the clone will retain the read-only state they had in the source data directory. The read-only options will not affect the cloning process itself.
If it is not desirable to have the same schema read-only state in the clone, the option must be changed explicitly for the clone after the cloning process is finished, using ALTER SCHEMA operations on the clone.
FR21
With NDB, executing 'ALTER SCHEMA s READ ONLY=1' on one mysqld server will be synchronized to other mysqld servers in the same cluster, making the schema become read only on all mysqld servers.
FR22
With NDB, non-SQL inserts and updates will not be affected by the schema read only option.
FR23
MEB backup/restore will not be affected by read only schemas - a read only schema will be backed up and restored like any other schema, and the read only option will be set also after restore if it was set at the time of backup.

Notes

(3)
Silently ignoring options is not in line with current practice, and our experience is that this is bound to lead to subsequent problems. Accepting the options and allowing them to have effect leads to issues when restoring dumps.
(4)
Alternatively, we could require the DROP privilege for the schema since this would allow the user to do pretty much what they would like anyway. However, in practice, such users are also likely to be the ones that have the ALTER privilege, so it seems to make sense that ALTER is sufficient to be allowed to set the new option.
(12)
The read only option will have an effect on the following SQL statements:
       CREATE SCHEMA
       ALTER SCHEMA
       DROP SCHEMA

       CREATE EVENT
       ALTER EVENT
       DROP EVENT
       + event execution

       CREATE VIEW
       ALTER VIEW
       DROP VIEW
       + selecting from views invoking functions with side effects
       + updating updatable views

       CREATE TRIGGER
       DROP TRIGGER
       + invoking a trigger

       CREATE FUNCTION
       DROP FUNCTION
       + selecting from a function with side effects

       CREATE PROCEDURE
       DROP PROCEDURE
       + calling a procedure with side effects

       CREATE TABLE
       ALTER TABLE
       CREATE INDEX
       DROP INDEX
       RENAME TABLE
       TRUNCATE TABLE
       DROP TABLE

       CALL (procedures with side effects)
       DELETE
       INSERT
       IMPORT TABLE
       LOAD DATA
       LOAD XML
       REPLACE
       SELECT (invoking functions with side effects)
       UPDATE
(15)
Assume we have schema s in state read only = 0, and a user is entering the statement:
       mysql> ALTER SCHEMA s READ ONLY = 1 READ ONLY = 0;
       ERROR 1302 (HY000): Conflicting declarations: ...

       mysql> ALTER SCHEMA s READ ONLY = 0 READ ONLY = 0;
       Query OK, 1 row affected (0,00 sec)
(16)
Assume we have schema s in state read only = 1, and a user is entering the statement:
       mysql> ALTER SCHEMA s READ ONLY = 1;
       Query OK, 1 row affected (0,00 sec)
Assume we have schema s in state read only = 1, and a user is entering the statement:
       mysql> ALTER SCHEMA s READ ONLY = 0;
       Query OK, 1 row affected (0,00 sec)
(17)
Assume we have schema s in state read only = 1, and a user is entering the statement:
       mysql> ALTER SCHEMA s DEFAULT COLLATE utf8mb4_bin;
       ERROR .... (HY000): Schema 's' is in read only mode...
(18)
Assume we have schema s in state read only = 1, and a user is entering the statement:
       mysql> ALTER SCHEMA s READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;
       ERROR .... (HY000): Schema 's' is in read only mode...
Assume we have schema s in state read only = 1, and a user is entering the statement:
       mysql> ALTER SCHEMA s READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;
       Query OK, 1 row affected (0,00 sec)
Assume we have schema s in state read only = 0, and a user is entering the statement:
       mysql> ALTER SCHEMA s READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;
       Query OK, 1 row affected (0,00 sec)

Contents

Overall design

The overall idea is to store the option persistently as part of the data dictionary, and to check the read-only property of accessed schemas in front of every statement that is executed.

Data structures, representation and parsing

The first section explains the static picture and outlines the data structures and the way we represent the read only state in various contexts.

Parsing

The new option accepts the values 0 (turn off) and 1 (turn on). This is in line with the handling of other similar options.

Representing state vs representing change

There are two valid states as far as the read only option is concerned, and we can represent it using a boolean.

A requested change of the read-only state can be also represented in the same way.

Representing state - persistent storage

The use case for the read-only option is temporarily setting it while migrating the schema to a different MySQL instance. However, we still need to persist it in case we have a server restart during the migration process.

The read only state is stored persistently as meta data in the existing 'options' column of the appropriate row in the DD table mysql.schemata. The state is stored as a single key-value pair with the key 'read_only'. The values are stored as the string literals '0' and '1'. No change of the DD table definition is required, so the DD version stays the same, and there are no DD upgrade actions required.

The default value for a newly created schema is 'read_only = 0', and this it highly unlikely to be changed. Thus, we do not store the implicit default value persistently, we only store values that indicate some sort of write protection.

Representing state - in memory storage

The read only state of a schema is also stored in memory as a data member in the 'options' field of the dd::Schema objects. Internally, it is stored as a key-value pair in the property hash map of the schema object, using the key 'read_only'. The value is the the string literals '0' and '1'.

In order to check the read-only state of a schema, the relevant dd::Schema object must be acquired from the shared DD cache, and the state can then be seen from that object.

Representing state - extending the output

To externalize the read only state of a schema, we have two mechanisms:

  • Extended SHOW CREATE output: The output is extended to contain the read only state enclosed in comment markers, but only if the read only state is turned on, e.g.:
      mysql> show create schema s;
      +----------+-----------------------------------------------------------------------+
      | Database | Create Database                                                       |
      +----------+-----------------------------------------------------------------------+
      | s        | CREATE DATABASE `s` /*!40100 DEFAULT ... */ /* READ ONLY = 1 */       |
      +----------+-----------------------------------------------------------------------+
  • Add a new information schema view 'information_schema.schemata_extensions':
      mysql> select * from information_schema.schemata_extensions;
      +--------------+--------------------+-------------+
      | CATALOG_NAME | SCHEMA_NAME        | OPTIONS     |
      +--------------+--------------------+-------------+
      | def          | mysql              |             |
      | def          | information_schema |             |
      | def          | performance_schema |             |
      | def          | sys                |             |
      | def          | test               |             |
      | def          | mtr                |             |
      | def          | s                  | READ ONLY=1 |
      +--------------+--------------------+-------------+

This also means that the I_S version must be incremented.

Representing state change after parsing

The structure HA_CREATE_INFO is extended by an additional field holding the read only change requested by the user, defined as a boolean variable:

struct HA_CREATE_INFO {
  ...
  bool schema_read_only{false};
  ...
}

Hence, the state change can be fetched during the execution of the ALTER SCHEMA statement.

Dynamic handling of the option

During execution, the altering and checking of the option is done as described below.

Validating the option value

When executing an ALTER SCHEMA statement, we need to check whether the same option is assigned different values. This must be reported as an error, e.g.:

    mysql> ALTER SCHEMA s READ ONLY = 1 READ ONLY = 0;
    ERROR 1302 (HY000): Conflicting declarations: ...'

This validation is done during statement parsing.

Checking the read-only option

Checking the read only option is done at statement execution time in the same manner as the check of the global read-only option. The schema names accessed are used to lookup in the data dictionary cache to get hold of the read-only option.

Concurrency and locking

ALTER SCHEMA will acquire X lock on the schema name, and X locks on all tables in the schema. This will ensure we have the required isolation and serialization of transactions.

A DDL operation for a schema qualified entity will acquire an IX lock on the schema. This will make sure ALTER SCHEMA is blocked until the DDL is finished. A DML operation will acquire some shared lock on the entity. Since a concurrent ALTER SCHEMA will acquire X locks on the same entities, it will be blocked. And vice versa, if the ALTER SCHEMA has acquired the locks it needs, it will block concurrent DML or DDL on schema qualified entities from progressing.

This means that ALTER SCHEMA does not need to acquire X locks on routines and events in the schema, because they can only be updated by DDL operations that take IX locks on the schema, and hence will be blocked by ALTER SCHEMA anyway. The situation is different for tables because they can be updated both by DDL (meta data change) and DML (user data change), and for blocking the latter, we need ALTER SCHEMA to acquire X locks on the tables.

Note that it is possible to enter deadlocks with the locking use cases seen here. E.g.:

  • Suppose we start with table s.t1 in the TDC and s.t2 not in the TDC.
  • We let transaction T1 access table t1, hence acquiring a meta data lock on its name.
  • Next, we let transaction T2 start ALTER SCHEMA s which will acquire an X lock on the schema name 's', which it will get. Further, it will try to acquire an X lock on the tables in the schema, which will cause it to wait since T1 has a lock on t1.
  • Then, if T1 tries to access table t2, it will try to acquire an IX lock on the schema name before populating the TDC. This will cause it to wait for T2, and hence, we have a deadlock.

This is nothing to be very concerned about since it's restricted to one locking system and will be detected. Moreover, it is a situation common also in other contexts, e.g. DROP SCHEMA has the same behavior.

Replication of the read-only option

Changing the read only option on a master should be binlogged and also take effect on replication slaves. The query sent to the binlog handlers in the SEs must also contain the read only clause because e.g. NDB needs to distribute this to other mysqld servers in the same cluster.

Performance issues

If the design above does not meet the required performance figures, we may introduce a THD local cache of schema names and read only states to avoid the need of acquiring schema MDL and fetching from the DD cache.

Other possibilities are to store the schema read only state in the TABLE_SHARE for tables, and use this for the required checking while accessing tables. For other schema qualified entities, the DD cache may be used, since these cases will be during DDL which is less performance critical.



Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.