Fusion de BDD

**olivier.pitton** · 06/01/2013, 20h47

Bien que la solution proposée est affreuse, elle a le mérite de fonctionner. Pourquoi ne pas faire un mysqldump + source, en changeant le nom de la BD. Cela t'évites tous les conflits de PRIMARY KEY etc...

Mais comme il a été dit précédemment, il faudrait voir avec tes supérieurs comment changer ce fonctionnement. Tu ne résous pas le problème de tes duplicats de BD, et tu ajoutes une solution crade / complexe pour masquer cela.

Je pense que le mieux serait de garder une unique base de données. Tu peux y ajouter des merge tables pour les grosses tables de ta BD, afin de splitter un peu le tout.

Au fait, chaque PC a une BDD en local (obligatoirement car les PC ne sont pas forcement connectés au réseau donc pas de maitre serveur ...), je voudrais donc créer une BDD sur mon poste avec les résultats des BDD locales ...

Si tu arrives à récupérer l'ensemble des BD locales sur ton poste, pour les mettre dans ta BD locale, pour mettre la fusion dans la BD centralisée .... pourquoi ne pas faire connecter les autres PCs sur la BD centralisée ? Si tu peux récupérer les BD, c'est que les utilisateurs peuvent se connecter à ta machine pour utiliser le MySQL centralisé, non ?

**SNY77** · 06/01/2013, 21h01

Au fait, les BDD sont sur des bancs de tests, et les bancs peuvent ne pas être connectés au réseau (et en cas de tests, s'il y a une panne réseau les tests sont interrompus ...). C'est pour cela que l'on garde les BDD en local sur les bancs de tests.
On voudraient cependant avoir une BDD centralisée regroupant les lignes des BDD locales, pour cela :
1/ Importation des dernier back up des BDD locales sur le PC centralisés (bdd_local1, bdd_local2) ...
2/ Copie des lignes des bdd locals dans la BDD CENTRALISEE, c'est ici que ça bloque parce que je suis obligé de copier le champs clés primaire apparament, ce qui provoque donc un erreur de duplication
3/ On aimerai réaliser le tout une fois par semaine par exemple ... J'ai donc pensé à faire un script batch qui appele ensuite un script sql pour la manipulation des requêtes ...
Merci beaucoup !

**CinePhil** · 07/01/2013, 10h05

On reste sur le même principe que j'avais décrit... quelques pages avant !

La difficulté que tu rencontres est que, pour une table donnée existant dans toutes les BDD puisqu'elles ont la même structure, le même identifiant peut être trouvé dans toutes les BDD. Lors de l'importation, il est impossible d'importer deux fois la même clé primaire.

Tu as 2 possibilités...

1) Soit il doit y avoir correspondance entre les identifiants de la BDD consolidée et des BDD décentralisées.
Il faut alors modifier la structure de la BDD centralisée en ajoutant dans les tables une colonne identifiant la BDD d'où provient la ligne.

Exemple :
Dans BDD1.table1.t1_id, qui est aussi la colonne définissant la clé primaire de la table, tu as l'identifiant 1.
Idem dans BDD2.table1.t1_id, la même valeur 1.

Il faut que dans la base de données consolidée, BDD0.table1 ait une colonne t1_BDD qui identifie le numéro de la BDD d'où provient chaque ligne, et que la clé primaire de cette table soit formée du couple {t1_bdd, t1_id}. Ainsi, dans BDD0.table1, tu pourras avoir les lignes suivantes :
t1_BDD, t1_id
1, 1
2, 1
3, 1
...

Le même id, mais pas la même clé primaire.

Lors de l'importation des données, ton programme doit valoriser cette nouvelle colonne en fonction de la BDD que tu importes.

