All pages
Powered by GitBook
Couldn't generate the PDF for 784 pages, generation stopped at 100.
Extend with 50 more pages.
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 ManagementAdministrative StatementsSQL FunctionsPrepared StatementsProgrammatic & Compound StatementsStored RoutinesTable StatementsTransactionsComment SyntaxHELP Command

Comment Syntax

There are three supported comment styles in MariaDB:

  1. From a '#' to the end of a line:

SELECT * FROM users; 

# This is a comment
  1. From a '--' to the end of a line. The space after the two dashes is required (as in MySQL).

SELECT * FROM users; -- This is a comment
  1. C style comments from an opening '/*' to a closing '*/'. Comments of this form can span multiple lines:

SELECT * FROM users; /* This is a
multi-line
comment */

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:

/*! MySQL or MariaDB-specific code */

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

/*!##### MySQL or MariaDB-specific code */

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:

/*!50100 MySQL and MariaDB 5.1.0 (and above) code goes here. */

MariaDB-only executable comment syntax (starting from MariaDB 5.3.1):

/*M! MariaDB-specific code */
/*M!###### MariaDB-specific code */

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.

/*!50701 MariaDB-10.x ignores MySQL-5.7 specific code */

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

/*M!50701 MariaDB-10.x does not ignore this */

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.

SELECT 2 /* +1 */;
SELECT 1 /*! +1 */;
SELECT 1 /*!50101 +1 */;
SELECT 2 /*M! +1 */;
SELECT 2 /*M!50301 +1 */;

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

/*M!100100 select 1 ; */
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

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

