1 of 100

SQL Statements

Learn to select data in MariaDB Server using SQL. This section covers various SELECT statement clauses, including WHERE, GROUP BY, and ORDER BY, to retrieve and filter your data effectively.

Data Definition (DDL)Data Manipulation (DML)Account Management Administrative Statements SQL Functions Prepared Statements Programmatic & Compound Statements Stored Routines Table Statements Transactions Comment Syntax HELP Command

Comment Syntax

This section details the different ways to add non-executable comments to your SQL statements and script files, including single-line & multi-line comments, and MariaDB-specific conditional comments.

There are three supported comment styles in MariaDB:

From a hash sign (#) to the end of a line:

From a double dash (--) to the end of a line. The space after the two dashes is required (as in MySQL).

C style comments from an opening '/*' to a closing '*/'. Comments of this form can span multiple lines:

Nested comments are possible in some situations, but they are not supported or recommended.

Executable Comments

As an aid to portability between different databases, MariaDB supports executable comments. These special comments allow you to embed SQL code which will not execute when run on other databases, but will execute when run on MariaDB.

MariaDB supports both MySQL's executable comment format, and a slightly modified version specific to MariaDB. This way, if you have SQL code that works on MySQL and MariaDB, but not other databases, you can wrap it in a MySQL executable comment, and if you have code that specifically takes advantage of features only available in MariaDB you can use the MariaDB specific format to hide the code from MySQL.

Executable Comment Syntax

MySQL and MariaDB executable comment syntax:

Code that should be executed only starting from a specific MySQL or MariaDB version:

The numbers, represented by '######' in the syntax examples above specify the specific the minimum versions of MySQL and MariaDB that should execute the comment. The first number is the major version, the second 2 numbers are the minor version and the last 2 is the patch level.

For example, if you want to embed some code that should only execute on MySQL or MariaDB starting from 5.1.0, you would do the following:

MariaDB-only executable comment syntax (starting from ):

MariaDB ignores MySQL-style executable comments that have a version number in the range 50700..99999. This is needed to skip features introduced in MySQL-5.7 that are not ported to MariaDB 10.x yet.

Note: comments which have a version number in the range 50700..99999 that use MariaDB-style executable comment syntax are still executed.

Statement delimiters cannot be used within executable comments.

Examples

In MySQL all the following will return 2: In MariaDB, the last 2 queries would return 3.

The following executable statement will not work due to the delimiter inside the executable portion:

Instead, the delimiter should be placed outside the executable portion:

_{This page is licensed: CC BY-SA / Gnu FDL}

Account Management

Learn account management SQL statements for MariaDB Server. This section covers commands like CREATE USER, GRANT, and REVOKE to securely manage user access and privileges within your database.

DROP ROLE

Remove a role from the system. Learn the syntax to delete defined roles and revoke them from any users or roles that currently hold them.

Syntax

Description

The DROP ROLE

DROP USER

Delete one or more user accounts. Understand how to remove users and their associated privileges from the database system safely.

Syntax

DROP USER [IF EXISTS] user_name [, user_name] ... [FORCE]

DROP USER [IF EXISTS] user_name [, user_name] ...

Description

The DROP USER statement removes one or more MariaDB accounts. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global privilege or the privilege for the mysql database. Each account is named using the same format as for the CREATE USER statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see .

If you specify an account that is currently connected, it is not deleted until the connection is closed. The connection is not automatically closed. The statement completes with a warning:

In , if a user is connected, the DROP USER statement fails with an error:

Use the FORCE clause to forcibly close connections of the users named in the DROP USER statement. This ends connections, and immediately deletes the users.

If you specify an account that is currently connected, it is not deleted until the connection is closed. The connection is not automatically closed.

However, a deleted user cannot initiate new connections any more.

If any of the specified user accounts do not exist, ERROR 1396 (HY000) results. If an error occurs, DROP USER still drops the accounts that do not result in an error. Only one error is produced for all users which have not been dropped:

Failed CREATE or DROP operations, for both users and roles, produce the same error.

IF EXISTS

If the IF EXISTS clause is used, MariaDB returns a note instead of an error if the user does not exist.

The CREATE USER statement creates new MariaDB accounts. To use it, you must have the global privilege or the privilege for the database.

If the IF EXISTS clause is used, MariaDB returns a note instead of an error if the user does not exist.

Examples

IF EXISTS:

RENAME USER

Rename existing database accounts. This guide explains how to change a user's name while preserving their current privileges and properties.

Syntax

Description

The RENAME USER statement renames existing MariaDB accounts. To use it, you must have the global privilege or the privilege for the

SET DEFAULT ROLE

Define the default role for a user. Learn how to configure which role is automatically active when a user connects to the server.

Syntax

Description

The SET DEFAULT ROLE

SET SESSION AUTHORIZATION

Impersonate another user for the current session. Learn how to assume the identity and privileges of another account for testing or administration.

This statement is available from MariaDB 12.0.

Certain users can perform server actions as another user:

This is implemented through the SET SESSION AUTHORIZATION statement.
This permits everything that can be done in a with an arbitrary definer.
In particular, this bypasses , , authentication, REQUIRE SSL checks, and so on.
Users are required to have the privilege.
Does not work inside , prepared statements and .

Examples

SET ROLE

Sets the current role for the session. Learn how to enable none, or a specific role to change your current privileges dynamically.

Syntax

Description

Administrative Statements

Explore administrative SQL statements for MariaDB Server. This section covers commands for server management, maintenance, and diagnostics, including BINLOG, KILL, SHUTDOWN, and SHOW.

BINLOG

Executes binary log events directly using base64-encoded data. Primarily used by the mariadb-binlog utility to re-apply events to the server.

Syntax

Description

BINLOG is an internal-use statement. It is generated by the

CACHE INDEX

Assigns specific table indices to a named key cache. Optimizes server performance by preloading or dedicating memory to frequently accessed keys.

Syntax

Description

The CACHE INDEX statement assigns table indexes to a specific key cache. It is used only for tables.

A default key cache exists and cannot be destroyed. To create more key caches, the server system variable.

The associations between tables indexes and key caches are lost on server restart. To recreate them automatically, it is necessary to configure caches in a and include some CACHE INDEX (and optionally ) statements in the init file.

Examples

The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:

Implementation (for MyISAM)

Normally CACHE INDEX should not take a long time to execute. Internally it's implemented the following way:

Find the right key cache (under LOCK_global_system_variables)
Open the table with a TL_READ_NO_INSERT lock.
Flush the original key cache for the given file (under key cache lock)
Flush the new key cache for the given file (safety)

The only possible long operations are getting the locks for the table and flushing the original key cache, if there were many key blocks for the file in it.

We plan to also add CACHE INDEX for Aria tables if there is a need for this.

_{This page is licensed: GPLv2, originally from}

DESCRIBE

Provides information about a table's columns. Acts as a shortcut for SHOW COLUMNS, displaying field names, types, and other attributes.

Syntax

Description

DESCRIBE provides information about the columns in a table.It is a shortcut for

HELP Command

Displays help information from the server's help tables. Useful for looking up SQL syntax and command descriptions directly from the client.

Syntax

Description

The HELP

PURGE BINARY LOGS

Deletes binary log files from the server. Removes logs based on a specific filename or date timestamp to reclaim disk space and manage storage.

Syntax

Description

The PURGE BINARY LOGS

RESET

Clears internal server buffers, caches, and status variables. Resets state information like the query cache or replication status without a restart.

Syntax

Description

The RESET

USE [DATABASE]

Selects the default database for the current session. Subsequent queries execute against this database unless explicitly qualified by name.

Syntax

Description

The 'USE db_name'

ANALYZE and EXPLAIN Statements

Learn commands for query analysis. This section covers ANALYZE TABLE and EXPLAIN, used to view execution plans and optimize query performance.

BACKUP Statements

Learn about backup statements for MariaDB Server. This section details SQL statements and utilities for creating consistent database backups, essential for disaster recovery and data protection.

FLUSH Statements

Explore commands to clear internal caches. Learn to use FLUSH to reload privileges, clear the query cache, or close open tables.

Plugin Statements

Manage server plugins. This section covers INSTALL PLUGIN, UNINSTALL PLUGIN, and SHOW PLUGINS for extending server functionality.

Legacy Replication Statements

Category for old replication statements

SET Statements

Assign values to system variables. Learn to use the SET statement to configure GLOBAL and SESSION variables for tuning server behavior.

SET SET CHARACTER SET SET GLOBAL SQL_SLAVE_SKIP_COUNTER SET NAMES SET PASSWORD SET ROLE SET SQL_LOG_BIN SET STATEMENT SET TRANSACTION SET Variable

SHOW Statements

View server metadata and status. This section lists SHOW statements for inspecting databases, tables, variables, and performance metrics.

DROP ROLE journalist;
ERROR 1396 (HY000): Operation DROP ROLE failed for 'journalist'

DROP ROLE IF EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Note (Code 1975): Can't drop role 'journalist'; it doesn't exist

CREATE ROLE journalist;
CREATE USER taniel;

SET DEFAULT ROLE journalist FOR taniel;
ERROR 1959 (OP000): Invalid role specification `journalist`

GRANT journalist TO taniel;
SET DEFAULT ROLE journalist FOR taniel;

files. The 'str' value is a base 64-encoded string that the server decodes to determine the data change indicated by the corresponding event.

mysql

statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used.

DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | YES  |     | NULL    |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

to executeRESET.

db_name

USE

+--------------------+--------------------+------------+
| user()             | current_user()     | database() |
+--------------------+--------------------+------------+
| msandbox@localhost | msandbox@localhost | test       |
+--------------------+--------------------+------------+
1 row in set (0.000 sec)

+---------------+----------------+------------+
| user()        | current_user() | database() |
+---------------+----------------+------------+
| foo@localhost | foo@%          | NULL       |
+---------------+----------------+------------+

files listed in the log index file prior to the specified log file name ordate. BINARY and MASTER are synonyms.Deleted log files also are removed from the list recorded in the index file, sothat the given log file becomes the first in the list.

KILL

Terminates a specific connection or query. Allows administrators to stop runaway threads or disconnect users to free up server resources.

Syntax

Description

Each connection to mariadbd runs in a separate thread. You can see which threadsare running with the SHOW PROCESSLIST statement and kill athread with the KILL thread_id statement.KILL allows the optional CONNECTION orQUERY modifier:

KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread or query id.
KILL QUERY terminates the statement that the connection thread_id is currently executing, but leaves the connection itself intact.
KILL QUERY ID terminates the query by query_id, leaving the connection intact.

If a connection is terminated that has an active transaction, the transaction will be rolled back. If only a query is killed, the current transaction will stay active. See also .

If you have the privilege, you can see all threads.

If you have the privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.

If you have the privilege, the privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.

Killing queries that repair or create indexes on MyISAM and Aria tables may result in corrupted tables. Use the SOFT option to avoid this!

The HARD option (default) kills a command as soon as possible. If you useSOFT, then critical operations that may leave a table in an inconsistent state will not be interrupted. Such operations include REPAIR and INDEX creation for and tables (, ).

KILL ... USER username will kill all connections/queries for agiven user. USER can be specified one of the following ways:

username (Kill without regard to hostname)
username@hostname
or

If you specify a thread id and that thread does not exist, you get the following error:

If you specify a query id that doesn't exist, you get the following error:

However, if you specify a user name, no error is issued for non-connected (or even non-existing) users. To check if the connection/query has been killed, you can use the function.

A client whose connection is killed receives the following error:

To obtain a list of existing sessions, use the statement or query the table.

Note: You cannot use KILL with the Embedded MariaDB Serverlibrary because the embedded server merely runs inside the threads of the hostapplication. It does not create any connection threads of its own.

Note: You can also usemariadb-admin kill thread_id [,thread_id...]to kill connections. To get a list of running queries,use mariadb-admin processlist. See .

contains a program, that can be used to automatically kill connections that match certain criteria. For example, it can be used to terminate idle connections, or connections that have been busy for more than 60 seconds.

SHOW CREATE SERVER

Display the CREATE SERVER statement. This statement shows the configuration details for a defined federated server connection.

Syntax

SHOW CREATE SERVER server_name

Description

Shows the CREATE SERVER statement that created the given server definition.

Example

SHOW CREATE SERVER srv1\G
*************************** 1. row ***************************
       Server: srv1
Create Server: CREATE SERVER `srv1` FOREIGN DATA WRAPPER mysql 
  OPTIONS (HOST '172.30.0.58', DATABASE

The SHOW CREATE SERVER statement is not available.

ANALYZE FORMAT=JSON Examples

Review practical examples of ANALYZE FORMAT=JSON output. Learn to identify performance bottlenecks by comparing estimated costs against actual execution metrics.

Example #1

Customers who have ordered more than 1M goods.

ANALYZE FORMAT=JSON
SELECT COUNT(*)
FROM customer
WHERE
  (SELECT SUM(o_totalprice) FROM orders WHERE o_custkey=c_custkey) > 1000*1000;

The query takes 40 seconds over cold cache.

ANALYZE shows that 39.208 seconds were spent in the subquery, which was executed 150K times (for every row of outer table).

_{This page is licensed: CC BY-SA / Gnu FDL}

CREATE ROLE journalist;
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'journalist'

CREATE OR REPLACE ROLE journalist;
Query OK, 0 rows affected (0.00 sec)

CREATE ROLE IF NOT EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)

+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1975 | Can't create role 'journalist'; it already exists |
+-------+------+---------------------------------------------------+

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 39872,
    "table": {
      "table_name": "customer",
      "access_type": "index",
      "key": "i_c_nationkey",
      "key_length": "5",
      "used_key_parts": ["c_nationkey"],
      "r_loops": 1,
      "rows": 150303,
      "r_rows": 150000,
      "r_total_time_ms": 270.3,
      "filtered": 100,
      "r_filtered": 60.691,
      "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
      "using_index": true
    },
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "r_loops": 150000,
          "r_total_time_ms": 39531,
          "table": {
            "table_name": "orders",
            "access_type": "ref",
            "possible_keys": ["i_o_custkey"],
            "key": "i_o_custkey",
            "key_length": "5",
            "used_key_parts": ["o_custkey"],
            "ref": ["dbt3sf1.customer.c_custkey"],
            "r_loops": 150000,
            "rows": 7,
            "r_rows": 10,
            "r_total_time_ms": 39208,
            "filtered": 100,
            "r_filtered": 100
          }
        }
      }
    ]
  }
}