2) Soit tu n'as pas besoin de conserver les identifiants des BDD décentralisées.
Tu peux alors te contenter d'un identifiant auto-incrémenté dans la BDD centralisée mais :
- attention à la cohérence des clés étrangères, ce qui complique les requêtes d'importation !
- tu dois trouver une autre clé candidate dans chaque table pour pouvoir n'importer que les lignes qui ne l'ont pas encore été et ce n'est pas toujours possible !

Je préconise donc plutôt la première solution qui permet de conserver une cohérence entre la BDD consolidée et les BDD décentralisées.

**SNY77** · 07/01/2013, 11h15

Parcontre, étant donnée que les BDD sont spécifiques au bancs de tests, on avait penser à introduire une colonne avec le bancs de tests en question (donc non incrémentés ...), dans certaines tables ce champs ne sera pas nécessaire, du coup j'aurai toujours le problème pour copier les données si l'on a déjà la même clés primaire existante sur la BDD centralisés! Avez vous une idée pour éviter les doublons (éviter d'entrer une valeur pour les champs ID avec la requête que j'ai posté ?).
Merci beaucoup!

**CinePhil** · 07/01/2013, 11h30

Soit la table fictive suivante figurant dans toutes tes BDD :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE table1
(
	t1_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	t1_col2 VARCHAR(32) NOT NULL,
	t1_col3 TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
	KEY (t1_col2)
)
ENGINE=InnoDB
CHARSET=utf8;

Dans la BDD de consolidation, tu auras la même table mais tu la modifies :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE BDD0.table1
DROP PRIMARY KEY;
 
ALTER TABLE BDD0.table1
ADD UNIQUE KEY (t1_id);
 
ALTER TABLE BDD0.table1
ADD COLUMN t1_BDD TINYINT NOT NULL;
 
ALTER TABLE BDD0.table1
ADD PRIMARY KEY (t1_BDD, t1_id);

Je ne sais pas si on peut faire les quatre modifications dans une seule requête ALTER TABLE, je n'ai jamais essayé.

Quand tu importes les données de la BDD1, tu fais ce genre de requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO BDD0.table1 t0 (t1_BDD, t1_id, t1_col2, t1_col3)
SELECT 1, t1.t1_id, t1.t1_col2, t1.t1_col3
FROM BDD1.table1 t1
LEFT OUTER JOIN BDD0.table1 t01 ON t01.t1_id = t1.t1_id
WHERE t01.t1_id IS NULL;

Ou bien, la même chose avec NOT EXISTS :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
INSERT INTO BDD0.table1 t0 (t1_BDD, t1_id, t1_col2, t1_col3)
SELECT 1, t1.t1_id, t1.t1_col2, t1.t1_col3
FROM BDD1.table1 t1
WHERE NOT EXISTS
(
	SELECT 1
	FROM BDD0.table1 t01
	WHERE t01.t1_id = t1.t1_id
);

**SNY77** · 07/01/2013, 11h49

OK, merci !
Dans certaines tables, la clés primaire est constitué de plusieurs colonnes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

PRIMARY KEY (`EQUIPMENT_PN`,`EQUIPMENT_SN`,`EQUIPMENT_EC`)

Après avoir appliquer le DROP ..., je mets le champs bdd_id avant les trois précédents ?
Mais dans la majorité des tables, j'ai qu'un seul champs table_id dans PRIMARY KEY.
Je n'aurai pas de doublons lors des copies avec INSERT INTO ?
Et je ne veux pas que le champs ID s'incrémente, puisque j'ajouterai ce champs sur les bases locales, les utilisateurs taperont ensuite le numéro du bancs, donc là ca coince ?! Sachant que le numéro du bancs est actuellement remplis sur une table, on pourraient peut être le copier sur toutes sur les autre tables (dans la colonne bdd_id) automatiquement afin de ne pas obliger les utilisateurs à le renseigner à chaque fois car il ne change pas dans la BDD locale ...
Merci beaucoup !!!

**CinePhil** · 07/01/2013, 12h18

Envoyé par SNY77

