Problème ajout de Clé Étrangère à l'aide de l'instruction Alter Table avec MYSQL existante de la Base de données ne peut pas l'ajouter! À l'aide!

J'ai une base de données de production où j'ai renommé plusieurs colonnes qui sont des clés étrangères. Évidemment, mysql fait d'elle une véritable douleur de faire dans mon expérience.

Ma solution a été de supprimer tous les index et les clés étrangères, renommer la colonne d'id, et puis ajouter à nouveau l'index et les clés étrangères.

Cela fonctionne très bien sur mysql 5.1 sur windows pour le développement de la base de données.

Je suis allé à exécuter mon script de migration sur mon serveur debian, qui est aussi l'utilisation de mysql 5.1, et il donne l'erreur suivante:

mysql> ALTER TABLE `company_to_module`
    -> ADD CONSTRAINT `FK82977604FE40A062` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can't create table 'jobprep_production.#sql-44a5_76' (errno: 150)

Il n'y a pas les valeurs de ce tableau qui seraient en conflit avec la clé étrangère, je suis en train d'ajouter. La base de données n'a pas changé. La clé étrangère N'existe pas avant... donc les données sont beaux. Il ne faut pas oublier que j'ai pris la MÊME base de données que j'ai sur le serveur, et il migre parfaitement sur Windows. Mais ces même clé étrangère migrations ne sont pas prise sur Debian.

Les colonnes sont en utilisant le même type BIGINT (20)

Les noms existent dans leurs tables respectives.

Les tables innodb. Ils ont déjà des clés étrangères dans les autres colonnes comme il est. Ce n'est pas une nouvelle base de données.

Je ne peux pas supprimer les tables parce que c'est une base de données de production.

