FEDERATED using trigger in mysql

Question

Here, i have two mysql server on different server and i wan't to copy one table data to another using FEDERATED.

But using below trigger code its not working and even there is no error.

Find original & federated table structure below.

Original Table structure for table vxp_config

CREATE TABLE IF NOT EXISTS `vxp_config` (
  `cfg_id` int(11) NOT NULL AUTO_INCREMENT,
  `cfg_key` varchar(50) NOT NULL,
  `cfg_value` varchar(500) NOT NULL,
  `cfg_field_type` enum('TEXT','TEXTAREA','FILE','CHECKBOX') NOT NULL DEFAULT 'TEXT',
  PRIMARY KEY (`cfg_id`),
  UNIQUE KEY `cfg_key` (`cfg_key`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

Federated Table structure for table vxp_config

CREATE TABLE IF NOT EXISTS `vxp_config` (
  `cfg_id` int(11) NOT NULL AUTO_INCREMENT,
  `cfg_key` varchar(50) NOT NULL,
  `cfg_value` varchar(500) NOT NULL,
  `cfg_field_type` enum('TEXT','TEXTAREA','FILE','CHECKBOX') NOT NULL DEFAULT 'TEXT',
  PRIMARY KEY (`cfg_id`),
  UNIQUE KEY `cfg_key` (`cfg_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CONNECTION='mysql://root:password@host:3306/replicate/vxp_config';

Triggers vxp_config

DROP TRIGGER IF EXISTS `config_bd`;
DELIMITER //
CREATE TRIGGER `config_bd` BEFORE DELETE ON `vxp_config`
 FOR EACH ROW BEGIN
   DELETE FROM vxp_config
   WHERE cfg_id= OLD.cfg_id;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `config_bi`;
DELIMITER //
CREATE TRIGGER `config_bi` BEFORE INSERT ON `vxp_config`
 FOR EACH ROW BEGIN
  INSERT INTO vxp_config (cfg_id,cfg_key, cfg_value,cfg_field_type) VALUES (NEW.cfg_id,NEW.cfg_key, NEW.cfg_value,NEW.cfg_field_type);
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `config_bu`;
DELIMITER //
CREATE TRIGGER `config_bu` BEFORE UPDATE ON `vxp_config`
 FOR EACH ROW BEGIN
  UPDATE vxp_config 
     SET cfg_id= NEW.cfg_id,
         cfg_key= NEW.cfg_key,
         cfg_value = NEW.cfg_value,
         cfg_field_type = NEW.cfg_field_type
   WHERE cfg_id = OLD.cfg_id;
END
//
DELIMITER ;

Answer 1

I don't think that's the way to use FEDERATED table.

is it a mistake or you are actually using:

ENGINE=InnoDB DEFAULT [...] CONNECTION

Also, federated does not make use of indexes and triggers do not work.

I think you would be better off by using proper replication from server one to server two and create all of your triggers on server 2, possibly on another table that mirrors the table replicated from server 1

Answer 2

When you want to define federated table, set ENGINE=FEDERATED

That way, you don't need your triggers, because FEDERATED table is just representation (or link to) original table. All queries you do on federated table are executed on original server(table).