MariaDB ALTER TABLE – Reintegration of .ibd File into Shared Tablespace

datafileinnodbmariadbMySQL

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).

Best Answer

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;

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.

Related Solutions

MySQL – Migrating from innodb_file_per_table to Off

Found a solution that works

MariaDB [(none)]>  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '%5377%';
+---------+---------------------------------+
| SPACE   | PATH                            |
+---------+---------------------------------+
| 2432544 | ./db5377/menu_router.ibd |
+---------+---------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]> SET SESSION old_alter_table=1
-> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> ALTER TABLE db5377.menu_router FORCE;
Query OK, 2331 rows affected, 1 warning (0.10 sec)
Records: 2331  Duplicates: 0  Warnings: 1

MariaDB [(none)]>  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES  WHERE PATH LIKE '%5377%';
Empty set (0.05 sec)

To convert all the separate table spaces into the shared space. Credit to: Migrate from innodb_file_per_table to off in MySQL

echo "SET SESSION old_alter_table=1;" > ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-uroot -p"
SQL="SELECT CONCAT('ALTER TABLE ',REPLACE(REPLACE(REPLACE(PATH,'./',''),'.ibd',''),'/','.'),' FORCE;')"
SQL="${SQL} FROM  INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH NOT LIKE '%mysql%'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> ConvertInnoDBToInnoDB.sql


mysql> source /root/ConvertInnoDBToInnoDB.sql

Mysql – Need correct thesqlfrm command path to restore a database from .ibd and .frm files

You cannot just move the .exe around like that and expect it to work. All libraries and dll's (in your case I suspect _bz2.pyd) have to be in the same directory structure.

You can execute commands from other locations by specifying the entire path or from any directory which is in your PATH environment variable.

You could either execute

C:\wamp\bin\mysql\mysql5.6.12\data\wordpress>c:\path\to\mysqlfrm --server=root:rootpassword@localhost --port=3307

or change your PATH