我有一个名为 的 MySQL 数据库camera_main_live，我使用它进行管理管理员。从表面上看，它是空的。因此，当我想添加一些内容时，我首先运行此脚本（将管理员设置为出错时停止）：
DROP TABLE IF EXISTS `Addresses`;
CREATE TABLE `Addresses` (
`Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`UserId` int(10) unsigned NOT NULL,
`FirstName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`LastName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Address` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Street` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Town` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`State` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Code` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Country` char(50) COLLATE utf8_unicode_ci DEFAULT 'Ireland',
`Billing` tinyint(3) unsigned DEFAULT '0',
`Shipping` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `IndexUserId` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
查询错误 (1813)：表“camera_main_live”的表空间存在。“Addresses”存在。请在导入之前丢弃表空间。
好吧，让我们试着弄清楚这一点。
ALTER TABLE `Addresses` DROP TABLESPACE;
查询错误（1146）：表“camera_main_live.Addresses”不存在
我们似乎陷入了僵局。
此时我们放弃 SQL 命令并尝试不同的操作：
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# ll
total 76
drwx------ 2 mysql mysql 4096 Jun 23 14:44 ./
drwx------ 13 mysql mysql 4096 Jun 23 11:12 ../
-rw-rw---- 1 mysql mysql 65536 Jun 10 14:09 Addresses.ibd
-rw-rw---- 1 mysql mysql 61 Jun 16 12:43 db.opt
啊，原来问题来了！
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# rm Addresses.ibd
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# ll
total 12
drwx------ 2 mysql mysql 4096 Jun 23 14:48 ./
drwx------ 13 mysql mysql 4096 Jun 23 11:12 ../
-rw-rw---- 1 mysql mysql 61 Jun 16 12:43 db.opt
但不！该CREATE TABLE命令抛出相同的错误。并且Addresses.ibd被重新创建。让我们尝试一些更激烈的事情。
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# cd ..
root@Web-Dev-HP-ProDesk:/var/lib/mysql# rm -r camera_main_live/
进而，
CREATE DATABASE `camera_main_live` COLLATE 'utf8_unicode_ci';
全新的数据库。一定是干净又空的吧？不，不是。我再次遇到同样的错误：表空间仍然存在。翻盖在哪里，我怎样才能摆脱它？
# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for debian-linux-gnu (x86_64) using EditLine wrapper
# uname --all
Linux Web-Dev-HP-ProDesk 3.19.0-21-generic #21-Ubuntu SMP Sun Jun 14 18:31:11 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 15.04
Release: 15.04
Codename: vivid
我在几个不同的本地数据库上看到了这一点：每次都有不同的表。到今天为止，camera_main_live似乎已经修复了，这更加令人困惑。但问题在其他地方仍然存在。以数据库test为例，
CREATE TABLE `ProductsRelatedTypes` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Position` int(11) NOT NULL,
`Title` char(100) COLLATE utf8_unicode_ci NOT NULL,
`Alert` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
查询错误 (1813)：表“test”的表空间。“ProductsRelatedTypes”存在。请在导入之前丢弃表空间。
DESC `ProductsRelatedTypes`
查询错误（1146）：表“test.ProductsRelatedTypes”不存在
SELECT * FROM `ProductsRelatedTypes`
查询错误（1146）：表“test.ProductsRelatedTypes”不存在
SHOW TABLES LIKE "ProductsRelatedTypes"
没有行。
root@Web-Dev-HP-ProDesk:/var/lib/mysql/test# ll | grep Related
-rw-rw---- 1 mysql mysql 8610 Jun 29 15:06 ProductsRelated.frm
-rw-rw---- 1 mysql mysql 0 Jun 29 15:06 ProductsRelated.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 29 15:06 ProductsRelated.MYI
-rw-rw---- 1 mysql mysql 65536 Jun 29 11:12 ProductsRelatedTypes.ibd
root@Web-Dev-HP-ProDesk:/var/lib/mysql/test# rm ProductsRelatedTypes.ibd
CREATE TABLE `ProductsRelatedTypes` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Position` int(11) NOT NULL,
`Title` char(100) COLLATE utf8_unicode_ci NOT NULL,
`Alert` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
查询错误 (1050)：表“test”.“ProductsRelatedTypes”已存在
啊？
DESC `ProductsRelatedTypes`
查询错误（1146）：表“test.ProductsRelatedTypes”不存在
SELECT * FROM `ProductsRelatedTypes`
查询错误（1146）：表“test.ProductsRelatedTypes”不存在
SHOW TABLES LIKE "ProductsRelatedTypes"
没有行。
root@Web-Dev-HP-ProDesk:/var/lib/mysql/test# ll | grep Related
-rw-rw---- 1 mysql mysql 8610 Jun 29 15:06 ProductsRelated.frm
-rw-rw---- 1 mysql mysql 0 Jun 29 15:06 ProductsRelated.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 29 15:06 ProductsRelated.MYI
-rw-rw---- 1 mysql mysql 65536 Jun 29 15:13 ProductsRelatedTypes.ibd
因此，当.ibd文件存在时，我被告知表空间已经存在。当我删除它时，我被告知桌子已经存在，尽管它显然不存在。让我感到困惑。
查询information_schema，我得到以下信息：
mysql> SELECT * FROM `INNODB_SYS_TABLES` WHERE `Name` LIKE "%Address%";
+----------+--------------------------------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+--------------------------------------+------+--------+-------+-------------+------------+---------------+
| 2826 | camera_main_live2/Addresses | 1 | 15 | 2812 | Antelope | Compact | 0 |
| 3789 | ronayne_mytools_main_live2/Addresses | 1 | 16 | 3775 | Antelope | Compact | 0 |
| 1312 | test/Addresses | 1 | 15 | 1298 | Antelope | Compact | 0 |
+----------+--------------------------------------+------+--------+-------+-------------+------------+---------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM `INNODB_SYS_TABLESPACES` WHERE `Name` LIKE "%Address%";
+-------+--------------------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+--------------------------------------+------+-------------+----------------------+-----------+---------------+
| 1298 | test/Addresses | 0 | Antelope | Compact or Redundant | 16384 | 0 |
| 2812 | camera_main_live2/Addresses | 0 | Antelope | Compact or Redundant | 16384 | 0 |
| 3775 | ronayne_mytools_main_live2/Addresses | 0 | Antelope | Compact or Redundant | 16384 | 0 |
+-------+--------------------------------------+------+-------------+----------------------+-----------+---------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `TABLESPACES`;
Empty set (0.00 sec)
这些都没有任何迹象表明camera_main_live.Addresses存在。任何查询information_schema.TABLES失败：
查询错误 (1018)：无法读取“.”的目录（错误号：24 - 打开的文件太多）
现在，重新启动计算机后，
mysql> SHOW STATUS LIKE 'open_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 61 |
| Open_streams | 0 |
| Open_table_definitions | 90 |
| Open_tables | 84 |
| Opened_files | 191 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
+--------------------------+-------+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "table_open_cache";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 431 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "innodb_open_files";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 431 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "open_files_limit";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0.01 sec)
尝试从命令提示符创建表：
mysql> CREATE TABLE `Addresses` ( `Id` int(10) unsigned NOT NULL AUTO_INCREMENT, `UserId` int(10) unsigned NOT NULL, `FirstName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `LastName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Address` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Street` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Town` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `State` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Code` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Country` char(50) COLLATE utf8_unicode_ci DEFAULT 'Ireland', `Billing` tinyint(3) unsigned DEFAULT '0', `Shipping` tinyint(3) unsigned DEFAULT '0', PRIMARY KEY (`Id`), KEY `IndexUserId` (`UserId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
ERROR 1813 (HY000): Tablespace for table '`test`.`Addresses`' exists. Please DISCARD the tablespace before IMPORT.
mysql> SHOW WARNINGS; +---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
| Error | 1813 | Tablespace for table '`test`.`Addresses`' exists. Please DISCARD the tablespace before IMPORT. |
| Error | 1030 | Got error 184 from storage engine |
+---------+------+------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
答案1
解决此问题的步骤（至少在 Windows 操作系统中）：
- 停止MySQL服务
- 删除
table.ibd文件 - 将所有文件从 MySQL Data 文件夹移动到备份文件夹
- 启动MySQL服务（所有主文件将再次创建）
- 创建您需要的表