📋

Description

This error occurs when a `COMMIT` statement, either explicitly written or implicitly triggered by certain SQL commands, is executed within a stored function or database trigger. MariaDB prohibits transaction control statements in these contexts to preserve the transactional integrity of the calling statement.

💬

Error Message

Explicit or implicit commit is not allowed in stored function or trigger.

🔍

Known Causes

3 known causes

⚠️

Direct Transaction Control

The stored function or trigger code explicitly contains a `COMMIT` or `ROLLBACK` statement.

⚠️

DDL Statement Execution

Data Definition Language (DDL) statements (e.g., `CREATE`, `ALTER`, `DROP`) implicitly commit the current transaction and are forbidden within these routines.

⚠️

Calling Prohibited Routines

The function or trigger calls another stored procedure or function that, in turn, attempts a `COMMIT` or `ROLLBACK` operation.

🛠️

Solutions

3 solutions available

1. Remove Explicit COMMIT/ROLLBACK Statements easy

Directly remove any `COMMIT` or `ROLLBACK` statements within the stored function or trigger.

1

Identify the stored function or trigger that is causing the error. You can find this by looking at the error message's context or by reviewing your stored routine definitions.

2

Open the definition of the identified stored function or trigger for editing. In MariaDB, you can use `SHOW CREATE FUNCTION function_name;` or `SHOW CREATE TRIGGER trigger_name;` to get the definition, and then `DROP FUNCTION function_name;` and `CREATE FUNCTION ...` or `DROP TRIGGER trigger_name;` and `CREATE TRIGGER ...` to modify it.

SHOW CREATE FUNCTION my_function;
-- OR
SHOW CREATE TRIGGER my_trigger;

3

Locate and delete any lines containing `COMMIT;` or `ROLLBACK;` within the routine's body.

-- Before:
BEGIN
  -- some statements
  COMMIT;
END

4

Recreate the stored function or trigger with the modified definition.

CREATE FUNCTION my_function (...) RETURNS ...
BEGIN
  -- some statements without COMMIT/ROLLBACK
END;

-- OR

CREATE TRIGGER my_trigger AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
  -- some statements without COMMIT/ROLLBACK
END;

2. Rethink Transaction Management Outside the Routine medium

Move transaction control statements to the application code or calling procedure.

1

Analyze where the stored function or trigger is being called from. Is it from another stored procedure, application code (PHP, Python, Java, etc.), or directly from a client tool?

2

If the routine is called from application code, ensure that the `COMMIT` or `ROLLBACK` statements are handled by the application's transaction management logic. The application should initiate a transaction, call the routine (without expecting it to commit), and then explicitly commit or rollback the entire transaction after the routine has completed.

// Example in Python with mariadb connector
import mariadb

try:
    conn = mariadb.connect(...)
    cur = conn.cursor()
    cur.execute("START TRANSACTION")
    cur.callproc("my_function_that_does_not_commit", [...])
    conn.commit() # Application commits
except mariadb.Error as e:
    print(f"Error: {e}")
    conn.rollback() # Application rolls back
finally:
    if 'cur' in locals() and cur:
        cur.close()
    if 'conn' in locals() and conn:
        conn.close()

3

If the routine is called from another stored procedure, refactor the calling procedure to manage the transaction. The called routine should perform its work without committing, and the calling procedure should then issue the `COMMIT` or `ROLLBACK`.

-- Calling procedure
DELIMITER //
CREATE PROCEDURE wrapper_procedure()
BEGIN
  START TRANSACTION;
  CALL my_function_that_does_not_commit(...);
  -- ... other operations
  COMMIT; -- Or ROLLBACK based on conditions
END //
DELIMITER ;

-- Modified stored function (no COMMIT/ROLLBACK)
DELIMITER //
CREATE FUNCTION my_function_that_does_not_commit(...)
RETURNS ...
BEGIN
  -- ... operations
END //
DELIMITER ;

3. Use Temporary Tables for Intermediate Results medium

If the routine needs to perform operations that implicitly commit (like creating a temporary table), refactor to avoid those operations.

1

Examine the stored function or trigger for any statements that might implicitly cause a commit. A common culprit is the creation of `TEMPORARY` tables within a routine if the `sql_log_bin` variable is set to `ON` and the `binlog_format` is `ROW` or `MIXED` in certain MariaDB versions. While less common for explicit commits, it's good practice to be aware of implicit transaction boundaries.

2

If the routine needs to store intermediate results, consider passing a table name as a parameter to the routine and have the routine populate that table, or have the calling code create the temporary table and pass it to the routine.

-- Example: Passing a table name to a procedure
DELIMITER //
CREATE PROCEDURE process_data(IN temp_table_name VARCHAR(255))
BEGIN
  -- Use the provided table name
  INSERT INTO temp_table_name (col1, col2) VALUES (1, 'a');
END //
DELIMITER ;

-- In calling code:
START TRANSACTION;
CREATE TEMPORARY TABLE my_temp_data (col1 INT, col2 VARCHAR(10));
CALL process_data('my_temp_data');
-- ... other operations
COMMIT;

3

Alternatively, if the routine is designed to return a set of rows (like a table-valued function), ensure it does not attempt to commit. The calling context should handle the transaction.

🔗

Related Errors

5 related errors

MariaDB Error 1422: Commit in Stored Routine