3

I am trying to run this query:

alter table about_member discard tablespace;

But when I do, my error logs are outputting this:

InnoDB: Error: table `diskise`.`about_member`
InnoDB: is in the system tablespace 0 which cannot be discarded

What can I do so I can discard this tablespace so I can import a backup that I have?

Get Off My Lawn
  • 34,175
  • 38
  • 176
  • 338

2 Answers2

1

See this manual page:

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

Advantages of Per-Table Tablespaces

You can reclaim disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping them creates free space internally in the ibdata files. That free space can only be used for new InnoDB data.

It sounds like you created your table in the default tablespace, so you cannot discard the tablespace. That said, you should be able to import data without discarding a tablespace first, unless you're just out of disk space.

Community
  • 1
  • 1
elixenide
  • 44,308
  • 16
  • 74
  • 100
  • I have also run `alter table about_member import tablespace;`, and I get the same error except the last word is **imported** instead of **discarded** – Get Off My Lawn Nov 26 '13 at 04:56
  • Right; `tablespace` in that command still refers to the system tablespace. Is there data in the existing table that you need to keep? If not, why not drop or truncate the existing table and then import the new data? – elixenide Nov 26 '13 at 05:10
  • Okay how can I import it from an ibd file? – Get Off My Lawn Nov 26 '13 at 05:19
1

I just ran into same problem while I was trying to restore one table from .ibd file. I know it's been a long time so I wrote this here for people who still running into the same problem.

Continuing with @Ed Cottrell's answer, you will need to change the mysql global variable innodb_file_per_table to let mysql to store .ibd in your own file instead of using system tablespace.

To achieve this you can do (also see official docs):

  • change your my.cnf file, adding following line and restart mysql server

    [mysqld]
innodb_file_per_table=1

  • set global variable in mysql command (sometimes not working due to security issue)

    SET GLOBAL innodb_file_per_table=1;

You may need to DROP your table before this take effects. After doing this, you will be able to see .ibd files inside PATH/TO/MYSQL/DB_NAME directory (e.g. /var/lib/mysql/DB_NAME).

Tom
  • 1,064
  • 1
  • 10
  • 7