OK, merci !
Dans certaines tables, la clés primaire est constitué de plusieurs colonnes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

PRIMARY KEY (`EQUIPMENT_PN`,`EQUIPMENT_SN`,`EQUIPMENT_EC`)

Après avoir appliquer le DROP ..., je mets le champs bdd_id avant les trois précédents ?

Oui.

Mais dans la majorité des tables, j'ai qu'un seul champs table_id dans PRIMARY KEY.
Je n'aurai pas de doublons lors des copies avec INSERT INTO ?

Non puisque la nouvelle clé primaire de ces tables, dans la base de données consolidée, sera composée de l'identifiant de la BDD + cette colonne (et pas champ !

) 'id'.

Et je ne veux pas que le champs ID s'incrémente,

Effectivement, sur la BDD consolidée, il ne faut pas que l'identifiant soit auto-incrémenté mais ça n'a pas grande importance puisque la valeur est donnée à cette colonne par la requête d'importation ; l'auto-incrémentation ne sera jamais utilisée.

puisque j'ajouterai ce champs sur les bases locales, les utilisateurs taperont ensuite le numéro du bancs, donc là ca coince ?!

La solution que je préconisais ajoutait l'identifiant de la BDD source dans la BDD consolidée mais pas dans les BDD locales.
Si tu as une BDD par banc, ça ne me semble pas utile d'ajouter cette colonne dans les BDD locales.

Sachant que le numéro du bancs est actuellement remplis sur une table, on pourraient peut être le copier sur toutes sur les autre tables (dans la colonne bdd_id) automatiquement afin de ne pas obliger les utilisateurs à le renseigner à chaque fois car il ne change pas dans la BDD locale ...

C'est cependant une autre possibilité, puisque cette information semble déjà présente. Mais ça demande peut-être à modifier toutes les BDD locales pour ajouter l'identifiant du banc dans les tables adéquates, c'est à dire potentiellement presque toutes. Bien sûr, si certaines tables enregistrent déjà cette information, pas besoin de la dupliquer !

**SNY77** · 07/01/2013, 14h05

La solution que je préconisais ajoutait l'identifiant de la BDD source dans la BDD consolidée mais pas dans les BDD locales.
Si tu as une BDD par banc, ça ne me semble pas utile d'ajouter cette colonne dans les BDD locales.

Je ne vois pas où est cette solution ? Ca serait pas mal, si on pourrait l'ajouter automatiquement lors de l'importation dans toutes les lignes !
Et merci beaucoup !

**SNY77** · 07/01/2013, 14h54

Apparemment pour les clés primaires ça fonctionne !(en tapant les requêtes deux à deux). Parcontre la requête d'insertion ne passe pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO bdd3.calibration (calibration_id,calibration_name,CALIBRATION_Tolerance,t1.bdd)
SELECT 1, (bdd2.calibration_id,bdd2.calibration_name,bdd2.CALIBRATION_Tolerance)
FROM bdd2.calibration bdd2

J'ai l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Operand should contain 1 column(s)

C'est peut être du au fait qu'il n'y a pas de valeurs pour l'instant à t1.bdd?
Merci!!

**CinePhil** · 07/01/2013, 14h57

Pourquoi avoir ajouté des parenthèses dans le SELECT ?

**SNY77** · 07/01/2013, 15h09

Exact, maintenant j'ai une erreur du type 'DATA TRUNCATED' pour la colonne calibration_tolerance (données du type 1.25) ?
Merci!

**CinePhil** · 07/01/2013, 15h22

Je n'avais pas regardé en détail ta requête mais elle est fausse !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO bdd3.calibration (calibration_id,calibration_name,CALIBRATION_Tolerance,t1.bdd)
SELECT 1, (bdd2.calibration_id,bdd2.calibration_name,bdd2.CALIBRATION_Tolerance)
FROM bdd2.calibration bdd2

