Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger

mysql stored-procedures stored-functions
bobobobo picture bobobobo · Jun 6, 2013 · Viewed 31k times · Source

Everywhere I look it seems MySQL stored procedures can do transactions. Yet when I declare my stored function

create function test( a int )
returns int
MODIFIES SQL DATA
BEGIN
  START TRANSACTION ;
  update t set col='some value' where id=a ;
  COMMIT ;
  return 0 ;
END //

I get

Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.

Answer

bobobobo picture bobobobo · Jun 6, 2013

Actually you are not allowed transactions inside stored functions. You are allowed transactions inside stored procedures only.

create procedure test( a int )
MODIFIES SQL DATA
BEGIN
  START TRANSACTION ;
  update t set col='some value' where id=a ;
  COMMIT ;
END //

To return values from the SP, use output parameters or use the result set from the last select statement in the SP.

Related questions

View stored procedure/function definition in MySQL

What is the MySQL command to view the definition of a stored procedure or function, similar to sp_helptext in Microsoft SQL Server? I know that SHOW PROCEDURE STATUS will display the list of the procedures available. I need to …

Read More
How to throw an error in MySql procedure?

What is the mechanism to force the MySQL to throw an error within the stored procedure? I have a procedure which call s another function: PREPARE my_cmd FROM @jobcommand; EXECUTE my_cmd; DEALLOCATE PREPARE my_cmd; the job command …

Read More
How to use Table output from stored MYSQL Procedure

I've been looking for the last hour or so and haven't found a conclusive answer to this seemingly simple problem: How do you call a stored MYSQL function/procedure and use its output in further SELECT queries? Although this obviously …

Read More