Eu tenho um banco de dados MySQL chamado camera_main_live , que eu administro usando o Administrador . É, aparentemente, vazia. Então, quando eu quero adicionar algum conteúdo, eu começo executando este script (com o Adminer definido para parar em erro):
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;
Error in query (1813): Tablespace for table ''camera_main_live'.'Addresses'' exists. Please DISCARD the tablespace before IMPORT.
Ok, vamos tentar esclarecer isso.
ALTER TABLE 'Addresses' DROP TABLESPACE;
Error in query (1146): Table 'camera_main_live.Addresses' doesn't exist
Parece que estamos em um impasse.
Neste ponto, desistimos dos comandos SQL e tentamos algo diferente:
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
Ah, então tem o problema!
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
Mas não! O comando CREATE TABLE gera um erro idêntico. E Addresses.ibd é recriado. Vamos tentar algo mais drástico.
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/
E então,
CREATE DATABASE 'camera_main_live' COLLATE 'utf8_unicode_ci';
Novo banco de dados. Deve estar limpo e vazio, certo? Não. Não é. Eu recebo o mesmo erro novamente: o espaço de tabela ainda existe. Onde está o problema e como posso me livrar dele?
# 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
Eu estava vendo isso em alguns bancos de dados locais diferentes: tabelas diferentes a cada vez. A partir de hoje, camera_main_live parece ter se corrigido, o que é ainda mais confuso. Mas o problema ainda existe em outro lugar. No banco de dados test , por exemplo,
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
Error in query (1813): Tablespace for table ''test'.'ProductsRelatedTypes'' exists. Please DISCARD the tablespace before IMPORT.
DESC 'ProductsRelatedTypes'
Error in query (1146): Table 'test.ProductsRelatedTypes' doesn't exist
SELECT * FROM 'ProductsRelatedTypes'
Error in query (1146): Table 'test.ProductsRelatedTypes' doesn't exist
SHOW TABLES LIKE "ProductsRelatedTypes"
No rows.
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
Error in query (1050): Table ''test'.'ProductsRelatedTypes'' already exists
Huh?
DESC 'ProductsRelatedTypes'
Error in query (1146): Table 'test.ProductsRelatedTypes' doesn't exist
SELECT * FROM 'ProductsRelatedTypes'
Error in query (1146): Table 'test.ProductsRelatedTypes' doesn't exist
SHOW TABLES LIKE "ProductsRelatedTypes"
No rows.
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
Portanto, quando o arquivo .ibd existe, me disseram que o espaço de tabela já existe. Quando eu removo isso, sou informado de que a tabela já existe, mesmo que claramente não exista. Colora-me confuso.
Consultando information_schema , recebo esta informação:
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)
Em nenhum destes há qualquer indicação de que camera_main_live.Addresses exista. Qualquer consulta em information_schema.TABLES falha:
Error in query (1018): Can't read dir of '.' (errno: 24 - Too many open files)
Agora, depois de reiniciar o computador,
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)
Tentando criar uma tabela no prompt de comando:
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)
Etapas para resolver esse problema (pelo menos no sistema operacional Windows):
table.ibd arquivo Tags mysql