/* 1. Revoking Privileges */
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM account_or_role [, account_or_role] ...

/* 2. Revoking All Privileges */
REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM account_or_role [, account_or_role] ...

/* 3. Revoking Proxy Access */
REVOKE PROXY ON user_or_role
    FROM account_or_role [, account_or_role] ...

/* 4. Revoking Roles */
REVOKE role [, role] ...
    FROM account_or_role [, account_or_role] ...

/* 5. Revoking Admin Option for Roles */
REVOKE ADMIN OPTION FOR role [, role] ...
    FROM account_or_role [, account_or_role] ...

/* Variable Definitions */

account_or_role:
    username
  | role
  | PUBLIC
  | CURRENT_USER [()]
  | CURRENT_ROLE [()]

priv_type:
    ALL [PRIVILEGES]
  | ALTER | ALTER ROUTINE | BINLOG ADMIN | BINLOG MONITOR | BINLOG REPLAY
  | CONNECTION ADMIN | CREATE | CREATE ROUTINE | CREATE TABLESPACE
  | CREATE TEMPORARY TABLES | CREATE USER | CREATE VIEW 
  | DELETE | DELETE HISTORY | DROP | EVENT | EXECUTE | FEDERATED ADMIN 
  | FILE | GRANT OPTION | INDEX | INSERT | LOCK TABLES | PROCESS 
  | READ ONLY ADMIN | RELOAD | REPLICATION CLIENT | REPLICATION MASTER ADMIN 
  | REPLICATION SLAVE | REPLICATION SLAVE ADMIN | REFERENCES 
  | SELECT | SET USER | SHOW CREATE ROUTINE | SHOW DATABASES | SHOW VIEW 
  | SHUTDOWN | SLAVE MONITOR | SUPER | TRIGGER | UPDATE | USAGE

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE
  | PACKAGE
  | PACKAGE BODY

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

UNINSTALL PLUGIN IF EXISTS example;
Query OK, 0 rows affected (0.099 sec)

UNINSTALL PLUGIN IF EXISTS example;
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1305 | PLUGIN example does not exist |
+-------+------+-------------------------------+

SHOW BINLOG EVENTS IN 'mysql_sandbox10019-bin.000002';
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name                      | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql_sandbox10019-bin.000002 |   4 | Format_desc       |         1 |         248 | Server ver: 10.0.19-MariaDB-log, Binlog ver: 4 |
| mysql_sandbox10019-bin.000002 | 248 | Gtid_list         |         1 |         273 | []                                             |
| mysql_sandbox10019-bin.000002 | 273 | Binlog_checkpoint |         1 |         325 | mysql_sandbox10019-bin.000002                  |
| mysql_sandbox10019-bin.000002 | 325 | Gtid              |         1 |         363 | GTID 0-1-1                                     |
| mysql_sandbox10019-bin.000002 | 363 | Query             |         1 |         446 | CREATE DATABASE blog                           |
| mysql_sandbox10019-bin.000002 | 446 | Gtid              |         1 |         484 | GTID 0-1-2                                     |
| mysql_sandbox10019-bin.000002 | 484 | Query             |         1 |         571 | use `blog`; CREATE TABLE bb (id INT)           |
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+

SHOW CREATE VIEW example\G
*************************** 1. row ***************************
                View: example
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `example` AS (select `t`.`id` AS `id`,`t`.`s` AS `s` from
`t`)
character_set_client: cp850
collation_connection: cp850_general_ci

SHOW CREATE VIEW example\G
*************************** 1. row ***************************
                View: example
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECU
RITY DEFINER VIEW example AS (select t.id AS id,t.s AS s from t)
character_set_client: cp850
collation_connection: cp850_general_ci

is set. It shows the CREATE statement that creates the given package specification.

SHOW ERRORS

Display errors from the last executed statement. View error codes, messages, and types for recent failures.

Syntax

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW ERRORS [LIMIT row_count OFFSET offset]
SHOW COUNT(*) ERRORS

Description

This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.

The LIMIT clause has the same syntax as for the statement.

The SHOW COUNT(*) ERRORS statement displays the number of errors. You can also retrieve this number from the variable.

The value of might be greater than the number of messages displayed by if the system variable is set so low that not all messages are stored.

For a list of MariaDB error codes, see MariaDB Error Codes.

Examples

_{This page is licensed: GPLv2, originally from}

Using Buffer UPDATE Algorithm

Understand the 'Using buffer' strategy for UPDATE operations. Learn how MariaDB prevents infinite update loops when modifying indexed columns during a range scan.

This article explains the UPDATE statement's Using Buffer algorithm.

Consider the following table and query:

Name

Salary

Babatunde

1000

Jolana

1050

Pankaja

1300

UPDATE employees SET salary = salary+100 WHERE salary < 2000;

Suppose the employees table has an index on the salary column, and the optimizer decides to use a range scan on that index.

The optimizer starts a range scan on the salary index. We find the first record Babatunde, 1000. If we do an on-the-fly update, we immediately instruct the storage engine to change this record to be Babatunde, 1000+100=1100.

Then we proceed to search for the next record, and find Jolana, 1050. We instruct the storage engine to update it to be Jolana, 1050+100=1150.

Then we proceed to search for the next record ... and what happens next depends on the storage engine. In some storage engines, data changes are visible immediately, so we will find the Babatunde, 1100 record that we wrote at the first step, modifying it again, giving Babatunde an undeserved raise. Then we will see Babatunde again and again, looping continually.

In order to prevent such situations, the optimizer checks whether the UPDATE statement is going to change key values for the keys it is using. In that case, it will use a different algorithm:

Scan everyone with "salary<2000", remembering the rowids of the rows in a buffer.
Read the buffer and apply the updates.

This way, each row will be updated only once.

The Using buffer output indicates that the buffer as described above will be used.

_{This page is licensed: CC BY-SA / Gnu FDL}

SET CHARACTER SET

Map strings to a specific character set. This command updates the character set for the client, results, and connection to ensure correct data encoding.

Syntax

SET {CHARACTER SET | CHARSET}
    {charset_name | DEFAULT}

Description

Sets the character_set_client and character_set_results session system variables to the specified character set and collation_connection to the value of , which implicitly sets to the value of .

This maps all strings sent between the current client and the server with the given mapping.

Example

SET SQL_LOG_BIN

Enable or disable binary logging for the current session. This statement allows administrators to perform operations without replicating them to replicas.

Syntax

SET [SESSION] sql_log_bin = {0|1}

Description

Sets the sql_log_bin system variable, which disables or enables binary logging for the current connection, if the client has the SUPER . The statement is refused with an error if the client does not have that privilege.

Note that setting sql_log_bin=1 has no effect if variable, which enables global binary logging, is not set.

You cannot set sql_log_bin as a global variable.

You can set sql_log_bin as a global variable. This is considered dangerous, though, as it can damage replication.

_{This page is licensed: GPLv2, originally from}

SHOW CONTRIBUTORS

List financial contributors to the MariaDB Foundation. View the names and contributions of companies and individuals supporting the project.

Syntax

SHOW CONTRIBUTORS

Description

The SHOW CONTRIBUTORS statement displays information about the companies and people who financially contribute to MariaDB. For each contributor, it displays Name, Location, and Comment values. All columns are encoded as latin1.

It displays all as well as other financial contributors.

Example

SHOW CREATE DATABASE

View the CREATE DATABASE statement. This statement returns the SQL syntax required to recreate a specific database with its current options.

Syntax

SHOW CREATE {DATABASE | SCHEMA} db_name

Description

Shows the CREATE DATABASE statement that creates the given database. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE. SHOW CREATE DATABASE quotes database names according to the value of the server system variable.

Examples

With turned off, the output looks slightly differently:

SHOW EVENTS

List scheduled events. View metadata such as the event name, schedule, status, and timing for events in a database.

Syntax

SHOW EVENTS [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr]

Description

Shows information about Event Manager events (created with CREATE EVENT). Requires the EVENT privilege. Without any arguments, SHOW EVENTS lists all of the events in the current schema:

To see the event action, use instead, or look at the table.

To see events for a specific schema, use the FROM clause. For example, to see events for the test schema, use the following statement:

The LIKE clause, if present, indicates which event names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in .

_{This page is licensed: GPLv2, originally from}

SHOW BINARY LOGS

List the binary log files on the server. View the file names and sizes to monitor replication logs and manage disk usage.

Syntax

SHOW BINARY LOGS
SHOW MASTER LOGS

Description

Lists the binary log files on the server. This statement is used as part of the procedure described in PURGE BINARY LOGS, that shows how to determine which logs can be purged.

This statement requires the privilege.

This statement requires the privilege and the privilege.

Examples

_{This page is licensed: GPLv2, originally from}

SHOW CREATE PACKAGE employee_tools\G
*************************** 1. row ***************************
             Package: employee_tools
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
      Create Package: CREATE DEFINER="root"@"localhost" PACKAGE "employee_tools" AS
  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
  PROCEDURE raiseSalaryStd(eid INT);
  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 1000,
      "filtered": 100,
      "attached_condition": "(t1.col1 = 1)"
    }
  }
}

. The LIKE clause, if present on its own, indicates which character set names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in

SELECT f();
ERROR 1305 (42000): FUNCTION f does not exist

SHOW COUNT(*) ERRORS;
+-----------------------+
| @@session.error_count |
+-----------------------+
|                     1 |
+-----------------------+

SHOW ERRORS;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Error | 1305 | FUNCTION f does not exist |
+-------+------+---------------------------+

SHOW VARIABLES LIKE 'character_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+

SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

SET CHARACTER SET utf8mb4;

SHOW VARIABLES LIKE 'character_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | latin1  |
| character_set_database   | latin1  |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | latin1  |
| character_set_system     | utf8    |
+--------------------------+---------+

SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

UNINSTALL SONAME IF EXISTS 'ha_example';
Query OK, 0 rows affected (0.099 sec)

UNINSTALL SONAME IF EXISTS 'ha_example';
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Note  | 1305 | SONAME ha_example.so does not exist |
+-------+------+-------------------------------------+

