0

I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):

  • Added innodb_file_per_table=ON
  • Restarted MySQL
  • Dumped all databases, one by one.

However, when I import the databases back, everything is written to ibdata1 file.
But, When I create a brand new DB via MySQL Client, and add a random table to it, the data is written to its own folder + table.ibd file.

Any idea what I could be doing wrong?

Few troubleshooting steps I tried:

  • Reboot
  • show variables like 'innodb_file_per_table'; (Value = ON)
Improve this question
7
  • 1
    However, when I import the databases back, everything is written to ibdata1 file. Have you deleted/recreated the database? or its tables at least? innodb_file_per_table setting is applied to newly created tables only and not affects on existing tables.
    – Akina
    Commented Dec 24, 2020 at 5:01
  • Yup. I deleted the databases. And then because of some mistakes, I had to purge mysql completely, then reinstall it.
    – FlyingZebra1
    Commented Dec 24, 2020 at 5:10
  • 1
    If so then check the dump. Maybe it contains a statement which disables file per table?
    – Akina
    Commented Dec 24, 2020 at 5:11
  • 1
    How would one go about doing that? The dump is common (but large) text file. You may search for innodb_file_per_table in it using some text viewer/aditor which can work with large foles, or simply using find command-line utility
    – Akina
    Commented Dec 24, 2020 at 5:37
  • 1
    Which section of my.cnf did you put the statement in? Did you "dump" using mysqldump? Or something else? What parameters were on that command?
    – Rick James
    Commented Dec 24, 2020 at 8:07

2 Answers 2

Reset to default
2

First of all, innodb_file_per_table is enabled by default for MySQL 8.

If you want to know what tables are sitting in ibdata1, run the following query

select * from information_schema.innodb_tables;

This will inform you about each table and what tablespace it resides in

EXAMPLE

I spun of a vagrant lab that runs MySQL 8.0.17

I loaded the KJV of the Bible and added a fulltext index

Here is the output of the query I mentioned

