MySQL Bugs: #15921: DROP TRIGGER - can't be drop trigger created in older version

Bug #15921	DROP TRIGGER - can't be drop trigger created in older version
Submitted:	22 Dec 2005 2:09	Modified:	31 Mar 2006 6:36
Reporter:	Alexandre Pereira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.20-BK	OS:	Linux (Linux/Windows)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
A trigger created in MySQL 5.0.15 can't be dropped in 5.0.17.
People who created triggers in version 5.0.15 and later installed 5.0.17 can´t drop them now.
Workaround: Uninstall MySQL 5.0.17, install MySQL 5.0.15, DROP TRIGGER, uninstall MySQL 5.0.15, install MySQL 5.0.17, CREATE TRIGGER.

How to repeat:
CREATE TRIGGER in MySQL 5.0.15, uninstall MySQL 5.0.15, install MySQL 5.0.17, trying to DROP TRIGGER fails [Error: 1344: Unexpected end of file while skipping unknown parameter].

Thank you for the bug report. This also happens on Windows's servers.

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.05 sec)

mysql> exit
Bye

C:\mysql\bin>mysqladmin shutdown -uroot

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TRIGGER ins_sum;
ERROR 1344 (HY000): Unexpected end of file while skipping unknown parameter 'trigger_table'
mysql>

The Manual stands:
Note: Prior to MySQL 5.0.10, the table name was required instead of the schema name (table_name.trigger_name). When upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers before upgrading and re-create them afterwards, or else DROP TRIGGER does not work after the upgrade. See Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0” for a suggested upgrade procedure. 

So 5.0.15 isn't included in the above restriction.

The manual mentions another kind of problem. Before MySQL 5.0.10 triggers were named using the table name (table_name.trigger_name), now (5.0.15 or 5.0.17) they are named using the schema name. The bug I reported has a different origin.

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/3930

Reviewed by email.

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/4208

Pushed into 5.0 tree, currently tagged 5.0.20.

Merged into 5.1 tree, currently tagged 5.1.9-beta.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.20 and 5.1.9 changelogs. Closed.