使用Alter Table与现有的MYSQL数据库添加外键问题 - 无法添加！帮帮我！ [英] Problem adding Foreign Key using Alter Table with existing MYSQL Database - can't add it! Help!

问题描述

我有一个生产数据库，我已经重命名了几个外键的列。很显然，mysql使得我真的很痛苦。

我的解决方案是删除所有索引和外键，重命名id列，然后重新添加索引和外键。

这对在开发数据库的windows 5.1上运行得很好。

I去我的debian服务器上运行我的迁移脚本，这也是使用mysql 5.1，它给出了以下错误：

$ $ p $ mysql> ; ALTER TABLE`company_to_module`
- > ADD CONSTRAINT`FK82977604FE40A062` FOREIGN KEY（`company_id`）REFERENCES`company`（`company_id`）ON DELETE RESTRICT ON UPDATE RESTRICT;
错误1005（HY000）：无法创建表'jobprep_production。＃sql-44a5_76'（errno：150）


这个表中没有任何值与我试图添加的外键冲突。数据库没有改变。外键DID存在之前...所以数据是好的。我们不要提及我使用服务器上的SAME数据库，它在Windows上迁移得很好。但是这些外键迁移并不在Debian上。

列使用相同的类型 - BIGINT（20）

这些名字实际上存在于它们各自的表格中。

表格是innodb。它们在其他列中已经有了外键。这不是一个新的数据库。

我不能删除表，因为这是一个生产数据库。

在我的数据库中as is：
$ b $ pre $ 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`）使用BTREE，
CONSTRAINT`FK8297760442C8F876` FOREIGN KEY（`module_id`）REFERENCES`module`（`module_id`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8


$ b

 创建表：CREATE TABLE`company`（
` （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'，
 （25）DEFAULT'＃c0d2e4'，$ b $ varchar（25）DEFAULT'＃f5f5f5'，
`selected_tab_text` varchar（25）DEFAULT'＃172636'，
`hover_table_row_background` varchar b``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`）参考`省`（`province_id`）
）ENGINE = InnoDB AUTO_INCREMENT = 24 DEFAULT CHARSET = utf8 
  

这是innodb状态：

$ $ $ $ $ $ $ $ ------------------
最新的外键错误
-------------------- ----
110415 3:14:34表jobprep_production /＃sql-44a5_1bc的外键约束出错：
FOREIGN KEY（`company_id`）REFERENCES`company`（`company_id`）ON DELETE RESTRICT ON UPDATE RESTRICT：
无法解析列名称接近：
）ON DELETE RESTRICT ON UPDATE RESTRICT

如果我尝试从'company_to_module'中删除索引，我得到这个错误：

$ $ p $ $ $ $ $ $＃将'./jobprep_production/#sql-44a5_23a'重命名为'./jobprep_production/company_to_module'（errno：150）

以下是我的innodb变量：

$ $ $ $ $ $ $ $ $ $ $ ----- --------------- + ------------------------ +
|变量名|值|
+ --------------------------------- + ----------- ------------- +
| 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损坏了我的数据库并将其销毁。我不得不从备份重新加载，以再次尝试。

帮助？ ：

解决方案

我已经简单地使用Windows重构，然后将数据库重新导入到Debian中 -

我认为在Debian服务器或Linux版本的Mysql上可能会搞砸了 - 也许是5.1版本中的一个bug？

无论如何，我也将服务器上的内存从1gb升级到了2gb，这些问题已经消失。

我觉得MySQL可能只是没有足够的内存来完成操作。如果是这样（似乎是这样），我认为MySQL应该简单地说，而不是吐出这些错误 - 使我和这里的每个人都认为这是一个语法或与模式有关的问题。

b
$ b

无论如何，感谢那些试图帮助的人。至少它帮助我隔离了所有不可能的东西。

I have a production database where I have renamed several column's that are foreign keys. Obviously mysql makes this a real pain to do in my experience.

My solution was to drop all the indexes and foreign keys, rename the id columns, and then re-add the indexes and foreign keys.

This works great on mysql 5.1 on windows for the development database.

I went to run my migration script on my debian server, which is also using mysql 5.1, and it gives the following error:

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)

There are no values in this table that would conflict with the foreign key I am trying to add. The database hasn't changed. The foreign key DID exist before... so the data is fine. Let's not mention that I took the SAME database that I have on the server and it migrates fine on Windows. But these same foreign key migrations are not taking on Debian.

The columns are using the same type - BIGINT (20)

The names do in fact exist in their respective tables.

The tables are innodb. They already have foreign keys in other columns as it is. This is not a new database.

I cannot drop tables because this is a production database.

The tables "as is" in my database:

 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

And

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

Here is the 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

If I try and drop the index from 'company_to_module', I get this error:

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

Here are my 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                  |
+---------------------------------+------------------------+

I also want to add that while I was playing with adding the foreign keys, mysql corrupted my database and destroyed it. I had to reload from a backup to try again.

Help? :/

解决方案

I have simply applied the refactorings using Windows and then reimported the database into Debian - it works.

I think it's safe to say that something was messed up on the Debian server, or with the linux version of Mysql - perhaps a bug in 5.1 build?

Anyway, I have also upgraded the ram on the server from 1gb to 2gb, and these problems have gone away.

I think MySQL maybe just didn't have enough ram to complete the operation. If that was the case (and it seems to be), I think MySQL should have simply said so rather than spitting out these errors - making me and everyone here think it was a syntax or a schema-related problem.

Anyway, thanks for those that tried to help. At least it helped me to isolate all the things it couldn't have been.

这篇关于使用Alter Table与现有的MYSQL数据库添加外键问题 - 无法添加！帮帮我！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！