I85PREP
⌘ K
1Z0-909 study materials
1Z0-909 study resources

1Z0-909: Error Handling and Diagnostics

1Z0-909 · Work through your exam, one topic at a time.

All guide sections

Error Handling and Diagnostics

What the domain tests

Error Handling and Diagnostics tests how stored programs and applications detect, handle, and report errors: condition handlers, SQLSTATE values, SIGNAL and RESIGNAL, GET DIAGNOSTICS, and warning inspection.

Core rules

  • DECLARE ... HANDLER FOR condition_value statement defines a handler. condition_value can be an SQLSTATE, a MySQL error number, or a named condition such as SQLEXCEPTION, SQLWARNING, or NOT FOUND.
  • Handler actions are CONTINUE or EXIT. MySQL stored-program handlers do not support UNDO; use explicit ROLLBACK where needed.
  • A handler's scope is the block in which it is declared. It is not visible in nested blocks unless declared there or inherited by nesting rules.
  • NOT FOUND is raised by cursor fetches that run out of rows and by SELECT ... INTO that returns no rows.
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'message' raises a user-defined error. RESIGNAL re-raises the current condition inside a handler, optionally changing the message.
  • GET DIAGNOSTICS can retrieve the number of conditions and individual condition information. SHOW WARNINGS and SHOW ERRORS display the warning or error list for the session.
  • In client code, check the error number and SQLSTATE; do not parse message text.

Worked example

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; If an INSERT fails inside the block, the handler rolls back the transaction and re-raises the original error to the caller. The reasoning is that EXIT leaves the block, ROLLBACK undoes transactional work, and RESIGNAL preserves the original SQLSTATE rather than replacing it with a generic error.

Traps

  • Declaring a handler after executable statements in the block.
  • Assuming CONTINUE is safe after a failed statement that leaves a transaction inconsistent.
  • Confusing SQLWARNING with SQLEXCEPTION; warnings do not invoke an SQLEXCEPTION handler.
  • Using RESIGNAL outside a handler, where it is invalid.
  • Ignoring warnings when strict mode is disabled and data has been silently converted.