/*M!100100 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+

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.

ALTER USER

Syntax

ALTER USER [IF EXISTS] 
 user_specification [,user_specification] ...
  [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  [WITH resource_option [resource_option] ...]
  [lock_option] [password_option] 

user_specification:
  username [authentication_option]

authentication_option:
  IDENTIFIED BY 'password' 
  | IDENTIFIED BY PASSWORD 'password_hash'
  | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule] ... 
 
authentication_rule:
  authentication_plugin
  | authentication_plugin {USING|AS} 'authentication_string'
  | authentication_plugin {USING|AS} PASSWORD('password')

tls_option
  SSL 
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

resource_option
  MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

password_option:
  PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY

lock_option:
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

Description

The ALTER USER statement modifies existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. The global SUPER privilege is also required if the read_only 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 RENAME USER.

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 CREATE USER statements. See account names from the CREATE USER page for details on how account names are specified.

CURRENT_USER 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:

ALTER USER CURRENT_USER() IDENTIFIED BY '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 Authentication from MariaDB 10.4 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 MariaDB 10.4.13, see MDEV-21928)

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

CREATE USER 'bob'@'localhost' 
  IDENTIFIED VIA mysql_native_password USING PASSWORD('pwd') 
  OR unix_socket;

SHOW CREATE USER 'bob'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for bob@localhost: CREATE USER `bob`@`localhost` 
  IDENTIFIED VIA mysql_native_password 
  USING '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' 
  OR 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.

ALTER USER 'bob'@'localhost' IDENTIFIED VIA mysql_native_password 
  USING PASSWORD('pwd2');

SHOW CREATE USER 'bob'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for bob@localhost: CREATE USER `bob`@`localhost` 
  IDENTIFIED BY PASSWORD '*38366FDA01695B6A5A9DD4E428D9FB8F7EB75512'

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 PASSWORD function prior to being stored in the mysql.user view.

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

ALTER USER foo2@test IDENTIFIED BY 'mariadb';

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 authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

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 PASSWORD#function. It will be stored in the mysql.user view as-is.

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

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+

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

ALTER USER foo2@test 
  IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

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 authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

IDENTIFIED {VIA|WITH} authentication_plugin

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

For example, this could be used with the PAM authentication plugin:

ALTER USER foo2@test IDENTIFIED VIA pam;

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

ALTER USER foo2@test IDENTIFIED VIA pam USING 'mariadb';

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 PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:

ALTER USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');

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 PASSWORD() 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 Secure Connections Overview 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 CREATE USER, ALTER USER, or GRANT statements. The following options are available:

Option
Description

REQUIRE NONE

TLS is not required for this account, but can still be used.

REQUIRE SSL

The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options.

REQUIRE X509

The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options.

REQUIRE ISSUER 'issuer'

The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order.

REQUIRE SUBJECT 'subject'

The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order.

REQUIRE CIPHER 'cipher'

The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order.

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:

ALTER USER 'alice'@'%'
 REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND
 ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
 AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

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 Securing Connections for Client and Server 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

MAX_QUERIES_PER_HOUR

Number of statements that the account can issue per hour (including updates)

MAX_UPDATES_PER_HOUR

Number of updates (not queries) that the account can issue per hour

MAX_CONNECTIONS_PER_HOUR

Number of connections that the account can start per hour

MAX_USER_CONNECTIONS

Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections.

MAX_STATEMENT_TIME

Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute.

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:

ALTER USER 'someone'@'localhost' WITH
    MAX_USER_CONNECTIONS 10
    MAX_QUERIES_PER_HOUR 200;

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 FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mysqladmin reload.

Per account resource limits are stored in the user table, in the mysql 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 default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global setting, for example:

ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;

See User Password Expiry 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:

ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;

See Account Locking 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.

  • Authentication from MariaDB 10.4

  • GRANT

  • CREATE USER

  • DROP USER

  • SET PASSWORD

  • SHOW CREATE USER

  • mysql.user

  • Password Validation Plugins - permits the setting of basic criteria for passwords

  • Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.

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

CREATE ROLE

Syntax

CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role 
  [WITH ADMIN 
    {CURRENT_USER | CURRENT_ROLE | user | role}]

Description

The CREATE ROLE statement creates one or more MariaDB roles. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE ROLE creates a new row in themysql.user table that has no privileges, and with the corresponding is_role field set to Y. It also creates a record in themysql.roles_mapping table.

If any of the specified roles already exist, ERROR 1396 (HY000) results. If an error occurs, CREATE ROLE will still create the roles that do not result in an error. The maximum length for a role is 128 characters. Role names can be quoted, as explained in the Identifier names page. Only one error is produced for all roles which have not been created:

ERROR 1396 (HY000): Operation CREATE ROLE failed for 'a','b','c'

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

PUBLIC and NONE are reserved, and cannot be used as role names. NONE is used to unset a role and PUBLIC has a special use in other systems, such as Oracle, so is reserved for compatibility purposes.

For valid identifiers to use as role names, see Identifier Names.

WITH ADMIN

The optional WITH ADMIN clause determines whether the current user, the current role or another user or role has use of the newly created role. If the clause is omitted, WITH ADMIN CURRENT_USER is treated as the default, which means that the current user will be able to GRANT this role to users.

OR REPLACE

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

DROP ROLE IF EXISTS name;
CREATE ROLE name ...;

IF NOT EXISTS

When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified role already exists. Cannot be used together with the OR REPLACE clause.

Examples

CREATE ROLE journalist;

CREATE ROLE developer WITH ADMIN lorinda@localhost;

Granting the role to another user. Only user lorinda@localhost has permission to grant the developer role:

SELECT USER();
+-------------------+
| USER()            |
+-------------------+
| henning@localhost |
+-------------------+
...
GRANT developer TO ian@localhost;
Access denied for user 'henning'@'localhost'

 SELECT USER();
+-------------------+
| USER()            |
+-------------------+
| lorinda@localhost |
+-------------------+

GRANT m_role TO ian@localhost;

The OR REPLACE and IF NOT EXISTS clauses. The journalist role already exists:

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)
SHOW WARNINGS;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1975 | Can't create role 'journalist'; it already exists |
+-------+------+---------------------------------------------------+

See Also

  • Identifier Names

  • Roles Overview

  • DROP ROLE

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

CREATE USER

Syntax

CREATE [OR REPLACE] USER [IF NOT EXISTS] 
 user_specification [,user_specification ...] 
  [REQUIRE {NONE | tls_option [[AND] tls_option ...] }]
  [WITH resource_option [resource_option ...] ]
  [lock_option] [password_option] 

user_specification:
  username [authentication_option]

authentication_option:
  IDENTIFIED BY 'password' 
  | IDENTIFIED BY PASSWORD 'password_hash'
  | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule  ...]

authentication_rule:
    authentication_plugin
  | authentication_plugin {USING|AS} 'authentication_string'
  | authentication_plugin {USING|AS} PASSWORD('password')

tls_option:
  SSL 
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

resource_option:
  MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

password_option:
  PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY

lock_option:
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

Description

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

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

For each account, CREATE USER creates a new row in mysql.user 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:

ERROR 1396 (HY000): 
  Operation CREATE USER failed for 'u1'@'%','u2'@'%'

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

See Account Names below for details on how account names are specified.

One can also create users with GRANT if SQL_MODE does not have NO_AUTO_CREATE_USER 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:

DROP USER IF EXISTS name;
CREATE USER name ...;

For example:

CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'

CREATE OR REPLACE USER foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

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:

CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'

CREATE USER IF NOT EXISTS foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1973 | Can't create user 'foo2'@'test'; it already exists |
+-------+------+----------------------------------------------------+

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'

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 PASSWORD function prior to being stored in the mysql.user/mysql.global_priv_table table.

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

CREATE USER foo2@test IDENTIFIED BY 'mariadb';

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 authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

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 PASSWORD function. It will be stored in the mysql.user/mysql.global_priv_table table as-is.

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

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)

And then we can create a user with the hash:

CREATE USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

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 authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

IDENTIFIED {VIA|WITH} authentication_plugin

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

VIA and WITH are synonyms.

For example, this could be used with the PAM authentication plugin:

CREATE USER foo2@test IDENTIFIED VIA pam;

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

CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb';

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 PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:

CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');

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

CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;

By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password 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.

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 Secure Connections Overview 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 CREATE USER, ALTER USER, or GRANT statements. The following options are available:

Option
Description

REQUIRE NONE

TLS is not required for this account, but can still be used.

REQUIRE SSL

The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options.

REQUIRE X509

The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options.

REQUIRE ISSUER 'issuer'

The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order.

REQUIRE SUBJECT 'subject'

The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order.

REQUIRE CIPHER 'cipher'

The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order.

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:

CREATE USER 'alice'@'%'
 REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
 AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
 AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

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 Securing Connections for Client and Server 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

MAX_QUERIES_PER_HOUR

Number of statements that the account can issue per hour (including updates)

MAX_UPDATES_PER_HOUR

Number of updates (not queries) that the account can issue per hour

MAX_CONNECTIONS_PER_HOUR

Number of connections that the account can start per hour

MAX_USER_CONNECTIONS

Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections.

MAX_STATEMENT_TIME

Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute.

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:

CREATE USER 'someone'@'localhost' WITH
    MAX_USER_CONNECTIONS 10
    MAX_QUERIES_PER_HOUR 200;

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 FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mariadb-admin reload.

Per account resource limits are stored in the user table, in the mysql 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).

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 LIKE 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:

ip_addr & netmask = base_ip

For example, given a user:

CREATE USER 'maria'@'247.150.130.0/255.255.255.0';

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 MariaDB 10.6, the host name component could be up to 60 characters in length. Starting from MariaDB 10.6, 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 Identifier Names.

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:

+---------+-------------+
| User    | Host        |
+---------+-------------+
| joffrey | 192.168.0.3 |
|         | 192.168.0.% |
| joffrey | 192.168.%   |
|         | 192.168.%   |
+---------+-------------+

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:

CREATE USER 'joffrey'@'192.168.0.3';
CREATE USER 'joffrey'@'%';
GRANT SELECT ON test.t1 to 'joffrey'@'192.168.0.3';
GRANT SELECT ON test.t2 to 'joffrey'@'%';

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:

CREATE USER ''@'localhost';
CREATE USER ''@'192.168.0.3';

Fixing a Legacy Default Anonymous Account

On some systems, the mysql.db table has some entries for the ''@'%' anonymous account by default. Unfortunately, there is no matching entry in the mysql.user/mysql.global_priv_table table, which means that this anonymous account doesn't exactly exist, but it does have privileges--usually on the default test database created by mariadb-install-db. 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:

CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'

The fix is to DELETE the row in the mysql.db table and then execute FLUSH PRIVILEGES:

DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;

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:

CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)

See MDEV-13486 for more information.

Password Expiry

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

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

See User Password Expiry 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:

CREATE USER 'marijn'@'localhost' ACCOUNT LOCK;

See Account Locking for more details.

The lock_option and password_option clauses can occur in either order.

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

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

See Also

  • GRANT

  • ALTER USER

  • RENAME USER

  • DROP USER

  • CREATE ROLE

  • SET PASSWORD

  • SHOW CREATE USER

  • Troubleshooting Connection Issues

  • Authentication from MariaDB 10.4

  • Identifier Names

  • mysql.user table

  • mysql.global_priv_table

  • Password Validation Plugins - permits the setting of basic criteria for passwords

  • Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.

This page is licensed: GPLv2, originally from fill_help_tables.sql

DROP ROLE

Syntax

DROP ROLE [IF EXISTS] role_name [,role_name ...]

Description

The DROP ROLE statement removes one or more MariaDB roles. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database.

DROP ROLE does not disable roles for connections which selected them with SET ROLE. If a role has previously been set as a default role, DROP ROLE does not remove the record of the default role from the mysql.user table. If the role is subsequently recreated and granted, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this.

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

ERROR 1396 (HY000): Operation DROP ROLE failed for 'a','b','c'

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

IF EXISTS

If the IF EXISTS clause is used, MariaDB will return a warning instead of an error if the role does not exist.

Examples

DROP ROLE journalist;

The same thing using the optional IF EXISTS clause:

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

See Also

  • Roles Overview

  • CREATE ROLE

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

DROP USER

Syntax

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 CREATE USER privilege or the DELETE privilege for the mysql database. Each account is named using the same format as for the CREATE USERstatement; 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 CREATE USER.

From MariaDB 12.1, by default, if you specify an account that is currently connected, it will not be deleted until the connection is closed. The connection will not automatically be closed. The statement will complete, and a warning, "Dropped users 'user'@'host[,...]' have active connections. Use KILL CONNECTION if they should not be used anymore" issued.

In Oracle mode, if a user is connected, the DROP USER statement will fail with an error "Operation DROP USER failed for 'foo'@'localhost'".

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

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

ERROR 1396 (HY000): Operation DROP USER failed for 'u1'@'%','u2'@'%'

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

IF EXISTS

If the IF EXISTS clause is used, MariaDB will return 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 CREATE USER privilege or the INSERT privilege for the mysql database.

If any of the specified accounts, or any permissions f

Examples

DROP USER bob;

DROP USER foo2@localhost,foo2@'127.%';

IF EXISTS:

DROP USER bob;
ERROR 1396 (HY000): Operation DROP USER failed for 'bob'@'%'

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

SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1974 | Can't drop user 'bob'@'%'; it doesn't exist |
+-------+------+---------------------------------------------+

See Also

  • CREATE USER

  • ALTER USER

  • GRANT

  • SHOW CREATE USER

  • mysql.user table

This page is licensed: GPLv2, originally from fill_help_tables.sql

GRANT

Syntax

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [ user_options ...]

user_specification:
  username [authentication_option]
  | PUBLIC
authentication_option:
  IDENTIFIED BY 'password' 
  | IDENTIFIED BY PASSWORD 'password_hash'
  | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule  ...]

authentication_rule:
    authentication_plugin
  | authentication_plugin {USING|AS} 'authentication_string'
  | authentication_plugin {USING|AS} PASSWORD('password')

GRANT PROXY ON username
    TO user_specification [, user_specification ...]
    [WITH GRANT OPTION]

GRANT rolename TO grantee [, grantee ...]
    [WITH ADMIN OPTION]

grantee:
    rolename
    username [authentication_option]

user_options:
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH with_option [with_option] ...]

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

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

with_option:
    GRANT OPTION
  | resource_option

resource_option:
  MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

tls_option:
  SSL 
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

Description

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

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

Use the SHOW GRANTS 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 CREATE USER statements. See account names 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 SQL_MODE is set, then accounts can only be created if authentication information is specified, or with a CREATE USER statement. If no authentication information is provided, GRANT will produce an error when the specified account does not exist, for example:

SHOW VARIABLES LIKE '%sql_mode%' ;
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY '';
ERROR 1133 (28000): Can't find any matching row in the user table

GRANT USAGE ON *.* TO 'user123'@'%' 
  IDENTIFIED VIA PAM using 'mariadb' require ssl ;
Query OK, 0 rows affected (0.00 sec)
 
SELECT host, user FROM mysql.user WHERE user='user123' ;

+------+----------+
| host | user     |
+------+----------+
| %    | user123 |
+------+----------+

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.

  • Global privileges priv_type 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 mysql.global_priv table.

  • Database privileges priv_type are granted using db_name.* for priv_level, or using just * to use default database. 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 mysql.db table.

  • Table privileges priv_type are granted using db_name.tbl_namefor priv_level, or using just tbl_name to specify a table in the default database. The TABLE keyword is optional. Table privileges include the ability to select and change data in the table. Certain table privileges can be granted for individual columns.

  • Column privileges priv_type are granted by specifying a table for priv_level and providing a column list after the privilege type. They allow you to control exactly which columns in a table users can select and change.

  • Function privileges priv_type are granted using FUNCTION db_name.routine_name for priv_level, or using just FUNCTION routine_name to specify a function in the default database.

  • Procedure privileges priv_type are granted using PROCEDURE db_name.routine_name for priv_level, or using just PROCEDURE routine_name to specify a procedure in the default database.

The USAGE Privilege

The USAGE privilege grants no real privileges. The SHOW GRANTS 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 column privileges, 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 binary log, including the PURGE BINARY LOGS statement and setting the system variables:

  • binlog_annotate_row_events

  • binlog_cache_size

  • binlog_commit_wait_count

  • binlog_commit_wait_usec

  • binlog_direct_non_transactional_updates

  • binlog_expire_logs_seconds

  • binlog_file_cache_size

  • binlog_format

  • binlog_row_image

  • binlog_row_metadata

  • binlog_stmt_cache_size

  • expire_logs_days

  • log_bin_compress

  • log_bin_compress_min_len

  • log_bin_trust_function_creators

  • max_binlog_cache_size

  • max_binlog_size

  • max_binlog_stmt_cache_size

  • sql_log_bin and

  • sync_binlog.

BINLOG ADMIN isn't available.

BINLOG MONITOR

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

Use REPLICATION CLIENT instead. SHOW REPLICA STATUS isn't included in this privilege, and REPLICA MONITOR is required.

Permits running SHOW commands related to the binary log, in particular the SHOW BINLOG STATUS and SHOW BINARY LOGS statements.

BINLOG REPLAY

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

  • gtid_domain_id

  • gtid_seq_no

  • pseudo_thread_id

  • server_id.

BINLOG REPLAY isn't available.

CONNECTION ADMIN

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

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

  • connect_timeout

  • disconnect_on_expired_password

  • extra_max_connections

  • init_connect

  • max_connections

  • max_connect_errors

  • max_password_errors

  • proxy_protocol_networks

  • secure_auth

  • slow_launch_time

  • thread_pool_exact_stats

  • thread_pool_dedicated_listener

  • thread_pool_idle_timeout

  • thread_pool_max_threads

  • thread_pool_min_threads

  • thread_pool_oversubscribe

  • thread_pool_prio_kickup_timer

  • thread_pool_priority

  • thread_pool_size, and

  • thread_pool_stall_limit.

CREATE USER

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

FEDERATED ADMIN

Execute CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

FEDERATED ADMIN is not available.

FILE

Read and write files on the server, using statements like LOAD DATA INFILE or functions like LOAD_FILE(). Also needed to create CONNECT 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 SHOW PROCESSLIST or mariadb-admin processlist. 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 read_only system variable, and can perform write operations even when the read_only option is active.

The READ_ONLY ADMIN privilege has been removed from SUPER. 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 read_only system variable, and can perform write operations even when the read_only option is active.

The READ_ONLY ADMIN privilege is included in SUPER.

READ\_ONLY ADMIN isn't available.

RELOAD

Execute FLUSH statements or equivalent mariadb-admin commands.

REPLICATION CLIENT

Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Renamed to BINLOG MONITOR (but still supported as an alias for compatibility reasons).

Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. SHOW SLAVE STATUS is part of REPLICATION CLIENT.

Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Using BINLOG MONITOR instead is still supported as an alias.

Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Renamed to BINLOG MONITOR in MariaDB 10.5.2 (but still supported as an alias for compatibility reasons). SHOW SLAVE STATUS was part of REPLICATION CLIENT prior to MariaDB 10.5.

REPLICATION MASTER ADMIN

Permits administration of primary servers, including the SHOW REPLICA HOSTS statement, and setting the gtid_binlog_state, gtid_domain_id, master_verify_checksum and server_id system variables.

REPLICATION MASTER ADMIN is not available.

REPLICA MONITOR

Permit SHOW REPLICA STATUS and SHOW RELAYLOG EVENTS.

See Reasoning tab as to why this was implemented.

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

This issue is fixed in MariaDB 10.5.9 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 REPLICA MONITOR privilege. The privilege fix occurs when the server is started up, not when mariadb-upgrade is performed.

However, when a database is upgraded from an early 10.5 minor release to 10.5.9 and later, the user will have to fix any user account privileges manually.

REPLICA MONITOR is not available.

REPLICATION REPLICA

Synonym for REPLICATION SLAVE.

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. REPLICATION REPLICA 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 START REPLICA/SLAVE, STOP REPLICA/SLAVE, CHANGE MASTER, SHOW REPLICA/SLAVE STATUS, SHOW RELAYLOG EVENTS statements, replaying the binary log with the BINLOG statement (generated by mariadb-binlog), and setting the system variables:

  • gtid_cleanup_batch_size

  • gtid_ignore_duplicates

  • gtid_pos_auto_engines

  • gtid_slave_pos

  • gtid_strict_mode

  • init_slave

  • read_binlog_speed_limit

  • relay_log_purge

  • relay_log_recovery

  • replicate_do_db

  • replicate_do_table

  • replicate_events_marked_for_skip

  • replicate_ignore_db

  • replicate_ignore_table

  • replicate_wild_do_table

  • replicate_wild_ignore_table

  • slave_compressed_protocol

  • slave_ddl_exec_mode

  • slave_domain_parallel_threads

  • slave_exec_mode

  • slave_max_allowed_packet

  • slave_net_timeout

  • slave_parallel_max_queued

  • slave_parallel_mode

  • slave_parallel_threads

  • slave_parallel_workers

  • slave_run_triggers_for_rbr

  • slave_sql_verify_checksum

  • slave_transaction_retry_interval

  • slave_type_conversions

  • sync_master_info

  • sync_relay_log, and

  • sync_relay_log_info.

REPLICATION SLAVE ADMIN is not available.

SET USER

Enables setting the DEFINER when creating triggers, views, stored functions and stored procedures.

SET USER isn't available.

SHOW DATABASES

List all databases using the SHOW DATABASES 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 SHUTDOWN or the mariadb-admin shutdown command.

SUPER

Execute superuser statements: CHANGE MASTER TO, KILL (users who do not have this privilege can only KILL their own threads), PURGE LOGS, SET global system variables, or the mariadb-admin debug command. Also, this permission allows the user to write data even if the read_only 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 init-connect mariadbd 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 (MDEV-21743). The privileges are:

  • SET USER

  • FEDERATED ADMIN

  • CONNECTION ADMIN

  • REPLICATION SLAVE ADMIN

  • BINLOG ADMIN

  • BINLOG REPLAY

  • REPLICA MONITOR

  • BINLOG MONITOR

  • REPLICATION MASTER ADMIN

  • READ_ONLY ADMIN

These grants are no longer a part of SUPER and need to be granted separately.

The READ_ONLY ADMIN privilege has been removed from SUPER. 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 (MDEV-29596).

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

  • SET USER

  • FEDERATED ADMIN

  • CONNECTION ADMIN

  • REPLICATION SLAVE ADMIN

  • BINLOG ADMIN

  • BINLOG REPLAY

  • REPLICA MONITOR

  • BINLOG MONITOR

  • REPLICATION MASTER ADMIN

  • READ_ONLY ADMIN

These grants are part of SUPER and don't need to be granted separately.

Use the SUPER privilege.

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 default database.

Privilege
Description

CREATE

Create a database using the CREATE DATABASE statement, when the privilege is granted for a database. You can grant the CREATE privilege on databases that do not yet exist. This also grants the CREATE privilege on all tables in the database.

CREATE ROUTINE

Create Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements.

CREATE TEMPORARY TABLES

Create temporary tables with the CREATE TEMPORARY TABLE statement. This privilege enable writing and dropping those temporary tables

DROP

Drop a database using the DROP DATABASE statement, when the privilege is granted for a database. This also grants the DROP privilege on all tables in the database.

EVENT

Create, drop and alter EVENTs.

GRANT OPTION

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

LOCK TABLES

Acquire explicit locks using the LOCK TABLES statement; you also need to have the SELECT privilege on a table, in order to lock it.

SHOW CREATE ROUTINE

Permit viewing the SHOW CREATE definition statement of a routine, for example SHOW CREATE FUNCTION, even if not the routine owner. From MariaDB 11.3.0.

Table Privileges

Privilege
Description

ALTER

Change the structure of an existing table using the ALTER TABLE statement.

CREATE

Create a table using the CREATE TABLE statement. You can grant the CREATE privilege on tables that do not yet exist.

CREATE VIEW

Create a view using the CREATE_VIEW statement.

DELETE

Remove rows from a table using the DELETE statement.

DELETE HISTORY

Remove historical rows from a table using the DELETE HISTORY statement. Displays as DELETE VERSIONING ROWS when running SHOW PRIVILEGES until MariaDB 10.5.2 (MDEV-20382). If a user has the SUPER privilege but not this privilege, running mariadb-upgrade will grant this privilege as well.

DROP

Drop a table using the DROP TABLE statement or a view using the DROP VIEW statement. Also required to execute the TRUNCATE TABLE statement.

GRANT OPTION

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

INDEX

Create an index on a table using the CREATE INDEX statement. Without the INDEX privilege, you can still create indexes when creating a table using the CREATE TABLE statement if the you have the CREATE privilege, and you can create indexes using the ALTER TABLE statement if you have the ALTER privilege.

INSERT

Add rows to a table using the INSERT statement. The INSERT privilege can also be set on individual columns; see Column Privileges below for details.

REFERENCES

Unused.

SELECT

Read data from a table using the SELECT statement. The SELECT privilege can also be set on individual columns; see Column Privileges below for details.

SHOW VIEW

Show the CREATE VIEW statement to create a view using the SHOW CREATE VIEW statement.

TRIGGER

Required to run the CREATE TRIGGER, DROP TRIGGER, and SHOW CREATE TRIGGER statements. When another user activates a trigger (running INSERT, UPDATE, or DELETE statements on the associated table), for the trigger to execute, the user that defined the trigger should have the TRIGGER privilege for the table. The user running the INSERT, UPDATE, or DELETE statements on the table is not required to have the TRIGGER privilege.

UPDATE

Update existing rows in a table using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause. The UPDATE privilege can also be set on individual columns; see Column Privileges below for details.

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.

GRANT SELECT (name, position) ON Employee TO 'jeffrey'@'localhost';
Privilege
Description

INSERT (column_list)

Add rows specifying values in columns using the INSERT statement. If you only have column-level INSERT privileges, you must specify the columns you are setting in the INSERT statement. All other columns will be set to their default values, or NULL.

REFERENCES (column_list)

Unused.

SELECT (column_list)

Read values in columns using the SELECT statement. You cannot access or query any columns for which you do not have SELECT privileges, including in WHERE, ON, GROUP BY, and ORDER BY clauses.

UPDATE (column_list)

Update values in columns of existing rows using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause.

Function Privileges

Privilege
Description

ALTER ROUTINE

Change the characteristics of a stored function using the ALTER FUNCTION statement.

EXECUTE

Use a stored function. You need SELECT privileges for any tables or columns accessed by the function.

GRANT OPTION

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

Procedure Privileges

Privilege
Description

ALTER ROUTINE

Change the characteristics of a stored procedure using the ALTER PROCEDURE statement.

EXECUTE

Execute a stored procedure using the CALL statement. The privilege to call a procedure may allow you to perform actions you wouldn't otherwise be able to do, such as insert rows into a table.

GRANT OPTION

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

GRANT EXECUTE ON PROCEDURE mysql.create_db TO maintainer;

Proxy Privileges

Privilege
Description

PROXY

Permits one user to be a proxy for another.

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

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

The pam authentication plugin is the only plugin included with MariaDB that currently supports proxy users. The PROXY privilege is commonly used with the pam authentication plugin to enable user and group mapping with PAM.

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

CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;

CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'dba'@'%' TO ''@'%';

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:

SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+

SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+-----------------------------------------------------------------------------------------------------------------------+

GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
ERROR 1698 (28000): Access denied for user 'alice'@'localhost'

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:

SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+

SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost'                                                               |
+-----------------------------------------------------------------------------------------------------------------------+

GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
ERROR 1698 (28000): Access denied for user 'alice'@'localhost'

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:

SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+

SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
| GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost' WITH GRANT OPTION                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------+

GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';

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:

GRANT PROXY ON ''@'%' TO 'dba'@'localhost' WITH GRANT OPTION;

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

SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+

SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'alice'@'localhost' WITH GRANT OPTION                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------+

GRANT PROXY ON 'app1_dba'@'localhost' TO 'bob'@'localhost';
Query OK, 0 rows affected (0.004 sec)

GRANT PROXY ON 'app2_dba'@'localhost' TO 'carol'@'localhost';
Query OK, 0 rows affected (0.004 sec)

The default root user accounts created by mariadb-install-db have this privilege. For example:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;

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 CREATE USER 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 PASSWORD function prior to being stored.

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

GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 'mariadb';

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.

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 SET PASSWORD statement to change a user's password with GRANT.

The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

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 PASSWORD function. It will be stored as-is.

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

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)

And then we can create a user with the hash:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 
  PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

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.

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 SET PASSWORD tastatement to change a user's password with GRANT.

The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

IDENTIFIED {VIA|WITH} authentication_plugin

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

For example, this could be used with the PAM authentication plugin:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam;

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

GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam USING 'mariadb';

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 PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:

CREATE USER safe@'%' IDENTIFIED VIA ed25519 
  USING PASSWORD('secret');

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

CREATE USER safe@'%' IDENTIFIED VIA ed25519 
  USING PASSWORD('secret') OR unix_socket;

By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password 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

MAX_QUERIES_PER_HOUR

Number of statements that the account can issue per hour (including updates)

MAX_UPDATES_PER_HOUR

Number of updates (not queries) that the account can issue per hour

MAX_CONNECTIONS_PER_HOUR

Number of connections that the account can start per hour

MAX_USER_CONNECTIONS

Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections.

MAX_STATEMENT_TIME

Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute.

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:

GRANT USAGE ON *.* TO 'someone'@'localhost' WITH
    MAX_USER_CONNECTIONS 0
    MAX_QUERIES_PER_HOUR 200;

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 FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mariadb-admin reload.

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 is reached.

Per account resource limits are stored in the user table, in the mysql 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).

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 Secure Connections Overview 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 CREATE USER, ALTER USER, or GRANT statements. The following options are available:

Option
Description

REQUIRE NONE

TLS is not required for this account, but can still be used.

REQUIRE SSL

The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options.

REQUIRE X509

The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options.

REQUIRE ISSUER 'issuer'

The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order.

REQUIRE SUBJECT 'subject'

The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order.

REQUIRE CIPHER 'cipher'

The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order.

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:

GRANT USAGE ON *.* TO 'alice'@'%'
  REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
  AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
  AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

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 Securing Connections for Client and Server for information on how to enable TLS on the client and server.

Roles

Syntax

GRANT role TO grantee [, grantee ... ]
[ WITH ADMIN OPTION ]

grantee:
    rolename
    username [authentication_option]

The GRANT statement is also used to grant the use of a role 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 CREATE ROLE 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.

GRANT journalist TO hulda;

GRANT journalist TO berengar WITH ADMIN OPTION;

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 SET ROLE statement.

TO PUBLIC

blog post

TO PUBLIC is unavailable.

Syntax

GRANT <privilege> ON <DATABASE>.<object> TO PUBLIC;
REVOKE <privilege> ON <DATABASE>.<object> FROM PUBLIC;

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 one only wants to state once that all users need to have a certain set of privileges. When running SHOW GRANTS, a user will also see all privileges inherited from PUBLIC. SHOW GRANTS FOR PUBLIC will only show 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:

CREATE USER 'alexander'@'localhost';
GRANT ALL PRIVILEGES ON  *.* TO 'alexander'@'localhost' WITH GRANT OPTION;

See Also

  • Troubleshooting Connection Issues

  • Authentication from MariaDB 10.4

  • --skip-grant-tables allows you to start MariaDB without GRANT. This is useful if you lost your root password.

  • CREATE USER

  • ALTER USER

  • DROP USER

  • SET PASSWORD

  • SHOW CREATE USER

  • mysql.global_priv table

  • mysql.user table

  • Password Validation Plugins - permits the setting of basic criteria for passwords

  • Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.

This page is licensed: GPLv2, originally from fill_help_tables.sql

RENAME USER

Syntax

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

Description

The RENAME USER statement renames existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE 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.

If any of the old user accounts do not exist or any of the new user accounts already exist, ERROR 1396 (HY000) results. If an error occurs, RENAME USERwill still rename the accounts that do not result in an error.

For modifying an existing account, see ALTER USER.

Examples

CREATE USER 'donald', 'mickey';
RENAME USER 'donald' TO 'duck'@'localhost', 'mickey' TO 'mouse'@'localhost';

Renaming the host component of a user:

RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'10.20.30.40';

This page is licensed: GPLv2, originally from fill_help_tables.sql

REVOKE

Privileges

Syntax

REVOKE 
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

Description

The REVOKE statement enables system administrators to revoke privileges (or roles - see section below) from MariaDB accounts. Each account is named using the same format as for the GRANT 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 details on the levels at which privileges exist, the allowablepriv_type and priv_level values, and the syntax for specifying users and passwords, see GRANT.

To use the first REVOKE syntax, you must have theGRANT OPTION privilege, and you must have the privileges that you are revoking.

To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. See GRANT.

Examples

REVOKE SUPER ON *.* FROM 'alexander'@'localhost';

Roles

Syntax

REVOKE role  [, role ...]
    FROM grantee [, grantee2 ... ]

REVOKE ADMIN OPTION FOR role FROM grantee [, grantee2]

Description

REVOKE is also used to remove a role from a user or another role that it's previously been assigned to. If a role has previously been set as a default role, REVOKE does not remove the record of the default role from the mysql.user table. If the role is subsequently granted again, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this.

REVOKE role is also permitted in prepared statements.

REVOKE role is not permitted in prepared statements.

Example

REVOKE journalist FROM hulda

This page is licensed: GPLv2, originally from fill_help_tables.sql

SET DEFAULT ROLE

Syntax

SET DEFAULT ROLE { role | NONE } [ FOR user@host ]

Description

The SET DEFAULT ROLE statement sets a default role for a specified (or current) user. A default role is automatically enabled when a user connects (an implicit SET ROLE statement is executed immediately after a connection is established).

To be able to set a role as a default, the role must already have been granted to that user, and one needs the privileges to enable this role (if you cannot do SET ROLE X, you won't be able to do SET DEFAULT ROLE X). To set a default role for another user one needs to have write access to the mysql database.

To remove a user's default role, use SET DEFAULT ROLE NONE [ FOR user@host ]. The record of the default role is not removed if the role is dropped or revoked, so if the role is subsequently re-created or granted, it will again be the user's default role.

The default role is stored in the default_role column in the mysql.user table/view, as well as in the Information Schema APPLICABLE_ROLES table, so these can be viewed to see which role has been assigned to a user as the default.

Examples

Setting a default role for the current user:

SET DEFAULT ROLE journalist;

Removing a default role from the current user:

SET DEFAULT ROLE NONE;

Setting a default role for another user. The role has to have been granted to the user before it can be set as default:

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;

Viewing mysql.user:

SELECT * FROM mysql.user WHERE user='taniel'\G
*************************** 1. row ***************************
                  Host: %
                  User: taniel
...
               is_role: N
          default_role: journalist
...

Removing a default role for another user

SET DEFAULT ROLE NONE FOR taniel;

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

SET PASSWORD

Syntax

SET PASSWORD [FOR user] =
    {
        PASSWORD('some password')
      | OLD_PASSWORD('some password')
      | 'encrypted password'
    }

Description

The SET PASSWORD statement assigns a password to an existing MariaDB user account.

If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD().

OLD_PASSWORD() should only be used if your MariaDB/MySQL clients are very old (< 4.0.0).

With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.

With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table (or view in current versions) entry.

The argument to PASSWORD() and the password given to MariaDB clients can be of arbitrary length.

Authentication Plugin Support

SET PASSWORD (with or without PASSWORD()) works for accounts authenticated via any authentication plugin that supports passwords stored in the mysql.global_priv table.

The ed25519, mysql_native_password, and mysql_old_password authentication plugins store passwords in the mysql.global_priv table.

If you run SET PASSWORD on an account that authenticates with one of these authentication plugins that stores passwords in the mysql.global_priv table, then the PASSWORD() function is evaluated by the specific authentication plugin used by the account. The authentication plugin hashes the password with a method that is compatible with that specific authentication plugin.

The unix_socket, named_pipe, gssapi, and pam authentication plugins do not store passwords in the mysql.global_priv table. These authentication plugins rely on other methods to authenticate the user.

If you attempt to run SET PASSWORD on an account that authenticates with one of these authentication plugins that doesn't store a password in the mysql.global_priv table, then MariaDB Server will issue an error like the following:

SET PASSWORD is ignored for users authenticating via unix_socket plugin

See Authentication from MariaDB 10.4 for an overview of authentication changes in MariaDB.

Passwordless User Accounts

User accounts do not always require passwords to login.

The unix_socket , named_pipe and gssapi authentication plugins do not require a password to authenticate the user.

The pam authentication plugin may or may not require a password to authenticate the user, depending on the specific configuration.

The mysql_native_password and mysql_old_password authentication plugins require passwords for authentication, but the password can be blank. In that case, no password is required.

If you provide a password while attempting to log into the server as an account that doesn't require a password, then MariaDB server will simply ignore the password.

A user account can be defined to use multiple authentication plugins in a specific order of preference. This specific scenario may be more noticeable in these versions, since an account could be associated with some authentication plugins that require a password, and some that do not.

Example

For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

If you want to delete a password for a user, you would do:

SET PASSWORD FOR 'bob'@localhost = PASSWORD("");

See Also

  • Password Validation Plugins - permits the setting of basic criteria for passwords

  • ALTER USER

This page is licensed: GPLv2, originally from fill_help_tables.sql

SET SESSION AUTHORIZATION

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 stored procedure with an arbitrary definer.

  • In particular, this bypasses account lock, expired password, authentication, REQUIRE SSL checks, and so on.

  • Users are required to have the SET USER privilege.

  • Does not work inside transactions, the Performance Schema and stored procedures.

Examples

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

set session authorization foo@localhost; 

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

SET ROLE

Syntax

SET ROLE { role | NONE }

Description

The SET ROLE statement enables a role, along with all of its associated permissions, for the current session. To unset a role, use NONE .

If a role that doesn't exist, or to which the user has not been assigned, is specified, an ERROR 1959 (OP000): Invalid role specification error occurs.

An automatic SET ROLE is implicitly performed when a user connects if that user has been assigned a default role. See SET DEFAULT ROLE.

Example

SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL         |
+--------------+

SET ROLE staff;

SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| staff        |
+--------------+

SET ROLE NONE;

SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NULL           |
+----------------+

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

Administrative Statements

BINLOG

Syntax

BINLOG 'str'

Description

BINLOG is an internal-use statement. It is generated by themariadb-binlog program as the printable representation of certain events in binary log files. The 'str' value is a base 64-encoded string that the server decodes to determine the data change indicated by the corresponding event.

This statement requires the BINLOG REPLAY privilege.

This statement requires the SUPER privilege.

See Also

  • MariaDB replication

This page is licensed: GPLv2, originally from fill_help_tables.sql

CACHE INDEX

Syntax

CACHE INDEX                      
  tbl_index_list [, tbl_index_list] ...
  IN key_cache_name                    

tbl_index_list:
  tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

Description

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

A default key cache exists and cannot be destroyed. To create more key caches, the key_buffer_size 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 configuration file and include some CACHE INDEX (and optionally LOAD INDEX) 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:

CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

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)

  • Move the file to the new key cache (under file share lock)

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 fill_help_tables.sql

DESCRIBE

Syntax

{DESCRIBE | DESC} tbl_name [col_name | wild]

Description

DESCRIBE provides information about the columns in a table.It is a shortcut for SHOW COLUMNS FROM.These statements also display information for views.

col_name can be a column name, or a string containing theSQL "%" and "_" wildcard characters toobtain output only for the columns with names matching the string. There is noneed to enclose the string within quotes unless it contains spaces or otherspecial characters.

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    |                |
+------------+----------+------+-----+---------+----------------+

The description for SHOW COLUMNS providesmore information about the output columns.

See Also

  • SHOW COLUMNS

  • INFORMATION_SCHEMA.COLUMNS Table

  • mysqlshow

This page is licensed: GPLv2, originally from fill_help_tables.sql

HELP Command

Syntax

HELP search_string

Description

The HELP command can be used in any MariaDB client, such as the mariadb command-line client, to get basic syntax help and a short description for most commands and functions.

If you provide an argument to the HELP command, the mariadb client uses it as a search string to access server-side help. The proper operation of this command requires that the help tables in the mysql database be initialized with help topic information.

If there is no match for the search string, the search fails. Use HELP contents to see a list of the help categories:

HELP contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Sequences
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

If a search string matches multiple items, MariaDB shows a list of matching topics:

HELP drop
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP INDEX
   DROP PACKAGE
   DROP PACKAGE BODY
   DROP PROCEDURE
   DROP ROLE
   DROP SEQUENCE
   DROP SERVER
   DROP TABLE
   DROP TRIGGER
   DROP USER
   DROP VIEW

Then you can enter a topic as the search string to see the help entry for that topic.

The help is provided with the MariaDB server and makes use of four help tables found in the mysql database: help_relation, help_topic, help_category and help_keyword. These tables are populated by the mariadb-install-db or fill_help_table.sql scripts.

This page is licensed: GPLv2, originally from fill_help_tables.sql

KILL

Syntax

KILL [HARD | SOFT] { {CONNECTION|QUERY} thread_id | QUERY ID query_id | USER user_name }

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

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

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

If you have the SUPER privilege, the CONNECTION ADMIN 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 MyISAM and Aria tables (REPAIR TABLE, OPTIMIZE TABLE).

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

  • CURRENT_USER or CURRENT_USER()

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

ERROR 1094 (HY000): Unknown thread id: <thread_id>

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

ERROR 1957 (HY000): Unknown query id: <query_id>

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 ROW_COUNT() function.

A client whose connection is killed receives the following error:

ERROR 1317 (70100): Query execution was interrupted

To obtain a list of existing sessions, use the SHOW PROCESSLIST statement or query the Information Schema PROCESSLIST 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 mariadb-admin.

Percona Toolkit contains a program, pt-kill 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.

See Also

  • Query limits and timeouts

  • Aborting statements that exceed a certain time to execute

  • idle_transaction_timeout

This page is licensed: GPLv2, originally from fill_help_tables.sql

PURGE BINARY LOGS

Syntax

PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

Description

The PURGE BINARY LOGS statement deletes all the binary logfiles 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.

The datetime expression is in the format 'YYYY-MM-DD hh:mm:ss'.

If a replica is active but has yet to read from a binary log file you attempt to delete, the statement will fail with an error. However, if the replica is not connected and has yet to read from a log file you delete, the file will be deleted, but the replica will be unable to continue replicating once it connects again.

This statement has no effect if the server was not started with the--log-bin option to enable binary logging.

To list the binary log files on the server, use SHOW BINARY LOGS. To see which files they are reading, use SHOW REPLICA STATUS. You can only delete the files that are older than the oldest file that is used by the slaves.

To list the binary log files on the server, use SHOW BINARY LOGS. To see which files they are reading, use SHOW SLAVE STATUS. You can only delete the files that are older than the oldest file that is used by the slaves.

To delete all binary log files, use RESET MASTER.To move to a new log file (for example if you want to remove the current log file), use FLUSH LOGS before you execute PURGE LOGS.

If the expire_logs_days server system variable is not set to 0, the server automatically deletes binary log files after the given number of days. From MariaDB 10.6, the binlog_expire_logs_seconds variable allows more precise control over binlog deletion, and takes precedence if both are non-zero.

Requires the BINLOG ADMIN privilege.

Requires the SUPER privilege.

Examples

PURGE BINARY LOGS TO 'mariadb-bin.000063';
PURGE BINARY LOGS BEFORE '2013-04-21';
PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';

See Also

  • Using and Maintaining the Binary Log

  • FLUSH LOGS.

This page is licensed: GPLv2, originally from fill_help_tables.sql

RESET

Syntax

RESET reset_option [, reset_option] ...

Description

The RESET statement is used to clear the state of various server operations. You must have the RELOAD privilege to executeRESET.

RESET acts as a stronger version of the FLUSH statement.

The different RESET options are:

Option
Description

SLAVE ["connection_name"] [ALL]

Deletes all relay logs from the slave and reset the replication position in the master binary log.

MASTER

Deletes all old binary logs, makes the binary index file (--log-bin-index) empty and creates a new binary log file. This is useful when you want to reset the master to an initial state. If you want to just delete old, not used binary logs, you should use the PURGE BINARY LOGS command.

QUERY CACHE

Removes all queries from the query cache. See also FLUSH QUERY CACHE.

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHUTDOWN

Syntax

SHUTDOWN [WAIT FOR ALL { SLAVES | REPLICAS } ]

Description

The SHUTDOWN command shuts the server down.

WAIT FOR ALL REPLICAS / SLAVES

The WAIT FOR ALL REPLICAS statement can be used throughout.

The WAIT FOR ALL SLAVES option was first added in MariaDB 10.4.4.

When a primary server is shutdown and it goes through the normal shutdown process, the primary kills client threads in random order. By default, the primary also considers its binary log dump threads to be regular client threads. As a consequence, the binary log dump threads can be killed while client threads still exist, and this means that data can be written on the primary during a normal shutdown that won't be replicated. This is true even if semi-synchronous replication is being used.

This problem can be solved by shutting down the server with the SHUTDOWN command and by providing the WAIT FOR ALL REPLICAS/WAIT FOR ALL SLAVES option to the command. For example:

SHUTDOWN WAIT FOR ALL REPLICAS;

When the WAIT FOR ALL REPLICAS option is provided, the server only kills its binary log dump threads after all client threads have been killed, and it only completes the shutdown after the last binary log has been sent to all connected replicas.

See Replication Threads: Binary Log Dump Threads and the Shutdown Process for more information.

Required Permissions

One must have a SHUTDOWN privilege (see GRANT) to use this command. It is the same privilege one needs to use the mariadb-admin shutdown command.

Shutdown for Upgrades

If you are doing a shutdown to migrate to another major version of MariaDB, please ensure that the innodb_fast_shutdown variable is not 2 (fast crash shutdown). The default of this variable is 1.

Example

The following example shows how to create an event which turns off the server at a certain time:

CREATE EVENT `test`.`shutd`
    ON SCHEDULE
        EVERY 1 DAY
        STARTS '2014-01-01 20:00:00'
    COMMENT 'Shutdown Maria when the office is closed'
DO BEGIN
    SHUTDOWN;
END;

Other Ways to Stop mariadbd

You can use the mariadb-admin shutdown command to take down mariadbd cleanly.

You can also use the system kill command on Unix with signal SIGTERM (15)

kill -SIGTERM pid-of-mariadbd-process

You can find the process number of the server process in the file that ends with .pid in your data directory.

The above is identical to mariadb-admin shutdown.

On windows you should use:

NET STOP MariaDB

See Also

  • mariadb-admin shutdown.

  • InnoDB fast shutdown option

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

USE [DATABASE]

Syntax

USE db_name

From MariaDB 11.7, one can also use

USE DATABASE db_name;

Description

The 'USE db_name' statement tells MariaDB to use thedb_name database as the default (current) database forsubsequent statements. The database remains the default until the end of thesession or another USE statement is issued:

USE db1;
SELECT COUNT(*) FROM mytable;   # selects FROM db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects FROM db2.mytable

The DATABASE() function (SCHEMA() is a synonym) returns the default database.

Another way to set the default database is specifying its name at mariadb command line client startup.

One cannot use USE DATABASE to a database one has no privileges to. The reason is thata user with no privileges to a database should not be able to know if a database exists or not.

See Also

  • Identifier Qualifiers

  • USE CATALOG

This page is licensed: GPLv2, originally from fill_help_tables.sql

ANALYZE and EXPLAIN Statements

Learn about ANALYZE and EXPLAIN statements in MariaDB Server. This section details how to optimize queries by analyzing table distribution statistics and understanding query execution plans.

ANALYZE FORMAT=JSON

ANALYZE FORMAT=JSON is a mix of the EXPLAIN FORMAT=JSON and ANALYZE statement features. The ANALYZE FORMAT=JSON $statement will execute $statement, and then print the output of EXPLAIN FORMAT=JSON, amended with data from the query execution.

Basic Execution Data

You can get the following also from tabular ANALYZE statement form:

  • r_rows is provided for any node that reads rows. It shows how many rows were read, on average.

  • r_filtered is provided whenever there is a condition that is checked. It shows the percentage of rows left after checking the condition.

Advanced Execution Data

The most important data not available in the regular tabular ANALYZE statement are:

  • r_loops field. This shows how many times the node was executed. Most query plan elements have this field.

  • r_total_time_ms field. It shows how much time in total, in milliseconds, was spent executing this node. If the node has subnodes, their execution time is included.

    • For UPDATE and DELETE statements, top-level query_block.r_total_time_ms does include the time to make row deletions/updates but does NOT include the time to commit the changes.

  • r_buffer_size field. Query plan nodes that make use of buffers report the size of buffer that was used.

InnoDB engine statistics

Starting from MariaDB 10.6.15, MariaDB 10.8.8, MariaDB 10.9.8, MariaDB 10.10.6, MariaDB 10.11.5, MariaDB 11.0.3, MariaDB 11.1.2 and MariaDB 11.2.1 (MDEV-31577), the following statistics are reported for InnoDB tables:

"r_engine_stats": {
        "pages_accessed":  integer,
        "pages_updated": integer,
        "pages_read_count": integer,
        "pages_prefetch_read_count": integer,
        "pages_read_time_ms": double,
        "old_rows_read": integer
      }

Only non-zero members are printed.

  • pages_accessed is the total number of buffer pool pages accessed when reading this table.

  • pages_updated is the total number of buffer pool pages that were modified during the execution of the statement.

  • pages_read_count is the number of pages that InnoDB had to read from disk for this table. If the query touches "hot" data in the InnoDB buffer pool, this value will be 0 and not present.

  • pages_prefetch_read_count Number of pages for which read-ahead was initiated. Not all such pages will necessarily be accessed.

  • pages_read_time_ms is the total time spent reading the table.

  • old_rows_read is the number of old row versions that InnoDB had to read. Old row version is the version of the row that is not visible to this transaction.

SHOW ANALYZE FORMAT=JSON

MariaDB starting with 10.9

SHOW ANALYZE FORMAT=JSON for <connection_id> extends ANALYZE [FORMAT=JSON] <select> to allow one to analyze a query currently running in another connection.

Data About Individual Query Plan Nodes

  • filesort node reports whether sorting was done with LIMIT n parameter, and how many rows were in the sort result.

  • block-nl-join node has r_loops field, which allows to tell whether Using join buffer was efficient.

  • range-checked-for-each-record reports counters that show the result of the check.

  • expression-cache is used for subqueries, and it reports how many times the cache was used, and what cache hit ratio was.

  • union_result node has r_rows so one can see how many rows were produced after UNION operation and so forth.

Use Cases

See Examples of ANALYZE FORMAT=JSON.

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

ANALYZE FORMAT=JSON Examples

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.

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
          }
        }
      }
    ]
  }
}

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

ANALYZE: Interpreting rows and filtered members

This article describes how to interpret r_rows and r_filtered members in ANALYZE FORMAT=JSON when an index-based access method is used.

Index-based access method

Index-based access method may employ some or all of the following:

  • Index Condition Pushdown

  • Rowid Filtering

  • attached_condition checking

Consider a table access which does all three:

"table": {
    "table_name": "t1",
    "access_type": "range",
    "possible_keys": ...,
    "key": "INDEX1",
    ...
    "rowid_filter": {
      ...
      "r_selectivity_pct": n.nnn,
    },
    ...
    "rows": 123,
    "r_rows": 125,
    ...
    "filtered": 8.476269722,
    "r_filtered": 100,
    "index_condition": "cond1",
    "attached_condition": "cond2"
  }

The access is performed as follows:

Access diagram

index-read-diagram-3

Statistics values in MariaDB before 11.5

In MariaDB versions before 11.5, the counters were counted as follows:

index-read-stats-old

that is,

  • r_rows is counted after Index Condition Pushdown check and Rowid Filter check.

  • r_filtered only counts selectivity of the attached_condition.

  • selectivity of the Rowid Filter is in rowid_filter.r_selectivity_pct.

Statistics values in MariaDB 11.5 and later versions

Starting from MariaDB 11.5 (MDEV-18478), the row counters are:

  • r_index_rows counts the number of enumerated index tuples, before any checks are made

  • r_rows is the same as before - number of rows after index checks.

The selectivity counters are:

  • r_icp_filtered is the percentage of records left after pushed index condition check.

  • rowid_filter.r_selectivity_pct shows selectivity of Rowid Filter, as before.

  • r_filtered is the selectivity of attached_condition check, as before.

  • r_total_filtered is the combined selectivity of all checks.

index-read-stats-new

ANALYZE output members

in ANALYZE FORMAT=JSON output these members are placed as follows:

"table": {
    "table_name": ...,

    "rows": 426,
    "r_index_rows": 349,
    "r_rows": 34,

Whenever applicable, r_index_rows is shown. It is comparable with rows - both are numbers of rows to enumerate before any filtering is done. If r_index_rows is not shown, r_rows shows the number of records enumerated.

Then, filtering members:

...
    "filtered": 8.476269722,
    "r_total_filtered": 9.742120344,

filtered is comparable with r_total_filtered: both show total amount of filtering.

...
    "index_condition": "lineitem.l_quantity > 47",
    "r_icp_filtered": 100,

ICP and its observed filtering. The optimizer doesn't compute an estimate for this currently.

...
    "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
    "r_filtered": 100

attached_condition and its observed filtering.

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

ANALYZE Statement

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 theANALYZE FORMAT=JSON command provides a more detailed view of the query plan and the query execution.

The syntax is

ANALYZE explainable_statement;

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

Command Output

Consider an example:

ANALYZE SELECT * FROM tbl1 
WHERE key1 
  BETWEEN 10 AND 200 AND 
  col1 LIKE 'foo%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: key1
          key: key1
      key_len: 5
          ref: NULL
         rows: 181
       r_rows: 181
     filtered: 100.00
   r_filtered: 10.50
        Extra: Using index condition; Using where

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.

ANALYZE SELECT *
FROM orders, customer 
WHERE
  customer.c_custkey=orders.o_custkey AND
  customer.c_acctbal < 0 AND
  orders.o_totalprice > 200*1000
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       9.13 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |     10 |   100.00 |      30.03 | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

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

  • orders.filtered=100, orders.r_filtered=30.03. The optimizer didn't have any way to estimate which fraction of records will be left after it checks the condition that is attached to table orders (it's orders.o_totalprice > 200*1000). So, it used 100%. In reality, it is 30%. 30% is typically not selective enough to warrant adding new indexes. For joins with many tables, it might be worth to collect and use column statistics for columns in question, this may help the optimizer to pick a better query plan.

Meaning of NULL in r_rows and r_filtered

Let's modify the previous example slightly

ANALYZE SELECT * 
FROM orders, customer 
WHERE
  customer.c_custkey=orders.o_custkey AND
  customer.c_acctbal < -0 AND 
  customer.c_comment LIKE '%foo%' AND
  orders.o_totalprice > 200*1000;
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       0.00 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |   NULL |   100.00 |       NULL | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

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

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 EXPLAIN in the slow query log feature allows MariaDB to have ANALYZE output of slow queries printed into the slow query log (see MDEV-6388).

See Also

  • ANALYZE FORMAT=JSON

  • SHOW ANALYZE

  • ANALYZE TABLE

  • JIRA task for ANALYZE statement, MDEV-406

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

EXPLAIN ANALYZE

Outdated syntax

The syntax for the EXPLAIN ANALYZE feature was changed to ANALYZE statement, available since MariaDB 10.1.0. See ANALYZE statement.

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

EXPLAIN FORMAT=JSON

Synopsis

EXPLAIN FORMAT=JSON is a variant of EXPLAIN command that produces output in JSON form. The output always has one row which has only one column titled "JSON". The contents are a JSON representation of the query plan:

EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE col1=1\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 1000,
      "filtered": 100,
      "attached_condition": "(t1.col1 = 1)"
    }
  }
}

Output is different from MySQL

The output of MariaDB's EXPLAIN FORMAT=JSON is different from EXPLAIN FORMAT=JSON in MySQL. The reasons for that are:

  • MySQL's output has deficiencies.

  • The output of MySQL's EXPLAIN FORMAT=JSON is not defined. Even MySQL Workbench has trouble parsing it (see this blog post).

  • MariaDB has query optimizations that MySQL does not have. This means that MariaDB generates query plans that MySQL does not generate.

Output Format

TODO: MariaDB's output format description.

See Also

  • ANALYZE FORMAT=JSON produces output like EXPLAIN FORMAT=JSON, but amended with the data from query execution.

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

EXPLAIN

Syntax

EXPLAIN tbl_name [col_name | wild]

or

EXPLAIN [EXTENDED | PARTITIONS | FORMAT=JSON] 
  {SELECT select_options | UPDATE update_options | DELETE delete_options}

or

EXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>

Description

The EXPLAIN statement can be used either as a synonym forDESCRIBE 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, UPDATE or DELETE, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.

  • EXPLAIN PARTITIONS is useful only when examining queries involving partitioned tables. For details, see Partition pruning and selection.

  • ANALYZE statement performs the query as well as producing EXPLAIN output, and provides actual as well as estimated statistics.

  • EXPLAIN output can be printed in the slow query log. See EXPLAIN in the Slow Query Log for details.

  • EXPLAIN FOR CONNECTION is an alias for SHOW EXPLAIN FOR.

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

The ANALYZE statement 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 EXPLAIN Analyzer 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

id

Sequence number that shows in which order tables are joined.

select_type

What kind of SELECT the table comes from.

table

Alias name of table. Materialized temporary tables for sub queries are named <subquery#>

type

How rows are found from the table (join type).

possible_keys

keys in table that could be used to find rows in the table

key

The name of the key that is used to retrieve rows. NULL is no key was used.

key_len

How many bytes of the key that was used (shows if we are using only parts of the multi-column key).

ref

The reference that is used as the key value.

rows

An estimate of how many rows we will find in the table for each key lookup.

Extra

Extra information about this join.

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

DEPENDENT SUBQUERY

The SUBQUERY is DEPENDENT.

DEPENDENT UNION

The UNION is DEPENDENT.

DERIVED

The SELECT is DERIVED from the PRIMARY.

MATERIALIZED

The SUBQUERY is MATERIALIZED.

Materialized tables will be populated at first access and will be accessed by the primary key (= one key lookup). Number of rows in EXPLAIN shows the cost of populating the table

PRIMARY

The SELECT is in the outermost query, but there is also a SUBQUERY within it.

SIMPLE

It is a simple SELECT query without any SUBQUERY or UNION.

SUBQUERY

The SELECT is a SUBQUERY of the PRIMARY.

UNCACHEABLE SUBQUERY

The SUBQUERY is UNCACHEABLE.

UNCACHEABLE UNION

The UNION is UNCACHEABLE.

UNION

The SELECT is a UNION of the PRIMARY.

UNION RESULT

The result of the UNION.

LATERAL DERIVED

The SELECT uses a Lateral Derived optimization

"Type" Column

This column contains information on how the table is accessed.

Value
Description

ALL

A full table scan is done for the table (all rows are read). This is bad if the table is large and the table is joined against a previous table! This happens when the optimizer could not find any usable index to access rows.

const

There is only one possibly matching row in the table. The row is read before the optimization phase and all columns in the table are treated as constants.

eq_ref

A unique index is used to find the rows. This is the best possible plan to find the row.

filter

A second index is being used with the Rowid Filtering Optimization.

fulltext

A fulltext index is used to access the rows.

index_merge

A 'range' access is done for several index and the found rows are merged. The key column shows which keys are used.

index_subquery

This is similar as ref, but used for sub queries that are transformed to key lookups.

index

A full scan over the used index. Better than ALL but still bad if index is large and the table is joined against a previous table.

range

The table will be accessed with a key over one or more value ranges.

ref_or_null

Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. This happens usually with sub queries.

ref

A non unique index or prefix of an unique index is used to find the rows. Good if the prefix doesn't match many rows.

system

The table has 0 or 1 rows.

unique_subquery

This is similar as eq_ref, but used for sub queries that are transformed to key lookups

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

  • Remove the used key columns from the WHERE (as this will be tested as part of the key lookup).

  • Remove impossible constant sub expressions. For example WHERE '(a=1 and a=2) OR b=1' becomes 'b=1'.

  • Replace columns with other columns that has identical values: Example: WHERE a=b and a=c may be treated as 'WHERE a=b and a=c and b=c'.

  • Add extra conditions to detect impossible row conditions earlier. This happens mainly with OUTER JOIN where we in some cases add detection of NULL values in the WHERE (Part of 'Not exists' optimization). This can cause an unexpected 'Using where' in the Extra column.

  • For each table level we remove expressions that have already been tested when we read the previous row. Example: When joining tables t1 with t2 using the following WHERE 't1.a=1 and t1.a=t2.b', we don't have to test 't1.a=1' when checking rows in t2 as we already know that this expression is true.

Value
Description

const row not found

The table was a system table (a table with should exactly one row), but no row was found.

Distinct

If distinct optimization (remove duplicates) was used. This is marked only for the last table in the SELECT.

Full scan on NULL key

The table is a part of the sub query and if the value that is used to match the sub query will be NULL, we will do a full table scan.

Impossible HAVING

The used HAVING clause is always false so the SELECT will return no rows.

Impossible WHERE noticed after reading const tables.

The used WHERE clause is always false so the SELECT will return no rows. This case was detected after we had read all 'const' tables and used the column values as constant in the WHERE clause. For example: WHERE const_column=5 and const_column had a value of 4.

Impossible WHERE

The used WHERE clause is always false so the SELECT will return no rows. For example: WHERE 1=2

No matching min/max row

During early optimization of MIN()/MAX() values it was detected that no row could match the WHERE clause. The MIN()/MAX() function will return NULL.

no matching row in const table

The table was a const table (a table with only one possible matching row), but no row was found.

No tables used

The SELECT was a sub query that did not use any tables. For example a there was no FROM clause or a FROM DUAL clause.

Not exists

Stop searching after more row if we find one single matching row. This optimization is used with LEFT JOIN where one is explicitly searching for rows that doesn't exists in the LEFT JOIN TABLE. Example: SELECT * FROM t1 LEFT JOIN t2 on (...) WHERE t2.not_null_column IS NULL. As t2.not_null_column can only be NULL if there was no matching row for on condition, we can stop searching if we find a single matching row.

Open_frm_only

For information_schema tables. Only the frm (table definition file was opened) was opened for each matching row.

Open_full_table

For information_schema tables. A full table open for each matching row is done to retrieve the requested information. (Slow)

Open_trigger_only

For information_schema tables. Only the trigger file definition was opened for each matching row.

Range checked for each record (index map: ...)

This only happens when there was no good default index to use but there may some index that could be used when we can treat all columns from previous table as constants. For each row combination the optimizer will decide which index to use (if any) to fetch a row from this table. This is not fast, but faster than a full table scan that is the only other choice. The index map is a bitmask that shows which index are considered for each row condition.

Scanned 0/1/all databases

For information_schema tables. Shows how many times we had to do a directory scan.

Select tables optimized away

All tables in the join was optimized away. This happens when we are only using COUNT(*), MIN() and MAX() functions in the SELECT and we where able to replace all of these with constants.

Skip_open_table

For information_schema tables. The queried table didn't need to be opened.

unique row not found

The table was detected to be a const table (a table with only one possible matching row) during the early optimization phase, but no row was found.

Using filesort

Filesort is needed to resolve the query. This means an extra phase where we first collect all columns to sort, sort them with a disk based merge sort and then use the sorted set to retrieve the rows in sorted order. If the column set is small, we store all the columns in the sort file to not have to go to the database to retrieve them again.

Using index

Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.

Using index condition

Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the index level.

Using index condition(BKA)

Like 'Using index condition' but in addition we use batch key access to retrieve rows.

Using index for group-by

The index is being used to resolve a GROUP BY or DISTINCT query. The rows are not read. This is very efficient if the table has a lot of identical index entries as duplicates are quickly jumped over.

Using intersect(...)

For index_merge joins. Shows which index are part of the intersect.

Using join buffer

We store previous row combinations in a row buffer to be able to match each row against all of the rows combinations in the join buffer at one go.

Using sort_union(...)

For index_merge joins. Shows which index are part of the union.

Using temporary

A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY.

Using where

A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. If you don't have 'Using where' together with a join type of ALL, you are probably doing something wrong!

Using where with pushed condition

Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the row level.

Using buffer

The UPDATE statement will first buffer the rows, and then run the updates, rather than do updates on the fly. See Using Buffer UPDATE Algorithm for a detailed explanation.

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 SHOW WARNINGS 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:

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    |                |
+------------+----------+------+-----+---------+----------------+

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

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      |         |               |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SELECT on a primary key:

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 |       |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+

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:

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 |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

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.

SHOW EXPLAIN example:

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)

Example of ref_or_null Optimization

SELECT * FROM table_name
  WHERE key_column=expr OR key_column IS NULL;

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.

See Also

  • SHOW EXPLAIN

  • Ignored Indexes

This page is licensed: GPLv2, originally from fill_help_tables.sql

Using Buffer UPDATE Algorithm

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:

  1. Scan everyone with "salary<2000", remembering the rowids of the rows in a buffer.

  2. Read the buffer and apply the updates.

This way, each row will be updated only once.

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

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

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.

BACKUP LOCK

BACKUP LOCK blocks a table from DDL statements. This is mainly intended to be used by tools like mariadb-backup that need to ensure there are no DDLs on a table while the table files are opened. For example, for an Aria table that stores data in 3 files with extensions .frm, .MAI and .MAD. Normal read/write operations can continue as normal.

Syntax

To lock a table:

BACKUP LOCK table_name

To unlock a table:

BACKUP UNLOCK

Usage in a Backup Tool

BACKUP LOCK [database.]table_name;
 - Open all files related to a table (for example, t.frm, t.MAI and t.MYD)
BACKUP UNLOCK;
- Copy data
- Close files

This ensures that all files are from the same generation, that is created at the same time by the MariaDB server. This works, because the open files will point to the original table files which will not be affected if there is any ALTER TABLE while copying the files.

Privileges

BACKUP LOCK requires the database LOCK TABLES privileges.

BACKUP LOCK requires the RELOAD privilege.

BACKUP LOCK requires the RELOAD privilege.

Notes

  • The idea is that the BACKUP LOCK should be held for as short a time as possible by the backup tool. The time to take an uncontested lock is very short! One can easily do 50,000 locks/unlocks per second on low end hardware.

  • One should use different connections for BACKUP STAGE commands and BACKUP LOCK.

Implementation

  • Internally, BACKUP LOCK is implemented by taking an MDLSHARED_HIGH_PRIO MDL lock on the table object, which protects the table from any DDL operations.

See Also

  • BACKUP STAGE

  • MDEV-17309 - BACKUP LOCK: DDL locking of tables during backup

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

BACKUP STAGE

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

Syntax

BACKUP STAGE [START | FLUSH | BLOCK_DDL | BLOCK_COMMIT | END ]

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 mariadb-backup 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.

  • Should work efficiently with backup tools that use disk snapshots.

  • Should work as efficiently as possible for all table types that store data on the local disks.

  • As little copying as possible under higher level stages/locks. For example, .frm (dictionary) and .trn (trigger) files should be copying while copying the table data.

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 CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and RENAME TABLE.

  • Blocks also start off a new ALTER TABLE and the final rename phase of ALTER TABLE. 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 mariadb-backup 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 MariaDB Enterprise Server.

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool. How mariadb-backup 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 MariaDB Enterprise Server.

See mariadb-backup and BACKUP STAGE Commands for some examples on how mariadb-backup 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 Storage Snapshots and BACKUP STAGE Commands for some examples on how to use each BACKUP STAGE command in an efficient way.

Privileges

BACKUP STAGE requires the RELOAD 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.

  • One can use the max_statement_time or lock_wait_timeout system variables to ensure that a BACKUP STAGE command doesn't block the server too long.

  • DDL logging is only be available from MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5 and MariaDB 11.2.4, or in MariaDB Enterprise Server.

  • A disconnect will automatically release backup stages.

  • There is no easy way to see which is the current stage.

See Also

  • BACKUP LOCK Locking a table from DDL.

  • MDEV-5336. Implement BACKUP STAGE for safe external backups.

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

Storage Snapshots and BACKUP STAGE Statements

The BACKUP STAGE statements are a set of statements 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.

Generic Backup Process with Storage Snapshots

A tool that backs up MariaDB by taking a snapshot of a file system, SAN, or some other kind of storage device could use each BACKUP STAGE command in the following way:

  • First, execute the following:

BACKUP STAGE START
BACKUP STAGE BLOCK_COMMIT
  • Then, take the snapshot.

  • Then, execute the following:

BACKUP STAGE END

The above ensures that all non-transactional tables are properly flushed to disk before the snapshot is done. Using BACKUP STAGE commands is also more efficient than using the FLUSH TABLES WITH READ LOCK command as the above set of commands will not block or be blocked by write operations to transactional tables.

Note that when the backup is completed, one should delete all files with the "#sql" prefix, as these are files used by concurrent running ALTER TABLE. Note that InnoDB will on server restart automatically delete any tables with the "#sql" prefix.

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

FLUSH Statements

Learn about FLUSH statements in MariaDB Server. This section details how to clear various internal caches and logs, essential for administrative tasks and ensuring consistent database state.

FLUSH QUERY CACHE

Description

You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.

The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

This page is licensed: GPLv2, originally from fill_help_tables.sql

FLUSH TABLES FOR EXPORT

Syntax

FLUSH TABLE[S] table_name [, table_name] FOR EXPORT

Description

FLUSH TABLES ... FOR EXPORT flushes changes to the specified tables to disk so that binary copies can be made while the server is still running. This works for Archive, Aria, CSV, InnoDB, MyISAM and MERGE tables.

The table is read locked until you issue UNLOCK TABLES.

If a storage engine does not support FLUSH TABLES FOR EXPORT, a 1031 error (SQLSTATE 'HY000') is produced.

If FLUSH TABLES ... FOR EXPORT is in effect in the session, the following statements will produce an error if attempted:

  • FLUSH TABLES WITH READ LOCK

  • FLUSH TABLES ... WITH READ LOCK

  • FLUSH TABLES ... FOR EXPORT

  • Any statement trying to update any table

If any of the following statements is in effect in the session, attempting FLUSH TABLES ... FOR EXPORT will produce an error.

  • FLUSH TABLES ... WITH READ LOCK

  • FLUSH TABLES ... FOR EXPORT

  • LOCK TABLES ... READ

  • LOCK TABLES ... WRITE

FLUSH FOR EXPORT is not written to the binary log.

This statement requires the RELOAD and the LOCK TABLES privileges.

If one of the specified tables cannot be locked, none of the tables will be locked.

If a table does not exist, an error like the following will be produced:

ERROR 1146 (42S02): Table 'test.xxx' doesn't exist

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

ERROR 1347 (HY000): 'test.v' is not BASE TABLE

Example

FLUSH TABLES test.t1 FOR EXPORT;
#  Copy files related to the table (see below)
UNLOCK TABLES;

For a full description, please see copying MariaDB tables.

See Also

  • FLUSH TABLES

  • Copying Tables Between Different MariaDB Databases and MariaDB Servers

  • Copying Transportable InnoDB Tablespaces

  • myisampack - Compressing the MyISAM data file for easier distribution.

  • aria_pack - Compressing the Aria data file for easier distribution

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

FLUSH

Syntax

FLUSH [NO_WRITE_TO_BINLOG | LOCAL]
    flush_option [, flush_option] ...

or when flushing tables:

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] TABLES [table_list]  [table_flush_option]

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

The RESET statement is similar to FLUSH. See RESET.

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

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

ERROR 1347 (HY000): 'test.v' is not BASE TABLE

By default, FLUSH statements are written to the binary log and will be replicated. 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

CHANGED_PAGE_BITMAPS

XtraDB only. Internal command used for backup purposes. See the Information Schema CHANGED_PAGE_BITMAPS Table.

CLIENT_STATISTICS

Reset client statistics (see SHOW CLIENT_STATISTICS).

DES_KEY_FILE

Reloads the DES key file (Specified with the --des-key-file startup option).

HOSTS

Flush the hostname cache (used for converting ip to host names and for unblocking blocked hosts. See max_connect_errors and performance_schema.host_cache

INDEX_STATISTICS

Reset index statistics (see SHOW INDEX_STATISTICS).

[ERROR

ENGINE

BINARY LOGS

FLUSH BINARY LOGS rotates the current binary log.

BINARY LOGS DELETE_DOMAIN_ID=(list-of-domains)

FLUSH BINARY LOGS DELETE_DOMAIN_ID can be used to discard obsolete GTID domains from the server's binary log state. In order for this to be successful, no event group from the listed GTID domains can be present in existing binary log files. If some still exist, then they must be purged prior to executing this command. If the command completes successfully, then it also rotates the binary log.

MASTER

Deprecated option, use RESET MASTER instead.

PRIVILEGES

Reload all privileges from the privilege tables in the mysql database. If the server is started with --skip-grant-table option, this will activate the privilege tables again.

QUERY CACHE

Defragment the query cache to better utilize its memory. If you want to reset the query cache, you can do it with RESET QUERY CACHE.

QUERY_RESPONSE_TIME

See the QUERY_RESPONSE_TIME plugin.

QUERY_RESPONSE_TIME_READ

See the QUERY_RESPONSE_TIME plugin. From MariaDB 11.5.

QUERY_RESPONSE_TIME_READ_WRITE

See the QUERY_RESPONSE_TIME plugin. From MariaDB 11.5.

QUERY_RESPONSE_TIME_WRITE

See the QUERY_RESPONSE_TIME plugin. From MariaDB 11.5.

SLAVE

Deprecated option, use RESET REPLICA or RESET SLAVE instead.

SSL

Used to dynamically reinitialize the server's TLS context by reloading the files defined by several TLS system variables. See FLUSH SSL for more information.

[ GLOBAL

SESSION ] STATUS

TABLE[S]

Close tables given as options or all open tables if no table list was used. Using without any table list will only close tables not in use, and tables not locked by the FLUSH TABLES connection. If there are no locked tables, FLUSH TABLES will be instant and will not cause any waits, as it no longer waits for tables in use. When a table list is provided, the server will wait for the end of any transactions that are using the tables. Previously, FLUSH TABLES only waited for the statements to complete.

TABLE[S] ... FOR EXPORT

For InnoDB tables, flushes table changes to disk to permit binary table copies while the server is running. See FLUSH TABLES ... FOR EXPORT for more.

TABLE[S] WITH READ LOCK

Closes all open tables. New tables are only allowed to be opened with read locks until an UNLOCK TABLES is given.

TABLE[S] WITH READ LOCK AND DISABLE CHECKPOINT

As TABLES WITH READ LOCK but also disable all checkpoint writes by transactional table engines. This is useful when doing a disk snapshot of all tables.

TABLE_STATISTICS

Reset table statistics (see SHOW TABLE_STATISTICS).

USER_RESOURCES

Resets all per hour user resources. This enables clients that have exhausted their resources to connect again.

USER_STATISTICS

Reset user statistics (see SHOW USER_STATISTICS).

USER_VARIABLES

Reset user variables (see User-defined variables).

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

FLUSH RELAY LOGS

FLUSH RELAY LOGS 'connection_name'

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:

FLUSH RELAY LOGS FOR CHANNEL 'connection_name';

FOR CHANNEL isn't available.

FLUSH STATUS

Server status variables can be reset by executing the following:

FLUSH STATUS

This statement requires the RELOAD 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:

  • Aborted_clients

  • Aborted_connects

  • Binlog_cache_disk_use

  • Binlog_cache_use

  • Binlog_stmt_cache_disk_use

  • Binlog_stmt_cache_use

  • Connection_errors_accept

  • Connection_errors_internal

  • Connection_errors_max_connections

  • Connection_errors_peer_address

  • Connection_errors_select

  • Connection_errors_tcpwrap

  • Created_tmp_files

  • Delayed_errors

  • Delayed_writes

  • Feature_check_constraint

  • Feature_delay_key_write

  • Max_used_connection_time

  • Max_used_connections

  • Opened_plugin_libraries

  • Performance_schema_accounts_lost

  • Performance_schema_cond_instances_lost

  • Performance_schema_digest_lost

  • Performance_schema_file_handles_lost

  • Performance_schema_file_instances_lost

  • Performance_schema_hosts_lost

  • Performance_schema_locker_lost

  • Performance_schema_mutex_instances_lost

  • Performance_schema_rwlock_instances_lost

  • Performance_schema_session_connect_attrs_lost

  • Performance_schema_socket_instances_lost

  • Performance_schema_stage_classes_lost

  • Performance_schema_statement_classes_lost

  • Performance_schema_table_handles_lost

  • Performance_schema_table_instances_lost

  • Performance_schema_thread_instances_lost

  • Performance_schema_users_lost

  • Qcache_hits

  • Qcache_inserts

  • Qcache_lowmem_prunes

  • Qcache_not_cached

  • Rpl_semi_sync_master_no_times

  • Rpl_semi_sync_master_no_tx

  • Rpl_semi_sync_master_timefunc_failures

  • Rpl_semi_sync_master_wait_pos_backtraverse

  • Rpl_semi_sync_master_yes_tx

  • Rpl_transactions_multi_engine

  • Server_audit_writes_failed

  • Slave_retried_transactions

  • Slow_launch_threads

  • Ssl_accept_renegotiates

  • Ssl_accepts

  • Ssl_callback_cache_hits

  • Ssl_client_connects

  • Ssl_connect_renegotiates

  • Ssl_ctx_verify_depth

  • Ssl_ctx_verify_mode

  • Ssl_finished_accepts

  • Ssl_finished_connects

  • Ssl_session_cache_hits

  • Ssl_session_cache_misses

  • Ssl_session_cache_overflows

  • Ssl_session_cache_size

  • Ssl_session_cache_timeouts

  • Ssl_sessions_reused

  • Ssl_used_session_cache_entries

  • Subquery_cache_hit

  • Subquery_cache_miss

  • Table_locks_immediate

  • Table_locks_waited

  • Tc_log_max_pages_used

  • Tc_log_page_waits

  • Transactions_gtid_foreign_engine

  • Transactions_multi_engine

FLUSH TABLES

MariaDB starting with 10.11.12

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

FLUSH TABLES causes InnoDB statistics to be reloaded or recalculated.

FLUSH TABLES causes InnoDB statistics 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 LOCK TABLES.

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 useBACKUP STAGE BLOCK_COMMIT.

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.

  • No changes to these tables allowed until UNLOCK TABLES.

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 TLS 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 TLS system variables:

  • ssl_cert

  • ssl_key

  • ssl_ca

  • ssl_capath

  • ssl_crl

  • ssl_crlpath

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

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

Reducing Memory Usage

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

FLUSH LOCAL HOSTS,
   QUERY CACHE, 
   TABLE_STATISTICS, 
   INDEX_STATISTICS, 
   USER_STATISTICS;

This page is licensed: GPLv2, originally from fill_help_tables.sql

Plugin Statements

Learn about plugin SQL statements in MariaDB Server. This section details how to install, uninstall, enable, and disable server plugins, extending database functionality as needed.

INSTALL PLUGIN

Syntax

INSTALL PLUGIN [IF NOT EXISTS] plugin_name SONAME 'plugin_library'

Description

This statement installs an individual plugin from the specified library. To install the whole library (which could be required), use INSTALL SONAME. See also Installing a Plugin.

plugin_name is the name of the plugin as defined in theplugin declaration structure contained in the library file. Plugin names arenot case sensitive. For maximal compatibility, plugin names should be limitedto ASCII letters, digits, and underscore, because they are used in C sourcefiles, shell command lines, M4 and Bourne shell scripts, and SQL environments.

plugin_library is the name of the shared library thatcontains the plugin code. The file name extension can be omitted (which makes the statement look the same on all architectures).

The shared library must be located in the plugin directory (that is,the directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory. Bydefault, plugin_dir is plugin directory under the directory named bythe pkglibdir configuration variable, but it can be changed by settingthe value of plugin_dir at server startup. For example, setits value in a my.cnf file:

[mariadbd]
plugin_dir=/path/to/plugin/directory

If the value of plugin_dir is a relative path name, it istaken to be relative to the base directory (the value of the basedir system variable).

INSTALL PLUGIN adds a line to the mysql.plugin table thatdescribes the plugin. This table contains the plugin name and library filename.

INSTALL PLUGIN causes the server to readoption (my.cnf) files just as during server startup. This enables the plugin topick up any relevant options from those files. It is possible to add pluginoptions to an option file even before loading a plugin (if the loose prefix isused). It is also possible to uninstall a plugin, edit my.cnf, and install theplugin again. Restarting the plugin this way enables it to the new optionvalues without a server restart.

INSTALL PLUGIN also loads and initializes the plugin code tomake the plugin available for use. A plugin is initialized by executing itsinitialization function, which handles any setup that the plugin must performbefore it can be used.

To use INSTALL PLUGIN, you must have theINSERT privilege for the mysql.plugin table.

At server startup, the server loads and initializes any plugin that islisted in the mysql.plugin table. This means that a plugin is installedwith INSTALL PLUGIN only once, not every time the serverstarts. Plugin loading at startup does not occur if the server is started withthe --skip-grant-tables option.

When the server shuts down, it executes the de-initialization functionfor each plugin that is loaded so that the plugin has a chance toperform any final cleanup.

If you need to load plugins for a single server startup when the--skip-grant-tables option is given (which tells the servernot to read system tables), use the--plugin-load mariadbd option.

IF NOT EXISTS

When the IF NOT EXISTS clause is used, MariaDB will return a note instead of an error if the specified plugin already exists. See SHOW WARNINGS.

Examples

INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

The extension can also be omitted:

INSTALL PLUGIN innodb SONAME 'ha_xtradb';
INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example';
Query OK, 0 rows affected (0.104 sec)

INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example';
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Note  | 1968 | Plugin 'example' already installed |
+-------+------+------------------------------------+

See Also

  • List of Plugins

  • Plugin Overview

  • INFORMATION_SCHEMA.PLUGINS Table

  • mariadb-plugin

  • SHOW PLUGINS

  • INSTALL SONAME

  • UNINSTALL PLUGIN

  • UNINSTALL SONAME

This page is licensed: GPLv2, originally from fill_help_tables.sql

INSTALL SONAME

Syntax

INSTALL SONAME 'plugin_library'

Description

This statement is a variant of INSTALL PLUGIN. It installs all plugins from a given plugin_library. See INSTALL PLUGIN for details.

plugin_library is the name of the shared library thatcontains the plugin code. The file name extension (forexample, libmyplugin.so or libmyplugin.dll) can be omitted (which makes the statement look the same on all architectures).

The shared library must be located in the plugin directory (that is,the directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory. Bydefault, plugin_dir is plugin directory under the directory named bythe pkglibdir configuration variable, but it can be changed by settingthe value of plugin_dir at server startup. For example, setits value in a my.cnf file:

[mariadbd]
plugin_dir=/path/to/plugin/directory

If the value of plugin_dir is a relative path name, it istaken to be relative to the MySQL base directory (the value of the basedirsystem variable).

INSTALL SONAME adds one or more lines to the mysql.plugin table thatdescribes the plugin. This table contains the plugin name and library filename.

INSTALL SONAME causes the server to readoption (my.cnf) files just as during server startup. This enables the plugin topick up any relevant options from those files. It is possible to add pluginoptions to an option file even before loading a plugin (if the loose prefix isused). It is also possible to uninstall a plugin, edit my.cnf, and install theplugin again. Restarting the plugin this way enables it to the new optionvalues without a server restart.

INSTALL SONAME also loads and initializes the plugin code tomake the plugin available for use. A plugin is initialized by executing itsinitialization function, which handles any setup that the plugin must performbefore it can be used.

To use INSTALL SONAME, you must have theINSERT privilege for the mysql.plugin table.

At server startup, the server loads and initializes any plugin that islisted in the mysql.plugin table. This means that a plugin is installedwith INSTALL SONAME only once, not every time the serverstarts. Plugin loading at startup does not occur if the server is started withthe --skip-grant-tables option.

When the server shuts down, it executes the de-initialization functionfor each plugin that is loaded so that the plugin has a chance toperform any final cleanup.

If you need to load plugins for a single server startup when the--skip-grant-tables option is given (which tells the servernot to read system tables), use the--plugin-load mariadbd option.

If you need to install only one plugin from a library, use the INSTALL PLUGIN statement.

Examples

To load the LOCALES plugin and all of its information_schema tables with one statement, use

INSTALL SONAME 'locales';

This statement can be used instead of INSTALL PLUGIN even when the library contains only one plugin:

INSTALL SONAME 'ha_sequence';

See Also

  • List of Plugins

  • Plugin Overview

  • SHOW PLUGINS

  • INSTALL PLUGIN

  • UNINSTALL PLUGIN

  • UNINSTALL SONAME

  • SHOW PLUGINS

  • INFORMATION_SCHEMA.PLUGINS Table

  • mysql_plugin

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

UNINSTALL PLUGIN

Syntax

UNINSTALL PLUGIN [IF EXISTS] plugin_name

Description

This statement removes a single installed plugin. To uninstall the whole library which contains the plugin, use UNINSTALL SONAME. You cannot uninstall a plugin if any table that uses it is open.

plugin_name must be the name of some plugin that is listedin the mysql.plugin table. The server executes the plugin's deinitializationfunction and removes the row for the plugin from the mysql.plugintable, so that subsequent server restarts will not load and initializethe plugin. UNINSTALL PLUGIN does not remove the plugin'sshared library file.

To use UNINSTALL PLUGIN, you must have theDELETE privilege for the mysql.plugin table.

IF EXISTS

If the IF EXISTS clause is used, MariaDB will return a note instead of an error if the plugin does not exist. See SHOW WARNINGS.

Examples

UNINSTALL PLUGIN example;
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 |
+-------+------+-------------------------------+

See Also

  • Plugin Overview

  • mysql_plugin

  • INSTALL PLUGIN

  • List of Plugins

This page is licensed: GPLv2, originally from fill_help_tables.sql

UNINSTALL SONAME

Syntax

UNINSTALL SONAME  [IF EXISTS] 'plugin_library'

Description

This statement is a variant of UNINSTALL PLUGIN statement, that removes all plugins belonging to a specified plugin_library. See UNINSTALL PLUGIN for details.

plugin_library is the name of the shared library thatcontains the plugin code. The file name extension (forexample, libmyplugin.so or libmyplugin.dll) can be omitted (which makes the statement look the same on all architectures).

To use UNINSTALL SONAME, you must have theDELETE privilege for the mysql.plugin table.

IF EXISTS

If the IF EXISTS clause is used, MariaDB will return a note instead of an error if the plugin library does not exist. See SHOW WARNINGS.

Examples

To uninstall the XtraDB plugin and all of its information_schema tables with one statement, use

UNINSTALL SONAME 'ha_xtradb';
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 |
+-------+------+-------------------------------------+

See Also

  • INSTALL SONAME

  • SHOW PLUGINS

  • INSTALL PLUGIN

  • UNINSTALL PLUGIN

  • SHOW PLUGINS

  • INFORMATION_SCHEMA.PLUGINS Table

  • mysql_plugin

  • List of Plugins

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

Replication Statements

Learn about replication SQL statements in MariaDB Server. This section covers commands for configuring, controlling, and monitoring replication, essential for high availability and data distribution.

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.

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

CHANGE MASTER ['connection_name'] TO master_def  [, master_def] ... 
  [FOR CHANNEL 'channel_name']

master_def:
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_DELAY = interval
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_CRL = 'crl_file_name'
  | MASTER_SSL_CRLPATH = 'crl_directory_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | MASTER_USE_GTID = {current_pos|slave_pos|no}
  | MASTER_DEMOTE_TO_SLAVE = bool
  | IGNORE_SERVER_IDS = (server_id_list)
  | DO_DOMAIN_IDS = ([N,..])
  | IGNORE_DOMAIN_IDS = ([N,..])
  | MASTER_RETRY_COUNT = long

Description

CHANGE MASTER is used on a replica to set up or change replication 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 multi-source replication, then you need to specify a connection name when you execute CHANGE MASTER. There are two ways to do this:

  • Setting the default_master_connection system variable prior to executing CHANGE MASTER.

  • Setting the connection_name parameter when executing CHANGE MASTER.

default_master_connection

SET default_master_connection = 'gandalf';
STOP SLAVE;
CHANGE MASTER TO 
   MASTER_PASSWORD='new3cret';
START SLAVE;

connection_name

STOP SLAVE 'gandalf';
CHANGE MASTER 'gandalf' TO 
   MASTER_PASSWORD='new3cret';
START SLAVE 'gandalf';

Options

Connection Options

MASTER_USER

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

This user account will need the REPLICATION REPLICA privilege on the primary.

This user account will need the REPLICATION SLAVE privilege on the primary.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_USER='repl',
   MASTER_PASSWORD='new3cret';
START SLAVE;

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 replica will use to connect to the primary as the user account defined by the MASTER_USER option.

For example:

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_PASSWORD='new3cret';
START SLAVE;

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 MDEV-29994, 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 primary.

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.

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 START SLAVE command would fail.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_HOST='dbserver1.example.com',
   MASTER_USER='repl',
   MASTER_PASSWORD='new3cret',
   MASTER_USE_GTID=slave_pos;
START SLAVE;

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 binarylog 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 MASTER_LOG_FILE and MASTER_LOG_POS options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable GTID mode for replication by setting the MASTER_USE_GTID 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 primary.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_HOST='dbserver1.example.com',
   MASTER_PORT=3307,
   MASTER_USER='repl',
   MASTER_PASSWORD='new3cret',
   MASTER_USE_GTID=slave_pos;
START SLAVE;

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 binarylog 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 MASTER_LOG_FILE and MASTER_LOG_POS options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable GTID mode for replication by setting the MASTER_USE_GTID 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.

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_CONNECT_RETRY=20;
START SLAVE;

MASTER_RETRY_COUNT

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

STOP SLAVE;
CHANGE MASTER TO
  MASTER_RETRY_COUNT=1; # attempt only once; do not retry if it fails
START SLAVE;

Setting this option resets the Connects_Tried statistic in SHOW REPLICA STATUS to 0.

The default is the --master-retry-count option, which be set either on the command-line or in a server option group in an option file 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 --master-retry-count option instead, which be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
master_retry_count=4294967295

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 MDEV-19248 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 binary log 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.

  • Its default value is the value of the slave_net_timeout system variable divided by 2.

  • If it's set to 0, then heartbeats are disabled.

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 RESET SLAVE statement is executed, then the heartbeat interval is reset to the default.

If the slave_net_timeout 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 TLS. 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 master_info_file option, but these TLS options are ignored unless the replica supports TLS.

See Replication with Secure Connections for more information.

MASTER_SSL

The MASTER_SSL option for CHANGE MASTER tells the replica whether to force TLS 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:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL=1;
START SLAVE;

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 TLS. This option requires that you use the absolute path, not a relative path.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

See Secure Connections Overview: Certificate Authorities (CAs) 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 TLS. 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 openssl rehash command.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CAPATH='/etc/my.cnf.d/certificates/ca/',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

See Secure Connections Overview: Certificate Authorities (CAs) 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 TLS. This option requires that you use the absolute path, not a relative path.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

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 TLS. 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 TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1,
   MASTER_SSL_CRL='/etc/my.cnf.d/certificates/crl.pem';
START SLAVE;

See Secure Connections Overview: Certificate Revocation Lists (CRLs) 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 TLS. 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 openssl rehash command.

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 TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1,
   MASTER_SSL_CRLPATH='/etc/my.cnf.d/certificates/crl/';
START SLAVE;

See Secure Connections Overview: Certificate Revocation Lists (CRLs) 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 TLS. This option requires that you use the absolute path, not a relative path.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

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 TLS. 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 Using TLSv1.3 for details.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1,
   MASTER_SSL_CIPHER='TLSv1.2';
START SLAVE;

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 server certificate verification. This option is enabled by default.

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

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

See Secure Connections Overview: Server Certificate Verification for more information.

Binary Log Options

These options are related to the binary log 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 replica's I/O thread should begin reading from the primary's binary logs the next time the thread starts.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4;
START SLAVE;

The MASTER_LOG_FILE and MASTER_LOG_POS options cannot be specified if the RELAY_LOG_FILE and RELAY_LOG_POS options were also specified.

The MASTER_LOG_FILE and MASTER_LOG_POS options are effectively ignored if you enable GTID mode for replication by setting the MASTER_USE_GTID 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 replica's I/O thread should begin reading from the primary's binary logs the next time the thread starts.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4;
START SLAVE;

The MASTER_LOG_FILE and MASTER_LOG_POS options cannot be specified if the RELAY_LOG_FILE and RELAY_LOG_POS options were also specified.

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

Relay Log Options

These options are related to the relay log position on the replica.

RELAY_LOG_FILE

The RELAY_LOG_FILE option for CHANGE MASTER can be used along with the RELAY_LOG_POS option to specify the coordinates at which the replica's SQL thread should begin reading from the relay log the next time the thread starts.

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

When you want to change the relay log position, you only need to stop the replica's SQL thread. The replica's I/O thread can continue running. The STOP SLAVE and START SLAVE statements support the SQL_THREAD option for this scenario. For example:

STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
   RELAY_LOG_FILE='slave-relay-bin.006',
   RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;

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

The RELAY_LOG_FILE and RELAY_LOG_POS options cannot be specified if the MASTER_LOG_FILE and MASTER_LOG_POS options were also specified.

RELAY_LOG_POS

The RELAY_LOG_POS option for CHANGE MASTER can be used along with the RELAY_LOG_FILE option to specify the coordinates at which the replica's SQL thread should begin reading from the relay log the next time the thread starts.

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

When you want to change the relay log position, you only need to stop the replica's SQL thread. The replica's I/O thread can continue running. The STOP SLAVE and START SLAVE statements support the SQL_THREAD option for this scenario. For example:

STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
   RELAY_LOG_FILE='slave-relay-bin.006',
   RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;

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

The RELAY_LOG_FILE and RELAY_LOG_POS options cannot be specified if the MASTER_LOG_FILE and MASTER_LOG_POS 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 global transaction ID (GTID) when connecting to a primary. The possible values are:

  • current_pos - Replicate in GTID mode and use gtid_current_pos 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, MASTER_DEMOTE_TO_SLAVE= option instead.

  • replica_pos - Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary.

  • no - Don't replicate in GTID mode.

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:

  • current_pos - Replicate in GTID mode and use gtid_current_pos 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, MASTER_DEMOTE_TO_SLAVE= option instead.

  • slave_pos - Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary.

  • no - Don't replicate in GTID mode.

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:

  • current_pos - Replicate in GTID mode and use gtid_current_pos 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, MASTER_DEMOTE_TO_SLAVE= option instead.

  • slave_pos - Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary. From MariaDB 10.5.1, replica_pos is an alias for slave_pos.

  • no - Don't replicate in GTID mode.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_USE_GTID = current_pos;
START SLAVE;

Or:

STOP SLAVE;
SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
   MASTER_USE_GTID = slave_pos;
START SLAVE;

MASTER_DEMOTE_TO_SLAVE

Used to transition a primary to become a replica. Replaces the old MASTER_USE_GTID=current_pos 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:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_DEMOTE_TO_SLAVE = 1;
START SLAVE;

MASTER_DEMOTE_TO_SLAVE is not available.

Replication Filter Options

Also see Replication filters.

IGNORE_SERVER_IDS

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

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

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_SERVER_IDS = (3,5);
START SLAVE;

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

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_SERVER_IDS = ();
START SLAVE;

DO_DOMAIN_IDS

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

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

STOP SLAVE;
CHANGE MASTER TO 
   DO_DOMAIN_IDS = (1,2);
START SLAVE;

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

STOP SLAVE;
CHANGE MASTER TO 
   DO_DOMAIN_IDS = ();
START SLAVE;

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

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_DOMAIN_IDS = (), 
   DO_DOMAIN_IDS = (1,2);
START SLAVE;

The DO_DOMAIN_IDS option can only be specified if the replica is replicating in GTID mode. Therefore, the MASTER_USE_GTID 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 replica to ignore binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

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

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;

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

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_DOMAIN_IDS = ();
START SLAVE;

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

STOP SLAVE;
CHANGE MASTER TO 
   DO_DOMAIN_IDS = (), 
   IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;

The IGNORE_DOMAIN_IDS option can only be specified if the replica is replicating in GTID mode. Therefore, the MASTER_USE_GTID 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 delayed replication. 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.

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_DELAY=3600;
START SLAVE;

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 MASTER_PASSWORD option by executing the following commands:

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_PASSWORD='new3cret';
START SLAVE;

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

Option Persistence

The values of the MASTER_LOG_FILE and MASTER_LOG_POS options (i.e. the binary log 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 master_info_file option. The replica's I/O thread keeps this binary log position updated as it downloads events only when MASTER_USE_GTID optionis set to NO. Otherwise the file is not updated on a per event basis.

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

[mariadb]
...
master_info_file=/mariadb/myserver1-master.info

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

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

[mariadb]
...
relay_log_info_file=/mariadb/myserver1-relay-log.info

GTID Persistence

If the replica is replicating binary log events that contain GTIDs, then the replica's SQL thread will write every GTID that it applies to the mysql.gtid_slave_pos table. This GTID can be inspected and modified through the gtid_slave_pos system variable.

If the replica has the log_slave_updates system variable enabled and if the replica has the binary log enabled, then every write by the replica's SQL thread will also go into the replica's binary log. This means that GTIDs of replicated transactions would be reflected in the value of the gtid_binlog_pos 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 binary log position or GTID position corresponding to the backup.

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

CHANGE MASTER TO
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4;
START SLAVE;

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

SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
   MASTER_USE_GTID=slave_pos;
START SLAVE;

See Setting up a Replication Slave with mariadb-backup for more information on how to do this with mariadb-backup.

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:

CHANGE MASTER TO
   MASTER_HOST='master2.mycompany.com',
   MASTER_USER='replication',
   MASTER_PASSWORD='bigs3cret',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4,
   MASTER_CONNECT_RETRY=10;
START SLAVE;

See Also

  • Setting up replication

  • START SLAVE

  • Multi-source replication

  • RESET SLAVE. Removes a connection created with CHANGE MASTER TO.

  • Global Transaction ID

This page is licensed: GPLv2, originally from fill_help_tables.sql

RESET MASTER

RESET MASTER [TO #]

Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file with a suffix of .000001.

If TO # is given, then the first new binary log file will start from number #.

This statement is for use only when the master is started for the first time, and should never be used if any slaves are actively replicating from the binary log.

See Also

  • The PURGE BINARY LOGS statement is intended for use in active replication.

This page is licensed: GPLv2, originally from fill_help_tables.sql

RESET REPLICA

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

RESET { SLAVE | REPLICA } ["connection_name"] [ALL]  [FOR CHANNEL "connection_name"].

Description

RESET REPLICA makes the replica forget its replication position in the master's binary log. This statement is meant to be used for a clean start. It deletes the master.info and relay-log.info files, all therelay log files, and starts a new relay log file. To use RESET REPLICA, the replica threads must be stopped (use STOP REPLICA if necessary).

Note: All relay log files are deleted, even if they have not been completely executed by the replica SQL thread. (This is a condition likely to exist on a replication replica if you have issued a STOP REPLICA statement or if the replica is highly loaded.)

Note: RESET REPLICA does not reset the globalgtid_slave_pos variable. This means that a replica server configured with CHANGE MASTER TO MASTER_USE_GTID=slave_pos will not receive events with GTIDs occurring before the state saved ingtid_slave_pos. If the intent is to reprocess these events,gtid_slave_pos must be manually reset, e.g. by executingset global gtid_slave_pos="".

Connection information stored in the master.info file is immediately reset using any values specified in the corresponding startup options. This information includes values such as master host, master port, master user, and master password. If the replica SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET REPLICA is issued, these replicated temporary tables are deleted on the replica.

The ALL also resets the PORT, HOST, USER and PASSWORD parameters for the replica. If you are using a connection name, it will permanently delete it and it will not show up anymore in SHOW ALL REPLICAS STATUS.

connection_name

The connection_name option is used for multi-source replication.

If there is only one nameless primary, or the default primary (as specified by the default_master_connection system variable) is intended, connection_name can be omitted. If provided, the RESET REPLICA statement will apply to the specified primary. connection_name is case-insensitive.

MariaDB starting with 10.7.0

The FOR CHANNEL keyword was added for MySQL compatibility. This is identical as using the channel_name directly after RESET REPLICA.

FOR CHANNEL is not available.

The FOR CHANNEL keyword was added for MySQL compatibility. This is identical as using the channel_name directly after RESET REPLICA.

MariaDB starting with 11.6.0

RESET REPLICA resets the Master/Slave_last_event_time and Connects_Tried values (see SHOW REPLICA STATUS).

RESET REPLICA resets the Master/Slave_last_event_time values (see SHOW REPLICA STATUS).

RESET REPLICA does not reset the Master/Slave_last_event_time values (see SHOW REPLICA STATUS).

See Also

  • STOP REPLICA stops the replica, but it can be restarted with START REPLICA or after next MariaDB server restart.

This page is licensed: GPLv2, originally from fill_help_tables.sql

SET GLOBAL SQL_SLAVE_SKIP_COUNTER

Syntax

SET GLOBAL sql_slave_skip_counter = N

Description

This statement skips the next N events from the primary. This is useful for recovering from replication stops caused by a statement.

If multi-source replication is used, this statement applies to the default connection. It could be necessary to change the value of the default_master_connection system variable.

Note that, if the event is a transaction, the whole transaction will be skipped. With non-transactional engines, an event is always a single statement.

This statement is valid only when the replica threads are not running. Otherwise, it produces an error.

The statement does not automatically restart the replica threads.

Example

SHOW SLAVE STATUS \G
...
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Multi-source replication:

SET @@default_master_connection = 'master_01';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Multiple Replication Domains

sql_slave_skip_counter can't be used to skip transactions on a replica if GTID replication is in use and if gtid_slave_pos contains multiple gtid_domain_id values. In that case, you'll get an error like the following:

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.

In order to skip transactions in cases like this, you will have to manually change gtid_slave_pos.

See Also

  • Selectively Skipping Replication of Binlog Events

This page is licensed: GPLv2, originally from fill_help_tables.sql

START REPLICA

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

START { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL                
    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos 
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos 
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
    MASTER_GTID_POS = <GTID position> 
START ALL { SLAVES | REPLICAS } [thread_type [, thread_type]] 

thread_type: IO_THREAD | SQL_THREAD

Description

START REPLICA is a synonym for START SLAVE which is considered deprecated.

Only START SLAVE can be used.

START SLAVE or START REPLICA with no thread_type options starts both of the replica threads (see replication) needed to connect with a master setup with CHANGE MASTER TO . The I/O thread reads events from the primary server and stores them in the relay log. The SQL thread reads events from the relay log and executes them.

START REPLICA requires the REPLICATION SLAVE ADMIN privilege.

START REPLICA requires the SUPER privilege.

If START REPLICA succeeds in starting the replica threads, it returns without any error. However, even in that case, it might be that the replica threads start and then later stop (for example, because they do not manage to connect to the primary or read its binary log, or some other problem). START REPLICA does not warn you about this. You must check the replica's error log for error messages generated by the replica threads, or check that they are running satisfactorily with SHOW REPLICA STATUS (SHOW REPLICA STATUS).

START REPLICA UNTIL

START REPLICA UNTIL refers to the SQL_THREAD replica position at which the SQL_THREAD replication will halt. If SQL_THREAD isn't specified both threads are started.

START REPLICA UNTIL master_gtid_pos=xxx is also supported. See Global Transaction ID/START REPLICA UNTIL master_gtid_pos=xxx for more details.

MariaDB starting with 11.3.0

SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS

The START REPLICA UNTIL statement contains the options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS to allow control of whether the replica stops before or after a provided GTID state. Its syntax is:

START REPLICA UNTIL (SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS)="<gtid_list>"

See Global Transaction ID#SQL_BEFORE_GTIDS/SQL_AFTER_GTIDS for details.

SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS are not available.

connection_name

If there is only one nameless primary, or the default primary (as specified by the default_master_connection system variable) is intended, connection_name can be omitted. If provided, the START REPLICA statement will apply to the specified primary. connection_name is case-insensitive.

The FOR CHANNEL keyword is available for MySQL compatibility. This is identical as using the channel_name directly after START REPLICA.

The FOR CHANNEL keyword is not available.

START ALL REPLICAS

START ALL REPLICAS starts all configured replicas (replicas with master_host not empty) that were not started before. It will give a note for all started connections. You can check the notes with SHOW WARNINGS.

See Also

  • Setting up replication.

  • CHANGE MASTER TO is used to create and change connections.

  • STOP REPLICA is used to stop a running connection.

  • RESET REPLICA is used to reset parameters for a connection and also to permanently delete a primary connection.

This page is licensed: GPLv2, originally from fill_help_tables.sql

STOP REPLICA

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

STOP { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ] 
[FOR CHANNEL "connection_name"]

STOP ALL { SLAVES | REPLICAS } [thread_type [, thread_type]]

STOP { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]

STOP ALL { SLAVES | REPLICAS } [thread_type [, thread_type]] 

thread_type: IO_THREAD | SQL_THREAD

Description

Stops the replica threads. STOP REPLICA requires the SUPER privilege, or, from MariaDB 10.5.2, the REPLICATION SLAVE ADMIN privilege.

Like START REPLICA, this statement may be used with the IO_THREAD andSQL_THREAD options to name the thread or threads to be stopped. In almost all cases, one never need to use the thread_type options.

STOP REPLICA waits until any current replication event group affecting one or more non-transactional tables has finished executing (if there is any such replication group), or until the user issues a KILL QUERY or KILL CONNECTION statement.

Note that STOP REPLICA doesn't delete the connection permanently. Next time you execute START REPLICA or the MariaDB server restarts, the replica connection is restored with it's original arguments. If you want to delete a connection, you should execute RESET REPLICA.

STOP ALL REPLICAS

STOP ALL REPLICAS stops all your running replicas. It will give you a note for every stopped connection. You can check the notes with SHOW WARNINGS.

connection_name

The connection_name option is used for multi-source replication.

If there is only one nameless master, or the default master (as specified by the default_master_connection system variable) is intended, connection_name can be omitted. If provided, the STOP REPLICA statement will apply to the specified master. connection_name is case-insensitive.

MariaDB starting with 10.7.0

The FOR CHANNEL keyword is available for MySQL compatibility. This is identical as using the channel_name directly after STOP REPLICA.

The FOR CHANNEL keyword is not available.

See Also

  • CHANGE MASTER TO is used to create and change connections.

  • START REPLICA is used to start a predefined connection.

  • RESET REPLICA is used to reset parameters for a connection and also to permanently delete a master connection.

This page is licensed: GPLv2, originally from fill_help_tables.sql

Legacy Replication Statements

Category for old replication statements

RESET SLAVE

Old name for RESET REPLICA

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

SHOW SLAVE HOSTS

Old name for SHOW REPLICA HOSTS

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

SHOW SLAVE STATUS

Old name for SHOW REPLICA STATUS

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

START SLAVE

Old name for START REPLICA

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

STOP SLAVE

Old name for STOP REPLICA

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

SET Statements

Learn about SET statements in MariaDB Server. This section details how to configure system variables at global, session, and local levels, enabling fine-tuning of database behavior and performance.

SETSET CHARACTER SETSET GLOBAL SQL_SLAVE_SKIP_COUNTERSET NAMESSET PASSWORDSET ROLESET SQL_LOG_BINSET STATEMENTSET TRANSACTIONSET Variable

SET

Syntax

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | [@@global. | @@session. | @@]system_var_name = expr

One can also set a user variable in any expression with this syntax:

user_var_name:= expr

Description

The SET statement assigns values to different types of variables that affect the operation of the server or your client.

The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION, but this syntax was deprecated in favor of SET without OPTION.

Changing a system variable by using the SET statement does not make the change permanently. To do so, the change must be made in a configuration file.

For setting variables on a per-query basis, see SET STATEMENT.

See SHOW VARIABLES for documentation on viewing server system variables.

See Server System Variables for a list of all the system variables.

GLOBAL / SESSION

When setting a system variable, the scope can be specified as either GLOBAL or SESSION.

A global variable change affects all new sessions. It does not affect any currently open sessions, including the one that made the change.

A session variable change affects the current session only.

If the variable has a session value, not specifying either GLOBAL or SESSION will be the same as specifying SESSION. If the variable only has a global value, not specifying GLOBAL or SESSION will apply to the change to the global value.

DEFAULT

Setting a global variable to DEFAULT will restore it to the server default, and setting a session variable to DEFAULT will restore it to the current global value.

Examples

  • innodb_sync_spin_loops is a global variable.

  • skip_parallel_replication is a session variable.

  • max_error_count is both global and session.

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 64            | 64           |
| SKIP_PARALLEL_REPLICATION | OFF           | NULL         |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

Setting the session values:

SET max_error_count=128;Query OK, 0 rows affected (0.000 sec)

SET skip_parallel_replication=ON;Query OK, 0 rows affected (0.000 sec)

SET innodb_sync_spin_loops=60;
ERROR 1229 (HY000): Variable 'innodb_sync_spin_loops' is a GLOBAL variable 
  and should be set with SET GLOBAL

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 128           | 64           |
| SKIP_PARALLEL_REPLICATION | ON            | NULL         |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

Setting the global values:

SET GLOBAL max_error_count=256;

SET GLOBAL skip_parallel_replication=ON;
ERROR 1228 (HY000): Variable 'skip_parallel_replication' is a SESSION variable 
  and can't be used with SET GLOBAL

SET GLOBAL innodb_sync_spin_loops=120;

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
  VARIABLE_NAME IN ('max_error_count', 'skip_parallel_replication', 'innodb_sync_spin_loops');
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 128           | 256          |
| SKIP_PARALLEL_REPLICATION | ON            | NULL         |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 120          |
+---------------------------+---------------+--------------+

SHOW VARIABLES will by default return the session value unless the variable is global only.

SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 128   |
+-----------------+-------+

SHOW VARIABLES LIKE 'skip_parallel_replication';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| skip_parallel_replication | ON    |
+---------------------------+-------+

SHOW VARIABLES LIKE 'innodb_sync_spin_loops';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_sync_spin_loops | 120   |
+------------------------+-------+

Using the inplace syntax:

SELECT (@a:=1);
+---------+
| (@a:=1) |
+---------+
|       1 |
+---------+

SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+

See Also

  • Using last_value() to return data of used rows

  • SET STATEMENT

  • SET Variable

  • SET Data Type

  • DECLARE Variable

This page is licensed: GPLv2, originally from fill_help_tables.sql

SET SQL_LOG_BIN

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 privilege. 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 log_bin 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 fill_help_tables.sql

SET STATEMENT

SET STATEMENT can be used to set the value of a system variable for the duration of the statement. It is also possible to set multiple variables.

Syntax

SET STATEMENT var1=value1 [, var2=value2, ...] 
  FOR <statement>

var is a system variable (list of allowed variables is provided below), and value is a constant literal.

Description

SET STATEMENT var1=value1 FOR stmt

is roughly equivalent to

SET @save_value=@@var1;
SET SESSION var1=value1;
stmt;
SET SESSION var1=@save_value;

The server parses the whole statement before executing it, so any variables set in this fashion that affect the parser may not have the expected effect. Examples include the charset variables, sql_mode=ansi_quotes, etc.

Examples

One can limit statement execution time max_statement_time:

SET STATEMENT max_statement_time=1000 FOR SELECT ... ;

One can switch on/off individual optimizations:

SET STATEMENT optimizer_switch='materialization=off' FOR SELECT ....;

It is possible to enable MRR/BKA for a query:

SET STATEMENT  join_cache_level=6, optimizer_switch='mrr=on'  FOR SELECT ...

Note that it makes no sense to try to set a session variable inside a SET STATEMENT:

#USELESS STATEMENT
SET STATEMENT sort_buffer_size = 100000 FOR SET SESSION sort_buffer_size = 200000;

For the above, after setting sort_buffer_size to 200000 it will be reset to its original state (the state before the SET STATEMENT started) after the statement execution.

Limitations

There are a number of variables that cannot be set on per-query basis. These include:

  • autocommit

  • character_set_client

  • character_set_connection

  • character_set_filesystem

  • collation_connection

  • default_master_connection

  • debug_sync

  • interactive_timeout

  • gtid_domain_id

  • last_insert_id

  • log_slow_filter

  • log_slow_rate_limit

  • log_slow_verbosity

  • long_query_time

  • min_examined_row_limit

  • profiling

  • profiling_history_size

  • query_cache_type

  • rand_seed1

  • rand_seed2

  • skip_replication

  • slow_query_log

  • sql_log_off

  • tx_isolation

  • wait_timeout

Source

  • The feature was originally implemented as a Google Summer of Code 2009 project by Joseph Lukas.

  • Percona Server 5.6 included it as Per-query variable statement

  • MariaDB ported the patch and fixed many bugs. The task in MariaDB Jira is MDEV-5231.

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

SHOW Statements

Learn about SHOW statements in MariaDB Server. These SQL commands provide insights into server status, database objects, configurations, and various internal states for monitoring and troubleshooting.

About SHOW

SHOW provides information about various aspects of MariaDB Server. A list of the various SHOW statements is here.

The general syntax is:

SHOW LIKE 'pattern' | WHERE expr

The LIKE and WHERE clauses make sense only for particular SHOW statements, though. See Extended SHOW for what SHOW statements benefit from using those clauses.

If the syntax for a given SHOW statement includes LIKE 'pattern' , 'pattern' is a string that can contain the SQL % and _ wildcard characters. The pattern is useful for restricting statement output to matching values.

This page is licensed: GPLv2, originally from fill_help_tables.sql

Extended SHOW

The following SHOW statements can be extended using a WHERE clause and a LIKE clause to refine the results:

  • SHOW CHARACTER SET

  • SHOW COLLATION

  • SHOW COLUMNS

  • SHOW DATABASES

  • SHOW FUNCTION STATUS

  • SHOW INDEX``

  • SHOW OPEN TABLES

  • SHOW PACKAGE STATUS

  • SHOW PACKAGE BODY STATUS

  • SHOW INDEX

  • SHOW PROCEDURE STATUS

  • SHOW STATUS

  • SHOW TABLE STATUS

  • SHOW TABLES

  • SHOW TRIGGERS

  • SHOW VARIABLES

As with a regular SELECT, the WHERE clause can be used for the specific columns returned, and the LIKE clause with the regular wildcards.

Examples

This statement shows all tables:

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

This statement only shows tables starting with the letter 'a':

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

This statement shows variables whose names start with aria and have a value greater than 8192:

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           |
+------------------------------+---------------------+

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

SHOW ANALYZE

SHOW ANALYZE was added in MariaDB 10.9.

Syntax

SHOW ANALYZE [FORMAT=JSON] FOR <connection_id>;

Description

SHOW ANALYZE allows one to retrieve ANALYZE-like output from a currently running statement. The statement

SHOW ANALYZE [FORMAT=JSON] FOR <connection_id>;

connects to the query running in connection connection_id, gets information about the query plan it is executing, also gets information about the runtime statistics of the execution so far and returns it in a format similar to ANALYZE [FORMAT=JSON] output.

This is similar to the SHOW EXPLAIN command, the difference being that SHOW ANALYZE also produces runtime statistics information.

Use Case

You're trying to troubleshoot a query that never finishes. Since it doesn't finish, it is not possible to get ANALYZE output for it. With SHOW ANALYZE, you can get the runtime statistics without waiting for the query to finish.

Examples

Example 1: Row Counts

Consider the tables orders and customer and a join query finding the total amount of orders from customers with Gold status:

EXPLAIN format=json
SELECT sum(orders.amount)
FROM
  customer JOIN orders ON customer.cust_id=orders.cust_id
WHERE
  customer.status='GOLD';

The output of this query looks like this:

+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+
| id   | select_type | table    | type | possible_keys | key     | key_len | ref              | rows   | Extra       |
+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+
|    1 | SIMPLE      | customer | ALL  | PRIMARY       | NULL    | NULL    | NULL             | 199786 | Using where |
|    1 | SIMPLE      | orders   | ref  | cust_id       | cust_id | 5       | customer.cust_id | 1      |             |
+------+-------------+----------+------+---------------+---------+---------+------------------+--------+-------------+

We run the SELECT, and it has been running for 30 seconds. Let's try SHOW ANALYZE:

SHOW ANALYZE format=json FOR 3;
| {
  "r_query_time_in_progress_ms": 32138,

The statement shows how long the query has been running.

"query_block": {
    "select_id": 1,
    "r_loops": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "r_loops": 1,
          "rows": 199786,
          "r_rows": 110544,

rows shows the number of rows expected. r_rows in shows how many rows were processed so far (110K out of expected 200K). r_loops shows we're doing the first table scan (which is obvious for this query plan).

"filtered": 100,
          "r_filtered": 9.538283398,
          "attached_condition": "customer.`status` = 'GOLD'"
        }
      },
      {
        "table": {
          "table_name": "orders",
          "access_type": "ref",
          "possible_keys": ["cust_id"],
          "key": "cust_id",
          "key_length": "5",
          "used_key_parts": ["cust_id"],
          "ref": ["test.customer.cust_id"],
          "r_loops": 10544,
          "rows": 1,
          "r_rows": 99.99222307,

rows: 1 shows the optimizer was expecting 1 order per customer. But r_rows: 99.9 shows that it has found on average 100 orders per customer. This may be the reason the query is slower than expected.

The final chunk of the output doesn't have anything interesting but here it is:

"filtered": 100,
          "r_filtered": 100
        }
      }
    ]
  }
}

Example 2: Timing Information

Regular SELECT queries collect row count information, so SHOW ANALYZE can display it. However, detailed timing information is not collected, as collecting it may have CPU overhead. But if the target query is collecting timing information, SHOW ANALYZE will display it. How does one get the target query to collect timing information? Currently there is one way: if the target is running ANALYZE, it IS collecting timing information. Re-running the previous example:

Connection 1> ANALYZE SELECT ... ;
Connection 2> SHOW ANALYZE FORMAT=JSON FOR <connection_id>;
ANALYZE
{
  "r_query_time_in_progress_ms": 30727,
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "r_loops": 1,
          "rows": 199786,
          "r_rows": 109994,
          "r_table_time_ms": 232.699,
          "r_other_time_ms": 46.355,

ANALYZE prints timing information in members named r_..._time_ms. You can see that, so far, out of 30 seconds, only 232 millisecond were spent in reading the customer table. The bottleneck is elsewhere...

"filtered": 100,
          "r_filtered": 9.085950143,
          "attached_condition": "customer.`status` = 'GOLD'"
        }
      },
      {
        "table": {
          "table_name": "orders",
          "access_type": "ref",
          "possible_keys": ["cust_id"],
          "key": "cust_id",
          "key_length": "5",
          "used_key_parts": ["cust_id"],
          "ref": ["test.customer.cust_id"],
          "r_loops": 9994,
          "rows": 1,
          "r_rows": 99.99779868,
          "r_table_time_ms": 29460.609,
          "r_other_time_ms": 986.842,

29.4 seconds were spent reading the orders table (and 0.986 seconds in processing the obtained rows). Now we can see where the query is spending time.

"filtered": 100,
          "r_filtered": 100
        }
      }
    ]
  }
}

See Also

  • SHOW EXPLAIN command

  • ANALYZE command

  • MDEV-27021: Extend SHOW EXPLAIN to support SHOW ANALYZE [FORMAT=JSON]

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

SHOW AUTHORS

Syntax

SHOW AUTHORS

Description

The SHOW AUTHORS statement displays information about the people who work on MariaDB. For each author, it displays Name, Location, and Comment values. All columns are encoded as latin1.

These include:

  • First the active people in MariaDB are listed.

  • Then the active people in MySQL.

  • Last the people that have contributed to MariaDB/MySQL in the past.

The order is somewhat related to importance of the contribution given to the MariaDB project, but this is not 100% accurate. There is still room for improvement and debate...

Example

SHOW AUTHORS\G
*************************** 1. row ***************************
    Name: Michael (Monty) Widenius
Location: Tusby, Finland
 Comment: Lead developer and main author
*************************** 2. row ***************************
    Name: Sergei Golubchik
Location: Kerpen, Germany
 Comment: Architect, Full-text search, precision math, plugin framework, merges etc
*************************** 3. row ***************************
    Name: Igor Babaev
Location: Bellevue, USA
 Comment: Optimizer, keycache, core work
*************************** 4. row ***************************
    Name: Sergey Petrunia
Location: St. Petersburg, Russia
 Comment: Optimizer
*************************** 5. row ***************************
    Name: Oleksandr Byelkin
Location: Lugansk, Ukraine
 Comment: Query Cache (4.0), Subqueries (4.1), Views (5.0)
*************************** 6. row ***************************
    Name: Timour Katchaounov
Location: Sofia , Bulgaria
 Comment: Optimizer
*************************** 7. row ***************************
    Name: Kristian Nielsen
Location: Copenhagen, Denmark
 Comment: Replication, Async client prototocol, General buildbot stuff
*************************** 8. row ***************************
    Name: Alexander (Bar) Barkov
Location: Izhevsk, Russia
 Comment: Unicode and character sets
*************************** 9. row ***************************
    Name: Alexey Botchkov (Holyfoot)
Location: Izhevsk, Russia
 Comment: GIS extensions, embedded server, precision math
*************************** 10. row ***************************
    Name: Daniel Bartholomew
Location: Raleigh, USA
 Comment: MariaDB documentation, Buildbot, releases
*************************** 11. row ***************************
    Name: Colin Charles
Location: Selangor, Malesia
 Comment: MariaDB documentation, talks at a LOT of conferences
*************************** 12. row ***************************
    Name: Sergey Vojtovich
Location: Izhevsk, Russia
 Comment: initial implementation of plugin architecture, maintained native storage engines (MyISAM, MEMORY, ARCHIVE, etc), rewrite of table cache
*************************** 13. row ***************************
    Name: Vladislav Vaintroub
Location: Mannheim, Germany
 Comment: MariaDB Java connector, new thread pool, Windows optimizations
*************************** 14. row ***************************
    Name: Elena Stepanova
Location: Sankt Petersburg, Russia
 Comment: QA, test cases
*************************** 15. row ***************************
    Name: Georg Richter
Location: Heidelberg, Germany
 Comment: New LGPL C connector, PHP connector
*************************** 16. row ***************************
    Name: Jan Lindström
Location: Ylämylly, Finland
 Comment: Working on InnoDB
*************************** 17. row ***************************
    Name: Lixun Peng
Location: Hangzhou, China
 Comment: Multi Source replication
*************************** 18. row ***************************
    Name: Olivier Bertrand
Location: Paris, France
 Comment: CONNECT storage engine
*************************** 19. row ***************************
    Name: Kentoku Shiba
Location: Tokyo, Japan
 Comment: Spider storage engine, metadata_lock_info Information schema
*************************** 20. row ***************************
    Name: Percona
Location: CA, USA
 Comment: XtraDB, microslow patches, extensions to slow log
*************************** 21. row ***************************
    Name: Vicentiu Ciorbaru
Location: Bucharest, Romania
 Comment: Roles
*************************** 22. row ***************************
    Name: Sudheera Palihakkara
Location: 
 Comment: PCRE Regular Expressions
*************************** 23. row ***************************
    Name: Pavel Ivanov
Location: USA
 Comment: Some patches and bug fixes
*************************** 24. row ***************************
    Name: Konstantin Osipov
Location: Moscow, Russia
 Comment: Prepared statements (4.1), Cursors (5.0), GET_LOCK (10.0)
*************************** 25. row ***************************
    Name: Ian Gilfillan
Location: South Africa
 Comment: MariaDB documentation
*************************** 26. row ***************************
    Name: Federico Razolli
Location: Italy
 Comment: MariaDB documentation Italian translation
*************************** 27. row ***************************
    Name: Guilhem Bichot
Location: Bordeaux, France
 Comment: Replication (since 4.0)
*************************** 28. row ***************************
    Name: Andrei Elkin
Location: Espoo, Finland
 Comment: Replication
*************************** 29. row ***************************
    Name: Dmitri Lenev
Location: Moscow, Russia
 Comment: Time zones support (4.1), Triggers (5.0)
*************************** 30. row ***************************
    Name: Marc Alff
Location: Denver, CO, USA
 Comment: Signal, Resignal, Performance schema
*************************** 31. row ***************************
    Name: Mikael Ronström
Location: Stockholm, Sweden
 Comment: NDB Cluster, Partitioning, online alter table
*************************** 32. row ***************************
    Name: Ingo Strüwing
Location: Berlin, Germany
 Comment: Bug fixing in MyISAM, Merge tables etc
*************************** 33. row ***************************
    Name: Marko Mäkelä
Location: Helsinki, Finland
 Comment: InnoDB core developer
...

See Also

  • SHOW CONTRIBUTORS. This list all members and sponsors of the MariaDB Foundation and other sponsors.

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW BINARY LOGS

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 BINLOG MONITOR privilege.

This statement requires the SUPER privilege and the REPLICATION_CLIENT privilege.

Examples

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 |
+--------------------+-----------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW BINLOG EVENTS

Syntax

SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Description

Shows the events in the binary log. If you do not specify log_name, the first binary log is displayed.

This statement requires the BINLOG MONITOR privilege.

This statement requires the REPLICATION SLAVE privilege.

Example

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)           |
+-------------------------------+-----+-------------------+-----------+-------------+------------------------------------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW MASTER STATUS

Syntax

SHOW BINLOG STATUS
SHOW MASTER STATUS

Description

Provides status information about the binary log files of the primary.

This statement requires the BINLOG MONITOR privilege.

This statement requires the SUPER privilege and the REPLICATION_CLIENT privilege.

To see information about the current GTIDs in the binary log, use thegtid_binlog_pos variable.

Example

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                    |
+--------------------------+

See Also

  • MariaDB replication

  • Using and Maintaining the Binary Log

  • The gtid_binlog_pos variable

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CHARACTER SET

Syntax

SHOW CHARACTER SET
    [LIKE 'pattern' | WHERE expr]

Description

The SHOW CHARACTER SET statement shows all available character sets. 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 Extended SHOW.

The same information can be queried from the Information Schema CHARACTER_SETS table.

See Setting Character Sets and Collations for details on specifying the character set at the server, database, table and column levels.

Examples

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 |
+---------+---------------------------+-------------------+--------+

See Also

  • Supported Character Sets and Collations

  • Setting Character Sets and Collations

  • Information Schema CHARACTER_SETS

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CLIENT_STATISTICS

Syntax

SHOW CLIENT_STATISTICS

Description

The SHOW CLIENT_STATISTICS statement has effectively been replaced by the generic SHOW TABLE STATISTICS statement. The information_schema.CLIENT_STATISTICS table holds statistics about client connections.

The SHOW CLIENT_STATISTICS statement is part of the User Statistics feature. The information_schema.CLIENT_STATISTICS table holds statistics about client connections.

The userstat system variable must be set to 1 to activate this feature. See the User Statistics and information_schema.CLIENT_STATISTICS articles for more information.

Example

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

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

SHOW COLLATION

Syntax

SHOW COLLATION
    [LIKE 'pattern' | WHERE expr]

Description

The output from SHOW COLLATION includes all availablecollations. 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 Extended SHOW.

Similar information, including some extra information, can be queried from the Information Schema COLLATIONS table.

No similar information or extra information can be queried from the Information Schema COLLATIONS table.

See Setting Character Sets and Collations for details on specifying the collation at the server, database, table and column levels.

Examples

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 |
+------------------------------+---------+------+---------+----------+---------+

See Also

  • Supported Character Sets and Collations

  • Setting Character Sets and Collations

  • Information Schema COLLATIONS

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW COLUMNS

Syntax

SHOW [FULL] {COLUMNS | FIELDS} FROM tbl_name [FROM db_name]
    [LIKE 'pattern' | WHERE expr]

Description

SHOW COLUMNS displays information about the columns in a given table. It also works for views. The LIKE clause, if present on its own, indicates which column names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.

If the data types differ from what you expect them to be based on aCREATE TABLE statement, note that MariaDB sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in the Silent Column Changes article.

The FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

You can use db_name.tbl_name as an alternative to thetbl_name FROM db_name syntax. In other words, these two statements are equivalent:

SHOW COLUMNS FROM mytable FROM mydb;
SHOW COLUMNS FROM mydb.mytable;

SHOW COLUMNS displays the following values for each table column:

Field indicates the column name.

Type indicates the column data type.

Collation indicates the collation for non-binary string columns, or NULL for other columns. This value is displayed only if you use the FULL keyword.

The Null field contains YES if NULL values can be stored in the column, NO if not.

The Key field indicates whether the column is indexed:

  • If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, non-unique index.

  • If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

  • If Key is UNI, the column is the first column of a unique-valued index that cannot contain NULL values.

  • If Key is MUL, multiple occurrences of a given value are allowed within the column. The column is the first column of a non-unique index or a unique-valued index that can contain NULL values.

If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is noPRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a compositeUNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

The Default field indicates the default value that is assigned to the column.

The Extra field contains any additional information that is available about a given column.

Value
Description

AUTO_INCREMENT

The column was created with the AUTO_INCREMENT keyword.

PERSISTENT

The column was created with the PERSISTENT keyword.

VIRTUAL

The column was created with the VIRTUAL keyword.

on update CURRENT_TIMESTAMP

The column is a TIMESTAMP column that is automatically updated on INSERT and UPDATE.

Privileges indicates the privileges you have for the column. This value is displayed only if you use the FULL keyword.

Comment indicates any comment the column has. This value is displayed only if you use the FULL keyword.

SHOW FIELDS is a synonym forSHOW COLUMNS. Also DESCRIBE and EXPLAIN can be used as shortcuts.

You can also list a table's columns with:

mariadb-show db_name tbl_name

See the mariadb-show command for more details.

The DESCRIBE statement provides information similar to SHOW COLUMNS. The information_schema.COLUMNS table provides similar, but more complete, information.

The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables.

Examples

SHOW COLUMNS FROM city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
SHOW COLUMNS FROM employees WHERE Type LIKE 'Varchar%';
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| first_name    | varchar(30) | NO   | MUL | NULL    |       |
| last_name     | varchar(40) | NO   |     | NULL    |       |
| position      | varchar(25) | NO   |     | NULL    |       |
| home_address  | varchar(50) | NO   |     | NULL    |       |
| home_phone    | varchar(12) | NO   |     | NULL    |       |
| employee_code | varchar(25) | NO   | UNI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

See Also

  • DESCRIBE

  • mariadb-show

  • SHOW CREATE TABLE

  • SHOW TABLE STATUS

  • SHOW INDEX

  • Extended SHOW

  • Silent Column Changes

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CONTRIBUTORS

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 members and sponsors of the MariaDB Foundation as well as other financial contributors.

Example

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                         |
+---------------------+-------------------------------+-------------------------------------------------------------+

See Also

  • Log of MariaDB contributors.

  • SHOW AUTHORS list the authors of MariaDB (including documentation, QA etc).

  • MariaDB Foundation page on contributing financially.

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE DATABASE

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 sql_quote_show_create server system variable.

Examples

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 */ |
+----------+-----------------------------------------------------------------+