Les tables "comme c'est" dans ma base de données:

 CREATE TABLE `company_to_module` (
  `company_id` bigint(20) NOT NULL,
  `module_id` bigint(20) NOT NULL,
  KEY `FK8297760442C8F876` (`module_id`),
  KEY `FK82977604FE40A062` (`company_id`) USING BTREE,
  CONSTRAINT `FK8297760442C8F876` FOREIGN KEY (`module_id`) REFERENCES `module` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Create Table: CREATE TABLE `company` (
`company_id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`postal_code` varchar(255) DEFAULT NULL,
`province_id` bigint(20) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`is_enabled` bit(1) DEFAULT NULL,
`director_id` bigint(20) DEFAULT NULL,
`homepage_viewable` bit(1) NOT NULL DEFAULT b'1',
`courses_created` int(10) NOT NULL DEFAULT '0',
`header_background` varchar(25) DEFAULT '#172636',
`display_name` varchar(25) DEFAULT '#ffffff',
`tab_background` varchar(25) DEFAULT '#284767',
`tab_text` varchar(25) DEFAULT '#ffffff',
`hover_tab_background` varchar(25) DEFAULT '#284767',
`hover_tab_text` varchar(25) DEFAULT '#f2e0bd',
`selected_tab_background` varchar(25) DEFAULT '#f5f5f5',
`selected_tab_text` varchar(25) DEFAULT '#172636',
`hover_table_row_background` varchar(25) DEFAULT '#c0d2e4',
`link` varchar(25) DEFAULT '#4e6c92',
PRIMARY KEY (`company_id`),
KEY `FK61AE555A71DF3E03` (`province_id`),
KEY `FK61AE555AAC50C977` (`director_id`),
CONSTRAINT `company_ibfk_1` FOREIGN KEY (`director_id`) REFERENCES `user_account` (`user_account_id`),
CONSTRAINT `FK61AE555A71DF3E03` FOREIGN KEY (`province_id`) REFERENCES `province` (`province_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8

Ici est la innodb status:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110415  3:14:34 Error in foreign key constraint of table jobprep_production/#sql-44a5_1bc:
FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT:
Cannot resolve column name close to:
) ON DELETE RESTRICT ON UPDATE RESTRICT

Si j'essaie et baisse de l'indice de "company_to_module', j'obtiens cette erreur:

#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150)

Voici mon innodb variables:

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 1048576                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 8388608                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 1048576                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_stats_on_metadata        | ON                     |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 20                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
+---------------------------------+------------------------+

Je tiens également à ajouter que pendant que je jouais avec l'ajout de clés étrangères, mysql corrompu de ma base de données et l'a détruit. J'ai dû recharger à partir d'une sauvegarde d'essayer de nouveau.

Aider? :/

Sont les deux tables InnoDB type?
Le company table ont un index sur company_id ?
Je suppose que votre table est MyISAM (la valeur par défaut si vous n'avez pas chnaged la config) et je pense que vous ne pouvez pas créer de contraintes de clés étrangères en MyISAM. Voir la description de la CREATE TABLE company_to_module.
Si les deux tables sont vides, de les déposer et de les recréer, en choisissant InnoDB que le moteur. Vous pouvez aussi ajouter la FOREIGN KEY contraintes dans le script de création de tables(s).
merci pour les conseils. Les deux tables ne sont pas vides - c'est une base de données de production. Je dois utilisateur de modifier le tableau. Toutes les tables innodb. En fait, il y a les clés étrangères existantes sur ces tables - ce n'est pas comme ce sont les premiers. Cette base de données a 40 tableaux des tonnes de clés étrangères dans toutes les tables. Donc je ne pense pas que c'est un de ces problèmes 🙁 Et oui, l'entreprise a l'id "company_id". J'étais sûr de le faire. L'application fonctionne avec la base de données en dépit de ne pas avoir les clés étrangères... mais je veux simplement ajouter de toute façon.

OriginalL'auteur egervari | 2011-04-15

9

Sont à la fois des tables de type InnoDB?

Ne la table de la société ont un index sur company_id ?

Je suppose que votre table MyISAM (la valeur par défaut si vous n'avez pas changé la config) et vous ne pouvez pas créer de contraintes de clés étrangères en MyISAM. Voir la description de la CREATE TABLE pour yout deux tables.

Si les deux tables sont vides, de les déposer et de les recréer, en choisissant InnoDB comme moteur. Vous pouvez également ajouter des contraintes de CLÉ ÉTRANGÈRE dans la création de tables de script(s).

De MySQL Manuel De Référence:
Clés étrangères, les définitions sont soumises aux conditions suivantes:
- Les deux tables doivent être InnoDB tables et
  ils ne doivent pas être des tables TEMPORAIRES.
- Colonnes correspondantes à l'étranger
  clé et la clé référencée doit avoir
  similaire types de données internes à l'intérieur
  InnoDB, de sorte qu'ils peuvent être comparés
  sans une conversion de type. La taille
  et signe des types d'entiers doit être le
  même. La longueur de la chaîne types de besoin
  pas être la même. Pour non binaire
  (caractère) les colonnes de la chaîne, la
  jeu de caractères et la collation doit être
  la même.
- InnoDB nécessite des indices sur les étrangers
  touches et référencé clés de sorte que
  clé étrangère de contrôles peuvent être rapide et
  pas besoin d'un tableau d'analyse. Dans le
  table de référence, il doit y avoir une
  index où les colonnes de clé étrangère
  sont répertoriés comme les premières colonnes dans
  le même ordre. Un tel indice est
  créé sur la table de référence
  automatiquement s'il n'existe pas.
  (Ceci est en contraste à certains anciens
  versions, dans lequel les index devait être
  créé explicitement ou la création de
  les contraintes de clé étrangère serait un échec.)
  index_name, si elle est donnée, est utilisé comme
  décrit précédemment.
- InnoDB permet une clé étrangère à
  référence à un indice de colonne ou un groupe
  des colonnes. Toutefois, dans le
  table référencée, il doit y avoir une
  index où les colonnes référencées
  sont répertoriés comme les premières colonnes dans
  le même ordre.
- Indice de préfixes sur les colonnes de clé étrangère
  ne sont pas pris en charge. L'une des conséquences de
  c'est que BLOB et TEXT
  ne peut pas être inclus dans une clé étrangère
  car les index sur les colonnes doivent
  toujours inclure un préfixe de longueur.
- Si la CONTRAINTE symbole de la clause est
  donné, la valeur du symbole doit être
  unique dans la base de données. Si la clause
  ce n'est pas donné, InnoDB crée le nom
  automatiquement.
@egervari: Qu'advient-il si vous exécutez ce:
```
CREATE TABLE `test` (
`company_id` bigint(20) NOT NULL,
`module_id` bigint(20) NOT NULL,
KEY  (`module_id`),
KEY  (`company_id`),
CONSTRAINT `test_fk_module`
FOREIGN KEY (`module_id`)
REFERENCES `module` (`module_id`),
CONSTRAINT `test_fk_company`
FOREIGN KEY (`company_id`)
REFERENCES `company` (`company_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
```
Et si vous exécutez:
```
ALTER TABLE `company_to_module`
ADD CONSTRAINT `company_to_module_fk_company` 
FOREIGN KEY (`company_id`)
REFERENCES `company` (`company_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
```
Yep. Je suis en train de faire toutes ces choses. Les tables Innodb. Les identifiants sont nommés correctement. Les données sont correctes. Les types sont corrects. Question: en Supposant que je n'enfreignent ces règles... pourquoi ces migrations de travail sur Windows en utilisant la même base de données? C'est le plus déconcertant chose.
Pourrait mémoire basse-être la cause de cette erreur? C'est juste un serveur de nuage et "libre" disent généralement j'ai 29-9 mégas gratuit de 1 go. Juste par curiosité :/ Un lot est utilisé pour les tampons, donc je ne suis pas sûr de ce que cela signifie.
Obtenez-vous un message similaire lorsque vous exécutez la CREATE TABLE ?
Il fonctionne parfaitement.
Et si vous exécutez l' ALTER TABLE ?

OriginalL'auteur ypercubeᵀᴹ
4

S'assurer que company_to_module.company_id et de l'entreprise.company_id sont exactement le même type de données. J'ai eu cela arrive quand la clé primaire a été configuré comme un UNSIGNED INT, mais le champ de clé étrangère était juste un INT. Ajout non signé pour le type de données résolu le problème.

Il a été vaut le coup de vérifier. Tous mes id BIGINT (20) 🙁
Je vais juste coller de ma table de définitions
a fonctionné pour moi - j'ai eu un unsigned int et un autre de type int que j'ai été tenté de faire le lien. merci!!!!

OriginalL'auteur squawknull
1

J'ai simplement appliqué les refactorings à l'aide de Windows, puis de réimporter les données dans la base de Debian, il fonctionne.

Je pense qu'il est sûr de dire que quelque chose a foiré sur le serveur Debian, ou avec la version linux de Mysql - peut-être un bug dans 5.1 build?

De toute façon, j'ai également mis à niveau la ram sur le serveur à partir de 1 go à 2 go, et ces problèmes ont disparu.

Je pense que MySQL peut-être juste de ne pas avoir assez de ram pour terminer l'opération. Si c'était le cas (et ça semble être le cas), je pense que MySQL doit avoir tout simplement dit donc, plutôt que de cracher ces erreurs de prise de moi et tout le monde ici pense que c'était une syntaxe ou un schéma liées au problème.

En tout cas, merci pour ceux qui ont essayé de l'aider. Au moins, il m'a aidé à isoler toutes les choses qu'il ne pouvait pas avoir été.

OriginalL'auteur egervari
0

Depuis il ne me semble pas être quelque chose de syntaxe liés, ma meilleure supposition est que vous êtes à court d'espace pour la création de tables InnoDB.

EDIT: Pouvez-vous coller votre InnoDB configuration:
```
SHOW VARIABLES LIKE "inno%";
```
"innodb_data_file_path" est pas situé dans mon.cnf pour une raison quelconque. Il n'est ni "innodb_file_per_table". Je n'ai pas fait pour mon.cnf personnellement, ce sont les valeurs par défaut. Cela pourrait être le problème? La valeur par défaut est vraiment faible?
C'est les stats sur ibdata1: "35651584 Avr 15 03:20 ibdata1"
j'ai collé les variables ci-dessus 😉

OriginalL'auteur nickgrim
0

Depuis en essayant de créer une copie de company_to_module manuellement vous donne la même erreur, vous devez vérifier soigneusement la fk contrainte déjà présents dans company_to_module. Est-il encore valide, ou avez-vous modifier le tableau module?

De la MySQL-Docs:

1005 (ER_CANT_CREATE_TABLE) Ne peut pas créer de table. Si le message d'erreur fait référence à une erreur de 150, la création de la table a échoué en raison d'une contrainte de clé étrangère n'a pas été correctement formé.

Il a donné l'erreur car j'ai oublié de changer le nom de clé étrangère dans mon double définition de la table de company_to_module. Une fois que j'ai réalisé que, de la création de la table travaillé, ce qui implique la création de nouvelles tables n'était pas le problème. Toutefois, l'abandon de l'index pour "module_id" a provoqué la nouvelle erreur que j'ai écrit ci-dessus. Honnêtement, je suis tellement malade et fatigué de cette "deviner" que je viens exporté ma base de données, ajout de la clé étrangère sur windows (oui, ils travaillent!), et re-importé la base de données de retour dans debian. Elle fonctionne. Je suis à une perte pour expliquer pourquoi cela fonctionne sur windows et pas linux. Autant pour la croix-compatibilité.

OriginalL'auteur titanoboa
0

@egervari Vous avez écrit - Ma solution a été de supprimer tous les index et les clés étrangères, renommer la colonne d'id, et puis ajouter à nouveau l'index et les clés étrangères.

D'accord avec vous. Mais il se pourrait que quelque chose n'allait pas. J'ai reproduit l'erreur, et (dans mon cas), il fixe.

Je vous suggère de courir OPTIMISER TABLEAU de commande pour table où la colonne a été renommé. La Documentation dit - Pour les tables InnoDB, OPTIMIZE TABLE est mappé à la commande ALTER TABLE, qui reconstruit la table à mettre à jour les statistiques d'index et libre de l'espace inutilisé dans l'index cluster.

La solution:

Drop de clé unique dans la table référencée (clé qui est utilisée par la clé étrangère, dans votre cas, c'est une clé primaire). Puis ajouter la nouvelle clé étrangère et de recréer larguaient clé unique.

La solution:

Essayez d'ajouter et supprimer des nouvelle colonne à la table référencée, puis essayez de créer votre clé étrangère.
```
ALTER TABLE company ADD COLUMN column1 VARCHAR(255) DEFAULT NULL;
ALTER TABLE company DROP COLUMN column1;
```
Bien que je n'ai jamais mentionné, je n'ai optimize table sur tout. Malheureusement, cela ne fonctionne pas.
J'ai ajouté quelques notes.
Je ne peux pas abandonner toutes les touches. C'est une base de données de production. :/
J'ai ajouté réponse.

OriginalL'auteur Devart

Vous devez vous connecter pour publier un commentaire.