SHOW CREATE TRIGGER example\G
*************************** 1. row ***************************
               Trigger: example
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES
,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_
ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER example BEFORE
 INSERT ON t FOR EACH ROW
BEGIN
        SET NEW.c = NEW.c * 2;
END
  character_set_client: cp850
  collation_connection: cp850_general_ci
  Database Collation: utf8_general_ci
  Created: 2016-09-29 13:53:34.35

SHOW CREATE FUNCTION VatCents\G
*************************** 1. row ***************************
            Function: VatCents
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `VatCents`(price DECIMAL(10,2)) RETURNS int(11)
    DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = price * 114;
 RETURN x;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SELECT CURRENT_USER(), SCHEMA();
+----------------+----------+
| CURRENT_USER() | SCHEMA() |
+----------------+----------+
| jon@ghidora    | myschema |
+----------------+----------+

SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: myschema
                Name: e_daily
             Definer: jon@ghidora
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2006-02-09 10:41:23
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     19039 |
| mariadb-bin.000002 |    717389 |
| mariadb-bin.000003 |       300 |
| mariadb-bin.000004 |       333 |
| mariadb-bin.000005 |       899 |
| mariadb-bin.000006 |       125 |
| mariadb-bin.000007 |     18907 |
| mariadb-bin.000008 |     19530 |
| mariadb-bin.000009 |       151 |
| mariadb-bin.000010 |       151 |
| mariadb-bin.000011 |       125 |
| mariadb-bin.000012 |       151 |
| mariadb-bin.000013 |       151 |
| mariadb-bin.000014 |       125 |
| mariadb-bin.000015 |       151 |
| mariadb-bin.000016 |       314 |
+--------------------+-----------+

CREATE USER foo4@test require cipher 'text' 
  issuer 'foo_issuer' subject 'foo_subject';

SHOW CREATE USER foo4@test\G
*************************** 1. row ***************************
CREATE USER 'foo4'@'test' 
  REQUIRE ISSUER 'foo_issuer' 
  SUBJECT 'foo_subject' 
  CIPHER 'text'

CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;

SHOW CREATE USER 'monty'@'localhost';
+------------------------------------------------------------------+
| CREATE USER for monty@localhost                                  |
+------------------------------------------------------------------+
| CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY |
+------------------------------------------------------------------+

SHOW BINLOG STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000016 |      475 |              |                  |
+--------------------+----------+--------------+------------------+
SELECT @@global.gtid_binlog_pos;
+--------------------------+
| @@global.gtid_binlog_pos |
+--------------------------+
| 0-1-2                    |
+--------------------------+

ERROR 1966 (HY000): When using parallel replication and GTID with multiple 
 replication domains, @@sql_slave_skip_counter can not be used. Instead, 
 setting @@gtid_slave_pos explicitly can be  used to skip to after a given GTID 
 position.

SHOW CLIENT_STATISTICS\G
*************************** 1. row ***************************
                Client: localhost
     Total_connections: 35
Concurrent_connections: 0
        Connected_time: 708
             Busy_time: 2.5557979999999985
              Cpu_time: 0.04123740000000002
        Bytes_received: 3883
            Bytes_sent: 21595
  Binlog_bytes_written: 0
             Rows_read: 18
             Rows_sent: 115
          Rows_deleted: 0
         Rows_inserted: 0
          Rows_updated: 0
       Select_commands: 70
       Update_commands: 0
        Other_commands: 0
   Commit_transactions: 1
 Rollback_transactions: 0
    Denied_connections: 0
      Lost_connections: 0
         Access_denied: 0
         Empty_queries: 35

SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+

SHOW CHARACTER SET WHERE Maxlen LIKE '2';
+---------+---------------------------+-------------------+--------+
| Charset | Description               | Default collation | Maxlen |
+---------+---------------------------+-------------------+--------+
| big5    | Big5 Traditional Chinese  | big5_chinese_ci   |      2 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci  |      2 |
| euckr   | EUC-KR Korean             | euckr_korean_ci   |      2 |
| gb2312  | GB2312 Simplified Chinese | gb2312_chinese_ci |      2 |
| gbk     | GBK Simplified Chinese    | gbk_chinese_ci    |      2 |
| ucs2    | UCS-2 Unicode             | ucs2_general_ci   |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci |      2 |
+---------+---------------------------+-------------------+--------+

CREATE SEQUENCE s1 START WITH 50;
SHOW CREATE SEQUENCE s1\G;
*************************** 1. row ***************************
       Table: s1
Create Table: CREATE SEQUENCE `s1` start with 50 minvalue 1 maxvalue 9223372036854775806 
  increment by 1 cache 1000 nocycle ENGINE=InnoDB

SHOW CREATE TABLE s1\G
*************************** 1. row ***************************
       Table: s1
Create Table: CREATE TABLE `s1` (
  `next_not_cached_value` bigint(21) NOT NULL,
  `minimum_value` bigint(21) NOT NULL,
  `maximum_value` bigint(21) NOT NULL,
  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created 
     or value if RESTART is used',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache_size` bigint(21) unsigned NOT NULL,
  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 
     1 if the sequence should begin a new cycle when maximum_value is passed',
  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1

SELECT * FROM s1\G
*************************** 1. row ***************************
next_not_cached_value: 50
        minimum_value: 1
        maximum_value: 9223372036854775806
          start_value: 50
            increment: 1
           cache_size: 1000
         cycle_option: 0
          cycle_count: 0

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
       SEQUENCE_CATALOG: def
        SEQUENCE_SCHEMA: test
          SEQUENCE_NAME: s1
              DATA_TYPE: bigint
      NUMERIC_PRECISION: 64
NUMERIC_PRECISION_RADIX: 2
          NUMERIC_SCALE: 0
            START_VALUE: 50
          MINIMUM_VALUE: 1
          MAXIMUM_VALUE: 9223372036854775806
              INCREMENT: 1
           CYCLE_OPTION: 0

plugin_library

SHOW SCHEMAS

SHOW DATABASES

LIKE

WHERE

LIKE

SHOW ENGINES

List available storage engines. View the support status (default, active, or disabled) and description for each engine.

Syntax

SHOW [STORAGE] ENGINES

Description

SHOW ENGINES displays status information about the server's storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is.SHOW TABLE TYPES is a deprecated synonym.

The table provides the same information.

Since storage engines are plugins, different information about them is also shown in the table and by the statement.

Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See . The same applies to .

The output consists of the following columns:

Engine indicates the engine's name.
Support indicates whether the engine is installed, and whether it is the default engine for the current session.
Comment is a brief description.

Examples

_{This page is licensed: GPLv2, originally from}

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

SHOW CONTRIBUTORS;
+---------------------+-------------------------------+-------------------------------------------------------------+
| Name                | Location                      | Comment                                                     |
+---------------------+-------------------------------+-------------------------------------------------------------+
| Alibaba Cloud       | https://www.alibabacloud.com/ | Platinum Sponsor of the MariaDB Foundation                  |
| Tencent Cloud       | https://cloud.tencent.com     | Platinum Sponsor of the MariaDB Foundation                  |
| Microsoft           | https://microsoft.com/        | Platinum Sponsor of the MariaDB Foundation                  |
| MariaDB Corporation | https://mariadb.com           | Founding member, Platinum Sponsor of the MariaDB Foundation |
| ServiceNow          | https://servicenow.com        | Platinum Sponsor of the MariaDB Foundation                  |
| Intel               | https://www.intel.com         | Platinum Sponsor of the MariaDB Foundation                  |
| SIT                 | https://sit.org               | Platinum Sponsor of the MariaDB Foundation                  |
| Visma               | https://visma.com             | Gold Sponsor of the MariaDB Foundation                      |
| DBS                 | https://dbs.com               | Gold Sponsor of the MariaDB Foundation                      |
| IBM                 | https://www.ibm.com           | Gold Sponsor of the MariaDB Foundation                      |
| Automattic          | https://automattic.com        | Silver Sponsor of the MariaDB Foundation                    |
| Percona             | https://www.percona.com/      | Sponsor of the MariaDB Foundation                           |
| Galera Cluster      | https://galeracluster.com     | Sponsor of the MariaDB Foundation                           |
| Google              | USA                           | Sponsoring encryption, parallel replication and GTID        |
| Facebook            | USA                           | Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc        |
| Ronald Bradford     | Brisbane, Australia           | EFF contribution for UC2006 Auction                         |
| Sheeri Kritzer      | Boston, Mass. USA             | EFF contribution for UC2006 Auction                         |
| Mark Shuttleworth   | London, UK.                   | EFF contribution for UC2006 Auction                         |
+---------------------+-------------------------------+-------------------------------------------------------------+

SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

SHOW CREATE SCHEMA test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

SHOW CREATE DATABASE test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+

Transactions, XA and Savepoints indicate whether transactions, XA transactions and transaction savepoints are supported by the engine.

LOCK TABLES ... WRITE

. The LIKE clause, if present on its own, indicates which collation names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in

The pad_attribute column (not shown in the examples below) has a value of NO PAD or PAD SPACE. This attribute affects whether trailing spaces are significant in string comparisons. See the INFORMATION_SCHEMA.COLLATIONS table description for more information.

SHOW COLLATION LIKE 'utf8mb4_bin'; 
+-------------+---------+------+---------+----------+---------+---------------+ 
| Collation   | Charset | Id   | Default | Compiled | Sortlen | Pad_attribute | 
+-------------+---------+------+---------+----------+---------+---------------+ 
| utf8mb4_bin | utf8mb4 | 46   |         | Yes      | 1       | PAD SPACE     | 
+-------------+---------+------+---------+----------+---------+---------------+

The pad_attribute column is not available.

SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: FEDERATED
     Support: YES
     Comment: FederatedX pluggable storage engine
Transactions: YES
          XA: NO
  Savepoints: YES
*************************** 6. row ***************************
      Engine: MRG_MyISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ***************************
      Engine: Aria
     Support: YES
     Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
          XA: NO
  Savepoints: NO
10 rows in set (0.00 sec)

SHOW TABLES;
+----------------------+
| Tables_in_test       |
+----------------------+
| animal_count         |
| animals              |
| are_the_mooses_loose |
| aria_test2           |
| t1                   |
| view1                |
+----------------------+

SHOW TABLES WHERE Tables_in_test LIKE 'a%';
+----------------------+
| Tables_in_test       |
+----------------------+
| animal_count         |
| animals              |
| are_the_mooses_loose |
| aria_test2           |
+----------------------+

SHOW VARIABLES WHERE Variable_name LIKE 'aria%' AND Value >8192;
+------------------------------+---------------------+
| Variable_name                | Value               |
+------------------------------+---------------------+
| aria_checkpoint_log_activity | 1048576             |
| aria_log_file_size           | 1073741824          |
| aria_max_sort_file_size      | 9223372036853727232 |
| aria_pagecache_buffer_size   | 134217728           |
| aria_sort_buffer_size        | 134217728           |
+------------------------------+---------------------+

SHOW CREATE PACKAGE BODY employee_tools\G
*************************** 1. row ***************************
        Package body: employee_tools
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
 Create Package Body: CREATE DEFINER="root"@"localhost" PACKAGE BODY "employee_tools" AS
  
  stdRaiseAmount DECIMAL(10,2):=500;
  
  PROCEDURE log (eid INT, ecmnt TEXT) AS
  BEGIN
    INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt);
  END;
  
  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2)) AS
    eid INT;
  BEGIN
    INSERT INTO employee (name, salary) VALUES (ename, esalary);
    eid:= last_insert_id();
    log(eid, 'hire ' || ename);
  END;

  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS
    nSalary DECIMAL(10,2);
  BEGIN
    SELECT salary INTO nSalary FROM employee WHERE id=eid;
    log(eid, 'getSalary id=' || eid || ' salary=' || nSalary);
    RETURN nSalary;
  END;

  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2)) AS
  BEGIN
    UPDATE employee SET salary=salary+amount WHERE id=eid;
    log(eid, 'raiseSalary id=' || eid || ' amount=' || amount);
  END;

  PROCEDURE raiseSalaryStd(eid INT) AS
  BEGIN
    raiseSalary(eid, stdRaiseAmount);
    log(eid, 'raiseSalaryStd id=' || eid);
  END;