With sql_quote_show_create turned off, the output looks slightly differently:

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

In the DEFAULT clause of a SHOW CREATE statement, numbers are quoted.

See Also

  • CREATE DATABASE

  • ALTER DATABASE

  • Character Sets and Collations

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE EVENT

Syntax

SHOW CREATE EVENT event_name

Description

This statement displays the CREATE EVENT statement that creates a given event, as well as the SQL_MODE that was used when the trigger was created, and the character set used by the connection. To find out which events are present, use SHOW EVENTS.

SHOW CREATE EVENT quotes identifiers according to the value of the sql_quote_show_create system variable.

SHOW CREATE EVENT quotes identifiers according to the value of the sql_quote_show_create system variable. Note, however, that the output of this statement is unreliably affected by the sql_quote_show_create system variable.

The information_schema.EVENTS table provides similar, but more complete, information.

Examples

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

See also

  • Events Overview

  • CREATE EVENT

  • ALTER EVENT

  • DROP EVENT

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE FUNCTION

Syntax

SHOW CREATE FUNCTION func_name

Description

This statement is similar to SHOW CREATE PROCEDURE but for stored functions.

SHOW CREATE FUNCTION quotes identifiers, according to the value of the sql_quote_show_create system variable.

SHOW CREATE FUNCTION quotes identifiers, according to the value of the sql_quote_show_create system variable. However, the output of this statement is unreliably affected by the sql_quote_show_create system variable.

