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

[22 Dec 2005 2:09] Alexandre Pereira
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].
[22 Dec 2005 2:41] Miguel Solorzano
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.
[22 Dec 2005 13:24] Alexandre Pereira
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.
[17 Mar 2006 15:42] 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/3930
[27 Mar 2006 20:34] Konstantin Osipov
Reviewed by email.
[27 Mar 2006 21:04] 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/4208
[27 Mar 2006 22:15] Alexander Nozdrin
Pushed into 5.0 tree, currently tagged 5.0.20.
[28 Mar 2006 11:27] Alexander Nozdrin
Merged into 5.1 tree, currently tagged 5.1.9-beta.
[31 Mar 2006 6:36] Jon Stephens
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.