When Server-Side Logic Helps—and When Application Logic Is the Better Boundary

Learning outcomes

By now you can create four kinds of server-side logic. The more important production skill is knowing when not to. This lesson replaces “stored procedures are good/bad” debates with explicit decision criteria and a refactoring exercise that makes hidden behavior observable in application tests.

The boundary question

Consider the status-audit rule from Lesson 3. A trigger guarantees that any SQL client updating the table produces the audit row. That is strong data-boundary enforcement. The cost is hidden behavior: the application sends one statement, yet a second table is written under a stored object's definer context.

Moving the same rule into application code makes the extra write visible in source control, telemetry, tests, and deployment review. The cost is that every writer must use the same service boundary—or the database no longer guarantees the audit invariant. Neither answer is universally correct.

Inventory before refactoring

Do not move logic you have not inventoried. Record every stored object, its definer/security context, dependencies, grants, owner, and failure evidence.

SELECT TABLE_NAME,IS_UPDATABLE,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA='servicehub_logic_lab'ORDER BY TABLE_NAME;SELECT ROUTINE_NAME,ROUTINE_TYPE,DEFINER,SECURITY_TYPE,DATA_ACCESSFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_logic_lab'ORDER BY ROUTINE_TYPE,ROUTINE_NAME;SELECT TRIGGER_NAME,EVENT_OBJECT_TABLE,ACTION_TIMING,EVENT_MANIPULATION,ACTION_ORDER,DEFINERFROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_logic_lab'ORDER BY EVENT_OBJECT_TABLE,ACTION_TIMING,EVENT_MANIPULATION,ACTION_ORDER;SELECT EVENT_NAME,DEFINER,TIME_ZONE,STATUS,LAST_EXECUTEDFROM INFORMATION_SCHEMA.EVENTSWHERE EVENT_SCHEMA='servicehub_logic_lab'ORDER BY EVENT_NAME;

The inventory is a deployment artifact, not just a debugging query. A schema diff that changes a table but omits dependent views/triggers/routines is incomplete.

Refactor: move hidden audit behavior into an explicit application transaction

For the experiment, remove the status-audit trigger after recording its definition. Keep the other lesson objects as needed. The application will perform the base update and audit insert in one InnoDB transaction with bound parameters.

SHOW CREATE TRIGGER trg_work_orders_status_audit\GDROP TRIGGER trg_work_orders_status_audit;SELECT COUNT(*) AS remaining_status_audit_triggersFROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_logic_lab'  AND TRIGGER_NAME='trg_work_orders_status_audit';

Now the application needs direct INSERT on the audit table. This is an explicit security tradeoff. In a real service, you might instead grant EXECUTE on a definer procedure and keep base tables private. For the refactor lab, grant only what this service transaction needs:

GRANT SELECT ON servicehub_logic_lab.work_ordersTO 'logic_app'@'127.0.0.1';GRANT UPDATE(status,closed_at) ON servicehub_logic_lab.work_ordersTO 'logic_app'@'127.0.0.1';GRANT INSERT,SELECT ON servicehub_logic_lab.work_order_auditTO 'logic_app'@'127.0.0.1';SHOW GRANTS FOR 'logic_app'@'127.0.0.1';

Driver-level implementation with explicit transaction ownership

The following Python example uses the free Oracle MySQL Connector/Python package. Install it in a local virtual environment if you want to run the integration lab. The password comes from an environment variable; no real secret belongs in source.

import osimport mysql.connectorfrom mysql.connector import Errorcnx = mysql.connector.connect(    host="127.0.0.1",    user="logic_app",    password=os.environ["MYSQL_LOGIC_APP_PASSWORD"],    database="servicehub_logic_lab",)try:    cur = cnx.cursor(dictionary=True)    cur.execute("SELECT CONNECTION_ID() AS id, USER() AS user, CURRENT_USER() AS current_user")    print(cur.fetchone())    cnx.start_transaction()    work_order_id = 2    cur.execute("SELECT status FROM work_orders WHERE work_order_id=%s FOR UPDATE", (work_order_id,))    row = cur.fetchone()    if row is None:        raise RuntimeError("work order not found")    old_status = row["status"]    cur.execute(        "UPDATE work_orders SET status=%s, closed_at=CURRENT_TIMESTAMP(6) WHERE work_order_id=%s",        ("closed", work_order_id),    )    cur.execute(        """INSERT INTO work_order_audit           (work_order_id,old_status,new_status,change_source,changed_by)           VALUES(%s,%s,%s,%s,CURRENT_USER())""",        (work_order_id, old_status, "closed", "python_service"),    )    cnx.commit()except Error as exc:    cnx.rollback()    print({"errno": exc.errno, "sqlstate": exc.sqlstate, "message": exc.msg})    raiseexcept Exception:    cnx.rollback()    raisefinally:    try:        cur.close()    except Exception:        pass    cnx.close()