Example

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

See Also

  • Stored Functions

  • CREATE FUNCTION

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE PACKAGE BODY

Syntax

SHOW CREATE PACKAGE BODY  [ db_name . ] package_name

Description

The SHOW CREATE PACKAGE BODY statement shows the CREATE PACKAGE BODY statement that creates the given package body (that is, the implementation of the package).

The SHOW CREATE PACKAGE BODY statement shows the CREATE PACKAGE BODY statement that creates the given package body (that is, the implementation of the package). CREATE PACKAGE BODY can be used when Oracle SQL_MODE is set.

SHOW CREATE PACKAGE BODY quotes identifiers according to the value of the sql_quote_show_create system variable.

Examples

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

See Also

  • CREATE PACKAGE

  • SHOW CREATE PACKAGE

  • DROP PACKAGE

  • CREATE PACKAGE BODY

  • DROP PACKAGE BODY

  • Oracle SQL_MODE

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

SHOW CREATE PACKAGE

Syntax

SHOW CREATE PACKAGE  [ db_name . ] package_name

Description

The SHOW CREATE PACKAGE statement can be used when Oracle SQL_MODE is set. It shows the CREATE statement that creates the given package specification.

SHOW CREATE PACKAGE quotes identifiers according to the value of the sql_quote_show_create system variable.

