InnoDB在删除所有表后还原数据库时抱怨外键名称正在使用 [英] InnoDB complaining about foreign key name in use when restoring database after dropping all tables
问题描述
我试图使用 mysqldump 将数据从一个服务器复制到另一个服务器。目标服务器安装了相同数据库的先前版本，因此引用的所有对象都已存在。我检查了从 mysqldump 的输出，并且它包括适当的语句（1）在重新创建期间禁用外键检查和（2）删除旧表。编辑的摘要如下所示：
I'm trying to use mysqldump to copy data from one server to another. The target server had a previous version of the same database installed, so all objects referenced already exist. I checked the output from mysqldump and it includes appropriate statements to (1) disable foreign key checks during the recreation and (2) drop the old tables. An edited excerpt looks like this:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
...
DROP TABLE IF EXISTS `bins`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bins` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`site` int(11) NOT NULL,
`type` varchar(255) NOT NULL,
`capacity` varchar(255) NOT NULL,
`quantity` int(11) NOT NULL,
`cost` decimal(9,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `sites_bins_idx` (`site`),
CONSTRAINT `sites_bins` FOREIGN KEY (`site`) REFERENCES `sites` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=457 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
我理解 / *！nnnnn ... * / 语法是一个条件语法，只有在服务器版本高于所包含的数字（我的是5.0）时才执行，因此所有包含的SET语句都应该执行。
I understand the /*!nnnnn ... */ syntax to be a conditional syntax that only executes if the server version is higher than the included number (which mine is, being 5.0), so all the included SET statements should be executing.
我运行脚本文件生成运行 mysql -p< dump.sql ，但收到以下错误：
I ran the script file generated by running mysql -p < dump.sql, but received the following error:
ERROR 1005 (HY000) at line 27: Can't create table './myschema/bins.frm' (errno: 121)
查询 show innodb status 提供以下附加信息：
Running the query show innodb status gives the following additional information:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140420 13:15:07 Error in foreign key constraint creation for table `myschema/bins`.
A foreign key constraint of name `myschema/sites_bins` already exists.
但是，当它所引用的表已被删除时，约束如何存在？ / p>
However, how can the constraint exist when the table(s) it refers to have been dropped?
mysql> show tables;
Empty set (0.00 sec)
mysql> SELECT constraint_name, table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY' AND table_schema = DATABASE() ORDER BY constraint_name;
+--------------------------+------------------+
| constraint_name | table_name |
+--------------------------+------------------+
| clients_sites | sites |
| client_contacts | contacts |
| scheduledvisit_site | scheduled_visits |
| sites_bins | bins |
| sites_visits | site_visits |
| sitevisit_sitevisitentry | site_visit_entry |
+--------------------------+------------------+
6 rows in set (0.00 sec)
$ c> drop database myschema ，但是约束仍然存在，即使在这之后。
I've even tried drop database myschema, but the constraints are still left behind even after this.
这些约束在它们的表已被丢弃？我如何摆脱他们，所以我可以重新创建他们各自的表？
How have these constraints been left behind after their tables have been dropped? And how do I get rid of them so I can recreate their respective tables?
推荐答案
InnoDB对对象名称的处理不区分大小写。 MySQL的依赖于操作系统的文件名处理，在我的情况下是敏感。先前版本的数据库已经创建了名字大写开头，所以虽然MySQL知道一个数据库 myschema 这是空的和 MySchema 这不是，InnoDB认为两者是一样的。删除 MySchema 数据库允许我从远程系统恢复转储。
InnoDB's handling of object names is case insensitive. MySQL's is dependent on the operating system's filename handling, which in my case was case sensitive. A previous version of the database had been created with the names capitalised at the start, so while MySQL was aware of a database myschema which was empty and MySchema which was not, InnoDB considered the two to be the same. Dropping the MySchema database allowed me to restore the dump from the remote system.
这篇关于InnoDB在删除所有表后还原数据库时抱怨外键名称正在使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