BEGIN  
  log(0, 'Session ' || connection_id() || ' ' || current_user || ' started');
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message                |
+-------+------+------------------------+
| Note  | 1003 | select sum(a) from tbl |
+-------+------+------------------------+
1 row in set (0.00 sec)

SHOW COLLATION LIKE 'latin1%';
+-------------------------+---------+------+---------+----------+---------+
| Collation               | Charset | Id   | Default | Compiled | Sortlen |
+-------------------------+---------+------+---------+----------+---------+
| latin1_german1_ci       | latin1  |    5 |         | Yes      |       1 |
| latin1_swedish_ci       | latin1  |    8 | Yes     | Yes      |       1 |
| latin1_danish_ci        | latin1  |   15 |         | Yes      |       1 |
| latin1_german2_ci       | latin1  |   31 |         | Yes      |       2 |
| latin1_bin              | latin1  |   47 |         | Yes      |       1 |
| latin1_general_ci       | latin1  |   48 |         | Yes      |       1 |
| latin1_general_cs       | latin1  |   49 |         | Yes      |       1 |
| latin1_spanish_ci       | latin1  |   94 |         | Yes      |       1 |
| latin1_swedish_nopad_ci | latin1  | 1032 |         | Yes      |       1 |
| latin1_nopad_bin        | latin1  | 1071 |         | Yes      |       1 |
+-------------------------+---------+------+---------+----------+---------+

SHOW COLLATION WHERE Sortlen LIKE '8' AND Charset LIKE 'utf8mb4';
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 |  225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 |  226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 |  227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci         | utf8mb4 |  228 |         | Yes      |       8 |
| utf8mb4_polish_ci            | utf8mb4 |  229 |         | Yes      |       8 |
| utf8mb4_estonian_ci          | utf8mb4 |  230 |         | Yes      |       8 |
| utf8mb4_spanish_ci           | utf8mb4 |  231 |         | Yes      |       8 |
| utf8mb4_swedish_ci           | utf8mb4 |  232 |         | Yes      |       8 |
| utf8mb4_turkish_ci           | utf8mb4 |  233 |         | Yes      |       8 |
| utf8mb4_czech_ci             | utf8mb4 |  234 |         | Yes      |       8 |
| utf8mb4_danish_ci            | utf8mb4 |  235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci        | utf8mb4 |  236 |         | Yes      |       8 |
| utf8mb4_slovak_ci            | utf8mb4 |  237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci          | utf8mb4 |  238 |         | Yes      |       8 |
| utf8mb4_roman_ci             | utf8mb4 |  239 |         | Yes      |       8 |
| utf8mb4_persian_ci           | utf8mb4 |  240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci         | utf8mb4 |  241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci         | utf8mb4 |  242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci           | utf8mb4 |  243 |         | Yes      |       8 |
| utf8mb4_german2_ci           | utf8mb4 |  244 |         | Yes      |       8 |
| utf8mb4_croatian_mysql561_ci | utf8mb4 |  245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci       | utf8mb4 |  246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci        | utf8mb4 |  247 |         | Yes      |       8 |
| utf8mb4_croatian_ci          | utf8mb4 |  608 |         | Yes      |       8 |
| utf8mb4_myanmar_ci           | utf8mb4 |  609 |         | Yes      |       8 |
| utf8mb4_unicode_nopad_ci     | utf8mb4 | 1248 |         | Yes      |       8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 |         | Yes      |       8 |
+------------------------------+---------+------+---------+----------+---------+

SHOW CREATE EVENT test.e_daily\G
*************************** 1. row ***************************
               Event: e_daily
            sql_mode: 
           time_zone: SYSTEM
        Create Event: CREATE EVENT `e_daily`
                        ON SCHEDULE EVERY 1 DAY
                        STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
                        ON COMPLETION NOT PRESERVE
                        ENABLE
                        COMMENT 'Saves total number of sessions then
                                clears the table each day'
                        DO BEGIN
                          INSERT INTO site_activity.totals (time, total)
                            SELECT CURRENT_TIMESTAMP, COUNT(*) 
                            FROM site_activity.sessions;
                          DELETE FROM site_activity.sessions;
                        END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

LIKE

WHERE

LIKE

ANALYZE

ANALYZE Statement

Learn to use the ANALYZE statement to execute a query and produce a performance report. This command reveals how close the optimizer's plan was to the actual execution.

Description

The ANALYZE statement is similar to the EXPLAIN statement. ANALYZE statement will invoke the optimizer, execute the statement, and then produce EXPLAIN output instead of the result set. The EXPLAIN output will be annotated with statistics from statement execution.

This lets one check how close the optimizer's estimates about the query plan are to the reality. ANALYZE produces an overview, while the command provides a more detailed view of the query plan and the query execution.

The syntax is

where the statement is any statement for which one can run .

Command Output

Consider an example:

Compared to EXPLAIN, ANALYZE produces two extra columns:

r_rows is an observation-based counterpart of the rows column. It shows how many rows were actually read from the table.
r_filtered is an observation-based counterpart of the filtered column. It shows which fraction of rows was left after applying the WHERE condition.

Interpreting the Output

Joins

Let's consider a more complicated example.

Here, one can see that

For table customer, customer.rows=149095, customer.r_rows=150000. The estimate for number of rows we will read was fairly precise
customer.filtered=18.08, customer.r_filtered=9.13. The optimizer somewhat overestimated the number of records that will match selectivity of condition attached to customer table (in general, when you have a full scan and r_filtered is less than 15%, it's time to consider adding an appropriate index).
For table orders, orders.rows=7, orders.r_rows=10. This means that on average, there are 7 orders for a given c_custkey, but in our case there were 10, which is close to the expectation (when this number is consistently far from the expectation, it may be time to run ANALYZE TABLE, or even edit the table statistics manually to get better query plans).

Meaning of NULL in r_rows and r_filtered

Let's modify the previous example slightly

The output of orders.r_rows=NULL and orders.r_filtered=NULL shows that the table orders was never scanned. Indeed, we can also see customer.r_filtered=0.00. This shows that a part of WHERE attached to table customer was never satisfied (or, satisfied in less than 0.01% of cases).

ANALYZE FORMAT=JSON

produces JSON output. It produces much more information than tabular ANALYZE.

Notes

ANALYZE UPDATE or ANALYZE DELETE will actually make updates/deletes (ANALYZE SELECT will perform the select operation and then discard the resultset).
PostgreSQL has a similar command, EXPLAIN ANALYZE.
The feature allows MariaDB to have ANALYZE

BACKUP STAGE

Control backup phases for external tools. Learn how to cycle through stages like START, BLOCK_DDL, and BLOCK_COMMIT to perform consistent backups with minimal locking.

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool.

Syntax

In the following text, a transactional table means InnoDB, or specifically an InnoDB-like engine with redo log that can lock redo purges and can be copied without locks by an outside process.

Goals with BACKUP STAGE Commands

To be able to do a majority of the backup with the minimum possible server locks. Especially for transactional tables (InnoDB, MyRocks etc) there is only need for a very short block of new commits while copying statistics and log tables.
DDL are only needed to be blocked for a very short duration of the backup while is copying the tables affected by DDL during the initial part of the backup.
Most non transactional tables (those that are not in use) will be copied during BACKUP STAGE START. The exceptions are system statistic and log tables that are not blocked during the backup until BLOCK_COMMIT.

`BACKUP STAGE` Commands

`BACKUP STAGE START`

The START stage is designed for the following tasks:

Blocks purge of redo files for storage engines that needs this (Aria)
Start logging of DDL commands into 'datadir'/ddl.log. This may take a short time as the command has to wait until there are no active DDL commands.

`BACKUP STAGE FLUSH`

The FLUSH stage is designed for the following tasks:

FLUSH all changes for inactive non-transactional tables, except for statistics and log tables.
Close all tables that are not in use, to ensure they are marked as closed for the backup.
BLOCK all new write locks for all non transactional tables (except statistics and log tables). The command will not wait for tables that are in use by read-only transactions.

DDLs don't have to be blocked at this stage as they can't cause the table to be in an inconsistent state. This is true also for non-transactional tables.

`BACKUP STAGE BLOCK_DDL`

The BLOCK_DDL stage is designed for the following tasks:

Wait for all statements using write locked non-transactional tables to end.
Blocks , , , and .
Blocks also start off a new and the final rename phase of . Running ALTER TABLES are not blocked.

`BACKUP STAGE BLOCK_COMMIT`

The BLOCK_COMMIT stage is designed for the following tasks:

Lock the binary log and commit/rollback to ensure that no changes are committed to any tables. If there are active commits or data to be copied to the binary log this will be allowed to finish. Active transactions will not affect BLOCK_COMMIT.
This doesn't lock temporary tables that are not used by replication. However these will be blocked when it's time to write to the binary log.
Lock system log tables and statistics tables, flush them and mark them closed.

When the BLOCK_COMMIT's stages return, this is the 'backup time'. Everything committed will be in the backup and everything not committed will roll back.

Transactional engines will continue to do changes to the redo log during the BLOCK COMMIT stage, but this is not important as all of these will roll back later as the changes will not be committed.

`BACKUP STAGE END`

The END stage is designed for the following tasks:

End DDL logging
Free resources

Using `BACKUP STAGE` Commands with Backup Tools

Using `BACKUP STAGE` Commands with mariadb-backup

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool.

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool. How uses these commands depends on which version you are using. It depends on whether you are using the version that is bundled with MariaDB Community Server or the version that is bundled with .

See for some examples on how uses these commands.

Using `BACKUP STAGE` Commands with Storage Snapshots

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool. These commands could even be used by tools that perform backups by taking a snapshot of a file system, SAN, or some other kind of storage device. See for some examples on how to use each BACKUP STAGE command in an efficient way.

Privileges

BACKUP STAGE requires the privilege.

Notes

Only one connection can run BACKUP STAGE START. If a second connection tries, it will wait until the first one has executed BACKUP STAGE END.
If the user skips a BACKUP STAGE, then all intermediate backup stages will automatically be run. This will allow us to add new stages within the BACKUP STAGE hierarchy in the future with even more precise locks without causing problems for tools using an earlier version of the BACKUP STAGE implementation.

ALTER USER

Modify existing user accounts. Learn how to change authentication plugins, expire passwords, lock accounts, and adjust resource limits for specific users.

Syntax

Description

The ALTER USER statement modifies existing MariaDB accounts. To use it, you must have the global privilege or the privilege for the database. The global privilege is also required if the system variable is enabled.

If any of the specified user accounts do not yet exist, an error results. If an error occurs, ALTER USER will still modify the accounts that do not result in an error. Only one error is produced for all users which have not been modified.

For renaming an existing account (user name and/or host), see .

IF EXISTS

When the IF EXISTS clause is used, MariaDB will return a warning instead of an error for each specified user that does not exist.

Account Names

For ALTER USER statements, account names are specified as the username argument in the same way as they are for statements. See from the CREATE USER page for details on how account names are specified.

or CURRENT_USER() can also be used to alter the account logged into the current session. For example, to change the current user's password to mariadb:

Authentication Options

From MariaDB 10.4, it is possible to use more than one authentication plugin for each user account. For example, this can be useful to slowly migrate users to the more secure ed25519 authentication plugin over time, while allowing the old mysql_native_password authentication plugin as an alternative for the transitional period. See for more.

When running ALTER USER, not specifying an authentication option in the IDENTIFIED VIA clause will remove that authentication method. (However this was not the case before , see )

For example, a user is created with the ability to authenticate via both a password and unix_socket:

If the user's password is updated, but unix_socket authentication is not specified in the IDENTIFIED VIA clause, unix_socket authentication will no longer be permitted.

IDENTIFIED BY 'password'

The optional IDENTIFIED BY clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the function prior to being stored in the view.

For example, if our password is mariadb, then we can set the account's password with:

If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.

The only that this clause supports are and .

IDENTIFIED BY PASSWORD 'password_hash'

The optional IDENTIFIED BY PASSWORD clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the #function. It will be stored in the view as-is.

For example, if our password is mariadb, then we can find the hash with:

And then we can set an account's password with the hash:

The only that this clause supports are and .

IDENTIFIED {VIA|WITH} authentication_plugin

The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific . The plugin name must be an active authentication plugin as per . If it doesn't show up in that output, then you will need to install it with or .

For example, this could be used with the :

Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the accepts a :

The exact meaning of the additional argument would depend on the specific authentication plugin.

The USING or AS keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the function. This is only valid for that have implemented a hook for the function. For example, the authentication plugin supports this:

The USING or AS keyword cannot be used to provide a plain-text password to a plugin if it's provided as an argument to the function.

TLS Options

By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.

To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors.

See for more information about how to determine whether your MariaDB server has TLS support.

You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the , , or statements. The following options are available:

Option

Description

The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required.

For example, you can alter a user account to require these TLS options with the following:

If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.

See for information on how to enable TLS on the client and server.

Resource Limit Options

It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:

Limit Type

Description

If any of these limits are set to 0, then there is no limit for that resource for that user.

Here is an example showing how to set an account's resource limits:

The resources are tracked per account, which means 'user'@'server'; not per user name or per connection.

The count can be reset for all users using , or .

Per account resource limits are stored in the table, in the database. Columns used for resources limits are named max_questions, max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for MAX_USER_CONNECTIONS).

