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

[5 Oct 2006 17:12] Peter Gulutzan
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
[5 Oct 2006 18:37] MySQL Verification Team
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
[20 Feb 2008 19:33] Omer Barnir
workaround: Drop and create the merge table
[13 Oct 2010 14:56] Konstantin Osipov
Won't fix: please don't do that.