1 of 65

SHOW Statements

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

About SHOW

Overview of the SHOW statement syntax. Learn how to use patterns and WHERE clauses to filter output from various SHOW statements.

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}

Extended SHOW

Understand the extensions to the SHOW statement. Learn how to use WHERE and LIKE clauses to perform complex filtering on metadata results.

The following statements can be extended using a WHERE clause and a LIKE clause to refine the results:

SHOW AUTHORS

Display a list of major contributors to the MariaDB and MySQL projects. View names, locations, and specific contributions of developers.

Syntax

Description

The SHOW AUTHORS

SHOW BINARY LOGS

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

Syntax

Description

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

SHOW BINLOG EVENTS

Inspect the contents of a binary log file. This statement displays the events within a specific binlog, useful for debugging replication issues.

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

Example

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

SHOW MASTER STATUS

View the current status of the primary server's binary log. This statement returns the current log file name and position for replication synchronization.

Syntax

SHOW BINLOG STATUS

SHOW MASTER STATUS

Description

Provides status information about the files of the primary.

This statement requires the privilege.

This statement requires the privilege and the privilege.

To see information about the current in the binary log, use the variable.

Example

SHOW CHARACTER SET

List available character sets. View the default collation, maximum length, and description for each supported character set.

Syntax

SHOW CHARACTER SET
    [LIKE 'pattern' | WHERE expr]

Description

The SHOW CHARACTER SET statement shows all available . 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 .

The same information can be queried from the table.

See for details on specifying the character set at the server, database, table and column levels.

Examples

SHOW CREATE SERVER

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

Syntax

Description

Shows the statement that created the given server definition.

SHOW PROFILES

Description

The SHOW PROFILES statement displays profiling information that indicates resource usage for statements executed during the course of the current session. It is used together with .

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

SHOW USER_STATISTICS

View statistics on user activity. This statement displays metrics like CPU time and rows read for each user account.

Syntax

Description

The SHOW USER_STATISTICS

SHOW USER_VARIABLES

List defined user variables. This statement displays the names and values of variables set in the current session.

See the for details.

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

SHOW WSREP_STATUS

View Galera Cluster status variables. This statement displays internal status metrics for the wsrep provider.

SHOW WSREP_STATUS is part of the plugin.

Syntax

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

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

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

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

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

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

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

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

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

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');

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

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

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

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

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

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

SHOW EVENTS

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

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

SHOW LOCALES;
+-----+-------+-------------------------------------+------------------------+
| Id  | Name  | Description                         | Error_Message_Language |
+-----+-------+-------------------------------------+------------------------+
|   0 | en_US | English - United States             | english                |
|   1 | en_GB | English - United Kingdom            | english                |
|   2 | ja_JP | Japanese - Japan                    | japanese               |
|   3 | sv_SE | Swedish - Sweden                    | swedish                |
...

SHOW WSREP_STATUS;
+------------+-------------+----------------+--------------+
| Node_Index | Node_Status | Cluster_Status | Cluster_Size |
+------------+-------------+----------------+--------------+
|          0 | Synced      | Primary        |            3 |
+------------+-------------+----------------+--------------+

Name

Location

Comment

latin1

SHOW CREATE DATABASE

SHOW PLUGINS SONAME

List plugins and their library files. View information about installed plugins and their associated shared object names.

Syntax

SHOW PLUGINS SONAME { library | LIKE 'pattern' | WHERE expr };

Description

SHOW PLUGINS SONAME displays information about compiled-in and all server plugins in the directory, including plugins that haven't been installed.

Examples

There is also a corresponding table, called , which contains more complete information.

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

SHOW ERRORS

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

Syntax

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

Description

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

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

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

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

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

Examples

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

SHOW QUERY_RESPONSE_TIME

View the distribution of query execution times. This statement groups queries into time-based buckets to analyze performance.

Syntax

SHOW QUERY_RESPONSE_TIME

Description

It is possible to use SHOW QUERY_RESPONSE_TIME as an alternative for retrieving information from the plugin.

This was introduced as part of the .

Example

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

SHOW PROCEDURE CODE

Display the internal instruction representation of a stored procedure. This debug statement shows the low-level opcodes of the routine.

