MySQL - Troubleshooting

mysql

How can we get a concise report of the server's load from within the mysql client program?:

mysql> STATUS;

How can we see what SQL statements are currently being executed?

SHOW PROCESSLIST
SHOW FULL PROCESSLIST

The SHOW PROCESSLIST statement displays information about the activity of each currently connected client. For example, the presence of a large number of blocked queries might indicate that another connection is running a query that is inefficient and should be examined to see if it can be optimized. If you have the PROCESS privilege, it also shows queries being run by other users / accounts.

What are some commands useful for debugging?

show engine innodb status;
show open tables where In_Use > 0 ;
show open tables from test;
mysqladmin debug

show variables like "%version%";
select database();
select version();
show processlist;
show full processlist;

SHOW WARNINGS;
SHOW ERRORS;

Messages typically have three components: a MySQL-specific error code, a SQLSTATE error code (defined by standard SQL and ODBC), a text message that describes the problem. An informational string is returned by statements that affect multiple rows. This string provides a summary of the statement outcome.

For cases such as the preceding SELECT from a non-existent table, where all three error values are displayed, you can simply look at the information provided to see what the problem was. In other cases, all information might not be displayed. The information string for multiple-row statements is a summary, not a complete diagnostics. An operating system error includes an Errcode number that might have system-specific meaning.

The SHOW WARNINGS and SHOW ERRORS statements display warning and error information for statements that produce diagnostic information.

The perror command-line utility (part of MySQL) displays the meaning of operating system-related error codes. There is a chapter in the MySQL Reference Manual that lists error codes and messages.

MySQL server generates warnings when it is not able to fully comply with a request or when an action has possibly unintended side effects. These warnings can be displayed with SHOW WARNINGS.

We can combine SHOW WARNINGS with LIMIT to scroll through the warnings one section at a time.

SHOW COUNT(*) WARNINGS;

Warnings generated by one statement are available from the server only for a limited time (until you issue another statement that can generate warnings). If you need to see warnings, you should always fetch them as soon as you detect that they were generated.

To suppress generation of Note warnings, you can set the sql_notes system variable to zero:

SET sql_notes = 0;

SHOW WARNINGS displays notes, warnings, and errors. SHOW ERRORS is similar to SHOW WARNINGS but only displays messages for error conditions. It shows only messages having higher severity and tends to produce less output. Like SHOW WARNINGS, SHOW ERRORS supports the LIMIT clause. You can also use SHOW COUNT(*) ERRORS to obtain a count of error messages.

The purpose of the perror program is to show you information about the error codes used by MySQL when operating system-level errors occur.

mysql> CREATE TABLE CountryCopy SELECT * FROM Country;
ERROR 1 (HY000): Can't create/write to file './world/CountryCopy.frm'
(Errcode: 13)

This error message indicates that MySQL cannot write to the file CountryCopy.frm, but does not report the reason. It might be due to a full disk, a filesystem permission problem, or some other error. To find out, run the perror program with an argument of the number given following the Errcode. perror displays a message indicating that the source of the problem is that someone has incorrectly set the filesystem permissions for the current database.

shell> perror 13
Error code 13:  Permission denied.
mysql> SELECT * FROM no_such_table;
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

mysql> INSERT INTO integers VALUES ('abc'), (-5), (NULL);
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3

mysql> CREATE TABLE CountryCopy SELECT * FROM Country;
ERROR 1 (HY000): Can't create/write to file './world/CountryCopy.frm'
(Errcode: 13)

How can we discover which database schema I am using?

SELECT DATABASE();

How can we display the number of tables in each database:

SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;

Basic commands:

SHOW DATABASES;
SHOW DATABASES LIKE 'm%';
SHOW TABLES;
SHOW TABLES FROM mysql;
SHOW COLUMNS FROM CountryLanguage;
SHOW FULL COLUMNS FROM CountryLanguage\G
SHOW KEYS FROM City\G
SHOW INDEX FROM City\G
SHOW CREATE TABLE tableName;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

How can we discover information about available character sets?

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

How can we discover information about collations for each character set?

SELECT * FROM INFORMATION_SCHEMA.COLLATIONS;

How can we discover information about which character set applies to each collation?

SELECT * FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY;

How can we display the default collation for each character set:

SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
 WHERE IS_DEFAULT = 'Yes';

How can we discover information about constraints on key columns?

DESC INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

How can we list available sub-routines, stored procedures, triggers, and functions?

DESC INFORMATION_SCHEMA.ROUTINES;

How can we list available databases?

DESC INFORMATION_SCHEMA.SCHEMATA;
SHOW DATABASES;

How can we display the storage engines used for the tables in a given database:

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world';

How can we find all the tables that contain columns of type SET:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE = 'set';

What information is stored in INFORMATION_SCHEMA.COLUMNS table?

The INFORMATION_SCHEMA.COLUMNS contains information about tables and columns.

DESC INFORMATION_SCHEMA.COLUMNS;
SHOW CREATE TABLE INFORMATION_SCHEMA.COLUMNS;

What information is stored in the INFORMATION_SCHEMA.COLUMN_PRIVILEGES table?

The INFORMATION_SCHEMA.COLUMN_PRIVILEGES contains information about column privileges held by your user accounts.

What information is stored in the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table?

It contains information about database privileges held by your user accounts.

What information is stored in the INFORMATION_SCHEMA.STATISTICS table?

It contains information about table indexes.

What information is stored in the INFORMATION_SCHEMA.TABLES table?

It contains information about tables in databases.

What information is stored in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table?

It contains information about constraints on tables.

**What information is stored in the INFORMATION_SCHEMA.TABLE_PRIVILEGES table?

It contains information about table privileges held by MySQL user accounts.

What information is stored in the INFORMATION_SCHEMA.TRIGGERS table?

It contains information about triggers in databases.

What information is stored in the INFORMATION_SCHEMA.USER_PRIVILEGES?

It contains information about global privileges held by user accounts.

What information is stored in the INFORMATION_SCHEMA.VIEWS?

It contains information about views in databases.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License