Password Expiry

Besides automatic password expiry, as determined by , password expiry times can be set on an individual user basis, overriding the global setting, for example:

See for more details.

Account Locking

Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example:

See for more details.

The lock_option and password_option clauses can occur in either order.

The lock_option must be placed before the password_option.

_{This page is licensed: CC BY-SA / Gnu FDL}

SHOW ENGINE INNODB STATUS

View extensive status information for the InnoDB engine. This statement displays details on deadlocks, buffer pool usage, and I/O activity.

Syntax

SHOW ENGINE INNODB STATUS is a specific form of the SHOW ENGINE statement that displays the InnoDB Monitor output, which is extensive InnoDB information which can be useful in diagnosing problems.

The following sections are displayed

Status: Shows the timestamp, monitor name and the number of seconds, or the elapsed time between the current time and the time the InnoDB Monitor output was last displayed. The per-second averages are based upon this time.
BACKGROUND THREAD: srv_master_thread lines show work performed by the main background thread.
SEMAPHORES: Threads waiting for a semaphore and stats on how the number of times threads have needed a spin or a wait on a mutex or rw-lock semaphore. If this number of threads is large, there may be I/O or contention issues. Reducing the size of the system variable may help if contention is related to thread scheduling. Spin rounds per wait shows the number of spinlock rounds per OS wait for a mutex.
LATEST FOREIGN KEY ERROR: Only shown if there has been a foreign key constraint error, it displays the failed statement and information about the constraint and the related tables.
LATEST DETECTED DEADLOCK: Only shown if there has been a deadlock, it displays the transactions involved in the deadlock and the statements being executed, held and required locked and the transaction rolled back to.
TRANSACTIONS: The output of this section can help identify lock contention, as well as reasons for the deadlocks.
FILE I/O: InnoDB thread information as well as pending I/O operations and I/O performance statistics.
INSERT BUFFER AND ADAPTIVE HASH INDEX: InnoDB insert buffer (old name for the ) and adaptive hash index status information, including the number of each type of operation performed, and adaptive hash index performance.
LOG: InnoDB log information, including current log sequence number, how far the log has been flushed to disk, the position at which InnoDB last took a checkpoint, pending writes and write performance statistics.
BUFFER POOL AND MEMORY: Information on buffer pool pages read and written, which allows you to see the number of data file I/O operations performed by your queries. See for more. Similar information is also available from the table.
ROW OPERATIONS:Information about the main thread, including the number and performance rate for each type of row operation.

If the system variable is set to 1, extended lock information will be displayed.

Example output:

_{This page is licensed: CC BY-SA / Gnu FDL}

SET TRANSACTION

Define isolation levels and access modes for transactions. Learn to configure the behavior of the next transaction or the entire session for data consistency.

Syntax

Description

This statement sets the transaction isolation level or the transaction access mode globally, for the current session, or for the next transaction:

With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected.
With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session.
Without any SESSION or GLOBAL keyword, the statement sets the isolation level for only the next (not started) transaction performed within the current session. After that it reverts to using the session value.

A change to the global default isolation level requires the privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction.

Isolation Level

To set the global default isolation level at server startup, use the option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the allowable values are ,, , or . For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mariadb] section of an option file:

To determine the global and session transaction isolation levels at runtime, check the value of the variable.

To determine the global and session transaction isolation levels at runtime, check the value of the system variable.

InnoDB supports each of the translation isolation levels described here using different locking strategies. The default level isREPEATABLE READ. For additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see , and .

Isolation Levels

The following sections describe how MariaDB supports the different transaction levels.

READ UNCOMMITTED

SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty read". Otherwise, this isolation level works likeREAD COMMITTED.

READ COMMITTED

A somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See .

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus allows the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because "phantom rows" must be blocked for MariaDB replication and recovery to work.

If the READ COMMITTED isolation level is used or the system variable is enabled, there is no InnoDB gap locking except for constraint checking and duplicate-key checking. Also, record locks for non-matching rows are released after MariaDB has evaluated the WHERE condition. If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

Rows that don't match are not being locked in a so called semiconsistent read. This means you might see only a partially consistent read when the transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

(A semiconsistent read applies to UPDATE and DELETE statements. Those statements skip locked rows, provided the version in the current read does not match the WHERE condition. Also, if the latest version of a record was successfully locked, but found not to match the condition, the lock is released.)

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See .

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. MariaDB does not relax the gap locking for unique indexes.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

This is the minimum isolation level for non-distributed .

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to if is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (non-locking) read and need not block for other transactions. (This means that to force a plain SELECT to block if other transactions have modified the selected rows, you should disable autocommit.)

Distributed should always use this isolation level.

innodb_snapshop_isolation

If the system variable is not set to ON, strictly-speaking anything other than READ UNCOMMITTED is not clearly defined. defaults to OFF for backwards compatibility. Setting to ON will result in attempts to acquire a lock on a record that does not exist in the current read view raising an error, and the transaction being rolled back.

If the system variable is not set to ON, strictly-speaking anything other than READ UNCOMMITTED is not clearly defined.

Access Mode

The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global mode, the privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.

It is not permitted to specify both READ WRITE and READ ONLY in the same statement.

READ WRITE and READ ONLY can also be specified in the statement, in which case the specified mode is only valid for one transaction.

Examples

Attempting to set the isolation level within an existing transaction without specifying GLOBAL or SESSION.

_{This page is licensed: GPLv2, originally from}

SHOW CREATE TABLE

Get the SQL statement to recreate a table. This statement shows the complete CREATE TABLE syntax, including column definitions and indexes.

Syntax

Description

Shows the CREATE TABLE statement that creates the given table. The statement requires the SELECT privilege for the table. This statement also works with and .

SHOW CREATE TABLE quotes table and column names according to the value of the server system variable.

Certain values can result in parts of the original CREATE statement not being included in the output. MariaDB-specific table options, column options, and index options are not included in the output of this statement if the , and flags are used. All MariaDB-specific table attributes are also not shown when a non-MariaDB/MySQL emulation mode is used, which includes , , , , or .

Invalid table options, column options and index options are normally commented out (note, that it is possible to create a table with invalid options, by altering a table of a different engine, where these options were valid). To have them uncommented, enable the . Remember that replaying a statement with uncommented invalid options will fail with an error, unless the is in effect.

Note that SHOW CREATE TABLE is not meant to provide metadata about a table. It provides information about how the table was declared, but the real table structure could differ a bit. For example, if an index has been declared as HASH, the CREATE TABLE statement returned by SHOW CREATE TABLE will declare that index as HASH; however, it is possible that the index is in fact a BTREE, because the storage engine does not support HASH.

MariaDB permits and data types to be assigned a value. As a result, SHOW CREATE TABLE will append a DEFAULT NULL to nullable TEXT or BLOB fields if no specific default is provided.

Numbers are quoted in the DEFAULT clause in SHOW CREATE statement.

Numbers are not quoted in the DEFAULT clause in SHOW CREATE statement.

Index Order

Indexes are sorted and displayed in the following order, which may differ from the order of the CREATE TABLE statement.

PRIMARY KEY
UNIQUE keys where all column are NOT NULL
UNIQUE keys that don't contain partial segments
Other UNIQUE keys

See sql/sql_table.cc for details.

Examples

With off:

impacting the output:

CREATE USER

Create new database accounts. This guide covers the syntax for defining users, setting authentication methods, and establishing initial resource limits.

Syntax

Description

The CREATE USER statement creates new MariaDB accounts. To use it, you must have the global privilege or the privilege for the database.

For each account, CREATE USER creates a new row in the view (and the underlying table) that has no privileges.

For each account, CREATE USER creates a new row in table that has no privileges.

If any of the specified accounts, or any permissions for the specified accounts, already exist, then the server returns ERROR 1396 (HY000). If an error occurs, CREATE USER will still create the accounts that do not result in an error. Only one error is produced for all users which have not been created:

CREATE USER, , , and all produce the same error code when they fail.

See below for details on how account names are specified.

One can also create users with if does not have set. NO_AUTO_CREATE_USER is set by default.

OR REPLACE

If the optional OR REPLACE clause is used, it is basically a shortcut for:

For example:

IF NOT EXISTS

When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified user already exists.

For example:

Authentication Options

If more than one authentication mechanism is declared using the OR keyword, the mechanisms are attempted in the order they are declared in the CREATE USER statement. As soon as one of the authentication mechanisms is successful, authentication is complete. If none of them is successful, the authentication has failed.

IDENTIFIED BY 'password'

For example, if our password is mariadb, then we can create the user with:

The only that this clause supports are and .

IDENTIFIED BY PASSWORD 'password_hash'

For example, if our password is mariadb, then we can find the hash with:

And then we can create a user with the hash:

The only that this clause supports are and .

IDENTIFIED {VIA|WITH} authentication_plugin

VIA and WITH are synonyms.

For example, this could be used with the :

Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the accepts a :

The exact meaning of the additional argument would depend on the specific authentication plugin.

One can specify many authentication plugins, they all work as alternatives ways of authenticating a user:

By default, when you create a user without specifying an authentication plugin, MariaDB uses the plugin.

TLS Options

MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors.

By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.

See for more information about how to determine whether your MariaDB server has TLS support.

Option

Description

The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required.

For example, you can create a user account that requires these TLS options with the following:

If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.

See for information on how to enable TLS on the client and server.

Resource Limit Options

It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:

Limit Type

Decription

If any of these limits are set to 0, then there is no limit for that resource for that user.

Here is an example showing how to create a user with resource limits:

The resources are tracked per account, which means 'user'@'server'; not per user name or per connection.

The count can be reset for all users using , or .

Account Names

Account names have both a user name component and a host name component, and are specified as 'user_name'@'host_name'.

