[PXB-2254] Redesign --lock-ddl-per-table - Percona JIRA

Details

Type: Improvement
Status: Done
Priority: Medium
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 2.4.21, 8.0.22-15
Component/s: None
Labels:
None

Description

The current implementation of --lock-ddl-per-table works as follow:

Creates a dedicated mysql connection for MDL and start a transaction.
xtrabackup start to scan directories (databases) looking for .ibd files
For each .ibd file it tries to transalate the space id recorded on the file header to a table (or tables in case of shared tablespace)
If it finds a table associate with the .ibd (or tables) it executes a SELECT * FROM table LIMIT 1 using the dedicated connection for MDL
From this point forward, the table is locked and new DDL will be blocked until the MDL connection commits.
With this, it has been assumed that if a MLOG_LOAD_INDEX event has been generated (https://dev.mysql.com/worklog/task/?id=7277) it's safe to continue since we have taken MDL on the table we are copying.

This design has a few flaws:

1. FTS have their own tablespace, which is been copied without MDL protection:

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|     1169 | test/FTS_000000000000002e_0000000000000508_INDEX_1 |  1157 |
|     1170 | test/FTS_000000000000002e_0000000000000508_INDEX_2 |  1158 |
|     1171 | test/FTS_000000000000002e_0000000000000508_INDEX_3 |  1159 |
|     1172 | test/FTS_000000000000002e_0000000000000508_INDEX_4 |  1160 |
|     1173 | test/FTS_000000000000002e_0000000000000508_INDEX_5 |  1161 |
|     1174 | test/FTS_000000000000002e_0000000000000508_INDEX_6 |  1162 |
|     1175 | test/FTS_000000000000002e_BEING_DELETED            |  1163 |
|     1176 | test/FTS_000000000000002e_BEING_DELETED_CACHE      |  1164 |
|     1177 | test/FTS_000000000000002e_CONFIG                   |  1165 |
|     1178 | test/FTS_000000000000002e_DELETED                  |  1166 |
|     1179 | test/FTS_000000000000002e_DELETED_CACHE            |  1167 |
+----------+----------------------------------------------------+-------+
11 rows in set (0.01 sec)

https://github.com/percona/percona-xtrabackup/blob/percona-xtrabackup-2.4.20/storage/innobase/xtrabackup/src/backup_mysql.cc#L2191 - This will try to execute a SELECT on FTS_000000000000002e_0000000000000508_INDEX_1 which is not something we can do.
We should translate 000000000000002e to the real table and acquire MDL on the base table.

200728 04:01:16 Locking MDL for `test`.`FTS_0000000000000032_DELETED_CACHE`
Error: failed to execute query 'SELECT * FROM `test`.`FTS_0000000000000032_DELETED_CACHE` LIMIT 1': 1146 (42S02) Table 'test.FTS_0000000000000032_DELETED_CACHE' doesn't exist
200728 04:01:16 [01] Copying ./test/FTS_0000000000000032_DELETED_CACHE.ibd to /home/mchawla/dbbackup_28_07_2020/full/test/FTS_0000000000000032_DELETED_CACHE.ibd
200728 04:01:16 [01]        ...done

2. The first time we create an FTS, it needs to rewrite the table, creating a temporary table. The FTS index files are linked to the intermediate temporary table. In that case, we cannot acquire MDL on the table temporary table by executing SELECT:

session 1> CREATE FULLTEXT INDEX full_index on joinit2 (s);
session 2> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE table_id >= 1319;
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|     1321 | test/#sql-ib1320-2000853746                        |  1309 |
|     1322 | test/FTS_0000000000000529_00000000000005b6_INDEX_1 |  1310 |
|     1323 | test/FTS_0000000000000529_00000000000005b6_INDEX_2 |  1311 |
|     1324 | test/FTS_0000000000000529_00000000000005b6_INDEX_3 |  1312 |
|     1325 | test/FTS_0000000000000529_00000000000005b6_INDEX_4 |  1313 |
|     1326 | test/FTS_0000000000000529_00000000000005b6_INDEX_5 |  1314 |
|     1327 | test/FTS_0000000000000529_00000000000005b6_INDEX_6 |  1315 |
|     1328 | test/FTS_0000000000000529_BEING_DELETED            |  1316 |
|     1329 | test/FTS_0000000000000529_BEING_DELETED_CACHE      |  1317 |
|     1330 | test/FTS_0000000000000529_CONFIG                   |  1318 |
|     1331 | test/FTS_0000000000000529_DELETED                  |  1319 |
|     1332 | test/FTS_0000000000000529_DELETED_CACHE            |  1320 |
|     1320 | test/joinit2                                       |  1308 |
+----------+----------------------------------------------------+-------+
FTS_0000000000000529 translates to table_id 1321

3. The first time we create an FTS, we write {{MLOG_INDEX_LOAD (}}which is skipped from the redo log):

Thread 39 "mysqld" hit Breakpoint 1, row_merge_write_redo (index=0x55faa571af68)
    at /work/mysql/src/storage/innobase/row/row0merge.cc:4270
4270	{
(gdb) bt
#0  row_merge_write_redo (index=0x55faa571af68) at /work/mysql/src/storage/innobase/row/row0merge.cc:4270
#1  0x000055faa31c91f0 in row_merge_build_indexes (trx=0x7f5404d88d20, old_table=0x7f52fb94d6d0,
    new_table=0x7f52fb94b6f0, online=false, indexes=0x7f52f8067e00, key_numbers=0x7f52f8067e18, n_indexes=3,
    table=0x7f52fb959360, add_cols=0x0, col_map=0x7f52f8067e98, add_autoinc=18446744073709551615, sequence=...,
    skip_pk_sort=true, stage=0x7f5298648e48, add_v=0x0, eval_table=0x7f52fb959360)
    at /work/mysql/src/storage/innobase/row/row0merge.cc:4685
#2  0x000055faa30b9cba in ha_innobase::inplace_alter_table (this=0x7f52f8004960, altered_table=0x7f52fb959360,
    ha_alter_info=0x7f53dc560b90) at /work/mysql/src/storage/innobase/handler/handler0alter.cc:6431