Examples

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

See Also

  • CREATE PACKAGE

  • DROP PACKAGE

  • CREATE PACKAGE BODY

  • SHOW CREATE PACKAGE BODY

  • DROP PACKAGE BODY

  • Oracle SQL_MODE

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

SHOW CREATE PROCEDURE

Syntax

SHOW CREATE PROCEDURE proc_name

Description

This statement is a MariaDB extension. It returns the exact string that can be used to re-create the named stored procedure, as well as the SQL_MODE that was used when the trigger has been created and the character set used by the connection.. A similar statement, SHOW CREATE FUNCTION, displays information about stored functions.

Both statements require that:

  • you are the owner of the routine;

  • you have the SHOW CREATE ROUTINE privilege (from MariaDB 11.3.0); or

  • you have the SELECT privilege on the mysql.proc table.

When none of the above statements are true, the statements display NULL for the Create Procedure or Create Function field.

Users with SELECT privileges on mysql.proc or USAGE privileges on *.* can view the text of routines, even when they do not have privileges for the function or procedure itself.

SHOW CREATE PROCEDURE quotes identifiers, according to the value of the sql_quote_show_create system variable.

SHOW CREATE PROCEDURE quotes identifiers, according to the value of the sql_quote_show_create system variable. The output of this statement is unreliably affected by the sql_quote_show_create system variable.

