Проблема при добавлении внешнего ключа с помощью Alter Table к существующей базе данных MYSQL - не могу добавить его! Помогите!
У меня есть производственная база данных, в которой я переименовал несколько столбцов, которые являются внешними ключами. Очевидно, MySQL делает это реальной болью в моем опыте.
Моим решением было удалить все индексы и внешние ключи, переименовать столбцы id, а затем повторно добавить индексы и внешние ключи.
Это прекрасно работает на MySQL 5.1 на окнах для базы данных разработки.
Я пошел, чтобы запустить мой скрипт миграции на моем сервере Debian, который также использует MySQL 5.1, и он выдает следующую ошибку:
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)
В этой таблице нет значений, которые могли бы конфликтовать с внешним ключом, который я пытаюсь добавить. База данных не изменилась. Внешний ключ DID существовал раньше... так что данные в порядке. Давайте не будем упоминать, что я взял ту же самую базу данных, которая у меня есть на сервере, и она прекрасно переносится в Windows. Но эти же миграции с использованием внешних ключей не принимают Debian.
Столбцы используют один и тот же тип - BIGINT (20)
Имена действительно существуют в соответствующих таблицах.
Столы innodb. У них уже есть внешние ключи в других столбцах, как есть. Это не новая база данных.
Я не могу удалить таблицы, потому что это производственная база данных.
Таблицы "как есть" в моей базе данных:
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
Вот статус innodb:
------------------------
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
Если я пытаюсь удалить индекс из "company_to_module", я получаю эту ошибку:
#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150)
Вот мои переменные innodb:
+---------------------------------+------------------------+
| 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 |
+---------------------------------+------------------------+
Я также хочу добавить, что во время игры с добавлением внешних ключей mysql повредил мою базу данных и уничтожил ее. Мне пришлось перезагрузить из резервной копии, чтобы повторить попытку.
Помогите?:/
6 ответов
Я просто применил рефакторинг с помощью Windows, а затем снова импортировал базу данных в Debian - это работает.
Я думаю, можно с уверенностью сказать, что что-то испортилось на сервере Debian или в Linux-версии Mysql - возможно, ошибка в сборке 5.1?
Во всяком случае, я также обновил оперативную память на сервере с 1 ГБ до 2 ГБ, и эти проблемы исчезли.
Я думаю, что MySQL, возможно, просто не хватило оперативной памяти для завершения операции. Если бы это было так (и, кажется, так), я думаю, что MySQL должен был просто сказать об этом, а не выплевывать эти ошибки - заставляя меня и всех присутствующих думать, что это был синтаксис или проблема, связанная со схемой.
В любом случае, спасибо за тех, кто пытался помочь. По крайней мере, это помогло мне изолировать все то, чего не могло быть.
Являются ли обе таблицы InnoDB типа?
Есть ли у таблицы компании индекс company_id?
Я предполагаю, что ваша таблица - MyISAM (по умолчанию, если вы не изменили конфигурацию), и вы не можете создавать ограничения внешнего ключа в MyISAM. Смотрите описание CREATE TABLE для двух таблиц.
Если обе таблицы пусты, удалите их и создайте заново, выбрав InnoDB в качестве движка. Вы также можете добавить ограничения FOREIGN KEY в сценарии создания таблиц.
Из справочного руководства MySQL:
На определения внешних ключей распространяются следующие условия:
Обе таблицы должны быть таблицами InnoDB, и они не должны быть временными таблицами.
Соответствующие столбцы во внешнем ключе и ссылочном ключе должны иметь одинаковые внутренние типы данных внутри InnoDB, чтобы их можно было сравнивать без преобразования типов. Размер и знак целочисленных типов должны быть одинаковыми. Длина типов строк не обязательно должна быть одинаковой. Для недвоичных (символьных) строковых столбцов набор символов и параметры сортировки должны быть одинаковыми.
InnoDB требует индексов для внешних ключей и ссылочных ключей, чтобы проверка внешних ключей могла быть быстрой и не требовала сканирования таблицы. В ссылочной таблице должен быть индекс, в котором столбцы внешнего ключа перечислены как первые столбцы в том же порядке. Такой индекс создается в ссылочной таблице автоматически, если он не существует. (Это в отличие от некоторых более старых версий, в которых индексы должны были создаваться явно, иначе создание ограничений внешнего ключа завершилось бы неудачей.) Index_name, если задано, используется, как описано ранее.
InnoDB позволяет внешнему ключу ссылаться на любой столбец индекса или группу столбцов. Однако в ссылочной таблице должен быть индекс, в котором указанные столбцы перечислены как первые столбцы в том же порядке.
Префиксы индекса в столбцах внешнего ключа не поддерживаются. Одним из следствий этого является то, что столбцы BLOB и TEXT не могут быть включены во внешний ключ, поскольку индексы в этих столбцах должны всегда включать длину префикса.
Если задано предложение символа CONSTRAINT, значение символа должно быть уникальным в базе данных. Если предложение не задано, InnoDB создает имя автоматически.
@egervari: что произойдет, если вы запустите это:
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 ;
И если вы запустите:
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;
Убедитесь, что company_to_module.company_id и company.company_id являются ТОЧНЫМ одним и тем же типом данных. Я имел это, когда первичный ключ был установлен как НЕ ПОДПИСАНО INT, но поле внешнего ключа было просто INT. Добавление UNSIGNED в тип данных устранило проблему.
@egervari Вы писали: мое решение состояло в том, чтобы удалить все индексы и внешние ключи, переименовать столбцы идентификаторов, а затем повторно добавить индексы и внешние ключи.
Согласен с тобой. Но, возможно, что-то пошло не так. Я воспроизвел ошибку и (в моем случае) исправил ее.
Я бы посоветовал вам запустить команду OPTIMIZE TABLE для таблицы, столбец которой был переименован. Документация гласит: для таблиц InnoDB OPTIMIZE TABLE сопоставляется с ALTER TABLE, который перестраивает таблицу для обновления статистики индекса и освобождения неиспользуемого пространства в кластерном индексе.
Еще одно решение:
Удалите уникальный ключ в ссылочной таблице (ключ, который используется внешним ключом, в вашем случае это первичный ключ). Затем добавьте новый внешний ключ и воссоздайте удаленный уникальный ключ.
Еще одно решение:
Попробуйте добавить и удалить новый столбец в ссылочной таблице, затем попытайтесь создать свой внешний ключ.
ALTER TABLE company ADD COLUMN column1 VARCHAR(255) DEFAULT NULL;
ALTER TABLE company DROP COLUMN column1;
Поскольку кажется, что это не связано с синтаксисом, я думаю, вам не хватает места для создания таблиц InnoDB.
РЕДАКТИРОВАТЬ: Можете ли вы вставить вашу конфигурацию InnoDB:
SHOW VARIABLES LIKE "inno%";
Так как пытается создать копию company_to_module вручную выдает ту же ошибку, вы должны тщательно проверить ограничение fk, уже присутствующее в company_to_module, Это все еще в силе, или вы изменили таблицу module?
Из MySQL-Docs:
1005 (ER_CANT_CREATE_TABLE) Невозможно создать таблицу. Если сообщение об ошибке ссылается на ошибку 150, создание таблицы завершилось неудачно, поскольку ограничение внешнего ключа было сформировано неправильно.