Bug #13672 Incorrect message with OPTIMIZE/ANALYZE/ALTER TABLE and trigger

• Submitted: 30 Sep 2005 21:53
• Modified: 3 Aug 2006 12:14
• Reporter: jocelyn fournier (Silver Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Linux (linux)
• Assigned to: Tomash Brechko
• CPU Architecture: Any

[30 Sep 2005 21:53] jocelyn fournier

Description:
Hi,

Just to report the error messages are not really correct when using OPTIMIZE TABLE/ALTER TABLE/ANALYZE TABLE in a trigger :

CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN OPTIMIZE TABLE t1; END |
ERROR 1314 (0A000): OPTIMIZE TABLE is not allowed in stored procedures

CREATE TRIGGER test  BEFORE INSERT ON t1 FOR EACH ROW BEGIN ALTER TABLE t1 CHANGE a b int; END |
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

CREATE TRIGGER test  BEFORE INSERT ON t1 FOR EACH ROW BEGIN ANALYZE TABLE t1; END |
ERROR 1415 (0A000): Not allowed to return a result set from a trigger

BTW if those commands are not allowed, why is REPAIR TABLE autorized ?

CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN REPAIR TABLE t1; END |
Query OK, 0 rows affected (0.00 sec)

Regards,
  Jocelyn

How to repeat:
CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN OPTIMIZE TABLE t1; END |

CREATE TRIGGER test  BEFORE INSERT ON t1 FOR EACH ROW BEGIN ALTER TABLE t1 CHANGE a b int; END |

CREATE TRIGGER test  BEFORE INSERT ON t1 FOR EACH ROW BEGIN ANALYZE TABLE t1; END |

Suggested fix:
Change the message to :

ERROR 1314 (0A000): OPTIMIZE TABLE is not allowed in stored function or trigger

ALTER TABLE is not allowed in stored function or trigger

ANALYZE TABLE is not allowed in stored function or trigger

...

to be consistant.

[22 Jun 2006 19:59] Konstantin Osipov

Changing the version number to 5.1 per discussion with Brian.

[3 Aug 2006 12:14] Andrey Hristov

Cannot repeat anymore with 5.1.12-dev

[3 Aug 2006 12:15] Andrey Hristov

ysql> use db2|
Database changed
mysql> CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN OPTIMIZE TABLE t1;
    -> END |
ERROR 1415 (0A000): Not allowed to return a result set from a trigger
mysql> CREATE TRIGGER test  BEFORE INSERT ON t1 FOR EACH ROW BEGIN ALTER TABLE t1
    -> CHANGE a b int; END |
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.
mysql> CREATE TRIGGER test  BEFORE INSERT ON t1 FOR EACH ROW BEGIN ANALYZE TABLE t1;
    -> END |
ERROR 1415 (0A000): Not allowed to return a result set from a trigger
mysql> CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN REPAIR TABLE t1; END
    -> |
ERROR 1415 (0A000): Not allowed to return a result set from a trigger