3.5.2 Statements

• The following describes read-only statements:

  • Statements are read-only if they start with:

    • SELECT

    • DO

    • VALUES

    • TABLE

    • WITH that is not followed by UPDATE or DELETE.

    • EXPLAIN, DESCRIBE, or DESC which are not followed by UPDATE or DELETE.

    • HELP

    • USE

    • CHECKSUM

    • Parenthesized Query Expressions.

  • If they start with any of the above and do not contain functions or keywords which must be executed on a read-write server. Such as:

    • GET_LOCK()

    • FOR UPDATE|SHARE

    • LOCK IN SHARE MODE

  • Some functions can produce a write from within a read-only statement. Such statements fail with an error similar to the following

    The MySQL server is running with the --super-read-only option so it cannot execute this statement.

    Such statements can be explicitly routed to a read-write server, with ROUTER SET or query_attributes. See Per-Session Configuration. You can also wrap the statement in a START TRANSACTION ... COMMIT.

• The following describes read-write statements:

  • Data Definition Statements.

  • The following DML: CALL, INSERT, UPDATE, DELETE, REPLACE, IMPORT TABLE, LOAD DATA, LOAD XML, WITH...UPDATE/DELETE.

  • The following Account Management statements: GRANT, REVOKE, RENAME USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, SET PASSWORD, SET ROLE, SET DEFAULT ROLE.

  • The following Transaction and Locking statements: BEGIN, START TRANSACTION , XA, SAVEPOINT, ROLLBACK, COMMIT.

  • The following database administration statements: SHOW CREATE..., SHOW VARIABLES, SHOW STATUS, SET TRANSACTION (including SET SESSION ...), SET NAMES, SET CHARACTER SET, FLUSH PRIVILEGES.

  • The following utility statements: EXPLAIN, DESCRIBE, DESC.

  • The following table maintenance statements: ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE, REPAIR TABLE.

• The following statements are not supported if access_mode=auto:

  • Any statement which is not read-only or read-write. This includes ALTER RESOURCE GROUP Statement, Replication Statements, and Other Administrative Statements.