Syntax

SHOW PROCEDURE CODE proc_name

Description

This statement is a MariaDB extension that is available only for servers that have been built with debugging support. It displays a representation of the internal implementation of the named . A similar statement, , displays information about .

Both statements require that you be the owner of the routine or have access to the table.

If the named routine is available, each statement produces a result set. Each row in the result set corresponds to one "instruction" in the routine. The first column is Pos, which is an ordinal number beginning with 0. The second column is Instruction, which contains an SQL statement (usually changed from the original source), or a directive which has meaning only to the stored-routine handler.

Examples

SHOW TABLE_STATISTICS

View usage statistics for tables. This statement displays metrics like rows read, changed, and updated for each table.

Syntax

SHOW TABLE_STATISTICS

Description

The SHOW TABLE_STATISTICS statement is part of the feature. It was effectively replaced by the generic SHOW TABLE STATISTICS statement. The table shows statistics on table usage.

The SHOW TABLE_STATISTICS statement is part of the feature. The table shows statistics on table usage.

The system variable must be set to 1 to activate this feature. See the and articles for more information.

Example

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

SHOW WSREP_MEMBERSHIP

View Galera Cluster membership. This statement displays the UUID, name, and address of nodes in the cluster.

SHOW WSREP_MEMBERSHIP is part of the WSREP_INFO plugin.

Syntax

SHOW WSREP_MEMBERSHIP

Description

The SHOW WSREP_MEMBERSHIP statement returns node cluster membership information. It returns the same information as found in the table. Only users with the privilege can access this information.

Examples

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

SHOW CREATE PACKAGE

View the CREATE PACKAGE statement. This statement returns the interface definition of a stored package in MariaDB.

Syntax

SHOW CREATE PACKAGE  [ db_name . ] package_name

Description

The SHOW CREATE PACKAGE statement can be used when is set. It shows the CREATE statement that creates the given package specification.

SHOW CREATE PACKAGE quotes identifiers according to the value of the system variable.

Examples

SHOW COLUMNS

Display information about columns in a table. View field names, types, default values, and other attributes for a specific table.

Syntax

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 .

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

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 and can be used as shortcuts.

You can also list a table's columns with:

See the command for more details.

The statement provides information similar to SHOW COLUMNS. The table provides similar, but more complete, information.

The , , and statements also provide information about tables.

Examples

SHOW ENGINES

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

Syntax

SHOW [STORAGE] ENGINES

Description

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

The table provides the same information.

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

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

The output consists of the following columns:

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

Examples

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

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

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

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

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

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

SHOW PRIVILEGES;
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Privilege                | Context                               | Comment                                                            |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
| Alter                    | Tables                                | To alter the table                                                 |
| Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |
| Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |
| Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |
| Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |
| Create view              | Tables                                | To create new views                                                |
| Create user              | Server Admin                          | To create new users                                                |
| Delete                   | Tables                                | To delete existing rows                                            |
| Delete history           | Tables                                | To delete versioning table historical rows                         |
| Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |
| Event                    | Server Admin                          | To create, alter, drop and execute events                          |
| Execute                  | Functions,Procedures                  | To execute stored routines                                         |
| File                     | File access on server                 | To read and write files on the server                              |
| Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |
| Index                    | Tables                                | To create or drop indexes                                          |
| Insert                   | Tables                                | To insert data into tables                                         |
| Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |
| Process                  | Server Admin                          | To view the plain text of currently executing queries              |
| Proxy                    | Server Admin                          | To make proxy user possible                                        |
| References               | Databases,Tables                      | To have references on tables                                       |
| Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |
| Binlog admin             | Server                                | To purge binary logs                                               |
| Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |
| Binlog replay            | Server                                | To use BINLOG (generated by mariadb-binlog)                        |
| Replication master admin | Server                                | To monitor connected slaves                                        |
| Replication slave admin  | Server                                | To start/stop slave and apply binlog events                        |
| Slave monitor            | Server                                | To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS                  |
| Replication slave        | Server Admin                          | To read binary log events from the master                          |
| Select                   | Tables                                | To retrieve rows from table                                        |
| Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |
| Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |
| Shutdown                 | Server Admin                          | To shut down the server                                            |
| Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |
| Trigger                  | Tables                                | To use triggers                                                    |
| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |
| Update                   | Tables                                | To update existing rows                                            |
| Set user                 | Server                                | To create views and stored routines with a different definer       |
| Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |
| Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |
| Read_only admin          | Server                                | To perform write operations even if @@read_only=ON                 |
| Usage                    | Server Admin                          | No privileges - allow connect only                                 |
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
41 rows in set (0.000 sec)

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

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