Examples

Here's a comparison of the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.

SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: 
    Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
                      BEGIN
                      SELECT COUNT(*) INTO param1 FROM t;
                      END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode:
     Create Function: CREATE FUNCTION `hello`(s CHAR(20))
                      RETURNS CHAR(50)
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

When the user issuing the statement does not have privileges on the routine, attempting to CALL the procedure raises Error 1370.

CALL test.prc1();
Error 1370 (42000): execute command denied to 
  user 'test_user'@'localhost' for routine 'test'.'prc1'

If the user neither has privilege to the routine nor the SELECT privilege on mysql.proc table, it raises Error 1305, informing them that the procedure does not exist.

SHOW CREATE TABLES test.prc1\G
Error 1305 (42000): PROCEDURE prc1 does not exist

See Also

  • Stored Procedure Overview

  • CREATE PROCEDURE

  • ALTER PROCEDURE

  • DROP PROCEDURE

  • SHOW PROCEDURE STATUS

  • Stored Routine Privileges

  • Information Schema ROUTINES Table

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE SEQUENCE

Syntax

SHOW CREATE SEQUENCE sequence_name;

Description

Shows the CREATE SEQUENCE statement that creates the given sequence. The statement requires the SELECT privilege for the table.

SHOW CREATE SEQUENCE quotes identifiers according to the value of the sql_quote_show_create system variable.

