Fédération d'une table Mysql entre Sequoia et Agathis


Afin d'éviter le problème mentionné au bas de cette page , il faut minimiser le nombre de SELECT effectués sur une table fédérée. Une solution est de lire dans une table classique, et d'exécuter uniquement les écritures (INSERT, UPDATE, DELETE) dans la table fédérée, à l'aide de triggers (déclencheurs).

- Sur Sequoia : Une table annuaire_tela existe à la base. C'est uniquement dans cette table que sont effectuées les écritures.
- Sur Agathis : Une table annuaire_tela est créée, copiée depuis celle de Sequoia.
- Sur Sequoia : Une table annuaire_agathis_federated est créée avec le moteur FEDERATED, liée à la table annuaire_tela de Agathis.
- Sur Sequoia : Trois triggers sont créés, afin que chacune des trois opérations (INSERT, UPDATE, DELETE) effectuée sur annuaire_tela soit répercutée sur annuaire_agathis_federated . Le moteur FEDERATED répercute ensuite automatiquement les opérations vers la table annuaire_tela sur Agathis.

Grâce à ce modèle, seules les opérations d'écritures sont traitées par le moteur FEDERATED, évitant ainsi les erreurs error reading communication packets. Les applications présentes sur Sequoia effectuent leurs requêtes de lecture et écriture dans la table annuaire_tela de Sequoia et les applications présentes sur Agathis effectuent leurs requêtes de lecture (seulement!) dans la table annuaire_tela d'Agathis.

Procédure


  • sur le serveur Agathis ajouter un utilisateur (ici "annuaire") ayant comme adresse "Client" l'IP du serveur Sequoia : 193.54.123.169. Lui donner tous les droits sur la table annuaire_tela
  • sur le serveur Sequoia créer une table avec la même structure mais dont le engine vaut FEDERATED
  • dans le champ CONNECTION indiquer les paramètres de connexion

