Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement
Submitted: 12 Jan 2006 4:57 Modified: 22 Feb 2006 19:36
Reporter: Peter Gulutzan
Status: Closed
Category:Server: SP Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov Target Version:

[12 Jan 2006 4: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 20: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 20: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 20: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 19: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 17: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 22:39] Andrey Hristov 
Fixed in the upcoming 5.1.7. See the changeset comments for more details.
[22 Feb 2006 19: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>