The user name and host name may be unquoted, quoted as strings using double quotes (") or single quotes ('), or quoted as identifiers using backticks (```). You must use quotes when using special characters (such as a hyphen) or wildcard characters. If you quote, you must quote the user name and host name separately (for example 'user_name'@'host_name').

Host Name Component

If the host name is not provided, it is assumed to be '%'.

Host names may contain the wildcard characters % and _. They are matched as if by the clause. If you need to use a wildcard character literally (for example, to match a domain name with an underscore), prefix the character with a backslash. See LIKE for more information on escaping wildcard characters.

Host name matches are case-insensitive. Host names can match either domain names or IP addresses. Use 'localhost' as the host name to allow only local client connections. On Linux, the loopback interface (127.0.0.1) will not match 'localhost' as it is not considered a local connection: this means that only connections via UNIX-domain sockets will match 'localhost'.

You can use a netmask to match a range of IP addresses using 'base_ip/netmask' as the host name. A user with an IP address ip_addr will be allowed to connect if the following condition is true:

For example, given a user:

the IP addresses satisfying this condition range from 247.150.130.0 to 247.150.130.255.

Using 255.255.255.255 is equivalent to not using a netmask at all. Netmasks cannot be used for IPv6 addresses.

Note that the credentials added when creating a user with the '%' wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence.

Before , the host name component could be up to 60 characters in length. Starting from , it can be up to 255 characters.

User Name Component

User names must match exactly, including case. A user name that is empty is known as an anonymous account and is allowed to match a login attempt with any user name component. These are described more in the next section.

For valid identifiers to use as user names, see .

It is possible for more than one account to match when a user connects. MariaDB selects the first matching account after sorting according to the following criteria:

Accounts with an exact host name are sorted before accounts using a wildcard in the host name. Host names using a netmask are considered to be exact for sorting.
Accounts with a wildcard in the host name are sorted according to the position of the first wildcard character. Those with a wildcard character later in the host name sort before those with a wildcard character earlier in the host name.
Accounts with a non-empty user name sort before accounts with an empty user name.
Accounts with an empty user name are sorted last. As mentioned previously, these are known as anonymous accounts. These are described more in the next section.

The following table shows a list of example account as sorted by these criteria:

Once connected, you only have the privileges granted to the account that matched, not all accounts that could have matched. For example, consider the following commands:

If you connect as joffrey from 192.168.0.3, you will have the SELECT privilege on the table test.t1, but not on the table test.t2. If you connect as joffrey from any other IP address, you will have the SELECT privilege on the table test.t2, but not on the table test.t1.

Usernames can be up to 80 characters long before 10.6 and starting from 10.6 it can be 128 characters long.

Anonymous Accounts

Anonymous accounts are accounts where the user name portion of the account name is empty. These accounts act as special catch-all accounts. If a user attempts to log into the system from a host, and an anonymous account exists with a host name portion that matches the user's host, then the user will log in as the anonymous account if there is no more specific account match for the user name that the user entered.

For example, here are some anonymous accounts:

Fixing a Legacy Default Anonymous Account

On some systems, the table has some entries for the ''@'%' anonymous account by default. Unfortunately, there is no matching entry in the / table, which means that this anonymous account doesn't exactly exist, but it does have privileges--usually on the default test database created by . These account-less privileges are a legacy that is leftover from a time when MySQL's privilege system was less advanced.

This situation means that you will run into errors if you try to create a ''@'%' account. For example:

The fix is to the row in the table and then execute :

Note that FLUSH PRIVILEGES is only needed if one modifies the mysql tables directly. It is not needed when using CREATE USER, DROP USER, GRANT etc.

And then the account can be created:

See for more information.

Password Expiry

Besides automatic password expiry, as determined by , password expiry times can be set on an individual user basis, overriding the global setting, for example:

See for more details.

Account Locking

See for more details.

The lock_option and password_option clauses can occur in either order.

Prior to and , the lock_option must be placed before the password_option.

From and , the lock_option and password_option clauses can occur in either order.

EXPLAIN

Syntax

Description

The EXPLAIN statement can be used either as a synonym for or as a way to obtain information about how MariaDB executes a SELECT, UPDATE or DELETE statement:

'EXPLAIN tbl_name' is synonymous with'[DESCRIBE](../describe.md) tbl_name' or'[SHOW COLUMNS](../show/show-columns.md) FROM tbl_name'.
When you precede a SELECT, UPDATE or a DELETE statement with the keywordEXPLAIN, MariaDB displays information from the optimizer about the query execution plan. That is, MariaDB explains how it would process the SELECT

shows the output of a running statement. In some cases, its output can be closer to reality than EXPLAIN.

The runs a statement and returns information about its execution plan. It also shows additional columns, to check how much the optimizer's estimation about filtering and found rows are close to reality.

There is an online that you can use to share EXPLAIN and EXPLAIN EXTENDED output with others.

EXPLAIN can acquire metadata locks in the same way that SELECT does, as it needs to know table metadata and, sometimes, data as well.

Columns in EXPLAIN ... SELECT

Column name

Description

Here are descriptions of the values for some of the more complex columns in EXPLAIN ... SELECT:

"Select_type" Column

The select_type column can have the following values:

Value

Description

Comment

"Type" Column

This column contains information on how the table is accessed.

Value

Description

"Extra" Column

This column consists of one or more of the following values, separated by ';'

Note that some of these values are detected after the optimization phase.

The optimization phase can do the following changes to the WHERE clause:

Add the expressions from the ON and USING clauses to the WHERE clause.
Constant propagation: If there is column=constant, replace all column instances with this constant.
Replace all columns from 'const' tables with their values.

Value

Description

EXPLAIN EXTENDED

The EXTENDED keyword adds another column, filtered, to the output. This is a percentage estimate of the table rows that will be filtered by the condition.

An EXPLAIN EXTENDED will always throw a warning, as it adds extra Message information to a subsequent statement. This includes what the SELECT query would look like after optimizing and rewriting rules are applied and how the optimizer qualifies columns and tables.

Examples

As synonym for DESCRIBE or SHOW COLUMNS FROM:

A simple set of examples to see how EXPLAIN can identify poor index usage:

SELECT on a primary key:

The type is const, which means that only one possible result could be returned. Now, returning the same record but searching by their phone number:

Here, the type is All, which means no index could be used. Looking at the rows count, a full table scan (all six rows) had to be performed in order to retrieve the record. If it's a requirement to search by phone number, an index will have to be created.

example:

Example of `ref_or_null` Optimization

ref_or_null is something that often happens when you use subqueries with NOT IN as then one has to do an extra check for NULL values if the first value didn't have a matching row.

FLUSH

Reload internal caches and clear buffers. Learn how to use the FLUSH statement to reset logs, privileges, hosts, and other server states without restarting.

Syntax

or when flushing tables:

where table_list is a list of tables separated by , (comma).

Description

The FLUSH statement clears or reloads various internal caches used by MariaDB. To execute FLUSH, you must have the RELOAD privilege. See .

The RESET statement is similar to FLUSH. See .

You cannot issue a FLUSH statement from within a or a . Doing so within a stored procedure is permitted, as long as it is not called by a stored function or trigger. See , and .

If a listed table is a view, an error like the following will be produced:

By default, FLUSH statements are written to the and will be . The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.

The different flush options are:

Option

Description

You can also use the client to flush things. Use mariadb-admin --help to examine what flush commands it supports.

`FLUSH RELAY LOGS`

Compatibility with MySQL

The FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after the FLUSH command. For example, one can now use:

FOR CHANNEL isn't available.

FLUSH STATUS

can be reset by executing the following:

This statement requires the privilege.

Specify FLUSH GLOBAL or FLUSH SESSION. Flushing of global status variables has been moved to FLUSH GLOBAL STATUS which is a synonym for FLUSH STATUS. You can use old-mode=OLD_FLUSH_STATUS to restore the old behavior of the FLUSH STATUS statement.

The variables flushed are mainly session, but some are global. Not all session (or global) variables are flushed - the decision was made per variable.

Global Status Variables that Support `FLUSH STATUS`

Not all global status variables support being reset by FLUSH STATUS. Currently, the following is an incomplete list of status variables are reset by FLUSH GLOBAL STATUS in 11.5 or FLUSH STATUS in earlier versions:

FLUSH TABLES

MariaDB starting with

FLUSH TABLES doesn't cause to be reloaded or recalculated. , however, triggers a reload of the statistics.

FLUSH TABLES causes to be reloaded or recalculated.

Purpose of FLUSH TABLES

The purpose of FLUSH TABLES is to clean up the open table cache and table definition cache of tables that are not in use. This frees up memory and file descriptors. Normally this is not needed as the caches works on a first-in, first-out basis, but can be useful if the server seems to use too much memory for some reason.

Purpose of FLUSH TABLES WITH READ LOCK

FLUSH TABLES WITH READ LOCK is useful if you want to take a backup of some tables. When FLUSH TABLES WITH READ LOCK returns, all write access to tables is blocked and all tables are marked as 'properly closed' on disk. The tables can still be used for read operations.

Purpose of FLUSH TABLES table_list

FLUSH TABLES table_list is useful if you want to copy a table object or files to or from the server. This command puts a lock that stops new users of the table and will wait until everyone has stopped using the table. The table is then removed from the table definition and table cache.

Note that it's up to the user to ensure that no one is accessing the table between FLUSH TABLES and the table is copied to or from the server. This can be secured by using .

If there are any tables locked by the connection that is using FLUSH TABLES all the locked tables will be closed as part of the flush and reopened and relocked before FLUSH TABLES returns. This allows one to copy the table after FLUSH TABLES returns without having any writes on the table. For now this works with most tables, except InnoDB as InnoDB may do background purges on the table even while it's write locked.

Purpose of FLUSH TABLES table_list WITH READ LOCK

FLUSH TABLES table_list WITH READ LOCK should work as FLUSH TABLES WITH READ LOCK, but only those tables that are listed will be properly closed. However in practice this works exactly like FLUSH TABLES WITH READ LOCK as the FLUSH command has anyway to wait for all WRITE operations to end because we are depending on a global read lock for this code. In the future we should consider fixing this to instead use meta data locks.

Implementation of FLUSH TABLES

Free memory and file descriptors not in use

Implementation of FLUSH TABLES WITH READ LOCK

Lock all tables read only for simple old style backup.
All background writes are suspended and tables are marked as closed.
No statement requiring table changes are allowed for any user until UNLOCK TABLES.

Instead of using FLUSH TABLE WITH READ LOCK one should in most cases instead use .

Implementation of FLUSH TABLES table_list

Free memory and file descriptors for tables not in use from table list.
Lock given tables as read only.
Wait until all translations has ended that uses any of the given tables.
Wait until all background writes are suspended and tables are marked as closed.

Implementation of FLUSH TABLES table_list FOR EXPORT

Free memory and file descriptors for tables not in use from table list.
Lock given tables as read.
Wait until all background writes are suspended and tables are marked as closed.
Check that all tables supports FOR EXPORT.

This is basically the same behavior as in older MariaDB versions if you first lock the tables, then do FLUSH TABLES. The tables will be copyable until you issue UNLOCK TABLES.

FLUSH SSL

The FLUSH SSL command can be used to dynamically reinitialize the server's context. This is most useful if you need to replace a certificate that is about to expire without restarting the server.

This operation is performed by reloading the files defined by the following :

These are not dynamic, so their values can not be changed without restarting the server.

If you want to dynamically reinitialize the server's context, then you need to change the certificate and key files at the relevant paths defined by these , without actually changing the values of the variables. See for more information.

Reducing Memory Usage

To flush some of the global caches that take up memory, you could execute the following command:

_{This page is licensed: GPLv2, originally from}

DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | YES  |     | NULL    |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

CREATE TABLE IF NOT EXISTS `employees_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(40) NOT NULL,
  `position` varchar(25) NOT NULL,
  `home_address` varchar(50) NOT NULL,
  `home_phone` varchar(12) NOT NULL,
  `employee_code` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_code` (`employee_code`),
  KEY `first_name` (`first_name`,`last_name`)
) ENGINE=Aria;

INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
  VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1');

SHOW INDEXES FROM employees_example;
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees_example |          0 | PRIMARY       |            1 | id            | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          0 | employee_code |            1 | employee_code | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          1 | first_name    |            1 | first_name    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          1 | first_name    |            2 | last_name     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

EXPLAIN SELECT * FROM employees_example WHERE id=1;
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table             | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | employees_example | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+

EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492';
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | employees_example | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

CHANGE MASTER TO

The terms master and slave have historically been used in replication, and MariaDB has begun the process of adding primary and replica synonyms. The old terms will continue to be used to maintain backward compatibility - see MDEV-18777 to follow progress on this effort.

Syntax

Description

CHANGE MASTER is used on a replica to set up or change settings for connecting to the primary.

The FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after CHANGE MASTER.

FOR CHANNEL is not available.

Multi-Source Replication

If you are using , then you need to specify a connection name when you execute CHANGE MASTER. There are two ways to do this:

Setting the system variable prior to executing CHANGE MASTER.
Setting the connection_name parameter when executing CHANGE MASTER.

default_master_connection

connection_name

Options

Connection Options

MASTER_USER

The MASTER_USER option for CHANGE MASTER defines the user account that the will use to connect to the .

This user account will need the privilege on the primary.

For example:

The maximum length of the MASTER_USER string is 128 characters.

The maximum length of the MASTER_USER string is 96 characters.

MASTER_PASSWORD

The MASTER_PASSWORD option for CHANGE MASTER defines the password that the will use to connect to the as the user account defined by the option.

For example:

