Error Handling

The perfect programmer, living in a perfect world, would always write programs that anticipate every possible circumstance. Those programs would either always work correctly, or fail "gracefully" by providing comprehensive diagnostic information to the support team and very readable messages to the user.

For a certain class of applicationssoftware supporting life support systems or the space shuttle, for instancethis level of perfection is actually a part of the requirements, because any unexpected failure of the software would be catastrophic. However, in the world of business applications, we usually make certain assumptions about our execution environmentwe assume the MySQL server will be running, that our tables have not been dropped, that the host machine is not on fire, and so on. If any of these conditions occurs, then we accept that our application will fail. In many other circumstances, we can and should anticipate potential failures and write code to manage those situations. This is where exception handling comes into play.

When a stored program encounters an error condition, execution ceases and an error is returned to the calling application. That's the default behavior. What if we need a different kind of behavior? What if, for example, we want to trap that error, log it, or report on it, and then continue execution of our application? For that kind of control, we need to define exception handlers in our programs.

When developing MySQL stored programs, a very common scenariofetching to the end of a result setalso requires that we define an exception handler.

In this chapter we explain how to create various types of exception handlers and how to improve the readability of error handling by using "named" conditions. We also identify several gaps in exception-handling functionality in MySQL 5, and explore ways of compensating for these omissions.