Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement
Submitted: 12 Jan 2006 3:57 Modified: 22 Feb 2006 18:36
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov CPU Architecture:Any

[12 Jan 2006 3:57] Peter Gulutzan 
Description:
Ordinarily, CREATE statements are illegal inside CREATE TRIGGER
because they involve an implicit "commit". But CREATE EVENT is
legal. There is a failure later, when the trigger is activated.

How to repeat:
mysql> create table t_16 (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_16 values (0);
ERROR 1100 (HY000): Table 'event' was not locked with LOCK TABLES
mysql> drop trigger t_16_bi;
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger t_16_bi before insert on t_16 for each row create table t_16b (s1 int);
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.
[30 Jan 2006 19:35] 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/commits/1890
[30 Jan 2006 19:39] 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/commits/1891
[30 Jan 2006 19:46] 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/commits/1892
[7 Feb 2006 18:03] 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/commits/2263
[15 Feb 2006 16:14] 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/commits/2658
[15 Feb 2006 21:39] Andrey Hristov 
Fixed in the upcoming 5.1.7. See the changeset comments for more details.
[22 Feb 2006 18:36] Mike Hillyer 
Documented in 5.1.7 changelog:

      <listitem>
        <para>
          CREATE/DROP/ALTER EVENT statements are not allowed in
          triggers. (Bug #16410)
        </para>
      </listitem>