Bug #52288 MySQL sometimes does not accept BEGIN [WORK]
Submitted: 23 Mar 2010 1:26 Modified: 26 May 2010 23:37
Reporter: Peter Brawley (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5.2, 5.0, 5.1 OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: qc

[23 Mar 2010 1:26] Peter Brawley
Description:
This test sproc creation succeeds when it starts a transaction with START TRANSACTION, fails when it starts the transaction with what the documentation says is equivalent syntax, BEGIN [WORK] ...

drop procedure if exists t;
delimiter go
create procedure t()
begin
  DECLARE errmsg CHAR(32) DEFAULT '';
  drop table if exists u,t;
  create table t(i int primary key, d datetime not null) engine=innodb;
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
      BEGIN
        SET errmsg = 'An error occurred';
        ROLLBACK;
      END;
 -- START TRANSACTION;
    BEGIN WORK ;
      insert into t values(1,now()),(2,now());
      update t set i=i+1 order by i desc;
      insert into t values(2,null);       -- illegal key, null
    COMMIT;
  END;
  IF errmsg <> '' THEN
    SELECT errmsg;
  ELSE
    select * from t;
  END IF;
end ;
go
delimiter ;
call t();

How to repeat:
As above

Suggested fix:
Use START TRANSACTION, fix docs refs to BEGIN [WORK].
[23 Mar 2010 6:22] Sveta Smirnova
Thank you for the report.

Verified as described: at http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html or at http://dev.mysql.com/doc/refman/5.1/en/commit.html there is no word BEGIN in stored routines only can be used as start of compound statement, not synonym of START TRANSACTION
[26 May 2010 20:19] Paul Dubois
This problem is not specific to procedures and functions, it applies to all stored programs (including triggers and events).

http://dev.mysql.com/doc/refman/5.1/en/commit.html
and
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html
say:

"
Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.
"
[26 May 2010 20:31] Peter Brawley
Yes, the documentation needs to emphasise this better.
[26 May 2010 22:43] Paul Dubois
"emphasise this better"

Please clarify. I think the referenced sections explain the issue with appropriate emphasis, which is why I closed the report.
[26 May 2010 23:37] Peter Brawley
The documentation is adequate for those who've read most of it. Obviously most readers haven't. In the far more likely case that you're new to MySQL, or new to InnoDB, or new  to transactions, you might type ...

BEGIN TRANSACTION

in the search box at http://dev.mysql.com/doc/refman/5.5/en/

Currently that gives you two hits, the first of which brings up a page where you learn ...

"A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK  statement."

There is no mention in that para---or indeed on that page---of the stored routine syntax exception. To be other than misleading, that para needs a prominent note about the syntax exception for stored routines and a pointer to a full discussion of the matter. Ditto for all other mentions of this point in the manual.