8. SHOW Statements – MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds

© Jesper Wisborg Krogh 2020
J. W. KroghMySQL 8 Query Performance Tuninghttps://doi.org/10.1007/978-1-4842-5584-1_8

8. SHOW Statements

Jesper Wisborg Krogh1 
(1)
Hornsby, NSW, Australia
 

The SHOW statements are the good old workhorse in MySQL for database administrators to obtain information about the schema objects and what happens on the system. While today most of the information can be found in the Information Schema or Performance Schema, the SHOW command is still very popular for interactive use due to its short syntax.

Tip

It is recommended to query the underlying Information Schema views and Performance Schema tables. This particularly applies to noninteractive access to the data. Querying the underlying sources is also more powerful as it allows you to join to other views and tables.

This chapter starts out with an overview of how the SHOW statements match up with the Information Schema views and Performance Schema tables. The remainder of the chapter covers SHOW statements that do not have views or tables in the Information Schema and Performance Schema including obtaining engine status information with a little more in-depth view of the InnoDB monitor output provided by the SHOW ENGINE INNODB STATUS statement as well as getting replication and binary log information.

Relationship to the Information Schema

For the SHOW statements returning information about the schema objects or privileges, the same information that can be found in the Information Schema. Table 8-1 lists the SHOW statements that get the information from Information Schema views and which views the information can be found in.
Table 8-1

Correlation between SHOW statements and the Information Schema

SHOW Statement

I_S Views

Comments

CHARACTER SET

CHARACTER_SETS

 

COLLATION

COLLATIONS

 

COLUMNS

COLUMNS

 

CREATE DATABASE

SCHEMATA

 

CREATE EVENT

EVENTS

 

CREATE FUNCTION

ROUTINES

ROUTINE_TYPE = 'FUNCTION'

CREATE PROCEDURE

ROUTINES

ROUTINE_TYPE = 'PROCEDURE'

CREATE TABLE

TABLES

 

CREATE TRIGGER

TRIGGERS

 

CREATE VIEW

VIEWS

 

DATABASES

SCHEMATA

 

ENGINES

ENGINES

 

EVENTS

EVENTS

 

FUNCTION STATUS

ROUTINES

ROUTINE_TYPE = 'FUNCTION'

GRANTS

COLUMN_PRIVILEGES

SCHEMA_PRIVILEGES

TABLE_PRIVILEGES

USER_PRIVILEGES

 

INDEX

STATISTICS

SHOW INDEXES and SHOW INDEXES are synonyms for SHOW INDEX.

PLUGINS

PLUGINS

 

PROCEDURE STATUS

ROUTINES

ROUTINE_TYPE = 'PROCEDURE'

PROCESSLIST

PROCESSLIST

It is recommended to use performance_schema.threads instead.

PROFILE

PROFILING

Deprecated – use the Performance Schema instead.

PROFILES

PROFILING

Deprecated – use the Performance Schema instead.

TABLE STATUS

TABLES

 

TABLES

TABLES

 

TRIGGERS

TRIGGERS

 

The information will not always be identical between the SHOW statement and the corresponding Information Schema views. In some cases, there is more information available using the views, and in general the views are more flexible.

There are also several SHOW statements where the underlying data can be found in the Performance Schema.

Relationship to the Performance Schema

After the Performance Schema was introduced, some of the information that was originally placed in the Information Schema has been moved to the Performance Schema where it logically belongs. That is also reflected in the relationship to the SHOW statements where there are now several tables as shown in Table 8-2 that get their data from Performance Schema tables.
Table 8-2

Correlation between SHOW statements and the Performance Schema

SHOW Statement

Performance Schema Tables

MASTER STATUS

log_status

SLAVE STATUS

log_status

replication_applier_configuration

replication_applier_filters

replication_applier_global_filters

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_status_by_worker

replication_connection_configuration

replication_connection_status

STATUS

global_status

session_status

events_statements_summary_global_by_event_name

events_statements_summary_by_thread_by_event_name

VARIABLES

global_variables

session_variables

The SHOW MASTER STATUS includes information about what filtering is enabled when writing events to the binary log. This information is not available from the Performance Schema, so if you are using the binlog-do-db or binlog-ignore-db option (not recommended as they can prevent point-in-time recoveries), then you still need to use SHOW MASTER STATUS.

There are a few columns in the SHOW SLAVE STATUS output that cannot be found in the Performance Schema tables. Some of those can be found in the slave_master_info and slave_relay_log_info tables in the mysql schema (if master_info_repository and relay_log_info_repository have been set to TABLE which is the default).

