How to Drop the Trigger

If we speak about MySQL triggers, does anybody need a "Howto" on this topic? It must be simple if you know the trigger name to drop, just use DROP TRIGGER, as manual explains:

mysql> use test
Database changedmysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tr1
               Event: INSERT
               Table: t1
           Statement: set @a=1
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)


So, we know trigger name, we are sure this trigger is not needed, we are root and have all the privileges, so we drop it:

mysql> drop trigger tr1;
ERROR 1360 (HY000): Trigger does not exist

Now, why this can happen? The table (InnoDB one) is there:

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.23 sec)




Maybe we have a bug? Quick search gives some hits:

Bug #64814 Inconsistent error when concurrently dropping table and trigger - "Verified" bug, but we do not drop the table concurrently, we try to drop trigger from a single session connected:

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              26 |
+-----------------+
1 row in set (0.01 sec)

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host           | db   | Command | Time | State | Info             |
+----+------+----------------+------+---------+------+-------+------------------+
| 26 | root | localhost:2616 | test | Query   |    0 | NULL  | show processlist |
+----+------+----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Bug #39489 Cannot create or drop triggers without SUPER privilege - "In progress" bug, so may affect the case, as I have binary logging enabled:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

But error message should be different, and I am root@localhost after all, I have SUPER:

mysql> select Super_priv, host from mysql.user where user='root';
+------------+-----------+
| Super_priv | host      |
+------------+-----------+
| Y          | localhost |
| N          | %         |
+------------+-----------+
2 rows in set (0.00 sec)

Bug #23022 Can't drop trigger of an altered merge table - "Won't fix" - does not sound good, the problem is not solved, but my table is NOT MERGE one. Again, does not apply.

Bug #15921 DROP TRIGGER - can't be drop trigger created in older version - the bug is "Closed", and I had created this trigger just yesterday, in the same version 5.5.29, not upgraded from some 5.0 or 5.1.

We can continue to search for bugs, but now I am not sure if even one more hour spent on this will be helpful. MySQL is NOT only about bugs after all.

What else? Time to take a look at database directory at filesystem level, maybe it is about permissions or something:

C:\...MySQL\MySQL Server 5.5\data\test>dir
 Volume in drive C is SQ003666
 Volume Serial Number is B4BD-A73C

 Directory of C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\test

01.02.2013  13:20    <DIR>          .
01.02.2013  13:20    <DIR>          ..
31.01.2013  10:20                61 db.opt
31.01.2013  10:20             8 582 t1.frm
31.01.2013  10:20            98 304 t1.ibd
31.01.2013  10:43               251 t1.TRG
               4 File(s)        107 198 bytes
               2 Dir(s)   3 399 356 416 bytes free

Sorry, I use Windows this time. Anyway, table files are there, but what is this .TRG file about? If you do not know this yet, quick search will help to find out, in the FAQ, for example:

Triggers for a table are currently stored in .TRG files, with one such file one per table.

OK, the file is there, what is inside:

C:\...MySQL\MySQL Server 5.5\data\test>more t1.TRG
TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` trigger tr1 before insert on t1 for
each row set @a=1'
sql_modes=1344274432
definers='root@localhost'
client_cs_names='utf8'
connection_cl_names='utf8_general_ci'
db_cl_names='utf8_general_ci'

OK, trigger code and some other details that SHOW TRIGGERS outputs. What else is needed, really, WTF?

If you do not know the answer (try to find it in the manual, by the way), only Google can help. If you are lucky eventually you'll find this Internals manual page, that says something useful finally:

Trigger definitions are stored in plain text files in the directory that contains the schema objects.
The file <schema>/<trigger>.TRN is the TRIGGERNAME file. It represents the fact that the object named “trigger” is a table trigger, and points to the table the trigger is attached to. Every trigger has a dedicated *.TRN file. This design decision is used to facilitate operating system filesystem services to enforce the SQL standard requirement that all triggers in a given schema must be unique.
The file <schema>/<table>.TRG is the TRIGGERS file. It represents all the table triggers attached to a given table, so this file can contain triggers for multiple events (BEFORE/AFTER, INSERT/UPDATE/DELETE). Currently it is impossible to have more than one trigger per table for a given trigger action time and type, hence this file may contain at most six trigger definitions.

OK, among other useful things we see that there MUST be .TRN file with the same name as trigger, and we do not see it in the listing above. Now, three questions remain:

1. What is in that file, precisely?
2. How to get that file back?
3. Why it disappeared?

 Again, if you do not know the answer to the first question, it is easy to find out. Just create one ore trigger, on different even, and check the file (let's hope it is there):

mysql> create trigger tr2 after insert on t1 for each row set @b=2;
Query OK, 0 rows affected (0.25 sec)

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tr1
               Event: INSERT
               Table: t1
           Statement: set @a=1
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: tr2
               Event: INSERT
               Table: t1
           Statement: set @b=2
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.03 sec)

It was created, now let's check the tr2.TRN file content:

C:\...MySQL\MySQL Server 5.5\data\test>more tr2.TRN
TYPE=TRIGGERNAME
trigger_table=t1

In hex, if you are smart enough to care about line breaks:

C:\...L Server 5.5\data\test\tr2.TRN        DOS            34     Col 0     100%
0000000000:  54 59 50 45 3D 54 52 49 │ 47 47 45 52 4E 41 4D 45  TYPE=TRIGGERNAME
0000000010:  0A 74 72 69 67 67 65 72 │ 5F 74 61 62 6C 65 3D 74  ◙trigger_table=t
0000000020:  31 0A                   │                          1◙

So, now you know how to create missing tr1.TRN: either copy any .TRN and edit it, or just use any editior to create it, just make sure correct line ending (UNIX style) are used. This is what you should get eventually:

C:\...MySQL\MySQL Server 5.5\data\test>dir
 Volume in drive C is SQ003666
 Volume Serial Number is B4BD-A73C

 Directory of C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\test

01.02.2013  13:39    <DIR>          .
01.02.2013  13:39    <DIR>          ..
31.01.2013  10:20                61 db.opt
31.01.2013  10:20             8 582 t1.frm
31.01.2013  10:20            98 304 t1.ibd
01.02.2013  13:34               411 t1.TRG
31.01.2013  10:43                34 tr1.trn
01.02.2013  13:34                34 tr2.TRN
               6 File(s)        107 426 bytes
               2 Dir(s)   3 399 024 640 bytes free

C:\...MySQL\MySQL Server 5.5\data\test>more tr1.trn
TYPE=TRIGGERNAME
trigger_table=t1

(well, better make sure it is .TRN, for Linux this matters, but on Windows may work anyway), and, finally:

mysql> drop trigger tr1;
Query OK, 0 rows affected (0.13 sec)

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tr2
               Event: INSERT
               Table: t1
           Statement: set @b=2
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.03 sec)

So, trigger is no longer undroppable and we can recreate it now etc. All because of single file missing, that can be just created, as it is a plain text one. Very confusing if you had never noted all these details before or try to read the manual and find the answer.

The only question that remains is: why file disappeared? If you had not deleted it manually (as I did), this is a result of some bug, either in MySQL or in some software that you had used to backup/restore your data. Check these, for example:

Bug #40351 innobackup does not copy .ARZ and .ARM files - should be fixed in recent Oracle MEB versions.

innobackupex does not copy the .TRN files if --databases=db.table is used - here bug status is not that clear.

You can try to search more, but anyway this problem is easy to fix when you encounter it.