Agenda

After completing this lesson, you should be able to:

• Set SQL modes to affect error output
• Interpret error messages
• Use the SHOW WARNINGS and SHOW ERRORS statements
• Examine MySQL diagnostic information
• Use the perror utility program to retrieve error details

SQL Modes

• SQL modes affect the SQL syntax and the data validation checks.
• Set with the sql_mode variable:
  • By starting mysqld with the --sql-mode command-line option or by setting the sql-mode option in an option file
  • By setting the sql_mode variable at run time with SET
  • By setting the sql_mode variable at run time with SET PERSIST to retain the value after MySQL server restarts
• SQL modes exist at a global level and at a session level.
  • New sessions use the global SQL mode by default.
  • All users can change their own session SQL modes.
  • Changing global SQL modes requires the SYSTEM_VARIABLES_ADMIN or SUPER privilege.

The Default SQL Mode

• Display the current SQL mode using:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;

• The default SQL mode is:

mysql> SELECT @@GLOBAL.sql_mode\G
****************** 1. row *******************
@@sql_mode: @@GLOBAL.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 (#.## sec)

• By default, the SQL mode for a new session is the same as the global SQL mode.

Setting SQL Modes

• The value of sql_mode variable may be an empty string, name of a SQL mode, or multiple SQL mode names separated by commas.
  • If the value is empty or contains more than one mode name, it must be quoted.
  • If the value contains a single mode name, quoting is optional.
  • If neither GLOBAL nor SESSION is specified, the default is SESSION.
  • SQL mode values are not case-sensitive.

mysql> SET sql_mode = '';
mysql> SET sql_mode = ANSI_QUOTES;
mysql> SET GLOBAL sql_mode = 'IGNORE_SPACE,ANSI_QUOTES';

Strict Mode

• When STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.
  • When strict mode is not enabled, MySQL converts the invalid values according to the table structure and constraints.
    • Conversion takes place when adding, replacing, or updating existing data:
      • INSERT, REPLACE, UPDATE, and LOAD DATA INFILE
      • Using ALTER TABLE to change a column containing data
    • MySQL adjusts invalid values to the closest valid value.
      • Out of range, NULL, or values of the wrong type
      • Generates a warning, which you can view with SHOW WARNINGS
  • In strict mode, MySQL generates an error and aborts the statement.

Example: Strict Mode

mysql> use world;
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (#.## sec)

mysql> SET sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected, 1 warning (#.## sec)

mysql> INSERT INTO t VALUES (500);
ERROR 1264 (22003): Out of range value for column 'i' at row 1

mysql> SET sql_mode='';
Query OK, 0 rows affected (#.## sec)

mysql> INSERT INTO t VALUES (500);
Query OK, 1 row affected, 1 warning (#.## sec)

mysql> SELECT * FROM t\G
*************************** 1. row ***************************
i: 127
1 row in set (#.## sec)

The NO_ENGINE_SUBSTITUTION SQL Mode

• The NO_ENGINE_SUBSTITUTION SQL mode controls what happens when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is unavailable.
  • When enabled, an error occurs and the CREATE TABLE or ALTER TABLE statement fails.
  • If NO_ENGINE_SUBSTITUTION is disabled and an engine is unavailable:
    • For CREATE TABLE, MySQL uses the default engine to create the table and raises a warning.
    • For ALTER TABLE, MySQL raises a warning and the table is not altered.

Other Important SQL Modes

• ANSI: Changes syntax and behavior to better conform to standard SQL.
  • REAL_AS_FLOAT,
  • PIPES_AS_CONCAT,
  • ANSI_QUOTES, IGNORE_SPACE, and
  • ONLY_FULL_GROUP_BY
• TRADITIONAL: Makes MySQL behave more like a “traditional” SQL database system, raising errors instead of warnings when inserting incorrect values into a column. :
  • STRICT_TRANS_TABLES,
  • STRICT_ALL_TABLES,
  • NO_ZERO_IN_DATE,
  • NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and
  • NO_ENGINE_SUBSTITUTION.
• ONLY_FULL_GROUP_BY: Does not allow queries when the SELECT list refers to columns that are not in the GROUP BY clause.

Interpreting Error Messages

• MySQL produces errors or warnings if a connection or statement fails.
• Clients display this information to assist you in troubleshooting.

mysql> SELECT * FROM no_such_table;
ERROR 1146 (42S02): Table 'world.no_such_table' doesn't exist

• • Error messages have three components:
    • A MySQL-specific error code, such as 1146
    • A SQLSTATE error code, such as 42S02. These codes are defined by SQL and ODBC standards.
      • Not all MySQL error numbers have corresponding SQLSTATE codes. In this case, MySQL uses HY000 (general error).
    • A text message that describes the problem

mysql> CREATE TABLE a (i INT) ENGINE MYISAM;
ERROR 1 (HY000): Can't create/write to file './world/a_391.sdi' (OS errno 13 - Permission denied)

• • OS errors contain a system-specific error number and message.
• An information string is returned by statements that affect multiple rows.
• This string provides a summary of the statement outcome:

mysql> SET sql_mode='';  
mysql> CREATE TABLE integers (i INT UNSIGNED NOT NULL);
mysql> INSERT INTO integers VALUES ('abc'), (-5), (NULL); 

Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3

• Set Default SQL_MODE

mysql>SET sql_mode=' ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION '; 

Investigating Error Codes with perror

• The purpose of the perror program is to show you information about the error codes used by MySQL when errors or warnings occur.
  • perror is included with MySQL distributions.
  • Use perror to examine error codes.

mysql> CREATE TABLE a (i INT) ENGINE MYISAM;
ERROR 1 (HY000): Can't create/write to file './world/a_391.sdi' (OS errno 13 - Permission denied)

• • This error message indicates that MySQL cannot write to the ./world/a_391.sdi file. You can use perror at the shell to find out why:

shell> perror 1 13
OS error code   1:  Operation not permitted

MySQL error code MY-000001: Can't create/write to file '%s' (OS errno %d - %s)
OS error code  13:  Permission denied

MySQL error code MY-000013: Can't get stat of '%s' (OS errno %d - %s)

Warnings, Errors and Notes

• An error occurs for the SELECT statement. For the DELETE statement, the message is only a note.
• That is certainly true when the statement finishes, even though the statement did nothing.

mysql> SELECT * FROM no_such_table;
mysql> SHOW WARNINGS;
mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;

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

mysql> SET sql_notes = 0;

Troubleshooting Syntax Errors

• You can use the error messages to troubleshoot syntax errors in SQL statements.
  • Error code 1064 indicates a syntax error.

mysql> SELECT * FROM country WHERE country LIKE LIMIT 5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 1

• • The hint near '...' indicates the point where MySQL server cannot recognize the syntax. Check the statement at the first word in the quotes and just before that word.

SELECT * FROM country WHERE country LIKE LIMIT 5;

• • • There is a missing operand between “LIKE” and “LIMIT 5”.
  • If the near '' is an empty string, the syntax error occurs at the end of the statement.

The SHOW WARNINGS Statement

• MySQL warnings can be displayed with the SHOW WARNINGS statement.
• For Example, warnings are generated for attempts to insert a character string, a negative integer, and NULL into a column that is defined as INT UNSIGNED NOT NULL.
• The SHOW WARNINGS statement provides information to help you understand what went wrong:

mysql> SHOW WARNINGS\G

Using SHOW WARNINGS

• MySQL Server generates a warning when it is not fully able to comply with a request or when an action might have unintended side effects.
  • SHOW WARNINGS shows the warnings resulting from the last statement.
  • These are overwritten by subsequent nondiagnostic statements.
  • The Level column indicates the severity:
    • Error, Warning, or Note
  • SHOW WARNINGS LIMIT 1,1 shows only the second warning.
    • The LIMIT clause with an offset allows you to “scroll” through the results.
  • SELECT COUNT(*) WARNINGS shows the number of warnings.
  • SELECT @@warning_count also shows the number of warnings, but this is a nondiagnostic statement that will clear the warnings.

Example: SHOW WARNINGS

mysql> DROP TABLE IF EXISTS no_such_table, dummy_table;
Query OK, 0 rows affected, 2 warnings (#.## sec)
mysql> SHOW WARNINGS;
+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Note  | 1051 | Unknown table 'world.no_such_table' |
| Note  | 1051 | Unknown table 'world.dummy_table'   |
+-------+------+-------------------------------------+
2 rows in set (#.## sec)

mysql> SHOW COUNT(*) WARNINGS;
+-------------------------+
| @@session.warning_count |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (#.## sec)

Using SHOW ERRORS

• The SHOW ERRORS statement is similar to SHOW WARNINGS, but displays messages for which the Level value is Error.
  • SHOW ERRORS tends to produce less output than SHOW WARNINGS.

mysql> SHOW ERRORS;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1146 | Table 'world.no_such_table' doesn't exist |
+-------+------+-------------------------------------------+
1 row in set (#.## sec)

• • SHOW ERRORS supports the same features as SHOW WARNINGS:

SHOW ERRORS LIMIT 1,1;
SHOW COUNT(*) ERRORS;
SELECT @@error_count;

Using MySQL Diagnostics

• SQL statements produce diagnostic information that populates the diagnostic area.
• The diagnostics area contains two types of diagnostic information:
  • Statement information
    • NUMBER: The number of condition areas that have information
    • ROW_COUNT: The number of rows affected by the statement
  • Condition information. Conditions include:
    • RETURNED_SQLSTATE: A string that indicates the SQLSTATE value for the condition
    • MESSAGE_TEXT: A string that indicates the error message for the condition
    • MYSQL_ERRNO: An integer that indicates the MySQL error code for the condition

Retrieving Diagnostic Information

• Use SHOW WARNINGS or SHOW ERRORS to retrieve warning or error messages in the diagnostic area.
• Use GET DIAGNOSTICS to retrieve either statement or condition information.
  • Statement information: Retrieve the number of conditions and rows affected

GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;

• • Condition information: Specify the condition number and store the desired condition items in variables

GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT, @p5 = MYSQL_ERRNO;

Example: Retrieving Diagnostic Information

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'

mysql> GET DIAGNOSTICS CONDITION 1
    -> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT, @P3 = MYSQL_ERRNO;
Query OK, 0 rows affected (#.## sec)

mysql> SELECT @p1, @p2, @p3;
+-------+------------------------------------+------+
| @p1   | @p2                                | @p3  |
+-------+------------------------------------+------+
| 42S02 | Unknown table 'test.no_such_table' | 1051 |
+-------+------------------------------------+------+ 
1 row in set (#.## sec)