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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.