ALTER TABLE ne veut pas modifier ma table [Résolu]

[gilles974]

Bonjour à tous,

Je me connecte en root et choisis ma table postfix pour modifier la clé
mais la requête me retourne un message d'erreur

mysql -u root -p
use postfix;

ALTER TABLE `vacation_notification` ADD CONSTRAINT `vacation_notification_pkey` FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE;

message d'erreur

ERROR 1005 (HY000): Can't create table 'postfix.#sql-a2b_12f' (errno: 121)

a+ merci d'avancce

[ced]

Bonjour,

La colonne référente et la colonne référencée ont-elles le même type ?
Peut-on voir la structure des 2 tables ?

[gilles974]

la table : vacation_notification

+-------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-------+
| on_vacation | varchar(255) | NO | PRI | NULL | |
| notified | varchar(255) | NO | PRI | NULL | |
| notified_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+--------------+------+-----+-------------------+-------+

la table : vacation

+---------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------------------+-------+
| email | varchar(255) | NO | PRI | NULL | |
| subject | varchar(255) | NO | | NULL | |
| body | text | NO | | NULL | |
| cache | text | NO | | NULL | |
| domain | varchar(255) | NO | | NULL | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
| active | tinyint(1) | NO | | 1 | |
+---------+--------------+------+-----+---------------------+-------+

[mydb_server]

Ta syntaxe est correcte.
Peux-tu ré-exécuter la requête et lancer la commande ci-dessous immédiatement après :

Code :

SHOW ENGINE INNODB STATUS\G

Rachid

[Maljuna Kris]

Saluton,
Je vais peut-être dire une ânerie mais j'observe que la clé primaire de la table vacation_notification est composite on_vacation+notified

+-------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-------+
| on_vacation | varchar(255) | NO | PRI | NULL | |
| notified | varchar(255) | NO | PRI | NULL | |
| notified_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+--------------+------+-----+-------------------+-------+

ne serait-ce pas ce qui empêche de faire d'une partie de la clé primaire, on_vacation, une clé étrangère ?

[gilles974]

bonjour

voila la réponse à

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
*************************** 1. row ***************************
  Type: InnoDB
  Name:
STATUS:
=====================================
110630 14:43:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated FROM the last 5 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 64, signal count 64
Mutex spin waits 0, rounds 80, OS waits 4
RW-shared spins 120, OS waits 60; RW-excl spins 1, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
110630 14:43:36 Error IN FOREIGN KEY constraint of TABLE postfix/#sql-a45_1f5:
 FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE:
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the first COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT match FOR constraint.
Note that the internal storage type of ENUM AND SET changed IN
TABLES created WITH >= InnoDB-4.1.12, AND such COLUMNS IN old TABLES
cannot be referenced BY such COLUMNS IN new TABLES.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
FOR correct FOREIGN KEY definition.
------------
TRANSACTIONS
------------
Trx id counter 0 23546
Purge done FOR trx's n:o < 0 23539 undo n:o < 0 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2629, OS thread id 2987531120
MySQL thread id 501, query id 3150 localhost root
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
46 OS file reads, 647 OS file writes, 478 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.40 writes/s, 0.60 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 5301906
Log flushed up to   0 5301906
Last checkpoint at  0 5301906
0 pending log writes, 0 pending chkp writes
273 log i/o's done, 0.20 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17597546; in additional pool allocated 957056
Dictionary memory allocated 62096
Buffer pool size   512
Free buffers       460
Database pages     51
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 51, created 0, written 423
0.00 reads/s, 0.00 creates/s, 2.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2629, id 2951265136, state: sleeping
Number of rows inserted 4, updated 66, deleted 4, read 149
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 
1 row in set (0.00 sec)

[mydb_server]

Aussi les résultats de ces deux commandes :

Code :

SHOW CREATE TABLE vacation_notification\G

et :

Code :

SHOW CREATE TABLE vacation\G

Rachid

[gilles974]

SHOW CREATE TABLE vacation_notification\G

Code :

1
2
3
4
5
6
7
8
9
10
11
 
 
*************************** 1. row ***************************
       TABLE: vacation_notification