mysql> select * from information_schema.innodb_tables;
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
| TABLE_ID | NAME                                                | FLAG | N_COLS | SPACE      | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
|     1025 | mysql/db                                            |  161 |     25 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1026 | mysql/user                                          |  161 |     54 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1027 | mysql/default_roles                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1028 | mysql/role_edges                                    |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1029 | mysql/global_grants                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1030 | mysql/password_history                              |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1031 | mysql/func                                          |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1032 | mysql/plugin                                        |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1054 | mysql/help_topic                                    |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1055 | mysql/help_category                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1057 | mysql/help_relation                                 |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1036 | mysql/servers                                       |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1037 | mysql/tables_priv                                   |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1038 | mysql/columns_priv                                  |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1056 | mysql/help_keyword                                  |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1040 | mysql/time_zone_name                                |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1041 | mysql/time_zone                                     |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1042 | mysql/time_zone_transition                          |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1043 | mysql/time_zone_transition_type                     |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1044 | mysql/time_zone_leap_second                         |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1045 | mysql/procs_priv                                    |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1046 | mysql/component                                     |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1047 | mysql/slave_relay_log_info                          |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1048 | mysql/slave_master_info                             |  161 |     31 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1049 | mysql/slave_worker_info                             |  161 |     16 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1050 | mysql/gtid_executed                                 |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1051 | mysql/server_cost                                   |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1052 | mysql/engine_cost                                   |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1053 | mysql/proxies_priv                                  |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1058 | sys/sys_config                                      |   33 |      7 |          1 | Dynamic    |             0 | Single     |            0 |
|     1101 | bible/verses                                        |   33 |      8 |         44 | Dynamic    |             0 | Single     |            0 |
|     1102 | bible/fts_000000000000044d_being_deleted            |   33 |      4 |         45 | Dynamic    |             0 | Single     |            0 |
|     1103 | bible/fts_000000000000044d_being_deleted_cache      |   33 |      4 |         46 | Dynamic    |             0 | Single     |            0 |
|     1104 | bible/fts_000000000000044d_config                   |   33 |      5 |         47 | Dynamic    |             0 | Single     |            0 |
|     1105 | bible/fts_000000000000044d_deleted                  |   33 |      4 |         48 | Dynamic    |             0 | Single     |            0 |
|     1106 | bible/fts_000000000000044d_deleted_cache            |   33 |      4 |         49 | Dynamic    |             0 | Single     |            0 |
|     1107 | bible/fts_000000000000044d_00000000000000cd_index_1 |   33 |      8 |         50 | Dynamic    |             0 | Single     |            0 |
|     1108 | bible/fts_000000000000044d_00000000000000cd_index_2 |   33 |      8 |         51 | Dynamic    |             0 | Single     |            0 |
|     1109 | bible/fts_000000000000044d_00000000000000cd_index_3 |   33 |      8 |         52 | Dynamic    |             0 | Single     |            0 |
|     1110 | bible/fts_000000000000044d_00000000000000cd_index_4 |   33 |      8 |         53 | Dynamic    |             0 | Single     |            0 |
|     1111 | bible/fts_000000000000044d_00000000000000cd_index_5 |   33 |      8 |         54 | Dynamic    |             0 | Single     |            0 |
|     1112 | bible/fts_000000000000044d_00000000000000cd_index_6 |   33 |      8 |         55 | Dynamic    |             0 | Single     |            0 |
|     1113 | bible/books                                         |   33 |      5 |         56 | Dynamic    |             0 | Single     |            0 |
|     1150 | ft_db/ft_test_innodb                                |   33 |      6 |         93 | Dynamic    |             0 | Single     |            0 |
|     1151 | ft_db/fts_000000000000047e_being_deleted            |   33 |      4 |         94 | Dynamic    |             0 | Single     |            0 |
|     1152 | ft_db/fts_000000000000047e_being_deleted_cache      |   33 |      4 |         95 | Dynamic    |             0 | Single     |            0 |
|     1153 | ft_db/fts_000000000000047e_config                   |   33 |      5 |         96 | Dynamic    |             0 | Single     |            0 |
|     1154 | ft_db/fts_000000000000047e_deleted                  |   33 |      4 |         97 | Dynamic    |             0 | Single     |            0 |
|     1155 | ft_db/fts_000000000000047e_deleted_cache            |   33 |      4 |         98 | Dynamic    |             0 | Single     |            0 |
|     1156 | ft_db/fts_000000000000047e_0000000000000107_index_1 |   33 |      8 |         99 | Dynamic    |             0 | Single     |            0 |
|     1157 | ft_db/fts_000000000000047e_0000000000000107_index_2 |   33 |      8 |        100 | Dynamic    |             0 | Single     |            0 |
|     1158 | ft_db/fts_000000000000047e_0000000000000107_index_3 |   33 |      8 |        101 | Dynamic    |             0 | Single     |            0 |
|     1159 | ft_db/fts_000000000000047e_0000000000000107_index_4 |   33 |      8 |        102 | Dynamic    |             0 | Single     |            0 |
|     1160 | ft_db/fts_000000000000047e_0000000000000107_index_5 |   33 |      8 |        103 | Dynamic    |             0 | Single     |            0 |
|     1161 | ft_db/fts_000000000000047e_0000000000000107_index_6 |   33 |      8 |        104 | Dynamic    |             0 | Single     |            0 |
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
55 rows in set (0.00 sec)

mysql> desc information_schema.innodb_tables;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| TABLE_ID      | bigint(21) unsigned | NO   |     |         |       |
| NAME          | varchar(655)        | NO   |     |         |       |
| FLAG          | int(11)             | NO   |     |         |       |
| N_COLS        | int(11)             | NO   |     |         |       |
| SPACE         | bigint(21)          | NO   |     |         |       |
| ROW_FORMAT    | varchar(12)         | YES  |     |         |       |
| ZIP_PAGE_SIZE | int(11) unsigned    | NO   |     |         |       |
| SPACE_TYPE    | varchar(10)         | YES  |     |         |       |
| INSTANT_COLS  | int(11)             | NO   |     |         |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>

Please note that any table whose space value is 0, resides in the ibdata1. It says to in the MySQL 8.0 Docs about INFORMATION_SCHEMA.INNODB_TABLES;

At the moment, there are no tables in ibdata1.

Let's see If I can inject one into ibdata1.

