Skip to main content
added 25 characters in body
Source Link
jozwikjp
  • 356
  • 3
  • 11

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

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

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

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)]>
Source Link
jozwikjp
  • 356
  • 3
  • 11

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

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

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