MySQL / Concurrency, performance, and operations / Lesson 16

Stored routines, handlers, and responsibility

Design small stored programs with clear validation, security, and transaction ownership.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish procedures, functions, triggers, and scheduled events.
Use SIGNAL and handlers without swallowing failures or committing callers unexpectedly.

Choose the smallest appropriate object

Use only mysql_course_lab for the optional routine definitions below. A procedure is invoked by CALL and can return result sets; a stored function returns a value used in an expression. A trigger runs because a table event occurs, and a scheduled event runs through the event scheduler. Hidden automatic behavior can surprise maintainers, so use these objects for clear local invariants rather than as an undocumented second application.

Validate before computing

A procedure should state input domains explicitly, including NULL cases. SQL three-valued logic can otherwise let a missing quantity bypass a simple quantity <= 0 condition. SIGNAL SQLSTATE '45000' raises a user-defined exception with a meaningful message. The example computes a quotation only; it deliberately performs no stock reservation and does not claim that the caller is authorized to buy.

sql A validation-first quoting procedure

USE mysql_course_lab;
DELIMITER //
CREATE PROCEDURE lab_quote_total(IN p_quantity INT,IN p_price DECIMAL(10,2))
SQL SECURITY INVOKER
BEGIN
 IF p_quantity IS NULL OR p_quantity<=0 OR p_price IS NULL OR p_price<0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Invalid quote input';
 END IF;
 SELECT p_quantity*p_price AS quote_total;
END//
DELIMITER ;
CALL lab_quote_total(2,2.50);

Expected output

quote_total
5.00

The CALL returns 5.00 because two units at 2.50 cost five. DELIMITER is a mysql client directive, not server SQL, and other clients submit the complete CREATE statement differently. SQL SECURITY INVOKER uses the caller's permissions. The definition is one-time lab DDL and is not protected by an ordinary application rollback.

Declare characteristics honestly

A function that only computes from its arguments can be DETERMINISTIC and NO SQL. These declarations describe behavior; they do not prove it automatically. Functions and triggers face restrictions on transaction control and side effects, and binary logging can impose additional creation requirements. Do not solve permission errors by casually weakening global trust settings. Review the exact deployment account and target server configuration instead.

sql A pure illustrative tax function

CREATE FUNCTION lab_taxed(p_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC NO SQL SQL SECURITY INVOKER
RETURN ROUND(p_amount*1.20,2);
SELECT lab_taxed(10.00) AS gross_amount;

Expected output

gross_amount
12.00

The result is 12.00 using the illustrative 20 percent rate. Real tax rules require jurisdiction, timing, and rounding policy beyond this arithmetic. If p_amount is NULL the expression returns NULL; a different required-input contract would need explicit validation rather than silently replacing missing amounts with zero.

Handle known errors without hiding unknown ones

CONTINUE handlers resume after handling, while EXIT handlers leave their declared block. A broad SQLEXCEPTION handler that reports success can conceal failed invariants. Capture diagnostics when needed and RESIGNAL failures the caller must see. Transaction ownership must be explicit: a routine that issues COMMIT can commit its caller's earlier writes. Prefer routines that participate in a caller-owned transaction unless a documented contract clearly chooses otherwise.

Deploy and observe stored code

Version routine definitions alongside schema migrations and review definers, grants, dependencies, and rollback plans. Triggers run inside the triggering statement's transaction but should not make remote network assumptions. Scheduled events need explicit operational ownership and monitoring; their mere existence does not prove execution. MySQL and MariaDB stored-program features diverge, so verify specific syntax and privileges on MySQL 8.4 rather than assuming procedural compatibility.

Common errors and fixes

ERROR 1644 (45000): Invalid quote input.

Why it happens: The quote procedure received NULL, nonpositive quantity, or negative price.
How to fix it: Correct the caller input and preserve the validation failure as a real error.
Prevent it next time: Validate boundary cases at the application and routine contract.

ERROR 1304 (42000): routine already exists.

Why it happens: The one-time CREATE script was repeated without a migration strategy.
How to fix it: Inspect the existing definition before a reviewed replacement migration.
Prevent it next time: Track routine versions and keep deployment idempotence deliberate.

A handler converts a failed purchase into apparent success.

Why it happens: A broad CONTINUE handler swallowed a constraint or SQL exception.
How to fix it: Handle only expected cases and RESIGNAL errors that invalidate the operation.
Prevent it next time: Review failure paths and transaction completion with the caller.

Check your understanding

1. Predict CALL lab_quote_total(NULL,2.50) and explain the NULL check.

Show worked solution

It raises ERROR 1644 (45000): Invalid quote input. The explicit IS NULL condition is true. Without it, NULL <= 0 would be unknown, so a naive comparison-only guard could fail to reject missing quantity and return a meaningless NULL quotation.

2. Who should commit a transaction that calls several validation and stock routines?