1

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)]>
1
  • 1
    You should add the MariaDB version, maybe it just uses ALGORITHM=INPLACE by default. You could force copy by adding ALGORITHM=COPY or possibly even by doing optimize table menu_router;.
    – jkavalik
    Apr 22, 2016 at 15:56

1 Answer 1

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
5
  • Force, old_alter, etc are not needed -- the alter takes time, and it will, of necessity, do the copy. Only after the copy will the DATAFILE be changed.
    – Rick James
    Apr 25, 2016 at 20:39
  • What was the "Warnings: 1"?
    – Rick James
    Apr 25, 2016 at 20:39
  • @RickJames so you are saying that even though those ibd files are still in place after running the commands that eventually they will be removed? Trying to reduce the amount of open files on the system.
    – jozwikjp
    Apr 26, 2016 at 11:44
  • That's a guess. If the .ibd files did not go away in a few minutes, then it is a 'wrong' guess.
    – Rick James
    Apr 26, 2016 at 20:47
  • I think it was a 'wrong' guess in my case because it never removed the files or moved the table space after a day.
    – jozwikjp
    Apr 27, 2016 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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