Exemple de structure de table federated :
--
-- Base de données: `tela_prod_v4`
-- Structure de la table `annuaire_agathis_federated`
--
CREATE TABLE IF NOT EXISTS `annuaire_agathis_federated` (
  `U_ID` int(11) NOT NULL AUTO_INCREMENT,
  `U_NAME` varchar(32) NOT NULL DEFAULT '',
  `U_SURNAME` varchar(32) NOT NULL DEFAULT '',
  `U_FONCTION` varchar(255) NOT NULL DEFAULT '',
  `U_TITLE` varchar(128) NOT NULL DEFAULT '',
  `U_PASSWD` varchar(32) NOT NULL DEFAULT '',
  `U_MAIL` varchar(128) NOT NULL DEFAULT '',
  `U_WEB` varchar(128) NOT NULL DEFAULT '',
  `U_ADDR1` varchar(128) NOT NULL DEFAULT '',
  `U_ADDR2` varchar(128) NOT NULL DEFAULT '',
  `U_ZIP_CODE` varchar(32) NOT NULL DEFAULT '',
  `U_CITY` varchar(32) NOT NULL DEFAULT '',
  `U_STATE` varchar(32) NOT NULL DEFAULT '',
  `U_COUNTRY` varchar(5) NOT NULL DEFAULT '000',
  `U_FRENCH_DPT` varchar(3) NOT NULL DEFAULT '0',
  `U_ABO` varchar(255) NOT NULL DEFAULT '',
  `U_NIV` tinyint(3) unsigned DEFAULT '4',
  `U_SPE` text NOT NULL,
  `U_GEO` text NOT NULL,
  `U_ACT` tinyint(3) DEFAULT '3',
  `U_VALIDE` int(11) NOT NULL DEFAULT '0',
  `U_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `U_ASS` tinyint(3) DEFAULT '3',
  `U_COT` tinyint(3) NOT NULL DEFAULT '0',
  `U_LETTRE` tinyint(4) NOT NULL DEFAULT '0',
  `at_nom_wiki` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`U_ID`),
  KEY `U_MAIL` (`U_MAIL`),
  KEY `U_NAME` (`U_NAME`),
  KEY `U_SURNAME` (`U_SURNAME`)
) 
ENGINE=FEDERATED  
DEFAULT CHARSET=latin1 
CONNECTION='mysql://annuaire:password@147.99.34.2:3306/tela_prod_v4/annuaire_tela';


Ensuite, créer sur Sequoia 3 triggers pour déclencher la mise à jour. Voir le principe: http://dba.stackexchange.com/questions/44758/create-a-trigger-to-update-table-data-on-another-servers-database

DELIMITER $$
CREATE TRIGGER annuaire_insert AFTER INSERT ON annuaire_tela FOR EACH ROW
BEGIN
	INSERT INTO annuaire_agathis_federated(U_ID, U_NAME, U_SURNAME, U_FONCTION, U_TITLE, U_PASSWD, U_MAIL, U_WEB, U_ADDR1, U_ADDR2, U_ZIP_CODE, U_CITY, U_STATE, U_COUNTRY, U_FRENCH_DPT, U_ABO, U_NIV, U_SPE, U_GEO, U_ACT, U_VALIDE, U_DATE, U_ASS, U_COT, U_LETTRE, at_nom_wiki)
		VALUES(NEW.U_ID, NEW.U_NAME, NEW.U_SURNAME, NEW.U_FONCTION, NEW.U_TITLE, NEW.U_PASSWD, NEW.U_MAIL, NEW.U_WEB, NEW.U_ADDR1, NEW.U_ADDR2, NEW.U_ZIP_CODE, NEW.U_CITY, NEW.U_STATE, NEW.U_COUNTRY, NEW.U_FRENCH_DPT, NEW.U_ABO, NEW.U_NIV, NEW.U_SPE, NEW.U_GEO, NEW.U_ACT, NEW.U_VALIDE, NEW.U_DATE, NEW.U_ASS, NEW.U_COT, NEW.U_LETTRE, NEW.at_nom_wiki);
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER annuaire_delete AFTER DELETE ON annuaire_tela FOR EACH ROW
BEGIN
	DELETE FROM annuaire_agathis_federated WHERE U_ID = OLD.U_ID;
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER annuaire_update AFTER UPDATE ON annuaire_tela FOR EACH ROW
BEGIN
	UPDATE annuaire_agathis_federated
		SET U_ID = NEW.U_ID,
			U_NAME = NEW.U_NAME,
			U_SURNAME = NEW.U_SURNAME,
			U_FONCTION = NEW.U_FONCTION,
			U_TITLE = NEW.U_TITLE,
			U_PASSWD = NEW.U_PASSWD,
			U_MAIL = NEW.U_MAIL,
			U_WEB = NEW.U_WEB,
			U_ADDR1 = NEW.U_ADDR1,
			U_ADDR2 = NEW.U_ADDR2,
			U_ZIP_CODE = NEW.U_ZIP_CODE,
			U_CITY = NEW.U_CITY,
			U_STATE = NEW.U_STATE,
			U_COUNTRY = NEW.U_COUNTRY,
			U_FRENCH_DPT = NEW.U_FRENCH_DPT,
			U_ABO = NEW.U_ABO,
			U_NIV = NEW.U_NIV,
			U_SPE = NEW.U_SPE,
			U_GEO = NEW.U_GEO,
			U_ACT = NEW.U_ACT,
			U_VALIDE = NEW.U_VALIDE,
			U_DATE = NEW.U_DATE,
			U_ASS = NEW.U_ASS,
			U_COT = NEW.U_COT,
			U_LETTRE = NEW.U_LETTRE,
			at_nom_wiki = NEW.at_nom_wiki
	WHERE U_ID = OLD.U_ID;
END $$
DELIMITER ;


Important

Utiliser le script mysql_transfert pour copier la base / table depuis Sequoia vers Agathis après avoir créé les triggers, car le dump effectué par ce script supprime les déclarations de triggers avec l'option --skip-triggers.
Si vous dumpez manuellement en oubliant cette option, les triggers seront créés sur Agathis également et seront déclenchés à chaque requête de mise à jour venant de FEDERATED; la table annuaire_agathis_federated n'existant pas sur Agathis, une erreur sera jetée et la requête échouera. Les données seront correctement insérées dans les autres tables, mais les scripts PHP renverront une erreur.