The maximum length of the MASTER_PASSWORD string is 32 characters. The effective maximum length of the string depends on how many bytes are used per character and can be up to 96 characters.

Due to , the password can be silently truncated to 41 characters when MariaDB is restarted. For this reason, it is recommended to use a password that is shorter than this.

MASTER_HOST

The MASTER_HOST option for CHANGE MASTER defines the hostname or IP address of the .

If you set the value of the MASTER_HOST option to the empty string, then that is not the same as not setting the option's value at all. If you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command will fail with an error. In MariaDB 5.3 and before, if you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command would succeed, but the subsequent command would fail.

For example:

If you set the value of the MASTER_HOST option in a CHANGE MASTER command, then the replica assumes that the primary is different from before, even if you set the value of this option to the same value it had previously. In this scenario, the replica will consider the old values for the primary's file name and position to be invalid for the new primary. As a side effect, if you do not explicitly set the values of the and options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable mode for replication by setting the option to some value other than no in the statement, then these values will effectively be ignored anyway.

Replicas cannot connect to primaries using Unix socket files or Windows named pipes. The replica must connect to the primary using TCP/IP.

The maximum length of the MASTER_HOST string is 255 characters.

The maximum length of the MASTER_HOST string is 60 characters.

MASTER_PORT

The MASTER_PORT option for CHANGE MASTER defines the TCP/IP port of the .

For example:

If you set the value of the MASTER_PORT option in a CHANGE MASTER command, then the replica assumes that the primary is different from before, even if you set the value of this option to the same value it had previously. In this scenario, the replica will consider the old values for the primary's file name and position to be invalid for the new primary. As a side effect, if you do not explicitly set the values of the and options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable mode for replication by setting the option to some value other than no in the statement, then these values will effectively be ignored anyway.

Replicas cannot connect to primaries using Unix socket files or Windows named pipes. The replica must connect to the primary using TCP/IP.

MASTER_CONNECT_RETRY

The MASTER_CONNECT_RETRY option for CHANGE MASTER defines how many seconds that the replica will wait between connection retries. The default is 60.

MASTER_RETRY_COUNT

The MASTER_RETRY_COUNT option limits the number of connection attempts (i.e., Connects_Tried in ). For example:

Setting this option resets the Connects_Tried statistic in to 0.

The default is the option, which be set either on the command-line or in a server in an prior to starting up the server. For example:

The MASTER_RETRY_COUNT option for CHANGE MASTER is only supported by MariaDB 12.0.1 and later and by MySQL. Please use the option instead, which be set either on the command-line or in a server in an prior to starting up the server. For example:

MASTER_BIND

The MASTER_BIND option for CHANGE MASTER is only supported by MySQL 5.6.2 and later and by MySQL NDB Cluster 7.3.1 and later. This option is not supported by MariaDB. See for more information.

The MASTER_BIND option for CHANGE MASTER can be used on replicas that have multiple network interfaces to choose which network interface the replica will use to connect to the primary.

MASTER_HEARTBEAT_PERIOD

The MASTER_HEARTBEAT_PERIOD option for CHANGE MASTER can be used to set the interval in seconds between replication heartbeats. Whenever the primary's is updated with an event, the waiting period for the next heartbeat is reset.

This option's interval argument has the following characteristics:

It is a decimal value with a range of 0 to 4294967 seconds.
It has a resolution of hundredths of a second.
Its smallest valid non-zero value is 0.001.

Heartbeats are sent by the primary only if there are no unsent events in the binary log file for a period longer than the interval.

If the statement is executed, then the heartbeat interval is reset to the default.

If the system variable is set to a value that is lower than the current heartbeat interval, then a warning will be issued.

TLS Options

The TLS options are used for providing information about . The options can be set even on replicas that are compiled without TLS support. The TLS options are saved to either the default master.info file or the file that is configured by the option, but these TLS options are ignored unless the replica supports TLS.

See for more information.

MASTER_SSL

The MASTER_SSL option for CHANGE MASTER tells the replica whether to force for the connection. The valid values are 0 or 1. Required to be set to 1 for the other MASTER_SSL* options to have any effect.

For example:

MASTER_SSL_CA

The MASTER_SSL_CA option for CHANGE MASTER defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for . This option requires that you use the absolute path, not a relative path.

For example:

See for more information.

The maximum length of MASTER_SSL_CA string is 511 characters.

MASTER_SSL_CAPATH

The MASTER_SSL_CAPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for . This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the command.

For example:

See for more information.

The maximum length of MASTER_SSL_CA_PATH string is 511 characters.

MASTER_SSL_CERT

The MASTER_SSL_CERT option for CHANGE MASTER defines a path to the X509 certificate file to use for . This option requires that you use the absolute path, not a relative path.

For example:

The maximum length of MASTER_SSL_CERT string is 511 characters.

MASTER_SSL_CRL

The MASTER_SSL_CRL option for CHANGE MASTER defines a path to a PEM file that should contain one or more revoked X509 certificates to use for . This option requires that you use the absolute path, not a relative path.

This option is only supported if the server was built with OpenSSL. If the server was built with yaSSL, then this option is not supported. See for more information about which libraries are used on which platforms.

For example:

See for more information.

The maximum length of MASTER_SSL_CRL string is 511 characters.

MASTER_SSL_CRLPATH

The MASTER_SSL_CRLPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for . This option requires that you use the absolute path, not a relative path. The directory specified by this variable needs to be run through the command.

For example:

See for more information.

The maximum length of MASTER_SSL_CRL_PATH string is 511 characters.

MASTER_SSL_KEY

The MASTER_SSL_KEY option for CHANGE MASTER defines a path to a private key file to use for . This option requires that you use the absolute path, not a relative path.

For example:

The maximum length of MASTER_SSL_KEY string is 511 characters.

MASTER_SSL_CIPHER

The MASTER_SSL_CIPHER option for CHANGE MASTER defines the list of permitted ciphers or cipher suites to use for . Besides cipher names, if MariaDB was compiled with OpenSSL, this option could be set to "SSLv3" or "TLSv1.2" to allow all SSLv3 or all TLSv1.2 ciphers. Note that the TLSv1.3 ciphers cannot be excluded when using OpenSSL, even by using this option. See for details.

For example:

The maximum length of MASTER_SSL_CIPHER string is 511 characters.

MASTER_SSL_VERIFY_SERVER_CERT

The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables . This option is enabled by default.

The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables . This option is disabled by default.

For example:

See for more information.

Binary Log Options

These options are related to the position on the primary.

MASTER_LOG_FILE

The MASTER_LOG_FILE option for CHANGE MASTER can be used along with MASTER_LOG_POS to specify the coordinates at which the should begin reading from the primary's the next time the thread starts.

For example:

The and options cannot be specified if the and options were also specified.

The and options are effectively ignored if you enable mode for replication by setting the option to some value other than no in the statement.

MASTER_LOG_POS

The MASTER_LOG_POS option for CHANGE MASTER can be used along with MASTER_LOG_FILE to specify the coordinates at which the should begin reading from the primary's the next time the thread starts.

For example:

The and options cannot be specified if the and options were also specified.

The and options are effectively ignored if you enable mode for replication by setting the option to some value other than no in the statement.

Relay Log Options

These options are related to the position on the replica.

RELAY_LOG_FILE

The RELAY_LOG_FILE option for CHANGE MASTER can be used along with the option to specify the coordinates at which the should begin reading from the the next time the thread starts.

The CHANGE MASTER statement usually deletes all files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing files are kept.

When you want to change the position, you only need to stop the . The can continue running. The and statements support the SQL_THREAD option for this scenario. For example:

When the value of this option is changed, the metadata about the position in the will also be changed in the relay-log.info file or the file that is configured by the system variable.

The and options cannot be specified if the and options were also specified.

RELAY_LOG_POS

The RELAY_LOG_POS option for CHANGE MASTER can be used along with the option to specify the coordinates at which the should begin reading from the the next time the thread starts.

The CHANGE MASTER statement usually deletes all files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing files are kept.

When you want to change the position, you only need to stop the . The can continue running. The and statements support the SQL_THREAD option for this scenario. For example:

When the value of this option is changed, the metadata about the position in the will also be changed in the relay-log.info file or the file that is configured by the system variable.

The and options cannot be specified if the and options were also specified.

GTID Options

MASTER_USE_GTID

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the when connecting to a primary. The possible values are:

current_pos - Replicate in mode and use as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, option instead.
replica_pos - Replicate in

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the when connecting to a primary. The possible values are:

current_pos - Replicate in mode and use as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, option instead.
slave_pos - Replicate in mode and use as the position to start downloading transactions from the primary. From , replica_pos is an alias for slave_pos.

For example:

Or:

MASTER_DEMOTE_TO_SLAVE

Used to transition a primary to become a replica. Replaces the old with a safe alternative by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. If gtid_slave_pos is morerecent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.

For example:

MASTER_DEMOTE_TO_SLAVE is not available.

Also see .

IGNORE_SERVER_IDS

The IGNORE_SERVER_IDS option for CHANGE MASTER can be used to configure a to ignore events that originated from certain servers. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of values. For example:

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

DO_DOMAIN_IDS

The DO_DOMAIN_IDS option for CHANGE MASTER can be used to configure a to only apply events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of values. Duplicate values are automatically ignored. For example:

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

The option and the option cannot both be set to non-empty values at the same time. If you want to set the option, and the option was previously set, then you need to clear the value of the option. For example:

The DO_DOMAIN_IDS option can only be specified if the replica is replicating in mode. Therefore, the option must also be set to some value other than no in order to use this option.

IGNORE_DOMAIN_IDS

The IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used to configure a to ignore events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of values. Duplicate values are automatically ignored. For example:

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

The IGNORE_DOMAIN_IDS option can only be specified if the replica is replicating in mode. Therefore, the option must also be set to some value other than no in order to use this option.

Delayed Replication Options

MASTER_DELAY

The MASTER_DELAY option for CHANGE MASTER can be used to enable . This option specifies the time in seconds (at least) that a replica should lag behind the primary up to a maximum value of 2147483647, or about 68 years. Before executing an event, the replica will first wait, if necessary, until the given time has passed since the event was created on the primary. The result is that the replica will reflect the state of the primary some time back in the past. The default is zero, no delay.

Changing Option Values

If you don't specify a given option when executing the CHANGE MASTER statement, then the option keeps its old value in most cases. Most of the time, there is no need to specify the options that do not need to change. For example, if the password for the user account that the replica uses to connect to its primary has changed, but no other options need to change, then you can just change the option by executing the following commands:

There are some cases where options are implicitly reset, such as when the and options are changed.

Option Persistence

The values of the and options (i.e. the position on the primary) and most other options are written to either the default master.info file or the file that is configured by the option. The keeps this position updated as it downloads events only when optionis set to NO. Otherwise the file is not updated on a per event basis.

The option can be set either on the command-line or in a server in an prior to starting up the server. For example:

The values of the and options (i.e. the position) are written to either the default relay-log.info file or the file that is configured by the system variable. The keeps this position updated as it applies events.

The system variable can be set either on the command-line or in a server in an prior to starting up the server. For example:

GTID Persistence

If the replica is replicating events that contain , then the will write every GTID that it applies to the table. This GTID can be inspected and modified through the system variable.

If the replica has the system variable enabled and if the replica has the enabled, then every write by the will also go into the replica's . This means that of replicated transactions would be reflected in the value of the system variable.

Creating a Replica from a Backup

The CHANGE MASTER statement is useful for setting up a replica when you have a backup of the primary and you also have the position or position corresponding to the backup.

After restoring the backup on the replica, you could execute something like this to use the position:

Or you could execute something like this to use the position:

See for more information on how to do this with .

Example

The following example changes the primary and primary's binary log coordinates. This is used when you want to set up the replica to replicate the primary:

GRANT

Assign privileges and roles. Learn the syntax to give users or roles permission to access databases, tables, and execute specific commands.

Syntax

Description

The GRANT statement allows you to grant privileges or to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

Use the statement to revoke privileges granted with the GRANT statement.

Use the statement to determine what privileges an account has.

Account Names

For GRANT statements, account names are specified as the username argument in the same way as they are for statements. See from the CREATE USER page for details on how account names are specified.

Implicit Account Creation

The GRANT statement also allows you to implicitly create accounts in some cases.

