How can I fully remove a tablespace from MySQL?

Question

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)

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 08 '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 08 '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 08 '15 at 15:54
Ah. Just noticed your first comment. Shall try emptying the database by hand and then restarting MySQL. — TRiG, Jul 08 '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 08 '15 at 15:59
Too many open files can be related! When mysql tries to open the table file, it silently fails, so can cause this... Either make higher open files limit in mysql start script OR lower mysql variables table_open_cache, innodb_open_files. What are current values of these (and open_files_limit)? Also post show status like 'open_%'; — Marki555, Jul 08 '15 at 20:19
Does http://stackoverflow.com/questions/17914446/mysqldump-problems-with-restore-error-please-discard-the-tablespace-before-imp help you any? I'm not a SQL expert, but it has several points in common with your question. — Jeff Schaller, Jul 09 '15 at 12:49
see also: http://superuser.com/questions/675445/mysql-innodb-lost-tables-but-files-exist — Jeff Schaller, Jul 09 '15 at 12:51
Any chance this could be a bug in Adminer? Have you verified this behavior in a mysql shell? Might be a nice sanity check as some admin clients can cause weird behavior. I have seen some strange behavior with an admin client related to table building. — 111---, Jul 09 '15 at 13:10
Today, camera_main_live is working fine, but test is broken. Pasted the DROP TABLE and CREATE TABLE commands onto a mysql> prompt: same result. @datUser. — TRiG, Jul 09 '15 at 13:15
I'm actually perfectly willing to throw away everything. Anything important is backed up. I can delete all the tables and all the databases and reinstall MySQL if it'll help. — TRiG, Jul 09 '15 at 13:18
So run the commands at a mysql prompt, and after the errors that are fed to the prompt run show warnings;. This should give you the error message and it's error code, I am curious if this is either a live mysql bug or one you might want to report. — 111---, Jul 09 '15 at 13:23
Added attempt, @datUser. It's possible that the previous table, removed with DROP TABLE IF EXISTS, was MyISAM, not InnoDB. I'm not certain. Might that make a difference? — TRiG, Jul 09 '15 at 13:25
Not sure, this is some pretty weird behavior. If this is as reproducable as it appears to be you may want to file a bug report over at the MYSQL Bug page. Or at minimum take a look and see if you see anything that looks similar. — 111---, Jul 09 '15 at 13:33
Does using an explicit COMMIT; statement after dropping the table help, just to make sure that that modification has been committed/flushed to disk? — Castaglia, Feb 18 '16 at 17:49
@Castaglia. Thanks. Just tried that. Nope. Should probably update this question, now I'm on the latest Ubuntu but still have the problem. I've kind of forgotten about it, and am have just abandoned the broken databases and am using others for my dev work. — TRiG, Feb 25 '16 at 14:35
I actually fixed this in the end by uninstalling MySQL, completely removing all traces of it, and then reinstalling it. — TRiG, Sep 09 '16 at 16:31

score 2 · Answer 1 · edited Mar 23 '16 at 13:22

2

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

stop MySQL Service
delete table.ibd file
move all files from MySQL Data folder to a backup folder
start MySQL Service (all main files will be created again)
create the table you need

edited Mar 23 '16 at 13:22

techraf

5,941

answered Mar 23 '16 at 13:00

Marco Aurélio

21

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

How can I fully remove a tablespace from MySQL?

1 Answers1