MySQL Bugs: #114253: No indexes found in information_schema.innodb

Bug #114253	No indexes found in information_schema.innodb_indexes after import tablespace
Submitted:	7 Mar 2024 2:12	Modified:	15 Mar 2024 4:50
Reporter:	George Ma (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Import table by discard and import tablespace, but can not found indexes in the information_schema.innodb_indexes table later.

How to repeat:
Doing as: https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html

I have built a debug version to select the dd tables, and I found:

1. when discard the tablespace, the dd tables have not been deleted (mysql.tablespaces, mysql.tables, mysql.indexes), but:
  - change the se_private_data in mysql.tables to add "discard=1;" 
  - change the se_private_id in mysql.tables to a new value (which is the table_id in information_schema.innodb_tables)

2. when import the tablespace, the dd tables also not rebuilt, but:
  - change the se_private_data in mysql.tables to add "discard=0;"

So, it will result that the records in mysql.indexes have not been changed, which will make the se_private_data in mysql.indexes not match the new se_private_id in mysql.tables, and no results will return when select information_schema.innodb_indexes.

Hi Mr. Ma,

Thank you for your bug report.

However, we are not able to repeat what you are reporting.

Let us inform you that this is a forum for the reports with a fully repeatable test cases. Such a test case should consist ONLY of the set of SQL statements that always lead to showing off the bug, that you are trying to report.

We do not see a test case like that in your report.

We are waiting for the test case as described above.

Can't repeat.

The link above has the complete case, here I just copy it:

Importing Tables
This example demonstrates how to import a regular non-partitioned table that resides in a file-per-table tablespace.

1. On the destination instance, create a table with the same definition as the table you intend to import. (You can obtain the table definition using SHOW CREATE TABLE syntax.) If the table definition does not match, a schema mismatch error is reported when you attempt the import operation.

mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;

2. On the destination instance, discard the tablespace of the table that you just created. (Before importing, you must discard the tablespace of the receiving table.)

mysql> ALTER TABLE t1 DISCARD TABLESPACE;

3. On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the table you intend to import. When a table is quiesced, only read-only transactions are permitted on the table.

mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;
FLUSH TABLES ... FOR EXPORT ensures that changes to the named table are flushed to disk so that a binary table copy can be made while the server is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB generates a .cfg metadata file in the schema directory of the table. The .cfg file contains metadata that is used for schema verification during the import operation.

Note
The connection executing FLUSH TABLES ... FOR EXPORT must remain open while the operation is running; otherwise, the .cfg file is removed as locks are released upon connection closure.

4. Copy the .ibd file and .cfg metadata file from the source instance to the destination instance. For example:

$> scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test
The .ibd file and .cfg file must be copied before releasing the shared locks, as described in the next step.

Note
If you are importing a table from an encrypted tablespace, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination instance together with the .cfg file. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 17.13, “InnoDB Data-at-Rest Encryption”.

5. On the source instance, use UNLOCK TABLES to release the locks acquired by the FLUSH TABLES ... FOR EXPORT statement:

mysql> USE test;
mysql> UNLOCK TABLES;
The UNLOCK TABLES operation also removes the .cfg file.

6. On the destination instance, import the tablespace:

mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;

Actually, you can just use one instance with two database to repeat the case.

Hi Mr. Ma,

Please, confirm that you are using innodb_file_per_table option and that tablespace contains one table only.

Also , please confirm that you are importing the table as described here:

https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html

Let us know that whether you have followed all these instructions.

Last , but not least, let us know which MySQL version/release are you using and whether you are using our binary or not ......

Thanks in advance.

Hi Mr. Ma,

There is no need for you to reply on any of our questions.

Simply, we followed your instructions with our official 8.0.36 binary and it worked like a charm:

 select * from information_schema.innodb_indexes;
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      152 | PRIMARY         |     1062 |    3 |        6 |       4 |     1 |              50 |
|     7575 | GEN_CLUST_INDEX |     3974 |    1 |        4 |       4 |  2810 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
2 rows in set (0.01 sec)

This is not a MySQL bug !!!!!

Sorry, it's my fault for forgetting the mysql version.

I meat this case first when using the community version 8.0.36, and to get more information I build the debug version with the latest 8.0.36 code.

I see that you can not repeat the case, but I just do step by step as the doc.

Maybe you can check the table_id in information_schema.innodb_tables and information_schema.innodb_indexes after you create and import the table t1, and you can also check the records in information_schema.innodb_indexes after you discard and import tablespace.

Hi Mr. Ma,

We have done the tests again and checked all of the above and everything is fine.

Are you sure that you are using our binary ???

I just fetch the rpm packages from the download page. Actually, I wonder to know that has the table_id in destination instance changed in you test?

Hi,

This are the outputs that you asked for.

This is also fully expected behaviour and this is not a bug.

+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      152 | PRIMARY         |     1062 |    3 |        6 |       4 |     1 |              50 |
|     7578 | GEN_CLUST_INDEX |     3980 |    1 |        4 |       4 |  2813 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+

+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      152 | PRIMARY         |     1062 |    3 |        6 |       4 |     1 |              50 |
|     7579 | GEN_CLUST_INDEX |     3980 |    1 |        4 |       4 |  2814 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+

I found the difference between our tests, in my test:

1. after create table t1, the data in i_s:

mysql> select * from innodb_tables where name = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1081 | test/t1 |   33 |      4 |    13 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from innodb_indexes where table_id = 1081;
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      166 | GEN_CLUST_INDEX |     1081 |    1 |        4 |       4 |    13 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.00 sec)