If the account does not yet exist, then GRANT can implicitly create it. To implicitly create an account with GRANT, a user is required to have the same privileges that would be required to explicitly create the account with the CREATE USER statement.

If the NO_AUTO_CREATE_USER is set, then accounts can only be created if authentication information is specified, or with a statement. If no authentication information is provided, GRANT will produce an error when the specified account does not exist, for example:

Privilege Levels

Privileges can be set globally, for an entire database, for a table or routine, or for individual columns in a table. Certain privileges can only be set at certain levels.

Global privileges do not take effect immediately and are only applied to connections created after the GRANT statement was executed.

are granted using *.* for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures. Global privileges are stored in .
are granted using db_name.* for priv_level, or using just * to use the . Database privileges include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database. Database privileges are stored in the .

The `USAGE` Privilege

The USAGE privilege grants no real privileges. The statement will show a global USAGE privilege for a newly-created user. You can use USAGE with the GRANT statement to change options like GRANT OPTIONand MAX_USER_CONNECTIONS without changing any account privileges.

The `ALL PRIVILEGES` Privilege

The ALL PRIVILEGES privilege grants all available privileges. Granting all privileges only affects the given privilege level. For example, granting all privileges on a table does not grant any privileges on the database or globally.

Using ALL PRIVILEGES does not grant the special GRANT OPTION privilege.

You can use ALL instead of ALL PRIVILEGES.

The `GRANT OPTION` Privilege

Use the WITH GRANT OPTION clause to give users the ability to grant privileges to other users at the given privilege level. Users with the GRANT OPTION privilege can only grant privileges they have. They cannot grant privileges at a higher privilege level than they have the GRANT OPTION privilege.

The GRANT OPTION privilege cannot be set for individual columns. If you use WITH GRANT OPTION when specifying , the GRANT OPTION privilege will be granted for the entire table.

Using the WITH GRANT OPTION clause is equivalent to listing GRANT OPTION as a privilege.

Global Privileges

The following table lists the privileges that can be granted globally. You can also grant all database, table, and function privileges globally. When granted globally, these privileges apply to all databases, tables, or functions, including those created later.

To set a global privilege, use *.* for priv_level.

BINLOG ADMIN

Enables administration of the , including the statement and setting the system variables:

BINLOG MONITOR

New name for . REPLICATION CLIENT can still be used, though.

Use instead. isn't included in this privilege, and is required.

Permits running SHOW commands related to the , in particular the and statements.

BINLOG REPLAY

Enables replaying the binary log with the statement (generated by ), executing when is set to replication, and setting the session values of system variables usually included in BINLOG output, in particular:

CONNECTION ADMIN

Enables administering connection resource limit options. This includes ignoring the limits specified by and , and allowing one extra connection over

The statements specified in are not executed, owned by other users is permitted. The following connection-related system variables can be changed:

CREATE USER

Create a user using the statement, or implicitly create a user with the GRANT statement.

FEDERATED ADMIN

Execute , , and statements.

FEDERATED ADMIN is not available.

FILE

Read and write files on the server, using statements like or functions like . Also needed to create outward tables. MariaDB server must have the permissions to access those files.

GRANT OPTION

Grant global privileges. You can only grant privileges that you have.

PROCESS

Show information about the active processes, for example via or . If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MariaDB account that you are using).

READ_ONLY ADMIN

User ignores the system variable, and can perform write operations even when the read_only option is active.

A user with that privilege can also change the (global) value of read_only.

The READ_ONLY ADMIN privilege has been removed from . The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary.

User ignores the system variable, and can perform write operations even when the read_only option is active.

A user with that privilege can also change the (global) value of

RELOAD

Execute statements or equivalent commands.

REPLICATION CLIENT

Execute and informative statements. Renamed to (but still supported as an alias for compatibility reasons).

Execute and informative statements. is part of .

Execute and informative statements. Using instead is still supported as an alias.

Execute and informative statements. Renamed to in (but still supported as an alias for compatibility reasons). was part of prior to .

REPLICATION MASTER ADMIN

Permits administration of primary servers, including the statement, and setting the , , and system variables.

REPLICATION MASTER ADMIN is not available.

REPLICA MONITOR

Permit and .

See Reasoning tab as to why this was implemented.

When a user would upgrade from an older major release to a minor release prior to , certain user accounts would lose capabilities. For example, a user account that had the REPLICATION CLIENT privilege in older major releases could run , but after upgrading to a minor release prior to , they could no longer run , because that statement was changed to require the REPLICATION REPLICA ADMIN privilege.

This issue is fixed in with this new privilege, which now grants the user the ability to execute SHOW [ALL] (SLAVE | REPLICA) STATUS.

When a database is upgraded from an older major release to MariaDB Server 10.5.9 or later, any user accounts with the REPLICATION CLIENT or REPLICATION SLAVE privileges will automatically be granted the new

REPLICATION REPLICA

Synonym for .

REPLICATION REPLICA is not available.

REPLICATION SLAVE

Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master. is an alias for REPLICATION SLAVE.

Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master.

REPLICATION SLAVE ADMIN

Permits administering replica servers, including , , , , statements, replaying the binary log with the statement (generated by ), and setting the system variables:

SET USER

Enables setting the DEFINER when creating , , and .

SET USER isn't available.

SHOW DATABASES

List all databases using the statement. Without the SHOW DATABASES privilege, you can still issue the SHOW DATABASES statement, but it will only list databases containing tables on which you have privileges.

SHUTDOWN

Shut down the server using or the command.

SUPER

Execute superuser statements: , (users who do not have this privilege can only KILL their own threads), , , or the command. Also, this permission allows the user to write data even if the startup option is set, enable or disable logging, enable or disable replication on replica, specify a DEFINER for statements that support that clause, connect once reaching the MAX_CONNECTIONS. If a statement has been specified for the option, that command will not be executed when a user with SUPER privileges connects to the server.

The SUPER privilege has been split into multiple smaller privileges to allow for more fine-grained privileges (). The privileges are:

Database Privileges

The following table lists the privileges that can be granted at the database level. You can also grant all table and function privileges at the database level. Table and function privileges on a database apply to all tables or functions in that database, including those created later.

To set a privilege for a database, specify the database usingdb_name.* for priv_level, or just use * to specify the current. database.

Privilege

Description

Table Privileges

Privilege

Description

Column Privileges

Some table privileges can be set for individual columns of a table. To use column privileges, specify the table explicitly and provide a list of column names after the privilege type. For example, the following statement would allow the user to read the names and positions of employees, but not other information from the same table, such as salaries.

Privilege

Description

Function Privileges

Privilege

Description

Procedure Privileges

Privilege

Description

Package Privileges

Privilege

Description

Proxy Privileges

Privilege

Description

The PROXY privilege allows one user to proxy as another user, which means their privileges change to that of the proxy user, and the function returns the user name of the proxy user.

The PROXY privilege only works with authentication plugins that support it. The default authentication plugin does not support proxy users.

The authentication plugin is the only plugin included with MariaDB that currently supports proxy users. The PROXY privilege is commonly used with the authentication plugin to enable .

For example, to grant the PROXY privilege to an that authenticates with the authentication plugin, you could execute the following:

A user account can only grant the PROXY privilege for a specific user account if the granter also has the PROXY privilege for that specific user account, and if that privilege is defined WITH GRANT OPTION. For example, the following example fails because the granter does not have the PROXY privilege for that specific user account at all:

And the following example fails because the granter does have the PROXY privilege for that specific user account, but it is not defined WITH GRANT OPTION:

But the following example succeeds because the granter does have the PROXY privilege for that specific user account, and it is defined WITH GRANT OPTION:

A user account can grant the PROXY privilege for any other user account if the granter has the PROXY privilege for the ''@'%' anonymous user account, like this:

For example, the following example succeeds because the user can grant the PROXY privilege for any other user account:

The default root user accounts created by have this privilege. For example:

This allows the default root user accounts to grant the PROXY privilege for any other user account, and it also allows the default root user accounts to grant others the privilege to do the same.

Authentication Options

The authentication options for the GRANT statement are the same as those for the statement.

IDENTIFIED BY 'password'

The optional IDENTIFIED BY clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the function prior to being stored.

For example, if our password is mariadb, then we can create the user with:

If the user account already exists and if you provide the IDENTIFIED BY clause, then the user's password will be changed. You must have the privileges needed for the statement to change a user's password with GRANT.

The only that this clause supports are and .

IDENTIFIED BY PASSWORD 'password_hash'

For example, if our password is mariadb, then we can find the hash with:

And then we can create a user with the hash:

If the user account already exists and if you provide the IDENTIFIED BY clause, then the user's password will be changed. You must have the privileges needed for the tastatement to change a user's password with GRANT.

The only that this clause supports are and .

IDENTIFIED {VIA|WITH} authentication_plugin

For example, this could be used with the :

Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the accepts a :

The exact meaning of the additional argument would depend on the specific authentication plugin.

One can specify many authentication plugins, they all work as alternative ways of authenticating a user:

By default, when you create a user without specifying an authentication plugin, MariaDB uses the plugin.

Resource Limit Options

It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:

Limit Type

Decription

If any of these limits are set to 0, then there is no limit for that resource for that user.

To set resource limits for an account, if you do not want to change that account's privileges, you can issue a GRANT statement with the USAGE privilege, which has no meaning. The statement can name some or all limit types, in any order.

Here is an example showing how to set resource limits:

The resources are tracked per account, which means 'user'@'server'; not per user name or per connection.

The count can be reset for all users using , or .

Users with the CONNECTION ADMIN privilege or the SUPER privilege are not restricted by max_user_connections or max_password_errors , and they are allowed one additional connection when max_connections is reached.

Users with the CONNECTION ADMIN privilege or the SUPER privilege are restricted by max_user_connections or max_password_errors , and they are not allowed one additional connection when max_connections

TLS Options

See for more information about how to determine whether your MariaDB server has TLS support.

Option

Description

The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required.

For example, you can create a user account that requires these TLS options with the following:

If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.

See for information on how to enable TLS on the client and server.

Roles

The GRANT statement is also used to grant the use of a to one or more users or other roles. In order to be able to grant a role, the grantor doing so must have permission to do so (see WITH ADMIN in the article).

Specifying the WITH ADMIN OPTION permits the grantee to in turn grant the role to another.

For example, the following commands show how to grant the same role to a couple different users.

If a user has been granted a role, they do not automatically obtain all permissions associated with that role. These permissions are only in use when the user activates the role with the statement.

Be careful to avoid conflicting role and user names. In case of a conflict, the role name takes precedence, as shown in the following example. The GRANT statement assigns privileges to the role, not to the user:

TO PUBLIC

Syntax

GRANT ... TO PUBLIC grants privileges to all users with access to the server. The privileges also apply to users created after the privileges are granted. This can be useful when you only want to state once that all users need to have a certain set of privileges. When running , a user also sees all privileges inherited from PUBLIC. only shows TO PUBLIC grants.

Grant Examples

Granting Root-like Privileges

You can create a user that has privileges similar to the default root accounts by executing the following:

SQL Statements

Comment Syntax

Executable Comments

Executable Comment Syntax

Examples

Account Management

DROP ROLE

Syntax

Description

DROP USER

Syntax

Description

IF EXISTS

Examples

See Also

RENAME USER

Syntax

Description

SET DEFAULT ROLE

Syntax

Description

SET SESSION AUTHORIZATION

Examples

SET ROLE

Syntax

Description

Administrative Statements

BINLOG

Syntax

Description

CACHE INDEX

Syntax

Description

Examples

Implementation (for MyISAM)

DESCRIBE

Syntax

Description

HELP Command

Syntax

Description

PURGE BINARY LOGS

Syntax

Description

RESET

Syntax

Description

USE [DATABASE]

Syntax

Description

ANALYZE and EXPLAIN Statements

BACKUP Statements

FLUSH Statements

Plugin Statements

Legacy Replication Statements

SET Statements

SHOW Statements

Account Management

Administrative Statements

SQL Statements

DROP ROLE

Syntax

Description

SET DEFAULT ROLE

Syntax

Description

BINLOG

Syntax

Description

RENAME USER

Syntax

Description

SET ROLE

Syntax

Description

DESCRIBE

Syntax

Description

RESET

Syntax