Error Handling - Handler Conditions (
Page 4 of 4 )
The handler condition defines the circumstances under which the handler will be invoked. The circumstance is always associated with an error condition, but you have three choices as to how you define that error:
- As a MySQL error code.
- As an ANSI-standard
SQLSTATE
code.
- As a named condition. You may define your own named conditions (described in the later section “Named Conditions”) or use one of the built-in conditions
SQLEXCEPTION
,
SQLWARNING
, and
NOT FOUND
.
MySQL has its own set of error codes that are unique to the MySQL server. A handler condition that refers to a numeric code without qualification is referring to a MySQL error code. For instance, the following handler will fire when MySQL error code 1062 (duplicate key value) is encountered:
DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;
SQLSTATE
error codes are defined by the ANSI standard and are database-independent, meaning that they will have the same value regardless of the underlying database. So, for instance, Oracle, SQL Server, DB2, and MySQL will always report the same
SQLSTATE
value (23000) when a duplicate key value error is encountered. Every MySQL error code has an associated
SQLSTATE
code, but the relationship is not one-to-one; some
SQLSTATE
codes are associated with many MySQL codes; HY000 is a general-purpose
SQLSTATE
code that is raised for MySQL codes that have no specific associated
SQLSTATE
code.
The following handler will fire when
SQLSTATE
23000 (duplicate key value) is encountered:
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;
SQLSTATE or MySQL Error Code?
In theory, using the SQLSTATE codes will make your code more portable to other database platforms and might therefore seem to be the best choice. However, there are a number of reasons to use MySQL error codes rather than SQLSTATE codes when writing MySQL stored programs:
- In reality, it is unlikely that you will move your stored programs to another RDBMS. The Oracle and SQL Server stored program languages are totally incompatible with MySQL. The DB2 stored program language is somewhat compatible (both are based on the SQL:2003 standard). It is very likely, however, that you will use MySQL-specific syntax as you write your application, which will prevent your stored code from being portable.
- Not all MySQL error codes have
SQLSTATE
equivalents. Although every MySQL error code is associated with some
SQLSTATE error
code, often it will be a general-purpose
SQLSTATE
that is not specific (such as HY000). Therefore, you will almost certainly have to code some handlers that refer directly to MySQL error codes. You’ll probably find that the advantages of using a consistent handler format will outweigh the theoretical portability advantage of
SQLSTATE error
codes.
We will, for the most part, use MySQL error codes in this book.
When the MySQL client encounters an error, it will report both the MySQL error code and the associated
SQLSTATE
code, as in the following output:
mysql> CALL nosuch_sp();
ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist
In this case, the MySQL error code is 1305 and the
SQLSTATE
code is 42000.
Table 6-1 lists some of the error codes you might expect to encounter in a MySQL stored program together with their
SQLSTATE
equivalents. Note, again, that many MySQL error codes map to the same
SQLSTATE
code (many map to HY000, for instance), which is why you may wish to sacrifice portability and use MySQL error codes— rather than
SQLSTATE
codes—in your error handlers.
Table 6-1. Some common MySQL error codes and SQLSTATE codes
| MySQL error code |
SQLSTATE code |
Error message |
|
1011 |
HY000 |
Error on delete of ‘%s’ (errno: %d) |
| 1021 |
HY000 |
Disk full (%s); waiting for someone to free some space . . . |
| 1022 |
23000 |
Can’t write; duplicate key in table ‘%s’ |
| 1027 |
HY000 |
‘%s’ is locked against change |
| 1036 |
HY000 |
Table ‘%s’ is read only |
| 1048 |
23000 |
Column ‘%s’ cannot be null |
| 1062 |
23000 |
Duplicate entry ‘%s’ for key %d |
| 1099 |
HY000 |
Table ‘%s’ was locked with a READ lock and can’t be updated |
| 1100 |
HY000 |
Table ‘%s’ was not locked with LOCK TABLES |
| 1104 |
42000 |
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay |
| 1106 |
42000 |
Incorrect parameters to procedure ‘%s’ |
| 1114 |
HY000 |
The table ‘%s’ is full |
| 1150 |
HY000 |
Delayed insert thread couldn’t get requested lock for table %s |
| 1165 |
HY000 |
INSERT DELAYED
can’t be used with table ‘%s’ because it is locked with
LOCK TABLES |
|
1242 |
21000 |
Subquery returns more than 1 row |
| 1263 |
22004 |
Column set to default value; NULL supplied to NOT NULL column ‘%s’ at row %ld |
| 1264 |
22003 |
Out of range value adjusted for column ‘%s’ at row %ld |
| 1265 |
1000 |
Data truncated for column ‘%s’ at row %ld |
| 1312 |
0A000 |
SELECT
in a stored program must have
INTO |
|
1317 |
70100 |
Query execution was interrupted |
| 1319 |
42000 |
Undefined CONDITION: %s |
| 1325 |
24000 |
Cursor is already open |
Table 6-1. Some common MySQL error codes and SQLSTATE codes (continued)
|
MySQL error code 1326 1328 |
SQLSTATE code 24000 HY000 |
Error message Cursor is not open Incorrect number of FETCHvariables |
|
1329 |
2000 |
No data to FETCH |
|
1336 1337 |
42000 42000 |
USEis not allowed in a stored program Variable or condition declaration after cursor or handler declaration |
|
1338 |
42000 |
Cursor declaration after handler declaration |
|
1339 |
20000 |
Case not found for CASE statement |
|
1348 1357 |
HY000 HY000 |
Column ‘%s’ is not updatable Can’t drop a %s from within another stored routine |
|
1358 1362 |
HY000 HY000 |
GOTOis not allowed in a stored program handler |
|
1363 |
HY000 |
Updating of %s row is not allowed in %s trigger There is no %s row in %s trigger |
You can find a complete and up-to-date list of error codes in Appendix B of the MySQL reference manual, available online at http://dev.mysql.com/doc/.
Please check back next week for the continuation of this article.