SHOW FULL TABLES;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| s1             | SEQUENCE   |
| student        | BASE TABLE |
| v1             | VIEW       |
+----------------+------------+

CREATE TABLE t (t INT(11));
CREATE TEMPORARY TABLE t (t INT(11));
CREATE TEMPORARY TABLE te (t INT(11));

SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+

CREATE TABLE t (t INT(11));
CREATE TEMPORARY TABLE t (t INT(11));
CREATE TEMPORARY TABLE te (t INT(11));

SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| te             |
| t              |
| t              |
+----------------+

SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             | Progress |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+
|  2 | event_scheduler | localhost | NULL | Daemon  | 2693 | Waiting on empty queue | NULL             |    0.000 |
|  4 | root            | localhost | NULL | Query   |    0 | Table lock             | SHOW PROCESSLIST |    0.000 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+

SHOW triggers Like 'animals' \G
*************************** 1. row ***************************
             Trigger: the_mooses_are_loose
               Event: INSERT
               Table: animals
           Statement: BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END
              Timing: AFTER
             Created: 2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW TRIGGERS FROM test WHERE `Table` = 'user' \G
*************************** 1. row ***************************
             Trigger: user_ai
               Event: INSERT
               Table: user
           Statement: BEGIN END
              Timing: AFTER
             Created:  2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW triggers WHERE Event Like 'Insert' \G
*************************** 1. row ***************************
             Trigger: the_mooses_are_loose
               Event: INSERT
               Table: animals
           Statement: BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END
              Timing: AFTER
             Created: 2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

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

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

SHOW VARIABLES LIKE 'aria%';
+------------------------------------------+---------------------+
| Variable_name                            | Value               |
+------------------------------------------+---------------------+
| aria_block_size                          | 8192                |
| aria_checkpoint_interval                 | 30                  |
| aria_checkpoint_log_activity             | 1048576             |
| aria_force_start_after_recovery_failures | 0                   |
| aria_group_commit                        | none                |
| aria_group_commit_interval               | 0                   |
| aria_log_file_size                       | 1073741824          |
| aria_log_purge_type                      | immediate           |
| aria_max_sort_file_size                  | 9223372036853727232 |
| aria_page_checksum                       | ON                  |
| aria_pagecache_age_threshold             | 300                 |
| aria_pagecache_buffer_size               | 134217728           |
| aria_pagecache_division_limit            | 100                 |
| aria_recover                             | NORMAL              |
| aria_repair_threads                      | 1                   |
| aria_sort_buffer_size                    | 134217728           |
| aria_stats_method                        | nulls_unequal       |
| aria_sync_log_dir                        | NEWFILE             |
| aria_used_for_temp_tables                | ON                  |
+------------------------------------------+---------------------+

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
  INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
  VARIABLE_NAME LIKE 'max_error_count' OR
  VARIABLE_NAME LIKE 'innodb_sync_spin_loops';
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 64            | 64           |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

SET GLOBAL max_error_count=128;

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
  INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
  VARIABLE_NAME LIKE 'max_error_count' OR
  VARIABLE_NAME LIKE 'innodb_sync_spin_loops';
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 64            | 128          |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

SET GLOBAL max_error_count=128;

SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+

SHOW GLOBAL VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 128   |
+-----------------+-------+

SHOW VARIABLES LIKE 'innodb_sync_spin_loops';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_sync_spin_loops | 30    |
+------------------------+-------+

SELECT 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+

SHOW COUNT(*) WARNINGS;
+-------------------------+
| @@session.warning_count |
+-------------------------+
|                       1 |
+-------------------------+

SHOW WARNINGS;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+

DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
  BEGIN
    DECLARE c CURSOR FOR SELECT * FROM not_existing;
    OPEN c;
    CLOSE c;
  END;
$$
CREATE OR REPLACE PROCEDURE p2()
  BEGIN
    CALL p1;
  END;
$$
DELIMITER ;
CALL p2;
ERROR 1146 (42S02): Table 'test.not_existing' doesn't exist

SHOW WARNINGS;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Error | 1146 | Table 'test.not_existing' doesn't exist |
| Note  | 4091 | At line 6 in test.p1                    |
| Note  | 4091 | At line 4 in test.p2                    |
+-------+------+-----------------------------------------+

SHOW ENGINE INNODB STATUS

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

Syntax

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

The following sections are displayed

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

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

Example output:

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

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

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

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\G
*************************** 1. row ***************************
        Table: employees_example
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 2. row ***************************
        Table: employees_example
   Non_unique: 0
     Key_name: employee_code
 Seq_in_index: 1
  Column_name: employee_code
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 3. row ***************************
        Table: employees_example
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 1
  Column_name: first_name
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 4. row ***************************
        Table: employees_example
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 2
  Column_name: last_name
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO

SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: bus_routes
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-05-24 11:17:46
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

SHOW ANALYZE

Retrieve runtime statistics for a currently executing query. This statement provides insights into query plan execution without waiting for completion.

SHOW ANALYZE was added in MariaDB 10.9.

Syntax

Description

SHOW ANALYZE allows one to retrieve -like output from a currently running statement. The statement

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

The output of this query looks like this:

We run the SELECT, and it has been running for 30 seconds. Let's try SHOW ANALYZE:

The statement shows how long the query has been running.

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

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:

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:

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

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.

SHOW REPLICA STATUS

View the status of replication threads. This statement displays critical metrics like log positions and error status for the replica.

Syntax

Description

This statement is to be run on a replica and provides status information on essential parameters of the threads.

This statement requires the privilege.

Multi-Source

The ALL and "connection_name" options allow you to connect to .

ALL REPLICAS gives you a list of all connections to the primary nodes.

ALL SLAVES gives you a list of all connections to the primary nodes.

The rows are sorted according to Connection_name.

If you specify a connection_name, you only get the information about that connection. If connection_name is not used, then the name set by default_master_connection is used. If the connection name doesn't exist you will get an error:There is no master connection for 'xxx'.

MariaDB starting with

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

Column Descriptions

The order in which the columns appear depends on the MariaDB version. This means that extracting a column value is best done by comparing the field name instead of using a fixed offset into the row.

These columns can also be viewed/extracted from the table.

These columns cannot be viewed/extracted from the table.

Connection_name: Name of the primary connection. Returned with SHOW ALL REPLICAS STATUS only.

Slave_SQL_State: State of SQL thread. Returned with SHOW ALL REPLICAS STATUSonly. See . Slave_IO_State: State of I/O thread. See .

Connection_name: Name of the primary connection. Returned with SHOW ALL SLAVES STATUS only.

Slave_SQL_State: State of SQL thread. Returned with SHOW ALL SLAVES STATUS only. See . Slave_IO_State: State of I/O thread. See .

Master_host: Master host that the replica is connected to.

Master_user: Account user name being used to connect to the primary.

Master_port: The port being used to connect to the primary.

Connect_Retry: Time in seconds between retries to connect. The default is 60. The statement can set this. The option determines the maximum number of reconnection attempts.

Master_Log_File: Name of the primary file that the I/O thread is currently reading from.

Read_Master_Log_Pos: Position up to which the I/O thread has read in the current primary file.

Relay_Log_File: Name of the relay log file that the SQL thread is currently processing.

Relay_Log_Pos: Position up to which the SQL thread has finished processing in the current relay log file.

Relay_Master_Log_File: Name of the primary file that contains the most recent event executed by the SQL thread.

Slave_IO_Running: Whether the replica I/O thread is running and connected (Yes), running but not connected to a primary (Connecting) or not running (No).

Slave_SQL_Running: Whether or not the SQL thread is running.

Replicate_Do_DB: Databases specified for replicating with the option.

Replicate_Ignore_DB: Databases specified for ignoring with the option.