For SHOW STATUS and SHOW VARIABLES, one difference is that the SHOW statements returning session scope values will include the global values if there is no session value. When querying the session_status and session_variables, only the values belonging to the requested scope are returned. Additionally, the SHOW STATUS statement includes the Com_% counters, whereas when querying the Performance Schema directly, these counters correspond to events in the events_statements_summary_global_by_event_name and events_statements_summary_by_thread_by_event_name tables (depending on whether the global or session scope is queried).

There are also some SHOW statements that do not have any corresponding tables. The first group of these that will be discussed is for the engine status.

Engine Status

The SHOW ENGINE statement can be used to get storage engine–specific information. It is currently implemented for the InnoDB, Performance_Schema, and NDBCluster engines. For all three engines, it is possible to request the status, and for the InnoDB engine, it is also possible to get mutex information.

The SHOW ENGINE PERFORMANCE_SCHEMA STATUS statement can be useful to get some status information about the Performance Schema including the size of the tables and their memory usage. (The memory usage can also be obtained from the memory instrumentation.)

By far, the most used engine status statement is SHOW ENGINE INNODB STATUS which provides a comprehensive report called the InnoDB monitor report which includes some information that cannot be obtained from other sources. The rest of this section introduces the InnoDB monitor report.

Tip

You can also make InnoDB output the monitor report to the error log at regular intervals by enabling the innodb_status_output system variable. When the innodb_status_output_locks option is set, the InnoDB monitor (whether generated because of innodb_status_output = ON or using SHOW ENGINE INNODB STATUS) includes additional lock information.

The InnoDB monitor report starts out with the header and a note saying how long the averages cover:
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-09-14 19:52:40 0x6480 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
The report itself is divided into several sections, including
  • BACKGROUND THREAD: The work done by the main background thread.

  • SEMAPHORES: Semaphore statistics. The section is most important in cases where contention causes long semaphore waits in which case the section can be used to get information about the locks and who holds them.

  • LATEST FOREIGN KEY ERROR: If a foreign key error has been encountered, this section includes details of the error. Otherwise, the section is omitted.

  • LATEST DETECTED DEADLOCK: If a deadlock has occurred, this section includes details of the two transactions and the locks that caused the deadlock. Otherwise, the section is omitted.

  • TRANSACTIONS: Information about the InnoDB transactions. Only transactions that have modified InnoDB tables are included. If the innodb_status_output_locks option is enabled, the locks held for each transaction are listed; otherwise, it is just locks involved in lock waits. It is in general better to use the information_schema.INNODB_TRX view to query the transaction information and for lock information to use the performance_schema.DATA_LOCKS and performance_schema.DATA_LOCK_WAITS tables.

  • FILE I/O: Information about the I/O threads used by InnoDB including the insert buffer thread, log thread, read threads, and write threads.

  • INSERT BUFFER AND ADAPTIVE HASH INDEX: Information about the change buffer (this was formerly called the insert buffer) and the adaptive hash index.

  • LOG: Information about the redo log.

  • BUFFER POOL AND MEMORY: Information about the InnoDB buffer pool. This information is better obtained from the information_schema.INNODB_BUFFER_POOL_STATS view.

  • INDIVIDUAL BUFFER POOL INFO: If innodb_buffer_pool_instances is greater than 1, this section includes information about the individual buffer pool instances with the same information as for the global summary in the previous section. Otherwise, the section is omitted. This information is better obtained from the information_schema.INNODB_BUFFER_POOL_STATS view.

  • ROW OPERATIONS: This section shows various information about InnoDB including the current activity, what the main thread is doing, and the row activity for inserts, updates, deletes, and reads.

Several of the sections will be used in later chapters when their content is used to analyze performance or lock problems.

Replication and Binary Logs

The SHOW statements have always been important when working with replication. While the Performance Schema replication tables have now largely replaced the SHOW SLAVE STATUS and SHOW MASTER STATUS statements, if you want to see which replicas are connected and inspect events in the binary log or relay log from inside MySQL, then you still need to use SHOW statements.

Listing Binary Logs

The SHOW BINARY LOGS statement is useful to check which binary logs exist. This can be useful if you want to know how much space the binary logs occupy, whether they are encrypted, and for position-based replication whether the logs required by a replica still exist.

An example of what the output can look like is
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000044 |      2616 | No        |
| binlog.000045 |       886 | No        |
| binlog.000046 |       218 | No        |
| binlog.000047 |       218 | No        |
| binlog.000048 |       218 | No        |
| binlog.000049 |       575 | No        |
+---------------+-----------+-----------+
6 rows in set (0.0018 sec)

