📋

Description

This error indicates an attempt to modify the `autocommit` system variable from within a stored function or trigger. MariaDB explicitly disallows changing transaction modes inside these types of stored programs to maintain transactional integrity and predictability, as their execution context is part of a larger transaction.

💬

Error Message

Not allowed to set autocommit from a stored function or trigger

🔍

Known Causes

3 known causes

⚠️

Direct Autocommit Modification

The stored function or trigger contains an explicit `SET autocommit = ...` statement attempting to change the session's autocommit mode.

⚠️

Misunderstanding Transaction Scope

Developers might incorrectly assume that `autocommit` can be managed locally within a stored function or trigger, similar to application-level code.

⚠️

Incompatible Code Porting

The routine's logic might originate from another database system that permits `autocommit` changes within stored programs, leading to compatibility issues when ported to MariaDB.

🛠️

Solutions

3 solutions available

1. Remove Autocommit Setting from Stored Routine easy

Identify and remove any explicit `SET autocommit = ...` statements within your stored functions or triggers.

1

Examine the source code of your stored functions and triggers. Look for any lines that attempt to modify the `autocommit` variable.

SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'your_database_name';

2

If you find a line like `SET autocommit = 1;` or `SET autocommit = 0;` within the routine, delete or comment it out.

-- SET autocommit = 1; -- This line should be removed or commented out

3

Re-create the stored function or trigger with the modified code.

DELIMITER //
CREATE FUNCTION your_function_name (...) RETURNS ...
BEGIN
  -- Your logic here without SET autocommit
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER your_trigger_name BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  -- Your logic here without SET autocommit
END //
DELIMITER ;

2. Manage Transactions Explicitly Outside the Routine medium

Control transaction behavior at the application level or in a calling stored procedure, rather than within the function or trigger itself.

1

Ensure that your application code or any calling stored procedure explicitly manages transactions using `START TRANSACTION;`, `COMMIT;`, and `ROLLBACK;`.

START TRANSACTION;
CALL your_stored_function(...);
-- Perform other operations if needed
COMMIT; -- or ROLLBACK;

2

If the stored routine needs to perform multiple operations that should be atomic, consider refactoring it into a stored procedure that handles its own transaction management.

DELIMITER //
CREATE PROCEDURE your_procedure_name ()
BEGIN
  START TRANSACTION;
  -- Call your function or perform other DML statements
  -- For example: SELECT your_function_name();
  -- INSERT INTO ...;
  COMMIT;
END //
DELIMITER ;

-- Then call the procedure from your application or another routine:
CALL your_procedure_name();

3. Adjust Server Configuration (with Caution) advanced

Modify the `sql_mode` server variable to exclude `NO_AUTO_COMMIT` if you absolutely need autocommit to be enabled within stored routines. This is generally not recommended.

1

Identify the current `sql_mode` setting. This can be done by logging into MariaDB and running the following command.

SHOW VARIABLES LIKE 'sql_mode';

2

If `NO_AUTO_COMMIT` is present in the `sql_mode` output, you can temporarily remove it. **Warning:** Changing `sql_mode` can have unintended consequences on other parts of your database. It's highly recommended to test this thoroughly in a non-production environment.

SET GLOBAL sql_mode = 'your_current_sql_mode_without_NO_AUTO_COMMIT'; -- Replace 'your_current_sql_mode_without_NO_AUTO_COMMIT' with the actual value

3

To make this change permanent, you will need to edit your MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`) and restart the MariaDB service.

[mysqld]
sql_mode = "your_current_sql_mode_without_NO_AUTO_COMMIT"

4

Restart the MariaDB service.

sudo systemctl restart mariadb

🔗

Related Errors

5 related errors

MariaDB Error 1445: Autocommit in Stored Routines