Il faut respecter l'ordre des colonnes entre le SELECT et l'INSERT !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO bdd3.calibration (calibration_id, calibration_name, CALIBRATION_Tolerance,t1.bdd)
SELECT bdd2.calibration_id, bdd2.calibration_name, bdd2.CALIBRATION_Tolerance, 2)
FROM bdd2.calibration bdd2

Et attention ! Ça ira comme ça pour le premier import mais pour les suivants de la même table de la même BDD, il faudra ajouter la clause WHERE que j'ai donnée dans un précédent message.

J'ai l'impression que tu te précipites un peu trop dans ce que tu fais et dans l'écriture des messages que tu postes. Essaie un peu de comprendre l'aide qu'on t'apporte car ça devient lassant de reprendre tes erreurs évidentes !

Avant de répondre mon message #63, j'ai pris le temps de relire toute la discussion pour me remémorer le problème et retrouver les solutions que j'avais suggérées.

**SNY77** · 07/01/2013, 16h41

C'est OK pour la commande, par contre j'avais une erreur DUPLICATE KEY (pour un 2éme enregistrements ou les clés CALIBRATION_id sont identiques MAIS la colonne t1_bdd change). Je me suis donc aperçu que la clés primaire n'est pas constituée des deux champs ! Pourtant j'ai bien tapé les 4 requêtes de ton postes précédent:
C'est la 1ére requête qui passe pas:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE BDD3.calibration
DROP PRIMARY KEY;

Message d'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Incorrect table definition; there can be only one auto column and it must be defined as a key

MERCI!

**CinePhil** · 07/01/2013, 17h11

OK il faut alors redéfinir la colonne pour supprimer l'auto-incrément :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE BDD3.calibration
-- redéfinition de la colonne actuellement clé primaire en enlevant AUTO_INCREMENT

Ensuite, tu peux supprimer la clé primaire.

**SNY77** · 08/01/2013, 10h10

Ok , je vais m'y concentrer merci ! Je dois proposer une autre solution ... :
Chaque bancs de tests dispose de sa propre BDD (même structure) on voudrait avoir une BDD regroupant toutes les BDD des bancs (MAJ fréquente car après çela je développerai une application d'exploitation des BDD depuis la BDD centralisée). A savoir que l'on peut pas mettre les BDD en réseau car les bancs peuvent être déconnectés du réseau et ils doivent toujours pouvoir faire les tests de produits ...
J'ai donc pensé à dupliquer les BDD sur chaque bancs (back up et importation planifiée) et ainsi utiliser une réplication vers le serveur centralisé (au moins la MAJ sera automatisée pour ce cas). Les bancs pourront continués à fonctionner en cas de non accès au réseau ainsi.
Avez vous une idée ?

**CinePhil** · 08/01/2013, 10h24

Euh... c'est toujours la même question depuis le début de cette discussion !

**SNY77** · 08/01/2013, 10h28

C'est pour rappeler le sujet ... J'aurai besoin d'une autre solution (j'avais pensé à celle là : duplication sur chaque bancs et réplication sur un serveur maitre). Ça vous semble possible, et judicieux surtout ? Il faut que je trouve la solution la plus adéquate à ce problème.
Merci!

**CinePhil** · 08/01/2013, 10h33

Je ne comprends pas l'intérêt e dupliquer toutes les BDD de tous les bancs sur chaque banc.
Chaque banc a t-il besoin des données des autres bancs ?

Sinon, le principe est le même que celui énoncé jusque là pour importer les BDD des bancs vers la BDD consolidée. C'est juste à multiplier et à paramétrer pour chaque banc.

**SNY77** · 08/01/2013, 10h39

Au fait, je veux avoir la BDD de chaque bancs sur mon serveur pour ensuite exploiter les données des bancs depuis cette unique BDD. Aprés la solution du script, je voulais savoir s'il a une autre solution (réplication maitre esclave ... en prenant en compte que les applications doivent communiquer en local avec la BDD)