mysql> use bible
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> set global innodb_file_per_table = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> create table injection (id int not null auto_increment, name varchar(20), primary key (id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into injection (name) values ('Hello, World !!!');
Query OK, 1 row affected (0.01 sec)

mysql> select * from injection;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Hello, World !!! |
+----+------------------+
1 row in set (0.00 sec)

mysql>

OK, now let's check information_schema

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1162 | bible/injection |   33 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>

TADA !!!

OK, so now go run

select * from information_schema.innodb_tables where space = 0;

If you don't see any rows returned, then you have nothing in ibdata1.

WHAT IF I STILL HAVE TABLES IN ibdata1?

If you are starting brand new in MySQL. You should not have this problem from the beginning. Notwithstanding, let's say you have this situation in MySQL 8.0. How to you do yank the table out of ibdata1 ???

Let's try ALTER TABLE ... ENGINE=InnoDB

Unfortunately, you cannot perform a NULL ALTER like before:

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1162 | bible/injection |   33 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql> set global innodb_file_per_table = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table bible.injection engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>

You could get away with this in MySQL 5.7 and back. For MySQL 8.0, the temp table used maintains the table's current tablespace metadata.

Let's try CREATE TABLE LIKE

mysql> create table bible.injection2 like bible.injection;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection  |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
|     1164 | bible/injection2 |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.00 sec)

mysql> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql>

OK Ouch !!!. That pesky tablespace metadata gets dragged around like luggage.

Let's try ALTER TABLE ... TABLESPACE=innodb_file_per_table

Here we go ...

mysql> create table bible.injection2 like bible.injection;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection  |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
|     1164 | bible/injection2 |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.00 sec)

mysql> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bible.injection2 TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bible.injection TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
Empty set (0.00 sec)

mysql>

TADA !!!

CONCLUSION

If you suspect any tables are in ibdata1, find them with

select * from information_schema.innodb_tables where space = 0;

Then just run

ALTER TABLE mydb.mytable TABLESPACE=innodb_file_per_table;

Please remember: The default innodb_file_per_table is ON.

No need to mysqldump and reload.

If you are attempting to shrink ibdata1, you will have load data into a brand new installation. You cannot just delete ibdata1 like you could in MySQL 5.5 and back. MySQL 5.6 + has innodb tables in the mysql schema database. So, you cannot play the same file manipulation games. MySQL 8.0 now demands a lot more respect from a DBA.

Improve this answer
3
  • Thank you, trying this out now, hopefully that will do the trick. That said, if I wanted to carry over database from server 1, that does not have filer per table enabled, to server 2, and have tables in one file per table - is there a way to do that via mysqldump (server 1 & 2 are mysql 8)? The method i tried originally, did not convert the tables to file per table.
    – FlyingZebra1
    Commented Dec 25, 2020 at 21:12
  • 1
    You did not specify --add-drop-table when launching the mysqldump. It probably has CREATE TABLE IF NOT EXISTS in the dump now. You will have to drop the tables in the place you are trying to load it. Try recreating the dump with --add-drop-table. When the script does the DROP TABLE command, the metadata will disappear with it. So, ibdata1 should no longer know about the table's prior existence.
    – RolandoMySQLDBA
    Commented Dec 25, 2020 at 21:25
  • Thank you for the suggestion. If i run into this again, I will re-create the mysqldump with this option. But for the time being, your original ALTER TABLE suggestion worked. Bummer ibdata file can not be deleted in mysql 8, but that is something I can live with until next upgrade.
    – FlyingZebra1
    Commented Dec 27, 2020 at 20:48
1

For simply finding out where a table is:

Versions 5.6, 5.7, most of MariaDB:

I created two tables in database ftp:

mysql>     SELECT  name, space
    ->         FROM  information_schema.INNODB_SYS_TABLES
    ->         WHERE  name LIKE 'fpt/%';
+-------------+-------+
| name        | space |
+-------------+-------+
| fpt/in_fpt  |   491 |  -- non-zero implies innodb_file_per_table = ON
| fpt/not_fpt |     0 |  -- implies OFF (that is, in ibdata1)
+-------------+-------+

In MySQL 8.0, use this table: information_schema.INNODB_TABLESPACES, but a non-file-per-table table is indicated by absence from the table, not 0.

Improve this answer

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.