2. after discard tablespace:

mysql> select * from innodb_tables where name = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1082 | test/t1 |   33 |      4 |    13 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from innodb_indexes where table_id = 1082;
Empty set (0.00 sec)

mysql> select * from innodb_indexes where table_id = 1081;
Empty set (0.01 sec)

3. after import:

mysql> select * from innodb_tables where name = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1082 | test/t1 |   33 |      4 |    13 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from innodb_indexes where table_id = 1082;
Empty set (0.00 sec)

mysql> select * from innodb_indexes where table_id = 1081;
Empty set (0.00 sec)

The table_id is changed after discard tablespace in my test, but not changed in you test.

Maybe we can simplify this case:

1. create table and search the information_schema.innodb_tables :

mysql> create database test;

mysql> use test;

mysql> create table t1 (c1 int);

mysql> select * from information_schema.innodb_tables where name = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1081 | test/t1 |   33 |      4 |    13 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_indexes where table_id = 1081;
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      166 | GEN_CLUST_INDEX |     1081 |    1 |        4 |       4 |    13 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.00 sec)

2. discard the table and search the information_schema.innodb_tables again :

mysql> alter table t1 discard tablespace;

mysql> select * from information_schema.innodb_tables where name = 'test/t1';
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1082 | test/t1 |   33 |      4 |    13 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_indexes where table_id = 1082;
Empty set (0.00 sec)

mysql> select * from information_schema.innodb_indexes where table_id = 1081;
Empty set (0.00 sec)

The table_id will must change.

Hi Mr. Ma,

We understand your logic.

However, our documentation does not specify that table_id MUST change.

Hence, there is no need to fix anything , since table_id may change or may not change, since there is no documentation and no standard that would specify either.

Not a bug.

So,

1. what should I do to make the table_id not change?

2. if the table_id changed, no indexes can be found in informartion_schema.innodb_indexes, I really think it is a bug.

Hi,

We repeated the test and we got the  indexes:

+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      152 | PRIMARY         |     1062 |    3 |        6 |       4 |     1 |              50 |
|     7578 | GEN_CLUST_INDEX |     3980 |    1 |        4 |       4 |  2813 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+

+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+
|      152 | PRIMARY         |     1062 |    3 |        6 |       4 |     1 |              50 |
|     7579 | GEN_CLUST_INDEX |     3980 |    1 |        4 |       4 |  2814 |              50 |
+----------+-----------------+----------+------+----------+---------+-------+-----------------+

We used new schemas and at the end, there was one table in both schemas and we ran a SELECT on the I_S table , with no conditions and got the above results.

You have probably searched the I_S on the wrong table_id.

Maybe we make this case complex, I am just wondering how to keep the table_id not change when do discard tablespace? If there is a config, please tell me, thanks.

Hi,

No, there is no server variable for that purpose.

Not a bug.

Hi,

I have search the latest 8.0.36 code, and extra the main logic of discard tablespace:

|--> Sql_cmd_discard_import_tablespace::mysql_discard_or_import_tablespace
|    |--> handler::ha_discard_or_import_tablespace
|    |    |--> ha_innobase::discard_or_import_tablespace
|    |    |    |--> row_discard_tablespace_for_mysql
|    |    |    |    |--> row_discard_tablespace
|    |    |    |    |    |--> row_mysql_table_id_reassign    // assign new table_id
|    |    |    |    |    |--> fil_discard_tablespace
|    |    |    |    |    |--> dict_table_change_id_in_cache
|    |    |    |--> dd_table_discard_tablespace
|    |    |    |    |--> table_def->set_se_private_id    // set the new table_id on dd table

In the above logic, the table_id must change when do discard tablespace. So, I am very doubt that how can you make the table_id not changed? Can you show me the full processes of your operations?

Hi Mr. Ma,

We truly apologise, but we have some (incomplete) documentation on the processes, but that documentation is 100 % for the internal usage.