3

We have a multi-GB InnoDB MariaDB (recently switched from MySQL). For some reason, the value of innodb_file_per_table changed to ON (we wanted only one innodb data file explicitely). So when I ran an ALTER TABLE statement on one of the big tables, it created a separate .ibd file which now occupies 35GB even though I had takes measures to make ~100GB space free in the shared tablespace (ibdata1).

How can I re-integrate the data from this file back into the shared tablespace? I thought maybe DISCARD TABLESPACE would do that, but this just deletes the file and the data in it (I tested it with a sample table of course).

3

While there are many reasons why you may want separate tablespaces, I will assume you are aware of all the disadvantages of having a single shared tablespace, specially in terms of administration (for example, how you can waste filesystem space when creating a temporary table on ALTER).

You probably got innodb_file_per_table changed automatically if you upgraded recently, as on MySQL 5.6 and some versions of MariaDB it is set by default to 1.

To reintegrate the table inside the main tablespace, execute the following on the running server:

SET GLOBAL innodb_file_per_table = 0;

Then recreate the table by doing either:

ALTER TABLE your_table ENGINE=InnoDB;

or

ALTER TABLE your_table ENGINE=InnoDB, ALGORITHM=COPY;

if you are using MariaDB 10/MySQL 5.6.

That will put your table back to the tablespace 0. Remember to change the innodb-file-per-table option on your my.cnf for it to survive a reboot!

Yes, DISCARD TABLESPACE is used for other reasons, mainly importing data with a transportable tablespace.

  • Yes, we have considered the advantages and disadvantages before we switched from MyISAM. I did try ALTER TABLE just now, and while your statement didn't work right away, it did work with ,ALGORITHM=COPY - the .idb file of the small test table disappeared. – Stefan Seidel Aug 15 '14 at 8:30
  • 1
    @Stefan Seidel On my defense, it not working without the algorithm definition was a relatively new change (5.6.17) dev.mysql.com/doc/refman/5.6/en/… and you didn't say which version of MariaDB you were using. :-) – jynus Aug 15 '14 at 8:44
  • no problem, we just switched a couple of days ago :) – Stefan Seidel Aug 15 '14 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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