Stored Procedures Interview Questions

A stored procedure lives in the data dictionary and executes inside the server session, so it can cut round-trips for multi-statement InnoDB work. App logic is easier to version in git, test, and roll back. Mixing both without a clear boundary leaves business rules in two places. Interviewers want a specific reason (latency, security definer, batch job), not procedures by default.

Prepared SQL inside a procedure (PREPARE/EXECUTE) is how you build dynamic identifiers that the parser will not accept as parameters. It is also how people accidentally concatenate unsanitized table names—SQL injection inside the server. DEALLOCATE PREPARE or reuse a session-stable statement name. Prefer static SQL when the identifier set is closed.

App logic in the client is easier to version in git, but a procedure can cut round-trips when one HTTP request would otherwise issue twenty statements. If the team cannot unit-test SQL or the logic calls external APIs, keep it out of mysqld. Procedures also complicate online schema change because callers pin old metadata. Default to the app unless you can name the round-trip or privilege reason.

SQL SECURITY INVOKER is safer for shared schemas because callers cannot inherit a SUPER definer. Combined with least-privilege EXECUTE, analysts cannot ride a root DEFINER into mysql.user. The trade-off is every role needs table GRANTs. Views have the same DEFINER trap. Audit SHOW CREATE PROCEDURE after every dump restore—DEFINER host parts often break.

A CONTINUE HANDLER for NOT FOUND is required around FETCH or the procedure aborts on the last row instead of exiting the loop. The handler must set a flag you inspect after FETCH; using it as a GOTO across statements is how people skip error handling. Nested cursors each need their own handler scope. This is MySQL procedure syntax, not an InnoDB cursor API.

Stored functions cannot start or commit transactions and cannot do DML that is not deterministic from the optimizer’s view in some contexts. They also cannot return result sets—only a scalar. Using a function in a WHERE disables index use unless it is generated-column-shaped. Replication of non-deterministic functions under STATEMENT format drifts replicas.

binlog_format=STATEMENT plus a non-deterministic procedure (NOW(), UUID(), user variables, RAND) logs the CALL, and the replica computes different values. ROW logs the actual InnoDB row images instead. MIXED switches per statement but still slips some user-variable cases. Mark routines NOT DETERMINISTIC honestly; lying to the optimizer also hurts caching.

SIGNAL / RESIGNAL (SQLSTATE '45000') is the supported way to raise application errors with MYSQL_ERRNO and a message. Leaving a handler that does nothing maps failures to a generic 1644/SQLSTATE. RESIGNAL inside a handler preserves the original InnoDB deadlock errno 1213 so the app can retry. Avoid INSERT into a log table as your only error path—it can fail too.

A procedure that issues COMMIT inside a loop breaks the caller's transaction boundary and can leave autocommit-sized pieces durable after a later SIGNAL. Nested START TRANSACTION in MySQL does not nest; it commits the outer work. Document whether CALL is the transaction or the application BEGIN is. This is a top interview failure mode for money-moving routines.

Prepared statements inside procedures share a per-session cache; leaking PREPARE without DEALLOCATE, or using a new random name per call, hits max_prepared_stmt_count. Connection pools keep the session, so the leak survives CALL. Reuse a fixed statement name or DEALLOCATE in a handler. PERFORMANCE_SCHEMA prepared_statements_instances shows the pile-up.

Deterministic vs not-deterministic flags affect the optimizer and binary logging; lying that a function is DETERMINISTIC when it reads a table can cache wrong results and break STATEMENT replication. READS SQL DATA vs MODIFIES SQL DATA is similarly advisory but tools and replicas consult it. Set NOT DETERMINISTIC if you touch NOW() or user variables. Replication tests beat trust in the flag.

Stored procedures on a replica with --replicate-wild-ignore-table can leave the routine definition applied but skip the DML, or skip CREATE PROCEDURE while row events still arrive. Either way InnoDB data diverges from expected CALL side effects. Filter on the application schema consistently, not on mysql.proc alone. GTID holes after a skipped CREATE are painful to repair.

DEFINER security runs the routine with the privileges of the creating account; INVOKER uses the caller. A DEFINER=root@localhost procedure is a hidden SUPER path if EXECUTE is granted widely. INVOKER is safer for shared schemas but then every caller needs table privileges. SHOW CREATE PROCEDURE prints both the definer and the security type.

Cursors in MySQL procedures are asensitive and read-only; they copy a result set and do not see later updates in the same session the way some engines claim. FETCH in a loop plus a CONTINUE HANDLER FOR NOT FOUND is the usual pattern. Huge cursors hold metadata locks and temp space. Set-based SQL almost always beats a row-by-row cursor on InnoDB.

DECLARE EXIT HANDLER FOR SQLEXCEPTION lets you ROLLBACK and SIGNAL a custom SQLSTATE so the caller sees a clean error. CONTINUE handlers are for NOT FOUND on FETCH, not for swallowing deadlock 1213. GET DIAGNOSTICS captures errno. Leaving autocommit ON inside a procedure still commits each statement unless you START TRANSACTION explicitly.

Replication of CREATE PROCEDURE ships the body as a statement event; the DEFINER user must exist on the replica or the object is unusable. ROW format still logs the CALL as the DML the procedure runs, not as a CALL row. Non-deterministic procedures under STATEMENT binlog are a classic data drift. Keep sql_mode of the creator matching production.

Cursors nested inside loops hold metadata locks for the duration; a huge scan cursor blocks ALTER even if InnoDB MVCC would allow readers. Each FETCH still does server-side work, so CPU sits in the procedure thread, not in a well-planned JOIN. Replace with a set-based UPDATE/JOIN. SHOW PROCESSLIST Command=Query with the CALL stack is the hint.

SHOW CREATE PROCEDURE reveals DEFINER, sql_mode snapshot, and character_set_client captured at create time. A procedure created under ANSI_QUOTES behaves differently after dump/restore if the session sql_mode changed. character_set_client mismatch garbles string literals. Those attributes are part of the object, not comments in the body.

Row-based replication still logs the DML the procedure executes, not the CALL, so a replica without the routine body can still apply row events. That helps failover, but triggers and cascading FKs inside the CALL multiply row events. STATEMENT logs CALL and requires the same procedure text. Mixing formats across a chain is how people lose events.

mysql.proc privileges (CREATE ROUTINE, ALTER ROUTINE, EXECUTE) are separate from table GRANTs. GRANT ALL ON db.* does not always include CREATE ROUTINE depending on version. A user who can ALTER ROUTINE can rewrite a DEFINER body. Least privilege means EXECUTE on the routine plus INVOKER table rights, not ALTER ROUTINE on *.* .