The Encrypted column was added in MySQL 8.0.14 together with the support for encrypted binary logs.

In general, the file size will be larger than in the example as the autorotation of the binary log files happens when the size exceeds max_binlog_size (defaults to 1 GiB) after writing a transaction. Since transactions are not split between files, if you have large transactions, the file can become somewhat larger than max_binlog_size.

Viewing Log Events

The SHOW BINLOG EVENTS and SHOW RELAYLOG EVENTS statements read the binary log and relay log, respectively, and return the events matching the arguments. There are four arguments, of which one only applies to relay log events:
  • IN: The name of the binary log or relay log file to read events from.

  • FROM: The position in bytes to start reading from.

  • LIMIT: The number of events to include with an optional offset. The syntax is the same as for SELECT statements: [offset], row_count.

  • FOR CHANNEL: For relay logs, the replication channel to read events for.

All arguments are optional. If the IN argument is not given, events from the first log are returned. An example of using SHOW BINLOG EVENTS is shown in Listing 8-1. If you want to try the example, you will need to replace the binary log filename, position, and limit.
mysql> SHOW BINLOG EVENTS IN 'binlog.000049' FROM 195 LIMIT 5\G
*************************** 1. row ***************************
   Log_name: binlog.000049
        Pos: 195
 Event_type: Gtid
  Server_id: 1
End_log_pos: 274
       Info: SET @@SESSION.GTID_NEXT= '4d22b3e5-a54f-11e9-8bdb-ace2d35785be:603'
*************************** 2. row ***************************
   Log_name: binlog.000049
        Pos: 274
 Event_type: Query
  Server_id: 1
End_log_pos: 372
       Info: BEGIN
*************************** 3. row ***************************
   Log_name: binlog.000049
        Pos: 372
 Event_type: Table_map
  Server_id: 1
End_log_pos: 436
       Info: table_id: 89 (world.city)
*************************** 4. row ***************************
   Log_name: binlog.000049
        Pos: 436
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 544
       Info: table_id: 89 flags: STMT_END_F
*************************** 5. row ***************************
   Log_name: binlog.000049
        Pos: 544
 Event_type: Xid
  Server_id: 1
End_log_pos: 575
       Info: COMMIT /* xid=44 */
5 rows in set (0.0632 sec)
Listing 8-1

Using SHOW BINLOG EVENTS

The example illustrates some of the limitations of using SHOW statements to inspect binary and relay logs. The result is a normal result set from a query, and since the files typically are around 1 GiB in size, it means the result can be equally large. You can do as in the example where only specific events are chosen, but it is not always trivial to know where the interesting events start, and you cannot filter by the event types or which tables they affect. Finally, the default event format (the binlog_format option) is the row format, and as it can be seen from the third and fourth rows in the result, all you can see from SHOW BINGOG EVENTS is that the transaction updated the world.city table. You cannot see which rows were updated and what the values are.

In practice, if you have access to the file system, it is in most cases better to use the mysqlbinlog utility that is shipped with MySQL. (The SHOW BINLOG EVENTS and SHOW RELAYLOG EVENTS statements can still be useful in controlled testing or when replication stops and you quickly want to inspect the event that caused the error.) The equivalent command using the mysqlbinlog utility to the previous SHOW BINLOG EVENTS statement is shown in Listing 8-2. That example also uses the verbose flag to show the before and after images of the row-based event that updates the world.city table.
shell> mysqlbinlog -v --base64-output=decode-rows --start-position=195 --stop-position=575 binlog.000049
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 124
#190914 20:38:43 server id 1  end_log_pos 124 CRC32 0x751322a6  Start: binlog v 4, server v 8.0.18 created 190914 20:38:43 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 195
#190915 10:18:45 server id 1  end_log_pos 274 CRC32 0xe1b8b9a1  GTID    last_committed=0        sequence_number=1       rbr_only=yes    original_committed_timestamp=1568506725779031   immediate_commit_timestamp=1568506725779031     transaction_length=380
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1568506725779031 (2019-09-15 10:18:45.779031 AUS Eastern Standard Time)
# immediate_commit_timestamp=1568506725779031 (2019-09-15 10:18:45.779031 AUS Eastern Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1568506725779031*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= '4d22b3e5-a54f-11e9-8bdb-ace2d35785be:603'/*!*/;
# at 274
#190915 10:18:45 server id 1  end_log_pos 372 CRC32 0x2d716bd5  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1568506725/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 372
#190915 10:18:45 server id 1  end_log_pos 436 CRC32 0xb62c64d7  Table_map: `world`.`city` mapped to number 89
# at 436
#190915 10:18:45 server id 1  end_log_pos 544 CRC32 0x62687b0b  Update_rows: table id 89 flags: STMT_END_F
### UPDATE `world`.`city`
### WHERE
###   @1=130
###   @2='Sydney'
###   @3='AUS'
###   @4='New South Wales'
###   @5=3276207
### SET
###   @1=130
###   @2='Sydney'
###   @3='AUS'
###   @4='New South Wales'
###   @5=3276208
# at 544
#190915 10:18:45 server id 1  end_log_pos 575 CRC32 0x149e2b5c  Xid = 44
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Listing 8-2

