Comment 6 for bug 1702408

The detailed description is the following. How the initial data led to the indexes order issue see item 1 in https://percona.zendesk.com/attachments/token/EzERiNDmk8a1gJjtQMhPfA7Dv/?name=descr.txt.

All the code and description is for 5.7.

Consider the procedure of tablespace importing for two cases:

1) with cfg file:
Let's look into row_import_for_mysql():
...
 err = row_import_read_cfg(table, trx->mysql_thd, cfg);

 /* Check if the table column definitions match the contents
 of the config file. */

 if (err == DB_SUCCESS) {

  /* We have a schema file, try and match it with our
  data dictionary. */

  err = cfg.match_schema(trx->mysql_thd);

  /* Update index->page and SYS_INDEXES.PAGE_NO to match the
  B-tree root page numbers in the tablespace. Use the index
  name from the .cfg file to find match. */

  if (err == DB_SUCCESS) {
   cfg.set_root_by_name();
   autoinc = cfg.m_autoinc;
  }

  rw_lock_s_unlock_gen(dict_operation_lock, 0);

  DBUG_EXECUTE_IF("ib_import_set_index_root_failure",
    err = DB_TOO_MANY_CONCURRENT_TRXS;);

 } else if (cfg.m_missing) {
...

So row_import_read_cfg() just reads metadata from cfg file into cfg object, while
row_import::match_schema() just checks the correctness of loaded metadata comparing them with what is currently in internal table object. row_import::set_root_by_name()
sets page and space numbers for the corresponding internal index objects in table->indexes container. To correspond loaded cfg index description with internal index description in table->indexes the index names are used (see row_import::set_root_by_name()).

2) without cfg file:
row_import_for_mysql()
...
 } else if (cfg.m_missing) {

  rw_lock_s_unlock_gen(dict_operation_lock, 0);

  /* We don't have a schema file, we will have to discover
  the index root pages from the .ibd file and skip the schema
  matching step. */

  ut_a(err == DB_FAIL);

  cfg.m_page_size.copy_from(univ_page_size);

  FetchIndexRootPages fetchIndexRootPages(table, trx);

  err = fil_tablespace_iterate(
   table, IO_BUFFER_SIZE(
    cfg.m_page_size.physical(),
    cfg.m_page_size.physical()),
   fetchIndexRootPages);

  if (err == DB_SUCCESS) {

   err = fetchIndexRootPages.build_row_import(&cfg);

   /* Update index->page and SYS_INDEXES.PAGE_NO
   to match the B-tree root page numbers in the
   tablespace. */

   if (err == DB_SUCCESS) {
    err = cfg.set_root_by_heuristic();
   }
  }

  space_flags = fetchIndexRootPages.get_space_flags();

 } else {
...

fetchIndexRootPages gathers index space-pageno pairs from the attached tablespace in
the order of their root pages appearance in the tablespace. The same order will be preserved for cfg object, as fetchIndexRootPages.build_row_import() just fills index descriptors array in cfg object. Then row_import::set_root_by_heuristic() does the same thing as row_import::set_root_by_name() in (1), i.e. sets page and space numbers for the corresponding internal index objects in tables->indexes container.

But the general thing is that row_import::set_root_by_heuristic() does not use index names to map internal index descriptor from table->indexes container to cfg index descriptor, indexes order is used instead.

So, for example, if indexes order during table creation is not the same as indexes root pages order in the attached table space, then the correspondence will be wrong and the indexes in table->indexes container will contain wrong page number.

There are two mtr test cases attached. They are identical as the reason of the error is the same - wrong root page number for the index if the tablespace is imported without cfg file. The difference is in the results itself. The first test finishes with crash, while the second test outputs wrong data when the correspondent index is used.

To make them work just copy them into "mysql-test/suite/innodb/t/".