Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files

Question

Trying to migrate individual tablespaces back into the shared ibdata tablespace. MariaDB version 10.0.16

SET innodb_file_per_table to off in MySQL, restated mysql and new tables are in fact going into shared space.

Attempted to run alter table statement to recreate move the table back into the shared tablespace but the tables are not moving.

SET SQL_LOG_BIN = 0; ALTER TABLE menu_router ENGINE=InnoDB;

But the file still exists

-rw-rw----    1 mysql mysql  3.0M Apr 22 14:50 menu_router.ibd

in information schema

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE %db4472%';


+---------+---------------------------------+
| SPACE   | PATH                            |
+---------+---------------------------------+
| 2431817 | ./db4472/menu_router.ibd |
+---------+---------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]>

Answer 1

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