Inspecting the binary log using the mysqlbinlog utility

The -v arguments request verbose mode and can be given up to two times to increase the amount of information included. A single -v is what generates the comment with the pseudo query in the event starting at position 436. The --base64-output=decode-rows argument tells mysqlbinlog not to include a base64 encoded version of the events in row format. The --start-position and --stop-position arguments specify the start and stop offsets in bytes.

The most interesting event in the transaction is the one starting with the comment # at 436 which means the event starts at offset 436 (in bytes). It is written as a pseudo update statement with the WHERE part showing the values before the change and the SET part the values after the update. This is also known as the before and after images.

Note

If you use encrypted binary logs, you cannot directly use mysqlbinlog to read the files. One option is to make mysqlbinlog connect to the server and read them which returns the logs unencrypted. Another option if you use the keyring_file plugin to store the encryption key is to use Python or standard Linux tools to decrypt the file. These methods are described in https://mysql.wisborg.dk/decrypt-binary-logs and https://mysqlhighavailability.com/how-to-manually-decrypt-an-encrypted-binary-log-file/.

Show Connected Replicas

Another useful command is to ask a source of replication to list all replicas connected to it. This can be used for auto-discovering a replication topology in monitoring tools.

The command to list the connected replicas is SHOW SLAVE HOSTS , for example:
mysql> SHOW SLAVE HOSTS\G
*************************** 1. row ***************************
 Server_id: 2
      Host: replica.example.com
      Port: 3308
 Master_id: 1
Slave_UUID: 0b072c80-d759-11e9-8423-ace2d35785be
1 row in set (0.0003 sec)

If no replicas are connected at the time the statement is executed, the result will be empty. The Server_id and Master_id columns are the values of the server_id system variable on the replica and source, respectively. The Host is the hostname of the replica as specified with the report_host option. Similarly, the Port column is the replica’s report_port value. Finally, the Slave_UUID column is the value of @@global.server_uuid on the replica.

The only group of SHOW statements left consists of various statements to get information about privileges, users, open tables, warnings, and errors.

Miscellaneous Statements

There are a few SHOW statements that are useful but that do not fit into any of the groups that have been discussed thus far. They can be used to list the available privileges, return the CREATE USER statement for an account, list the open tables, and list warnings or errors after executing a statement. The statements are summarized in Table 8-3.
Table 8-3

Miscellaneous SHOW statements

SHOW Statement

Description

PRIVILEGES

Lists the available privileges, which context they apply to, and for some privileges a description of what the privilege controls.

CREATE USER

Returns the CREATE USER statement for an account.

GRANTS

Lists the assigned privileges for the current account or another account.

OPEN TABLES

Lists the tables in the table cache, the number of table locks or lock requests, and whether the name of the table is locked (happens during DROP TABLE or RENAME TABLE).

WARNINGS

Lists the warnings and errors and if sql_notes is enabled (the default) notes for the last executed statement.

ERRORS

Lists the errors for the last executed statement.

The three most commonly used of the miscellaneous SHOW statements are SHOW CREATE USER, SHOW GRANTS, and SHOW WARNINGS.

The SHOW CREATE USER statement can be used to retrieve the CREATE USER statement for an account. This is useful for inspecting metadata for the account without querying the underlying mysql.user table directly. All users are allowed to execute the statement for the current user. For example:
mysql> SET print_identified_with_as_hex = ON;
Query OK, 0 rows affected (0.0200 sec)
mysql> SHOW CREATE USER CURRENT_USER()\G
*************************** 1. row ***************************
CREATE USER for root@localhost: CREATE USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS 0x24412430303524377B743F5E176E1A77494F574D216C41563934064E58364E385372734B77314E43587745314F506F59502E747079664957776F4948346B526B59467A642F30 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.0003 sec)

