6

I have a MySQL database called camera_main_live, which I administer using Adminer. It is, to all appearance, empty. So when I want to add some content, I begin by running this script (with Adminer set to stop on error):

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.

Okay, let’s try to clear that up.

ALTER TABLE `Addresses` DROP TABLESPACE;

Error in query (1146): Table 'camera_main_live.Addresses' doesn't exist

We would appear to be at an impasse.

At this point we give up on the SQL commands and try something different:

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, so there’s the problem!

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

But nope! The CREATE TABLE command throws an identical error. And Addresses.ibd is recreated. Let’s try something more drastic.

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/

And then,

CREATE DATABASE `camera_main_live` COLLATE 'utf8_unicode_ci';

Brand new database. It must be clean and empty, right? No. It isn’t. I get the same error again: the tablespace still exists. Where the flip is it, and how can I get rid of it?

# 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

I was seeing this on a few different local databases: different tables each time. As of today, camera_main_live seems to have fixed itself, which is even more confusing. But the problem still exists elsewhere. On the database test, for example,

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

So when the .ibd file exists, I am told that the tablespace already exists. When I remove that, I am told that the table already exists, even though it clearly doesn’t. Colour me confused.


Querying information_schema, I get this information:

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)

In none of these is there any indication that camera_main_live.Addresses exists. Any query on information_schema.TABLES fails:

Error in query (1018): Can't read dir of '.' (errno: 24 - Too many open files)

Now, after rebooting my computer,

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)

Trying to create a table from the command prompt:

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)

closed as off-topic by Jeff Schaller, Stephen Rauch, Dmitry Grigoryev, Archemar, Anthony Geoghegan Jul 11 '17 at 17:49

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions describing a problem that can't be reproduced and seemingly went away on its own (or went away when a typo was fixed) are off-topic as they are unlikely to help future readers." – Jeff Schaller, Stephen Rauch, Dmitry Grigoryev, Archemar, Anthony Geoghegan
If this question can be reworded to fit the rules in the help center, please edit the question.

  • If you remove the ibd file or directory, mysql process still has it open, so nothing changes... when later mysql closes that file handle, it "fixes itself" because on subsequent open, the file/dir is not there. – Marki555 Jul 8 '15 at 13:41
  • You can check what tables/tablespaces exist by quering the information_schema database... can you post results here? For example tables INNODB_SYS_TABLES, TABLES, TABLESPACES – Marki555 Jul 8 '15 at 14:02
  • I reported earlier that camera_main_live had fixed itself. However, when I tried running that original query again, it dropped the table, as expected, but failed to recreate it. So apparently camera_main_live has broken itself again. Gah! Anyway, the information you requested from information_schema is now in place. – TRiG Jul 8 '15 at 15:54
  • Ah. Just noticed your first comment. Shall try emptying the database by hand and then restarting MySQL. – TRiG Jul 8 '15 at 15:55
  • That worked, for now. Maybe that'll be it. Still not sure why this problem happened in the first place. – TRiG Jul 8 '15 at 15:59
1

Steps to resolve this matter (at least in Windows OS):

  1. stop MySQL Service
  2. delete table.ibd file
  3. move all files from MySQL Data folder to a backup folder
  4. start MySQL Service (all main files will be created again)
  5. create the table you need
  • WARNING: these steps doesn't work very well for every cases... sorry! If you proceeded that way, just copy your files from backup folder to original folder. – Marco Aurélio Mar 23 '16 at 14:47
  • Yes, do not fucking delete my sql files unless you just want to drop the fucking schema and reinstall fuck of everything! – Alex Barker Jan 21 at 23:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.