The transaction is now visible in application code. The driver error object exposes MySQL error number, SQLSTATE, and message, which can be mapped to structured telemetry rather than converted to a generic “database error.” Bound parameters separate values from SQL text.

Deterministic integration test: prove both state changes or neither

A refactor is not complete because the code “looks equivalent.” Test the database state. A simple integration test can capture the audit count before the operation, execute the transaction, then assert the exact work-order state and one matching audit record.

# Pseudocode using the same connector setup.# Arrange: choose a disposable open/waiting work order and record audit count.SELECT status FROM work_orders WHERE work_order_id=3;SELECT COUNT(*) FROM work_order_auditWHERE work_order_id=3 AND change_source='python_service';# Act: run the service transaction once.# Assert:SELECT status,closed_at FROM work_orders WHERE work_order_id=3;SELECT old_status,new_status,change_sourceFROM work_order_auditWHERE work_order_id=3 AND change_source='python_service'ORDER BY audit_id;# Failure injection test:# inside a transaction, perform UPDATE, then deliberately raise an application exception# before INSERT and call rollback. Assert the work_order status is unchanged and no audit row was added.

The failure-injection assertion is essential. It proves that transaction ownership still provides atomicity after moving logic out of the trigger.

Alternative: move the boundary into a definer procedure instead

If several clients must perform the operation and you do not want each client to receive direct audit-table privileges, a narrow procedure can be a better server-side API. The caller receives EXECUTE, while the procedure owns the transaction and definer privilege boundary. This is exactly why the chapter teaches server-side objects as a toolkit rather than a hierarchy where one mechanism is always “best.”

Wrong architecture: split one invariant across four hidden places

A fragile system might have a view that filters rows, a procedure that changes status, a trigger that writes audit history, an event that “repairs” missed rows, and application code that also retries and inserts audit records. Each individual object may seem reasonable, but together they produce duplicate side effects and unclear ownership.

The repair is to define one authoritative write path and make other mechanisms read-only or operationally separate. Document:

• who owns the business transaction;
• which account/definer has each privilege;
• which object can mutate which table;
• how errors are returned and retried;
• where success/failure metrics live;
• how schema/object migrations are ordered and rolled back;
• which integration tests prove invariants after deployment.

Observe the session and plan where relevant

Server-side logic does not eliminate normal SQL performance engineering. Views and routines eventually execute statements whose plans can regress. Capture session identity and explain expensive read statements just as Chapter 09 taught:

SELECT CONNECTION_ID(),USER(),CURRENT_USER(),@@session.time_zone,@@session.sql_mode;SHOW SESSION STATUS LIKE 'Ssl_cipher';EXPLAIN FORMAT=TREESELECT work_order_id,site_id,status,priority,summaryFROM v_open_work_ordersWHERE site_id=1ORDER BY work_order_id;EXPLAIN ANALYZESELECT work_order_id,site_id,status,priority,summaryFROM v_open_work_ordersWHERE site_id=1ORDER BY work_order_id;

A view does not make an expensive query cheap merely by naming it. Keep plans, statement metrics, and indexes observable.

Final Chapter 10 cleanup

When you finish the integration tests, remove the disposable objects and accounts. If you want to continue experimenting, export your SQL definitions first.

-- Run as local admin after disconnecting logic_owner/logic_app sessions.DROP DATABASE IF EXISTS servicehub_logic_lab;DROP USER IF EXISTS 'logic_owner'@'127.0.0.1';DROP USER IF EXISTS 'logic_app'@'127.0.0.1';DROP USER IF EXISTS 'event_limited'@'127.0.0.1';SELECT USER(),CURRENT_USER(),VERSION();

This cleanup deliberately drops only Chapter 10 names. Never use broad account/database cleanup patterns on a shared server.

Hands-on decision record

Chapter summary and bridge to Chapter 11

Chapter 10 made hidden server logic explicit: views have update/security/dependency semantics; routines have parameter, transaction, handler, and definer contracts; triggers add automatic row-level side effects; events add time-driven execution and scheduler ownership. The design principle is not “keep logic in SQL” or “keep logic in the app.” It is to put each invariant at a boundary whose privileges, failure modes, tests, deployment, and observability you can defend.

Chapter 11 now deepens the security model behind every example you just built: MySQL accounts, host matching, authentication, roles, privileges, dynamic administrative privileges, and least-privilege operations.

Authoritative references

• MySQL 8.4 — Stored Objects
• MySQL 8.4 — Stored Object Access Control
• MySQL 8.4 — Statements That Cannot Be Rolled Back
• MySQL Connector/Python Developer Guide
• MySQL 8.4 — Optimizing Queries with EXPLAIN