Replicate_Do_Table: Tables specified for replicating with the option.

Replicate_Ignore_Table: Tables specified for ignoring with the option.

Replicate_Wild_Do_Table: Tables specified for replicating with the option.

Replicate_Wild_Ignore_Table: Tables specified for ignoring with the option.

Last_Errno: Alias for Last_SQL_Errno (see below)

Last_Error: Alias for Last_SQL_Error (see below)

Skip_Counter: Number of events that a replica skips from the master, as recorded in the system variable.

Exec_Master_Log_Pos: Position up to which the SQL thread has processed in the current master file. Can be used to start a new replica from a current replica with the option.

Relay_Log_Space: Total size of all relay log files combined.

Until_Condition: One of four possible values: None, Master, Relay, or Gtid, depending on the respective condition.

Until_Log_File: The MASTER_LOG_FILE value of the condition.

Until_Log_Pos: The MASTER_LOG_POS value of the condition.

Master_SSL_Allowed: Whether an SSL connection is permitted (Yes), not permitted (No) or permitted but without the replica having SSL support enabled (Ignored)

Master_SSL_CA_File: The MASTER_SSL_CA option of the statement.

Master_SSL_CA_Path: The MASTER_SSL_CAPATH option of the statement.

Master_SSL_Cert: The MASTER_SSL_CERT option of the statement.

Master_SSL_Cipher: The MASTER_SSL_CIPHER option of the statement.

Master_SSL_Key: The MASTER_SSL_KEY option of the statement.

Seconds_Behind_Master: Difference between the timestamp logged on the master for the event that the replica is currently processing, and the current timestamp on the replica. Zero if the replica is not currently processing an event. With serial replication, seconds_behind_master is updated when the SQL thread begins executing a transaction. With , seconds_behind_master is updated only after transactions commit. An exception is thrown on the parallel replica to additionally update seconds_behind_master when the first transaction received after idling is queued to a worker for execution, to provide a reliable initial value for the duration until a transaction commits.

Additional behavior to be aware of:

Seconds_Behind_Master will update for ignored events, e.g. those skipped due to .

Master_SSL_Verify_Server_Cert: The MASTER_SSL_VERIFY_SERVER_CERT option of the statement.