CREATE TABLE: CREATE TABLE `vacation_notification` (
  `on_vacation` varchar(255) NOT NULL,
  `notified` varchar(255) NOT NULL,
  `notified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`on_vacation`,`notified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Postfix Admin - Virtual Vacation Notifications'
1 row IN SET (0.00 sec)

SHOW CREATE TABLE vacation\G

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
*************************** 1. row ***************************
       TABLE: vacation
CREATE TABLE: CREATE TABLE `vacation` (
  `email` varchar(255) NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 NOT NULL,
  `body` text CHARACTER SET utf8 NOT NULL,
  `cache` text NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`email`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Vacation'
1 row IN SET (0.00 sec)

[mydb_server]

Les deux tables ont un CHARSET différent, il faudra mettre le même pour les deux tables, ou au moins pour les colonnes concernées.
Autre remarque, le second index sur 'email' est inutile, vu que MySQL en crée un automatiquement (primary key)

Rachid

[ericd69]

salut,

petite remarque garde ton index sur email mais crée un colonne id int(4) not null auto_increment et modifiie ton primary key pour que ce soit elle le primary key

les indexes texte sont le mal absolu en terme de place et de performance si tu t'en sers comme clé primaire car tout autre index sur ta table sera construit sur ses valeurs à lui... ainsi que si tu t'en sers comme clé étrangère ailleurs...

idem pour l'autre table... un index texte ne doit être fait que si tu en as besoin sur une colonne...
si tu as besoin de contrôler juste de l'unicité, y a les triggers pour ça en innodb...

faire une clé primaire sur 2 colonnes de type texte veut dire que la taille de ton index va au moins tripler ou quadrupler la taille de ta table en terme de stockage et de bufferisation des indexes liés en mémoire pour leur manipulation...

c'est pour ça qu'on prend toujours un index numérique (car très compact) comme clé primaire... pour mémoire int(4) ça permet 4 milliards environ de combinaisons...

voilou...

[gilles974]

merci pour vos réponses

pour modifier le charset, est ce que un upgrade suffit ???

pour les index , si je rajoute une colonne ID cela ne va pas perturber les INSERT dans la table ??

le programme qui utilise tout ça est postfixadmin et les tables vacation* sont aussi utilisées par vacation.pl

pour info, cela fait une bonne huitaine que j’essaie de faire tourner cette saloperie de programme vacation avec mes users virtuels

[mydb_server]

petite remarque garde ton index sur email mais crée un colonne id int(4) not null auto_increment et modifiie ton primary key pour que ce soit elle le primary key

ericd69,

Peux-tu stp m'expliquer l'intérêt d'avoir deux index différents sur cette même colonne "email"?

Rachid

[ced]

Je pense que ce qu'il veut dire, c'est déplacer la clé primaire sur une nouvelle colonne, mais conserver l'index sur la colonne email. Ce qui fait que cette dernière n'a plus qu'un seul index, vu qu'elle n'est plus clé primaire.
Ou alors, moi non plus, je ne pige pas l'intérêt

[ericd69]

techniquement on ,'a pas tout le schéma...

ta solution est la meilleure si les tables ne sont référencées par aucune autre ou si il y a impossibilité de modifier les tables à cause des scripts qui les utilisent

après on n'a pas tous les tenants et aboutissants...

je ne connais pas l'application, c'est sensé être un truc pro pour gérer les réservations ? ça a l'air très brouillon comme truc...

enfin même pas peur

[gilles974]

non ericd69, ce n'est pas une appli de reservation

vacation est un répondeur automatique qui prévient l’émetteur que son correspondant est absent

quand un mail arrive, vacation qui est un script perl retourne un mail à l’émetteur et range le mail arrivé dans sa boite mail

ça marche avec postfix,postfixamin et éventuellement avec roundcube

le but de cette manip est d’empêcher un conflit entre lors de l'enregistrement dans une table .... je recherche l'article qui traite de ça

[ericd69]

je vois...

perso je pense aussi que ce qui t'empêche de faire ce que tu veux c'est que tu tentes de référencer une partie de ta clé primaire comme clé étrangère avec cascade delete aussi...

soit un champ d'une table est dans la primary key soit dans une foreign key soit dans rien...

[Maljuna Kris]

Envoyé par ericd69

je vois...

perso je pense aussi que ce qui t'empêche de faire ce que tu veux c'est que tu tentes de référencer une partie de ta clé primaire comme clé étrangère avec cascade delete aussi...

soit un champ d'une table est dans la primary key soit dans une foreign key soit dans rien...

C'est ce que je subodore depuis mon post d'hier 11:38

[mydb_server]

C'est un problème assez connu, les colonnes impliquées dans une relation de clé étrangère doivent avoir le même CHARSET.

gilles974,
Peux-tu tester et nous confirmer que cela fonctionne?

Rachid

[gilles974]

juste une remarque

pour les index , si je rajoute une colonne ID cela ne va pas perturber les INSERT dans la table ??

pour modifier le charset, est ce que un upgrade suffit ??? ou faut il une requête plus sophistiquée ?

je fais la solution d'ericd69 et vous préviens dès lundi

petite remarque garde ton index sur email mais crée un colonne id int(4) not null auto_increment et modifiie ton primary key pour que ce soit elle le primary key

bon week à vous

[ericd69]

normalement non vu que les insert normalement ne s'occuperont pas d'elle

mais comme on est 3 à te le dire, ton problème est sur l'autre table... pour ton alter

en gros, avant de modifier tes tables...

si c'est une application toute faite (même mal mais sensée marcher), pourquoi tu veux modifier tes tables et notamment faire cet alter?