#3  0x000055faa2d4b48d in handler::ha_inplace_alter_table (this=0x7f52f8004960, altered_table=0x7f52fb959360,
    ha_alter_info=0x7f53dc560b90) at /work/mysql/src/sql/handler.h:3450
#4  0x000055faa2d42645 in mysql_inplace_alter_table (thd=0x7f52f8000e10, table_list=0x7f52f80089e8,
    table=0x7f52fb950100, altered_table=0x7f52fb959360, ha_alter_info=0x7f53dc560b90,
    inplace_supported=HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE, target_mdl_request=0x7f53dc560db0,
    alter_ctx=0x7f53dc561500) at /work/mysql/src/sql/sql_table.cc:7588
#5  0x000055faa2d47b23 in mysql_alter_table (thd=0x7f52f8000e10, new_db=0x7f52f8008f70 "test",
    new_name=0x7f52f80089b0 "joinit2", create_info=0x7f53dc562690, table_list=0x7f52f80089e8,
    alter_info=0x7f53dc5625e0) at /work/mysql/src/sql/sql_table.cc:9807
#6  0x000055faa2ca5442 in mysql_execute_command (thd=0x7f52f8000e10, first_level=true)
    at /work/mysql/src/sql/sql_parse.cc:3347
#7  0x000055faa2cabdc5 in mysql_parse (thd=0x7f52f8000e10, parser_state=0x7f53dc563530)
    at /work/mysql/src/sql/sql_parse.cc:5584
#8  0x000055faa2ca0c7b in dispatch_command (thd=0x7f52f8000e10, com_data=0x7f53dc563de0, command=COM_QUERY)
    at /work/mysql/src/sql/sql_parse.cc:1491
#9  0x000055faa2c9fb0d in do_command (thd=0x7f52f8000e10) at /work/mysql/src/sql/sql_parse.cc:1032
#10 0x000055faa2de59d4 in handle_connection (arg=0x55faa60f3ae0)
    at /work/mysql/src/sql/conn_handler/connection_handler_per_thread.cc:313
#11 0x000055faa34e2e38 in pfs_spawn_thread (arg=0x55faa60f3b30) at /work/mysql/src/storage/perfschema/pfs.cc:2197
#12 0x00007f540e40c609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#13 0x00007f540dfe8103 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95(gdb) p do_command::thd->m_query_string
$1 = {str = 0x7f52f8008010 "CREATE FULLTEXT INDEX full_index on joinit2 (s)", length = 47}

4. Tablespace ID's for FTS will change if you CREATE / DROP / CREATE, in case we implement approach 1, we will still be copying the FTS files under no MDL protection:

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES;
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|       42 | test/FTS_0000000000000029_000000000000002b_INDEX_1 |    25 |
|       43 | test/FTS_0000000000000029_000000000000002b_INDEX_2 |    26 |
|       44 | test/FTS_0000000000000029_000000000000002b_INDEX_3 |    27 |
|       45 | test/FTS_0000000000000029_000000000000002b_INDEX_4 |    28 |
|       46 | test/FTS_0000000000000029_000000000000002b_INDEX_5 |    29 |
|       47 | test/FTS_0000000000000029_000000000000002b_INDEX_6 |    30 |
|       48 | test/FTS_0000000000000029_BEING_DELETED            |    31 |
|       49 | test/FTS_0000000000000029_BEING_DELETED_CACHE      |    32 |
|       50 | test/FTS_0000000000000029_CONFIG                   |    33 |
|       51 | test/FTS_0000000000000029_DELETED                  |    34 |
|       52 | test/FTS_0000000000000029_DELETED_CACHE            |    35 |
|       41 | test/tb1                                           |    24 |
+----------+----------------------------------------------------+-------+
. . .

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES;
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|       53 | test/FTS_0000000000000029_0000000000000038_INDEX_1 |    36 |
|       54 | test/FTS_0000000000000029_0000000000000038_INDEX_2 |    37 |
|       55 | test/FTS_0000000000000029_0000000000000038_INDEX_3 |    38 |
|       56 | test/FTS_0000000000000029_0000000000000038_INDEX_4 |    39 |
|       57 | test/FTS_0000000000000029_0000000000000038_INDEX_5 |    40 |
|       58 | test/FTS_0000000000000029_0000000000000038_INDEX_6 |    41 |
|       59 | test/FTS_0000000000000029_BEING_DELETED            |    42 |
|       60 | test/FTS_0000000000000029_BEING_DELETED_CACHE      |    43 |
|       61 | test/FTS_0000000000000029_CONFIG                   |    44 |
|       62 | test/FTS_0000000000000029_DELETED                  |    45 |
|       63 | test/FTS_0000000000000029_DELETED_CACHE            |    46 |
|       41 | test/tb1                                           |    24 |
+----------+----------------------------------------------------+-------+

While test/tb1 still has table_id 31 and space 24, FTS tables have changed, for example, _INDEX_1 changed from 42 / 25 to 53 / 36. So PXB will try to lock whatever table is under space 25 but that query will return 0 rows, but FTS_0000000000000029_0000000000000038_INDEX_1.ibd exists again, and PXB will be copying it without MDL protection.

5. If a table is created after xtrabackup has gathered the list of .ibd s to copy and before the backup has finished, this particular table will be part of the backup as it will be contained within the redo log. However, if a bulk index load operations have been performed ( MLOG_INDEX_LOAD has been written to the redo log), this change to the data file will not be tracked and will be missed from the backup.

Attachments

Issue Links

causes

PXB-2272 Modify regexp to consider all #sql as temporary tables

Done

is duplicated by

PXB-2097 PXB crashes in incremental backup prepare when row format is changed or full text index is created/dropped

Done

relates to

PXB-2239 Partitioned table is not restored correctly when partitions are changed during backup

Done

PXB-2240 Database crashes after restore when full text index is created/dropped during backup

Done

PXB-793 Fix syntax error when executing --lock-ddl-per-table queries

Done

PXB-2092 PXB crashes in full backup during CREATE/DROP index

Done

PXB-2097 PXB crashes in incremental backup prepare when row format is changed or full text index is created/dropped

Done

PXB-2216 Verify encryption version when opening tables to avoid changing encryption version

Done

PXB-2257 Modify --lock-ddl-per-table to properly close database connection

Done

PXC-2365 DDL during SST blocks Donor

Done

PXB-953 Improve stdout for the end of usage of --lock-ddl-per-table

Done

PXB-2258 document --lock-ddl and --lock-ddl-per-table

Done

(7 relates to)

Redesign --lock-ddl-per-table

Details

Description

Attachments

Issue Links

Activity

People

Dates

Time Tracking

Smart Checklist