The print_identified_with_as_hex variable (available in 8.0.17 and later) is enabled to return the password digest in hexadecimal notation. This is the preferred when returning the value to the console as the digest may include unprintable characters. The SHOW CREATE USER output is equivalent to how the user was created and can be used to create a new user with the same settings, including password.

Note

Specifying the authentication digest in hexadecimal notation when creating a user is only supported in MySQL 8.0.17 and later.

The SHOW GRANTS statement complements SHOW CREATE USER by returning the privileges assigned to the account. The default is to return for the current user, but if you have the SELECT privilege for the mysql system database, you can also obtain the privileges assigned to other accounts. For example, to list the privileges for the root@localhost account:
mysql> SHOW GRANTS FOR root@localhost\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON “@” TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.0129 sec)

The SHOW WARNINGS statement is one of the most underused statements in MySQL. If MySQL encounters a problem but is able to continue, it will generate a warning but otherwise complete the execution of the statement. While the statement completes without error, the warning may be a sign of a larger problem, and it is best practice to always check for warnings and aim at never having warnings in the queries executed by your application.

Note

MySQL Shell does not support the SHOW WARNINGS statement as it will automatically fetch warnings if the \W mode has been enabled (the default) and otherwise not make the warnings available. The statement is however still useful in the legacy mysql command-line client and from some connectors such as MySQL Connector/Python.

Listing 8-3 shows an example where SHOW WARNINGS is used with the legacy mysql command-line client to identify that the schema definition and data do not match.
mysql> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.0004 sec)
mysql> SET sql_mode = sys.list_drop(
                          @@sql_mode,
                          'STRICT_TRANS_TABLES'
                      );
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 3135
Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1 row in set (0.00 sec)
mysql> UPDATE world.city
          SET Population = Population/0
        WHERE ID = 130;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 2
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1365
Message: Division by 0
*************************** 2. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'Population' cannot be null
2 rows in set (0.00 sec)
mysql> SELECT *
         FROM world.city
        WHERE ID = 130\G
*************************** 1. row ***************************
         ID: 130
       Name: Sydney
CountryCode: AUS
   District: New South Wales
 Population: 0
1 row in set (0.03 sec)
Listing 8-3

Using SHOW WARNINGS to identify problems

The example starts with the SQL mode set to the default in MySQL 8. First, the SQL mode is changed using the sys.list_drop() function to remove the STRICT_TRANS_TABLES mode which triggers a warning as disabling the strict mode should be done together with other modes as they will be merged together at a later date. Then the population of one of the cities in the world.city table is updated, but the calculation ends up dividing with 0 which triggers two warnings. One warning is for division by 0 which is not defined, so MySQL uses a NULL value which causes the second warning as the Population column is a NOT NULL column. The result is that a population of 0 is assigned to the city, which is probably not what is expected in the application. This also illustrates why it is important to enable the strict SQL mode as that would have made the division by zero an error and prevented the update.

Caution

Do not disable the STRICT_TRANS_TABLES SQL mode as it makes it more likely that you end up with invalid data in your tables.

Summary

This chapter introduced the SHOW statements which date back to before the Information Schema and Performance Schema were implemented. Nowadays, it is often better to use the underlying data sources in the Information Schema and Performance Schema. The mapping between the SHOW statements and data sources was given in the first two sections.

There are also some SHOW statements that return data that cannot be accessed through other sources. A commonly used feature is the InnoDB monitor report from InnoDB obtained with the SHOW ENGINE INNODB STATUS statement . The report is split into several sections, of which some will be used when investigating performance and lock issues.

There are also some statements for replication and the binary logs that are useful. The most commonly used statement of these is SHOW BINARY LOGS which lists the binary logs that MySQL knows of for that instance. The information includes the size and whether the log is encrypted. You can also list events in the binary logs or relay logs, but in practice the mysqlbinlog utility is usually a better option.

Finally, a group of miscellaneous SHOW statements were covered. The three most used of these are SHOW CREATE USER to display a statement that can be used to recreate the user, SHOW GRANTS that returns the privileges assigned to a user, and SHOW WARNINGS which lists the errors, warnings, and by default notes that occurred for the last executed query. Checking the warnings is an often-overlooked aspect of executing queries as warnings can be an indication that the result of a query is not what you expect. It is recommended to always check for warnings and to enable the STRICT_TRANS_TABLES SQL mode.

The final chapter about sources of information is about the slow query log.