MySQL Bugs: #43362: Missing implicit commit after statements that commit active transactions

Bug #43362	Missing implicit commit after statements that commit active transactions
Submitted:	4 Mar 2009 10:10	Modified:	28 Apr 2010 12:41
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.33	OS:	Any
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
In http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html there is a number of commands that have an explicit commit before the statement, but since they have effect immediately and cannot be rolled back, they are actually separate transactions and not part of a transaction that follows that statement.

How to repeat:
It can be seen in the code, but can probably also be triggered by enabling the binary log in a later version, starting a transaction with one of the statements, adding some transactional DML, and then rolling back the transaction.

Suggested fix:
Add an implicit commit after execution of the statements that do an implicit commit before the statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68330

2757 Mats Kindahl	2009-03-05
      BUG#43362: Missing implicit commit after statements that commit
      active transactions
      
      This is backport to 5.1 of a patch from 6.0 that is part of the
      work done for WL#4284 by Davi.
      
      For statements that do an implicit commit before, there is a missing
      commit after. This means that the statement can participate in the
      following transaction, and it is especially important to get all
      commits right for the binary logging to work correctly.
      
      This patch adds two flags to the sql_command_flags: one for implicit
      commit before and one for implicit commit after. It then extends the
      command flags for the statements that do an implicit commit before to
      use both these flags.  The explicit calls to end_active_trans() for
      statements doing implicit commit is removed and a new function
      opt_implicit_commit() is added. This function will do an implicit
      commit if the command flag says so, otherwise, it will do nothing.
      
      In addition, the patch adds a new set of server command flags, which
      are used to replace code that decide when next_query_id() should be
      called and when statistics_increment() should be called. Instead,
      there are now two new flags: one that can be set if the command should
      skip increasing the statistics for questions, and one that can be set
      if the command should not step the query id.
     @ mysql-test/include/commit.inc
        Commit count changed.
     @ mysql-test/t/implicit_commit.test
        File to test for implicit commits.
     @ sql/events.cc
        Removing call to end_active_trans() since this is handled in
        sql_parse.cc now.
     @ sql/sql_class.h
        Adding flags for statistics and query id increment as well as flags
        for implicit commit before and after execution of command.
     @ sql/sql_delete.cc
        Removing explicit call to commit since this is handled in
        sql_parse.cc now.
     @ sql/sql_parse.cc
        Adding function opt_implicit_commit() that handles implicit commit
        for statements.
        
        Adding server_command_flags to handle statistics and query id
        increment and replacing explicit code for doing that with code
        that checks the flags.
        
        Setting implicit commit flags for all statements that do implicit
        commit before and after and adding code to call opt_implicit_commit()
        function before and after execution of commands.
        
        Removing unneeded calls to end_active_trans().
     @ sql/sql_table.cc
        Removing call to end_active_trans() since this is handled in
        sql_parse.cc now.
     @ tests/mysql_client_test.c
        Adding test to see that COM_REFRESH issues an implicit commit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68332

2810 Mats Kindahl	2009-03-05 [merge]
      Merging in fix for BUG#43362.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68496

2825 Mats Kindahl	2009-03-06 [merge]
      Merging patch for BUG#43362 into 5.1-dev-performance-version.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73755

2847 Mikael Ronstrom	2009-05-08
      BUG#43362: Missing implicit commit after statements that commit
                active transactions
      
                This is backport to 5.1 of a patch from 6.0 that is part of the
                work done for WL#4284 by Davi.
      
                For statements that do an implicit commit before, there is a missing
                commit after. This means that the statement can participate in the
                following transaction, and it is especially important to get all
                commits right for the binary logging to work correctly.
      
                This patch adds two flags to the sql_command_flags: one for implicit
                commit before and one for implicit commit after. It then extends the
                command flags for the statements that do an implicit commit before to
                use both these flags.  The explicit calls to end_active_trans() for
                statements doing implicit commit is removed and a new function
                opt_implicit_commit() is added. This function will do an implicit
                commit if the command flag says so, otherwise, it will do nothing.
      
                In addition, the patch adds a new set of server command flags, which
                are used to replace code that decide when next_query_id() should be
                called and when statistics_increment() should be called. Instead,
                there are now two new flags: one that can be set if the command should
                skip increasing the statistics for questions, and one that can be set
                if the command should not step the query id.
      modified:
        mysql-test/include/commit.inc
        mysql-test/r/commit_1innodb.result
        sql/events.cc
        sql/mysql_priv.h
        sql/sql_class.h
        sql/sql_delete.cc
        sql/sql_parse.cc
        sql/sql_table.cc
        tests/mysql_client_test.c

This is part of MDL, but one that can be extracted out. Entire MDL is planned for Celosia.

This is now in the trunk and in a public release too.
No documentation entry is necessary since this is an internal issue.