Last_IO_Errno: Error code of the most recent error that caused the I/O thread to stop (also recorded in the replica's error log). 0 means no error. or will reset this value.

Last_IO_Error: Error message of the most recent error that caused the I/O thread to stop (also recorded in the replica's error log). An empty string means no error. or will reset this value.

Last_SQL_Errno: Error code of the most recent error that caused the SQL thread to stop (also recorded in the replica's error log). 0 means no error. or will reset this value.

Last_SQL_Error: Error message of the most recent error that caused the SQL thread to stop (also recorded in the replica's error log). An empty string means no error. or will reset this value.

Replicate_Ignore_Server_Ids: List of that are currently being ignored for replication purposes, or an empty string for none, as specified in the IGNORE_SERVER_IDS option of the statement.

Master_Server_Id: The master's value.

Master_SSL_Crl: The MASTER_SSL_CRL option of the statement.

Master_SSL_Crlpath: The MASTER_SSL_CRLPATH option of the statement.

Using_Gtid: Whether or not are being used for replication (can be No, Slave_Pos, or Current_Pos).

Gtid_IO_Pos: Current value.

Replicate_Do_Domain_Ids: List of that are currently being recognized for replication purposes, or an empty string for none, as specified in the DO_DOMAIN_IDS option of the statement.

Replicate_Ignore_Domain_Ids: List of that are currently being ignored for replication purposes, or an empty string for none, as specified in the IGNORE_DOMAIN_IDS option of the statement.

Parallel_Mode: The as configured by the system variable.

SQL_Delay: Value specified by MASTER_DELAY in (or 0 if none).

SQL_Remaining_Delay: When the replica is delaying the execution of an event due to MASTER_DELAY, this is the number of seconds of delay remaining before the event will be applied. Otherwise, the value is NULL.

Slave_SQL_Running_State: The state of the SQL driver threads, same as in . When the replica is delaying the execution of an event due to MASTER_DELAY, this field displays: "Waiting until MASTER_DELAY seconds after master executed event".

Slave_DDL_Groups: This status variable counts the occurrence of DDL statements. This is a replica-side counter for optimistic parallel replication.

Slave_Non_Transactional_Groups: This status variable counts the occurrence of non-transactional event groups. This is a replica-side counter for optimistic parallel replication.

Slave_Transactional_Groups: This status variable counts the occurrence of transactional event groups. This is a replica-side counter for optimistic parallel replication.

Replicate_Rewrite_DB: Databases specified for replicating and with the system variable/option.

Replica_Rewrite_DB is not available.

Retried_transactions: The number of times that replicating transactions have been retried due to temporary errors (e.g. deadlocks in parallel replication). Returned with SHOW ALL SLAVES STATUS only.

Max_relay_log_size: Max size for this connection. Returned with SHOW ALL SLAVES STATUS only.

Executed_log_entries: Number of binary log events that have been executed, irrespective of error outcome (i.e. if the event execution results in an error, this number will still increase). Returned with SHOW ALL SLAVES STATUS only.

Slave_received_heartbeats: Number of that the slave has received. Note this counter does not reset when the slave is restarted; only when a new command has executed. Returned with SHOW ALL SLAVES STATUS only.

Slave_heartbeat_period: Configured (by ) interval in seconds between replication heartbeats. Returned with SHOW ALL SLAVES STATUS only.

Gtid_Slave_Pos: The value of the global variable , i.e. the GTID of the last event group replicated on a replica server, for each replication domain, as stored in the system variable. Returned with SHOW ALL SLAVES STATUS only.

Master_last_event_time: Timestamp of the last event read from the primary by the IO thread. NULL until the replica has started and the replica has read one query event from the primary that changes data.

Slave_last_event_time: Primary timestamp of the last event committed on the replica. NULL until the replica has started and the replica has read one query event from the primary that changes data.

Master_Slave_time_diff: The difference of the above two timestamps. NULL until the replica has started and the replica has read one query event from the primary that changes data.

Master_last_event_time, Slave_last_event_time, and Master_Slave_time_diff are not available.

Connects_Tried: The number of attempts done to connect to the primary. It starts from 0 with (but not ), or , and increments after each connection attempt until one succeeds or, after this reaches Master_Retry_Count, aborts the connection.

Master_Retry_Count: The limit to Connects_Tried as configured by .

Connects_Tried: and Master_Retry_Count: are not available. If the Performance Schema is enabled, has CONNECTION_RETRY_COUNT available as an older alternative to Master_Retry_Count.

Examples

If you issue this statement using the client, you can use a \G statement terminator rather than a semicolon to obtain a more readable vertical layout.

You can also access some of the variables directly from status variables:

SHOW Statements

About SHOW

Extended SHOW

SHOW AUTHORS

Syntax

Description

SHOW BINARY LOGS

Syntax

Description

SHOW BINLOG EVENTS

Syntax

Description

Example

SHOW MASTER STATUS

Syntax

Description

Example

See Also

SHOW CHARACTER SET

Syntax

Description

Examples

See Also

SHOW CLIENT_STATISTICS

Syntax

Description

SHOW CONTRIBUTORS

Syntax

Description

SHOW CREATE DATABASE

Syntax

Description

SHOW CREATE EVENT

Syntax

Description

SHOW CREATE FUNCTION

Syntax

Description

SHOW CREATE SERVER

SHOW CREATE USER

Syntax

Description

SHOW EVENTS

Syntax

Description

SHOW FUNCTION CODE

Syntax

Description

SHOW INNODB STATUS (removed)

Syntax

Description

SHOW LOCALES

Syntax

Description

SHOW PROFILES

Syntax

Description

SHOW USER_STATISTICS

Syntax

Description

SHOW USER_VARIABLES

SHOW WSREP_STATUS

Syntax

SHOW Statements

SHOW CHARACTER SET

Syntax

Description

Examples

See Also

About SHOW

SHOW BINLOG EVENTS

Syntax

Description

Example

SHOW BINARY LOGS

Syntax

Description

SHOW CLIENT_STATISTICS

Syntax

Description