Home arrow MySQL arrow Page 4 - Error Handling

Handler Conditions - MySQL

In this first article in a three-part series, you will learn how to create various types of exception handlers. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Error Handling
  2. Handling Last Row Conditions
  3. Condition Handlers
  4. Handler Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 14
August 30, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:

  1. As a MySQL error code.
  2. As an ANSI-standardSQLSTATEcode.
  3. 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 conditionsSQLEXCEPTION,SQLWARNING, andNOT 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;

SQLSTATEerror 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 sameSQLSTATEvalue (23000) when a duplicate key value error is encountered. Every MySQL error code has an associatedSQLSTATEcode, but the relationship is not one-to-one; someSQLSTATE codes are associated with many MySQL codes; HY000 is a general-purposeSQLSTATEcode that is raised for MySQL codes that have no specific associatedSQLSTATEcode.

The following handler will fire whenSQLSTATE23000 (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 haveSQLSTATEequivalents. Although every MySQL error code is associated with someSQLSTATE errorcode, often it will be a general-purposeSQLSTATE 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 ofSQLSTATE errorcodes.

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 associatedSQLSTATEcode, 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 theSQLSTATEcode is 42000.

Table 6-1 lists some of the error codes you might expect to encounter in a MySQL stored program together with theirSQLSTATEequivalents. Note, again, that many MySQL error codes map to the sameSQLSTATEcode (many map to HY000, for instance), which is why you may wish to sacrifice portability and use MySQL error codes— rather thanSQLSTATEcodes—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 DELAYEDcan’t be used with table ‘%s’ because it is locked withLOCK 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 SELECTin a stored program must haveINTO
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.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: