Percona Server moved to https://jira.percona.com/projects/PS

IMPORT TABLESPACE failed without cfg file

Bug #1702408 reported by Nickolay Ihalainen
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
In Progress
High
Vlad Lesin
5.5
New
Undecided
Unassigned
5.6
New
Undecided
Unassigned
5.7
Won't Fix
High
Vlad Lesin

Bug Description

[ERROR] InnoDB: Trying to access page number 1473773568 in space 28, space name test/testtable, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.

mysqld process exists unexpectedly during ibd file import (created by xtrabackup:
ibbackup_version = 2.4.7
server_version = 5.7.18-14-log
)

#0 fil_report_invalid_page_access (block_offset=1612972032, space_id=28, space_name=0x7fff880332b8 "aaaaa/app_id_mappings", byte_offset=0, len=16384, is_read=true) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/fil/fil0fil.cc:5440
#1 0x0000000001c4cb55 in _fil_io (type=..., sync=true, page_id=..., page_size=..., byte_offset=0, len=16384, buf=0x7fffead94000, message=0x7fffea688f50, trx=0x0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/fil/fil0fil.cc:5625
#2 0x0000000001bebafc in buf_read_page_low (err=0x7ffff12cd48c, sync=true, type=0, mode=132, page_id=..., page_size=..., unzip=false, trx=0x0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/buf/buf0rea.cc:233
#3 0x0000000001bec34a in buf_read_page (page_id=..., page_size=..., trx=0x0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/buf/buf0rea.cc:490
#4 0x0000000001bc21eb in buf_page_get_gen (page_id=..., page_size=..., rw_latch=1, guess=0x0, mode=10, file=0x2321e40 "percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/dict/dict0stats.cc", line=1491, mtr=0x7ffff12cdc00, dirty_with_no_latch=false) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/buf/buf0buf.cc:4301
#5 0x0000000001c2ff57 in dict_stats_analyze_index_below_cur (cur=0x7ffff12ce1c0, n_prefix=4, n_diff=0x7ffff12ce1b8, n_external_pages=0x7ffff12ce1b0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/dict/dict0stats.cc:1491
#6 0x0000000001c3068d in dict_stats_analyze_index_for_n_prefix (index=0x7fff880183e0, n_prefix=4, boundaries=0x7fff88036af8, n_diff_data=0x7fff88036bc8, mtr=0x7ffff12ce360) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/dict/dict0stats.cc:1769
#7 0x0000000001c311b1 in dict_stats_analyze_index (index=0x7fff880183e0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/dict/dict0stats.cc:2154
#8 0x0000000001c31457 in dict_stats_update_persistent (table=0x7fff881b5b90) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/dict/dict0stats.cc:2238
#9 0x0000000001c33110 in dict_stats_update (table=0x7fff881b5b90, stats_upd_option=DICT_STATS_RECALC_PERSISTENT) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/dict/dict0stats.cc:3105
#10 0x000000000193c80c in ha_innobase::discard_or_import_tablespace (this=0x7fff8802d1b0, discard=0 '\000') at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/innobase/handler/ha_innodb.cc:13170
#11 0x0000000000fad6ee in handler::ha_discard_or_import_tablespace (this=0x7fff8802d1b0, discard=0 '\000') at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/handler.cc:5072
#12 0x0000000001647688 in mysql_discard_or_import_tablespace (thd=0x7fff881291d0, table_list=0x7fff88034df0, discard=false) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/sql_table.cc:6316
#13 0x00000000017b89f1 in Sql_cmd_discard_import_tablespace::execute (this=0x7fff88035378, thd=0x7fff881291d0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/sql_alter.cc:390
#14 0x00000000015b73dd in mysql_execute_command (thd=0x7fff881291d0, first_level=true) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/sql_parse.cc:5155
#15 0x00000000015b9644 in mysql_parse (thd=0x7fff881291d0, parser_state=0x7ffff12d0600) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/sql_parse.cc:5929
#16 0x00000000015add4a in dispatch_command (thd=0x7fff881291d0, com_data=0x7ffff12d0df0, command=COM_QUERY) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/sql_parse.cc:1493
#17 0x00000000015acb78 in do_command (thd=0x7fff881291d0) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/sql_parse.cc:1021
#18 0x00000000016e99e6 in handle_connection (arg=0x3b66d90) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/sql/conn_handler/connection_handler_per_thread.cc:312
#19 0x0000000001d603b5 in pfs_spawn_thread (arg=0x3bbbb00) at percona-server-0b6f903a789ea30faa47b3e5c16a9dc50a5390a7k/storage/perfschema/pfs.cc:2188
#20 0x00007ffff7bc06ca in start_thread () from /lib64/libpthread.so.0
#21 0x00007ffff65eff7f in clone () from /lib64/libc.so.6

The problem could be reproduced with both release and debug percona server 5.7.
With persistent statistics disabled import finishes without error, but after enabling persistent statistics back, exits with same error during analyze table.

I_S.*sys* tables shows identical results for both table loaded with .cfg and for table loaded with disabled persistent statistics:
select t.*,c.* from information_schema.innodb_sys_tables t inner join information_schema.INNODB_SYS_COLUMNS c on (c.table_id = t.table_id) where t.name in ('t1/tablename','t2/tablename') order by c.pos;

At the same time load happens without any errors if cfg file is provided.
I'm not able to crash database with analyze table if it was loaded with cfg file.

The index causing crash is always the same, page number varies from run to run.

The issue always reproduced with specific ibd file, but disappears after ibd file rebuild.

Nickolay Ihalainen (ihanick)
Changed in percona-server:
status: New → Confirmed
Revision history for this message
Nickolay Ihalainen (ihanick) wrote :

check i196447 for ibd and table structure file required for reproducing.

Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :

Bug is repeatable with upstream

Sveta Smirnova (svetasmirnova)
tags: added: upstream
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Is there an upstream bug report?

Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :

Not, because issue is repeatable only with sensitive data, attached to our ticket, so we do not have test case which can be shared with Oracle.

Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

Briefly. When we create table it's dictionary contains the description of indexes in the certain order. In the case if we import tablespace for the table without cfg file(which contains metadata for indexes as well), there can be the case when the order of indexes in innodb dictionary (in .frm file as well) is not the same as the order of root pages for the indexes in tablespace file what cause the error during tablespace import or when the indexes are used.

The detailed description is here https://percona.zendesk.com/attachments/token/EzERiNDmk8a1gJjtQMhPfA7Dv/?name=descr.txt .

Revision history for this message
Vlad Lesin (vlad-lesin) wrote :
Download full text (3.8 KiB)

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...

Read more...

Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

3) How index descriptors are sorted during table creation?
Their initial order is the same as in the table definition,
see mysql_execute_command():

  case SQLCOM_CREATE_TABLE:
  {
...
    Alter_info alter_info(lex->alter_info, thd->mem_root);
...
  }

then the index descriptors are sorted in mysql_prepare_create_table():

...
  if (auto_increment > 0)
  {
    my_message(ER_WRONG_AUTO_KEY, ER(ER_WRONG_AUTO_KEY), MYF(0));
    DBUG_RETURN(TRUE);
  }
  /* Sort keys in optimized order */
  my_qsort((uchar*) *key_info_buffer, *key_count, sizeof(KEY),
    (qsort_cmp) sort_keys);
  create_info->null_bits= null_fields;

  /* Check fields. */
  it.rewind();
...

If we look into sork_keys() function comments we will see the following:

   Sort keys according to the following properties, in decreasing order of
   importance:
   - PRIMARY KEY
   - UNIQUE with all columns NOT NULL
   - UNIQUE without partial segments
   - UNIQUE
   - without fulltext columns
   - without virtual generated columns

So in the case if the tables were just created and their indexes were not altered, then heuristic, based on index orders and described in (2), works fine. But in the case if we, for example, alter table and add new indexes, for example, UNIQUE indexes after non-unique indexes, then there will be different order of index descriptors in table definition and index root pages in the attached tablespace, the heuristic will not work, and there will be error on tablespace import or indexes usage.

Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

https://bugs.mysql.com/bug.php?id=88418

Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

I don't have any ideas how to fix it. If we does not have metadata, i.e. index names in correspondent order, then it looks like there is no way to map dictionary indexes on index root pages in innodb tablespace.

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1104

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Bug attachments

Remote bug watches

Bug watches keep track of this bug in other bug trackers.