Example

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

Notes

If you want to see the underlying table structure used for the SEQUENCE you can use SHOW CREATE TABLE on the SEQUENCE. You can also use SELECT to read the current recorded state of the SEQUENCE:

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

The Information Schema SEQUENCES Table also provides information about available sequences:

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

See Also

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • Information Schema SEQUENCES Table

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

SHOW CREATE SERVER

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 'db1', USER 'maxscale', PASSWORD 'password');

The SHOW CREATE SERVER statement is not available.

See Also

  • CREATE SERVER

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

SHOW CREATE TABLE

Syntax

SHOW CREATE TABLE tbl_name

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 views and SEQUENCE.

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

Certain SQL_MODE 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 NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE flags are used. All MariaDB-specific table attributes are also not shown when a non-MariaDB/MySQL emulation mode is used, which includes ANSI, DB2, POSTGRESQL, MSSQL, MAXDB or ORACLE.

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 IGNORE_BAD_TABLE_OPTIONS SQL_MODE. Remember that replaying a CREATE TABLE statement with uncommented invalid options will fail with an error, unless the IGNORE_BAD_TABLE_OPTIONS SQL_MODE 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 TEXT and BLOB data types to be assigned a DEFAULT 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

  • LONG UNIQUE keys

  • Normal keys

  • Fulltext keys

