MySQL Bugs: #23022: Can't drop trigger of an altered merge table

Bug #23022	Can't drop trigger of an altered merge table
Submitted:	5 Oct 2006 17:12	Modified:	13 Oct 2010 14:56
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
If I create a trigger on a merge table, then alter one
of the underlying tables, then try to drop the trigger,
I get error 1168. So it's now impossible to drop the trigger
unless I drop the table.

I'm aware that the ALTER is improper, and that may be
related to bug#457. But I'm not trying to open the table.

This problem was originally mentioned in a comment for bug#10974.

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

mysql> create table tm2 (s1 int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tm3 (s1 int) engine=merge union=(tm1,tm2) insert_method=last;
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger tm3_bi before insert on tm3 for each row set new.s1 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table tm2 partition by hash (s1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop trigger tm3_bi; /* Result: error 1168 */
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exists

Thank you for the bug report. Notice that the error messages had changed
from a server built some days ago with the current one:

mysql> drop trigger tm3_bi; /* Result: error 1168 */
ERROR 1017 (HY000): Can't find file: 'tm3' (errno: 2)
mysql> exit

mysql> drop trigger tm3_bi; /* Result: error 1168 */
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exists

workaround: Drop and create the merge table

Won't fix: please don't do that.