1

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'"

  • Please run SHOW CREATE TABLE tblname\G and post it in the question. – RolandoMySQLDBA Jun 25 '15 at 14:50
  • Please run SELECT @@global.datadir; – RolandoMySQLDBA Jun 25 '15 at 14:51
  • @RolandoMySQLDBA updated post. But I don't see any reference to the trigger in the output of SHOW CREATE TABLE – abl Jun 25 '15 at 15:10
  • Please run SHOW GLOBAL VARIABLES LIKE 'datadir'; – RolandoMySQLDBA Jun 25 '15 at 15:11
  • @RolandoMySQLDBA it shows the variable datadir, whose value certainly is the path to the data directory of the database. – abl Jun 25 '15 at 15:22
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;

  • 1
    7. Switch to InnoDB so you won't have to run REPAIR again. – Rick James Jun 28 '15 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.