See sql/sql_table.cc for details.

Examples

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

With sql_quote_show_create off:

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  s char(60) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SQL_MODE impacting the output:

SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

CREATE TABLE `t1` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `msg` varchar(100) DEFAULT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SET SQL_MODE=ORACLE;

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE "t1" (
  "id" int(11) NOT NULL,
  "msg" varchar(100) DEFAULT NULL,
  PRIMARY KEY ("id")

See Also

  • SHOW CREATE SEQUENCE

  • SHOW CREATE VIEW

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE TRIGGER

Syntax

SHOW CREATE TRIGGER trigger_name

Description

This statement shows a CREATE TRIGGER statement that creates the given trigger, as well as the SQL_MODE that was used when the trigger has been created and the character set used by the connection.

The TRIGGER privilege is required on the table the trigger is defined for to execute this statement.

SHOW CREATE TRIGGER quotes identifiers, according to the value of the sql_quote_show_create system variable.

SHOW CREATE TRIGGER quotes identifiers, according to the value of the sql_quote_show_create system variable. However, the output of this statement is unreliably affected by the sql_quote_show_create system variable.

Examples

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

The Created column serves to better view multiple trigger events.

The Created column is unavailable.

See Also

  • Trigger Overview

  • CREATE TRIGGER

  • DROP TRIGGER

  • information_schema.TRIGGERS Table

  • SHOW TRIGGERS

  • Trigger Limitations

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW CREATE USER

Syntax

SHOW CREATE USER [user-name]

Description

Shows the CREATE USER statement that creates the given user. The statement requires the SELECT privilege for the mysql database, except for the current user. The CREATE USER statement for the current user is shown where no user is specified.

SHOW CREATE USER quotes identifiers according to the value of the sql_quote_show_create system variable.

Examples

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'

User Password Expiry:

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 |
+------------------------------------------------------------------+

See Also

  • CREATE USER

  • ALTER USER

  • SHOW GRANTS shows the GRANTS/PRIVILEGES for a user.

  • SHOW PRIVILEGES shows the privileges supported by MariaDB.

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

SHOW CREATE VIEW

Syntax

SHOW CREATE VIEW [view-name]

Description

This statement shows a CREATE VIEW statement that creates the given view, as well as the character set used by the connection when the view was created. This statement also works with views.

SHOW CREATE VIEW quotes table, column and stored function names according to the value of the sql_quote_show_create server system variable.

Examples

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

With sql_quote_show_create off:

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

Grants

To be able to see a view, you need to have the SHOW VIEW and the SELECT privilege on the view:

GRANT SHOW VIEW,SELECT ON test_database.test_view TO 'test'@'localhost';

See Also

  • Grant privileges to tables, views etc

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW DATABASES

Syntax

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

Description

SHOW DATABASES lists the databases on the MariaDB server host.SHOW SCHEMAS is a synonym forSHOW DATABASES. The LIKE clause, if present on its own, indicates which database names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.

You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. You can also get this list using the mariadb-show command.

If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.

The list of results returned by SHOW DATABASES is based on directories in the data directory, which is how MariaDB implements databases. It's possible that output includes directories that do not correspond to actual databases.

The Information Schema SCHEMATA table also contains database information.

Examples

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
SHOW DATABASES LIKE 'm%';
+---------------+
| Database (m%) |
+---------------+
| mysql         |
+---------------+

See Also

  • CREATE DATABASE

  • ALTER DATABASE

  • DROP DATABASE

  • SHOW CREATE DATABASE

  • Character Sets and Collations

  • Information Schema SCHEMATA Table

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW ENGINE INNODB STATUS

Syntax

SHOW ENGINE INNODB STATUS

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 innodb_thread_concurrency 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 change buffer) 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 InnoDB Buffer Pool for more. Similar information is also available from the INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS table.

  • ROW OPERATIONS:Information about the main thread, including the number and performance rate for each type of row operation.

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

Example output:

=====================================
2019-09-06 12:44:13 0x7f93cc236700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 83698 srv_idle
srv_master_thread log flush and writes: 83682
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15
OS WAIT ARRAY INFO: signal count 8
RW-shared spins 0, rounds 20, OS waits 7
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 20.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 236
Purge done for trx's n:o < 236 undo n:o < 0 state: running
History list length 22
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421747401994584, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421747401990328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
286 OS file reads, 171 OS file writes, 22 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 445926
Log flushed up to   445926
Pages flushed up to 445926
Last checkpoint at  445917
0 pending log flushes, 0 pending chkp writes
18 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 50768
Buffer pool size   8012
Free buffers       7611
Database pages     401
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 264, created 137, written 156
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 401, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4267, Main thread ID=140272021272320, state: sleeping
Number of rows inserted 1, updated 0, deleted 0, read 1
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

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

SHOW ENGINE

Syntax

SHOW ENGINE [engine-name] {STATUS | MUTEX}

Description

SHOW ENGINE displays operational information about a storage engine. The following statements currently are supported:

SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE PERFORMANCE_SCHEMA STATUS
SHOW ENGINE ROCKSDB STATUS

If the Sphinx Storage Engine is installed, the following is also supported:

SHOW ENGINE SPHINX STATUS

SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS displays extensive information from the standard InnoDB Monitor about the state of the InnoDB storage engine. See SHOW ENGINE INNODB STATUS for more.

SHOW ENGINE INNODB MUTEX

SHOW ENGINE INNODB MUTEX displays InnoDB mutex statistics.

The statement displays the following output fields:

  • Type: Always InnoDB.

  • Name: The source file where the mutex is implemented, and the line number in the file where the mutex is created. The line number is dependent on the MariaDB version.

  • Status: This field displays the following values if UNIV_DEBUG was defined at compilation time (for example, in include/univ.h in the InnoDB part of the source tree). Only the os_waits value is displayed if UNIV_DEBUG was not defined. Without UNIV_DEBUG, the information on which the output is based is insufficient to distinguish regular mutexes and mutexes that protect rw-locks (which allow multiple readers or a single writer). Consequently, the output may appear to contain multiple rows for the same mutex.

    • count indicates how many times the mutex was requested.

    • spin_waits indicates how many times the spinlock had to run.

    • spin_rounds indicates the number of spinlock rounds. (spin_rounds divided by spin_waits provides the average round count.)

    • os_waits indicates the number of operating system waits. This occurs when the spinlock did not work (the mutex was not locked during the spinlock and it was necessary to yield to the operating system and wait).

    • os_yields indicates the number of times a the thread trying to lock a mutex gave up its timeslice and yielded to the operating system (on the presumption that allowing other threads to run will free the mutex so that it can be locked).

    • os_wait_times indicates the amount of time (in ms) spent in operating system waits, if the timed_mutexes system variable is 1 (ON). If timed_mutexes is 0 (OFF), timing is disabled, so os_wait_times is 0. timed_mutexes is off by default.

Information from this statement can be used to diagnose system problems. For example, large values of spin_waits and spin_rounds may indicate scalability problems.

The information_schema.INNODB_MUTEXES table provides similar information.

SHOW ENGINE PERFORMANCE_SCHEMA STATUS

This statement shows how much memory is used for performance_schema tables and internal buffers.

The output contains the following fields:

  • Type: Always performance_schema.

  • Name: The name of a table, the name of an internal buffer, or the performance_schema word, followed by a dot and an attribute. Internal buffers names are enclosed by parenthesis. performance_schema means that the attribute refers to the whole database (it is a total).

  • Status: The value for the attribute.

The following attributes are shown, in this order, for all tables:

  • row_size: The memory used for an individual record. This value will never change.

  • row_count: The number of rows in the table or buffer. For some tables, this value depends on a server system variable.

  • memory: For tables and performance_schema, this is the result of row_size * row_count.

For internal buffers, the attributes are:

  • count

  • size

SHOW ENGINE ROCKSDB STATUS

See also MyRocks Performance Troubleshooting

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW ENGINES

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 information_schema.ENGINES table provides the same information.

Since storage engines are plugins, different information about them is also shown in the information_schema.PLUGINS table and by the SHOW PLUGINS 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 XtraDB and InnoDB. The same applies to FederatedX.

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.

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

Examples

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)

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW ERRORS

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 SELECT statement.

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

SHOW COUNT(*) ERRORS;
SELECT @@error_count;

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

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

Examples

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 |
+-------+------+---------------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW EVENTS

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:

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

To see the event action, use SHOW CREATE EVENT instead, or look at the information_schema.EVENTS 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:

SHOW EVENTS FROM test;

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 Extended Show.

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW EXPLAIN

Syntax

SHOW EXPLAIN [FORMAT=JSON] FOR <connection_id>;
EXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>;

Description

The SHOW EXPLAIN command allows one to get an EXPLAIN (that is, a description of a query plan) of a query running in a certain connection.

SHOW EXPLAIN FOR <connection_id>;

will produce an EXPLAIN output for the query that connection number connection_id is running. The connection id can be obtained with SHOW PROCESSLIST.

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)

The output is always accompanied with a warning which shows the query the target connection is running (this shows what the EXPLAIN is for):

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

EXPLAIN FOR CONNECTION

The EXPLAIN FOR CONNECTION syntax was added for MySQL compatibility.

FORMAT=JSON

SHOW EXPLAIN [FORMAT=JSON] FOR <connection_id> extends SHOW EXPLAIN to return more detailed JSON output.

EXPLAIN FOR CONNECTION and FORMAT=JSON are not available.

Possible Errors

The output can be only produced if the target connection is currently running a query, which has a ready query plan. If this is not the case, the output will be:

SHOW EXPLAIN FOR 2;
ERROR 1932 (HY000): Target is not running an EXPLAINable command

You will get this error when:

  • The target connection is not running a command for which one can run EXPLAIN;

  • The target connection is running a command for which one can run EXPLAIN, but there is no query plan yet (for example, tables are open and locks are acquired before the query plan is produced).

Differences Between SHOW EXPLAIN and EXPLAIN Outputs

Background

In MySQL, EXPLAIN execution takes a slightly different route from the way the real query (typically the SELECT) is optimized. This is unfortunate, and has caused a number of bugs in EXPLAIN. (For example, see MDEV-326, MDEV-410, and lp:1013343.lp:992942 is not directly about EXPLAIN, but it also would not have existed if MySQL didn't try to delete parts of a query plan in the middle of the query)

SHOW EXPLAIN examines a running SELECT, and hence its output may be slightly different from what EXPLAIN SELECT would produce. We did our best to make sure that either the difference is negligible, or SHOW EXPLAIN's output is closer to reality than EXPLAIN's output.

List of Recorded Differences

  • SHOW EXPLAIN may have Extra='no matching row in const table', where EXPLAIN would produce Extra='Impossible WHERE ...'

  • For queries with subqueries, SHOW EXPLAIN may print select_type==PRIMARY where regular EXPLAIN used to print select_type==SIMPLE, or vice versa.

Required Permissions

Running SHOW EXPLAIN requires the same permissions as running SHOW PROCESSLIST would.

See Also

  • EXPLAIN

  • EXPLAIN ANALYZE, which will perform a query and outputs enhanced EXPLAIN results.

  • SHOW ANALYZE

  • It is also possible to save EXPLAIN into the slow query log.

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

SHOW FUNCTION CODE

Syntax

SHOW FUNCTION CODE func_name

Description

SHOW FUNCTION CODE shows a representation of the internal implementation of the stored function.

It is similar to SHOW PROCEDURE CODE but for stored functions.

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW FUNCTION STATUS

Syntax

SHOW FUNCTION STATUS
    [LIKE 'pattern' | WHERE expr]

Description

This statement is similar to SHOW PROCEDURE STATUS but for stored functions.

The LIKE clause, if present on its own, indicates which function names to match.

The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.

The information_schema.ROUTINES table contains more detailed information.

Examples

Showing all stored functions:

SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

Stored functions whose name starts with 'V':

SHOW FUNCTION STATUS LIKE 'V%' \G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

Stored functions with a security type of 'DEFINER':

SHOW FUNCTION STATUS WHERE Security_type LIKE 'DEFINER' \G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW GRANTS

Syntax

SHOW GRANTS [FOR user|role]

Description

The SHOW GRANTS statement lists privileges granted to a particular user or role.

Users

The statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MariaDB user account. The account is named using the same format as for theGRANT 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 GRANT.

SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

If SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is used in DEFINER context (such as within a stored procedure that is defined withSQL SECURITY DEFINER), the grants displayed are those of the definer and not the invoker.

Roles

SHOW GRANTS can also be used to view the privileges granted to a role.

Example

SHOW GRANTS FOR journalist;
+------------------------------------------+
| Grants for journalist                    |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'journalist'       |
| GRANT DELETE ON `test`.* TO 'journalist' |
+------------------------------------------+

FOR PUBLIC

GRANT ... TO PUBLIC grants privileges to all users. SHOW GRANTS FOR PUBLIC shows all these grants.

SHOW GRANTS FOR public;
+------------------------------------------------+
| Grants for PUBLIC                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON `dev_db`.* TO `PUBLIC` |
+------------------------------------------------+

FOR PUBLIC is not available.

See Also

  • Authentication from MariaDB 10.4

  • SHOW CREATE USER shows how the user was created.

  • SHOW PRIVILEGES shows the privileges supported by MariaDB.

  • Roles

This page is licensed: GPLv2, originally from fill_help_tables.sql

SHOW INDEX_STATISTICS

Syntax

SHOW INDEX_STATISTICS

Description

The information_schema.INDEX_STATISTICS table shows statistics on index usage and makes it possible to do such things as locating unused indexes and generating the commands to remove them.

SHOW INDEX_STATISTICS is replaced by the generic SHOW TABLE STATISTICS statement.

The SHOW INDEX_STATISTICS statement was introduced in MariaDB 5.2 as part of the User Statistics feature. It was removed as a separate statement in MariaDB 10.1.1, but effectively replaced by the generic SHOW TABLE STATISTICS statement.

The userstat system variable must be set to 1 to activate this feature. See the User Statistics and information_schema.INDEX_STATISTICS table for more information.

Example

SHOW INDEX_STATISTICS;
+--------------+-------------------+------------+-----------+
| Table_schema | Table_name        | Index_name | Rows_read |
+--------------+-------------------+------------+-----------+
| test         | employees_example | PRIMARY    |         1 |
+--------------+-------------------+------------+-----------+

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