Cannot delete MySQL trigger. Appears in information_schema, but no .TRN file

Question

I have a MySQL database with a few tables and triggers. A process was trying to drop one of the triggers and it failed with the following error:

Malformed file type header in file 'trigger_name.TRN'

I tried to drop the trigger manually, but obviously got the same error. So I went ahead and deleted the .TRN file itself (was that a bad idea?).

After deleting the .TRN file, the trigger still appears in information_schema.TRIGGERS. If I try to drop it I get error "Trigger does not exist". If I try to create another trigger with the same name, table and event manipulation I get error "Trigger already exists".

I don't know what else to do. How can I really delete this trigger so that it can be created again?

MySQL version is 5.0.22 and all tables are MyISAM.

SHOW CREATE TABLE outputs:

CREATE TABLE `cash_operation` (
 `id` int(11) NOT NULL auto_increment,
 `amount` decimal(16,8) NOT NULL default '0.00000000',
 `comment` varchar(500) NOT NULL default '',
 `reason_id` int(11) NOT NULL default '-1',
 `date` datetime NOT NULL default '1980-01-01 00:00:00',
 `reconciliation_number` int(11) NOT NULL default '-1',
 `reason_desc` varchar(250) NOT NULL default '',
 `automatic` tinyint(1) NOT NULL default '0',
 `employee_id` int(11) NOT NULL default '-1',
 `employee_code` varchar(250) NOT NULL default '',
 `employee_name` varchar(250) NOT NULL default '',
 `payed_employee_id` int(11) NOT NULL default '-1',
 PRIMARY KEY (`id`),
 KEY `cash_operation_reason_id` USING BTREE (`reason_id`),
 KEY `cash_operation_reconciliation_number` USING BTREE (`reconciliation_number`),
 KEY `cash_operation_automatic` USING BTREE (`automatic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

SELECT @@global.datadir; results in error "Unkown system variable 'datadir'"

• RolandoMySQLDBA: Please run SHOW CREATE TABLE tblname\G and post it in the question.
• RolandoMySQLDBA: Please run SELECT @@global.datadir;
• abl: @RolandoMySQLDBA updated post. But I don't see any reference to the trigger in the output of SHOW CREATE TABLE
• RolandoMySQLDBA: Please run SHOW GLOBAL VARIABLES LIKE 'datadir';
• abl: @RolandoMySQLDBA it shows the variable datadir, whose value certainly is the path to the data directory of the database.

Answer 1

Based on the suggestion I gave in the chat room, here is my answer

1. CREATE TABLE cash_op LIKE cash_operation;
2. Goto the datadir in your Windows server
3. rename cash_op.MYD cash_op.MYX
4. copy cash_operation.MYD cash_op.MYD
5. login to mysql and run
   • REPAIR TABLE cash_op;
   • ALTER TABLE cash_operation RENAME cash_op_old;
   • ALTER TABLE cash_op RENAME cash_operation;
6. Go create the two triggers on the new cash_operation table

You said in the chat room : Awesome, that worked! You can post it as the answer.

I am glad you gave it a try and it worked for you !!!

BTW you can drop the old table with DROP TABLE cash_op_old;

• Rick James: 7. Switch to InnoDB so you won't have to run REPAIR again.