在现有 MYSQL 数据库中使用 Alter Table 添加外键时出现问题 - 无法添加！帮助！

2021-11-27 00:00:00 foreign-keys mysql alter-table mysql-error-1025 mysql-error-1005

我有一个生产数据库，我在其中重命名了几个作为外键的列.显然，根据我的经验，mysql 使这变得非常痛苦.

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.

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

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

这在用于开发数据库的 windows 上的 mysql 5.1 上效果很好.

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

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

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)

此表中没有与我尝试添加的外键冲突的值.数据库没有改变.外键 DID 之前存在...所以数据很好.更不用说我使用了服务器上的 SAME 数据库，它在 Windows 上迁移得很好.但是这些相同的外键迁移并没有发生在 Debian 上.

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.

列使用相同的类型 - BIGINT (20)

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

这些名称实际上存在于各自的表中.

The names do in fact exist in their respective tables.

表是innodb.他们已经在其他列中拥有外键.这不是一个新的数据库.

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

和

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状态:

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

如果我尝试从company_to_module"中删除索引，我会收到此错误:

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)

这是我的 innodb 变量:

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                  |
+---------------------------------+------------------------+

我还想补充一点，当我在添加外键时，mysql 损坏了我的数据库并销毁了它.我不得不从备份重新加载才能重试.

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.

帮助?:/

推荐答案

我只是使用 Windows 应用了重构，然后将数据库重新导入到 Debian - 它有效.

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

我认为可以肯定地说 Debian 服务器或 linux 版本的 Mysql 出现了问题——也许是 5.1 版本中的错误?

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?

反正我也把服务器上的内存从1gb升级到2gb，这些问题都没有了.

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

我认为 MySQL 可能只是没有足够的 ram 来完成操作.如果是这样(而且似乎是这样)，我认为 MySQL 应该简单地这么说而不是吐出这些错误 - 让我和这里的每个人都认为这是一个语法或与模式相关的问题.

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.

相关文章