Bug #13343 CREATE|etc TRIGGER|VIEW|USER don't commit the transaction (inconsistency)
Submitted: 20 Sep 2005 9:30 Modified: 13 Oct 2005 2:09
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:Linux (linux)
Assigned to: Elliot Murphy CPU Architecture:Any

[20 Sep 2005 9:30] Guilhem Bichot 
Description:
While CREATE|DROP|ALTER PROCEDURE|FUNCTION commit the current transaction (fix of BUG#12870), and CREATE|DROP|ALTER|RENAME TABLE also do, and CREATE|DROP DATABASE also do, the new statements
CREATE|DROP|RENAME|ALTER VIEW|TRIGGER|USER
do not commit the current transaction.
This is an inconsistency, there seemed to be a consensus that all DDLs should have an implicit commit. Will now ask PeterG if he also thinks this is a good thing to do.

How to repeat:
run this test:
-- source include/have_innodb.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1 (ok_statements varchar(100)) engine=innodb;

begin;
insert into t1 values("this table is not capable of rollback if you see this");
rollback;

begin;
insert into t1 values("CREATE TRIGGER");
create trigger trg before insert on t1 for each row set @a:=1;
rollback;

begin;
insert into t1 values("CREATE PROCEDURE");
create procedure foo42()
  insert into t1 values("");
rollback;

begin;
insert into t1 values("CREATE VIEW");
create view v1 (c) as select 1 from t1;
rollback;

begin;
insert into t1 values("CREATE USER");
CREATE USER dummy@localhost;
rollback;

select * from t1;

#TODO: test ALTER and DROP and RENAME

drop table t1;
DROP USER dummy@localhost;

Suggested fix:
end_active_trans()
[10 Oct 2005 16:28] Bugs System 
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/internals/30863
[12 Oct 2005 18:01] Elliot Murphy 
Fix has been pushed for 5.0.15.

CREATE VIEW
ALTER VIEW
DROP VIEW
CREATE TRIGGER
DROP TRIGGER
CREATE USER
RENAME USER
DROP USER

All these statements now cause implicit commit (alter view was already doing this) of the active transaction.
[13 Oct 2005 2:09] Mike Hillyer 
Documented in 5.0.15:

<listitem>
        <para>
          The following statements now cause an implicit
          <literal>COMMIT</literal>:
        </para>
        
        <itemizedlist>
          <listitem><para>CREATE VIEW</para></listitem>
          <listitem><para>ALTER VIEW</para></listitem>
          <listitem><para>DROP VIEW</para></listitem>
          <listitem><para>CREATE TRIGGER</para></listitem>
          <listitem><para>DROP TRIGGER</para></listitem>
          <listitem><para>CREATE USER</para></listitem>
          <listitem><para>RENAME USER</para></listitem>
          <listitem><para>DROP USER</para></listitem>
        </itemizedlist>
      </listitem>
[15 Nov 2005 17:31] Bugs System 
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/internals/32282
[16 Nov 2005 12:09] Bugs System 
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/internals/32312