MacLochlainns Weblog

Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL statement generates one. This means you can’t include any DDL statement inside a stored function.

The following example shows how a DDL statement creates an immediate implicit COMMIT. It requires two sessions and Transaction Control Language (TCL) statements.

In session one, create a table, start a transaction scope, and insert one row into the table:

-- Create a table.
CREATE TABLE message
( message_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, message_text  VARCHAR(20));
 
-- Start a transaction context.
BEGIN WORK;
 
-- Insert a row into the MESSAGE table.
INSERT INTO message (message_text) VALUES ('Implicit Commit?');

In session two, the empty set is displayed when you query the MESSAGE table. Returning to session one, add a new column to the MESSAGE table with this statement:

ALTER TABLE message ADD (sent DATETIME);

The ALTER statement automatically makes current any pending data changes, effectively committing the record from session one. You can return to the second session and query the table you get the following results:

+------------+------------------+------+
| message_id | message_text     | sent |
+------------+------------------+------+
|          1 | Implicit Commit? | NULL |
+------------+------------------+------+

When you embed DML statements inside functions, they rely on automatic commit behaviors or an external transaction scope. This function compiles and returns 1 when successful and 0 when unsuccessful, which effectively controls all error conditions within the function:

CREATE FUNCTION modifies_data ( pv_message  CHAR(20) ) RETURNS INT
MODIFIES SQL DATA
BEGIN
  /* Declare Boolean-like variables as FALSE. */
  DECLARE lv_return_value INT DEFAULT FALSE;
  DECLARE lv_error_value INT DEFAULT FALSE;
 
  /* Declare a generic exit handler to reset error control variable to true. */
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET lv_error_value := TRUE;   
 
  /* Insert statement with auto commit enabled. */
  INSERT INTO message (message_text) VALUES (pv_message);
 
  /* True unless the CONTINUE HANDLER disables the error control variable. */ 
  IF lv_error_value = FALSE THEN
    SET lv_return_value := TRUE;
  END IF;
 
  /* Return local variable. */
  RETURN lv_return_value;
END;
$$

With auto commit enabled, any call to the function in a query writes a row to the table. If you start a transaction and in the scope of the transaction test the function in one session and query the table in another you’ll see that transaction control can be managed outside the function. You can also manage the transaction control inside a stored procedure, which holds all the TCL commands. An example follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE PROCEDURE test_modifies_data ( pv_message  CHAR(20) )
MODIFIES SQL DATA
BEGIN
  /* Declare a control variable that manages transaction
     success or failure. */
  DECLARE lv_success_value  INT DEFAULT FALSE;
 
  /* Start a transaction context. */
  START TRANSACTION;
 
  /* Set a SAVEPOINT in the transaction context. */
  SAVEPOINT before_transaction;
 
  /* Call the function. */
  SET lv_success_value := modifies_data(pv_message);
 
  /* Check the status of the control variable, and commit
     or rollback the transaction. */
  IF lv_success_value = TRUE THEN
    COMMIT;
  ELSE
    ROLLBACK TO before_transaction;
  END IF;
 
END;
$$

Test the two with the following call to the procedure:

CALL test_modifies_data('Agent Coulson');

Answers my question and I hope it helps others.