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)]>
Improve this question
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;. Commented Apr 22, 2016 at 15:56

1 Answer 1

Reset to default
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
Improve this answer
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. Commented Apr 25, 2016 at 20:39
  • What was the "Warnings: 1"? Commented 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. Commented 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. Commented 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. Commented Apr 27, 2016 at 1:39

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.