MySQL SHOW WARNINGS

MySQL SHOW WARNINGS Statement

The SHOW WARNINGS statement in MySQL is used to display diagnostic messages such as warnings, errors, and notes generated during the execution of a query. It is beneficial for understanding why a query did not execute as expected or why it generated specific warnings.

Syntax

SHOW WARNINGS;

Additional Options:

• LIMIT [offset,] row_count: Limits the number of rows displayed.

SHOW WARNINGS LIMIT 5;

• SHOW COUNT(*) WARNINGS: Displays the count of warning messages without listing them.

SHOW COUNT(*) WARNINGS;

How It Works

• MySQL automatically clears the warning messages after executing any statement, so SHOW WARNINGS reflects the diagnostics for the last executed statement only.
• The output includes the following columns:
  • Level: The severity level (Error, Warning, or Note).
  • Code: The numeric error code.
  • Message: A description of the issue.

Examples

1. Basic Example

CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50), id INT);

This query generates a warning because the column id is declared twice. You can retrieve the warning with:

SHOW WARNINGS;

Output:

+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Error | 1060 | Duplicate column name 'id'                    |
+-------+------+------------------------------------------------+

2. Display Warnings After a Query

LOAD DATA INFILE 'nonexistent_file.txt' INTO TABLE test_table;

The query fails because the file does not exist. Use SHOW WARNINGS to check the details:

SHOW WARNINGS;

Output:

+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Error | 29   | File 'nonexistent_file.txt' not found        |
+-------+------+----------------------------------------------+

3. Count Warnings

To count the number of warnings generated:

SHOW COUNT(*) WARNINGS;

Output:

+----------+
| Count(*) |
+----------+
| 1        |
+----------+

Use Cases

1. Debugging: Identify why a query is not working as expected or what issues were encountered.
2. Optimization: Analyze and resolve warnings to ensure clean execution of queries.
3. Error Handling: Detect issues in automated scripts or batch operations.

Key Notes

1. SHOW WARNINGS only displays messages related to the most recent statement.
2. The maximum number of warnings stored depends on the max_error_count system variable.
3. Diagnostic messages are stored per session, meaning warnings from one session are not accessible in another.

Conclusion

The SHOW WARNINGS statement is an essential diagnostic tool for troubleshooting and fine-tuning queries in MySQL. It helps developers understand errors, warnings, and notes, ensuring better query execution and robust database management.