[MDEV-13564] TRUNCATE TABLE and undo tablespace truncation are not compatible with Mariabackup - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.2
Fix Version/s: 10.3.10, 10.4.0, 10.2.19
Component/s: Backup, Storage Engine - InnoDB
Labels:
- backup
- ddl
- performance
- recovery

Description

MariaDB 10.2.2 imported MySQL 5.7.9, which introduced separate log files, for server startup to determine if any tables or undo tablespace need "truncate fixup".

There is no logic in Mariabackup to deal with this.

A cleaner solution would be to remove the separate log files and to make the InnoDB redo log self-contained with respect to the truncate operations. This would likely require writing a new redo log record type MLOG_FILE_CREATE that would cause the file to be initialized from the scratch, followed by some page-level redo log records that would initialize the page contents.
This would also remove the need for a redo log checkpoint during the truncate operations.

~~MDEV-13563~~ proposes a Mariabackup option that could be used to prevent TRUNCATE TABLE from occurring during backups. It would not prevent undo tablespace truncation from happening.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

truncate.patch
79 kB
2018-08-02 13:30

Issue Links

blocks

MDEV-14481 Execute InnoDB crash recovery in the background

Closed

causes

MDEV-17816 InnoDB: Failing assertion: trx->dict_operation_lock_mode == RW_X_LATCH upon TRUNCATE TABLE after converting to REDUNDANT

Closed

MDEV-17849 Undo tablespace truncation recovery fails to shrink file

Closed

MDEV-17885 TRUNCATE on temporary table causes ER_GET_ERRNO and "Could not remove temporary table" in the log

Closed

MDEV-18836 Race conditions in TRUNCATE TABLE

Closed

MDEV-19449 1030: Got error 168 "Unknown (generic) error from engine" for valid TRUNCATE (temporary) TABLE

Closed

MDEV-21496 Downgrade from current 10.2 to 10.2.19: InnoDB: Failing assertion: trx->dict_operation_lock_mode == RW_X_LATCH

Closed

MDEV-23705 Assertion `table->data_dir_path || !space' failed in row_drop_table_for_mysql on TRUNCATE after DISCARD TABLESPACE

Closed

MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or ER_CRASHED_ON_USAGE after ALTER on table with foreign key

Closed

MDEV-26450 Corruption due to innodb_undo_log_truncate

Closed

is blocked by

MDEV-14717 RENAME TABLE in InnoDB is not crash-safe

Closed

is duplicated by

MDEV-9459 Truncate table causes innodb stalls

Closed

relates to

MDEV-9459 Truncate table causes innodb stalls

Closed

MDEV-14585 Automatically remove #sql- tables in innodb dictionary during recovery

Closed

MDEV-16557 Remove INNOBASE_SHARE::idx_trans_tbl

Closed

MDEV-17049 Enable --suite=innodb_undo on buildbot

Closed

MDEV-17138 Reduce redo log volume for undo tablespace initialization

Closed

MDEV-17158 TRUNCATE is not atomic after MDEV-13564

Closed

MDEV-17780 innodb.truncate_recover crashes in recovery due to out-of-bounds page read

Closed

MDEV-17794 Do not assign persistent ID for temporary tables

Closed

MDEV-17831 Assertion `supports_instant()' failed in dict_table_t::prepare_instant upon ADD COLUMN on table with KEY_BLOCK_SIZE

Closed

MDEV-18739 crash (long semaphore wait)

Closed

MDEV-19769 Mariabackup should write warning during backup if server does not have innodb_safe_truncate=ON set

Open

MDEV-22733 XA PREPARE breaks MDL in pseudo_slave_mode=1

Stalled

MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or ER_CRASHED_ON_USAGE after ALTER on table with foreign key

Closed

MDEV-25051 Race condition between persistent statistics and RENAME TABLE or TRUNCATE

Closed

MDEV-25710 Dead code os_file_opendir() in the server

Closed

MDEV-33112 innodb_undo_log_truncate=ON is blocking page writes

Closed

MDEV-9459 Truncate table causes innodb stalls

Closed

MDEV-13563 lock DDL for mariabackup in 10.2+

Closed

MDEV-14481 Execute InnoDB crash recovery in the background

Closed

MDEV-14545 Backup fails due to MLOG_INDEX_LOAD record

Closed

MDEV-15154 WSREP: BF lock wait long after a TRUNCATE TABLE

Closed

MDEV-15522 Change galera suite MTR tests to use mariabackup instead of xtrabackup

Closed

MDEV-16306 TRUNCATE waits for metadata lock on the tables when a SELECT is executing on it

Open

MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys

Closed

MDEV-17043 Purge of indexed virtual columns may cause hang on table-rebuilding DDL

Closed

MDEV-17304 Replace use of XtraBackup with MariaDB Backup

Closed

MDEV-18654 Failing assertion: sym_node->table != NULL in buildbot with innodb_fts.sync_ddl and outside

Closed

MDEV-18960 Assertion `!omits_virtual_cols(*form->s)' failed after upgrade from 10.0/10.1 with PERSISTENT generated column

Closed

(5 causes, 1 is blocked by, 1 is duplicated by, 28 relates to)

Activity

Ascending order - Click to sort in descending order

View 10 older comments

Marko Mäkelä added a comment - 2018-09-07 19:24

Even though I prepared a fix for 10.2, I decided not to push it yet, because I fear that ~~MDEV-17158~~ could occasionally cause loss of data when InnoDB is killed during a table-rebuilding operation, such as TRUNCATE or ALTER or OPTIMIZE.

Marko Mäkelä added a comment - 2018-09-07 19:24 Even though I prepared a fix for 10.2, I decided not to push it yet, because I fear that MDEV-17158 could occasionally cause loss of data when InnoDB is killed during a table-rebuilding operation, such as TRUNCATE or ALTER or OPTIMIZE .

Marko Mäkelä added a comment - 2018-09-13 14:38

We decided not to push this to 10.2.18, because it backports a large amount of code to 10.2, which could be risky right before a 10.2 release. So, the backport could be merged to 10.2.19 at the earliest.

Note that the crash-downgrade prevention for 10.2 will prevent Percona Xtrabackup from working with MariaDB Server 10.2 with the backport included. (Xtrabackup already does not work with MariaDB Server 10.3 or later.)

Marko Mäkelä added a comment - 2018-09-13 14:38 We decided not to push this to 10.2.18, because it backports a large amount of code to 10.2, which could be risky right before a 10.2 release. So, the backport could be merged to 10.2.19 at the earliest. Note that the crash-downgrade prevention for 10.2 will prevent Percona Xtrabackup from working with MariaDB Server 10.2 with the backport included. (Xtrabackup already does not work with MariaDB Server 10.3 or later.)

Marko Mäkelä added a comment - 2018-10-10 16:26 - edited

bb-10.2-marko
MariaDB 10.2.19 will support the backup-unsafe TRUNCATE TABLE by default, to retain perceived compatibility with xtrabackup.
Undo tablespace truncation will use the redo log, but older versions of the server or older backup tools will fail to shrink the undo tablespace files on recovery.
The backup-safe TRUNCATE can be enabled in MariaDB 10.2 by setting the start-up parameter loose_innodb_unsafe_truncate=OFF. This parameter will not be available in 10.3 or later releases.

Edit: the option was renamed to innodb_safe_truncate.

Marko Mäkelä added a comment - 2018-10-10 16:26 - edited bb-10.2-marko MariaDB 10.2.19 will support the backup-unsafe TRUNCATE TABLE by default, to retain perceived compatibility with xtrabackup. Undo tablespace truncation will use the redo log, but older versions of the server or older backup tools will fail to shrink the undo tablespace files on recovery. The backup-safe TRUNCATE can be enabled in MariaDB 10.2 by setting the start-up parameter loose_innodb_unsafe_truncate=OFF . This parameter will not be available in 10.3 or later releases. Edit: the option was renamed to innodb_safe_truncate .

Marko Mäkelä added a comment - 2018-10-11 05:12

Buildbot seems to be OK with the change. I conducted a manual test of crash-downgrading to mariadb-10.2.18:

./mtr --manual-gdb innodb.truncate_crash

./mtr --manual-gdb innodb_zip.wl6501_crash_3

Once the server is killed by the test, switch to the 10.2.18 executable (using the file command in GDB) and restart (run in GDB).

With the first test (which uses the backup-safe mechanism), the InnoDB in MariaDB Server 10.2.18 would refuse to start up, after emitting the following message to the error log:

2018-10-11 7:58:30 140737330922240 [ERROR] InnoDB: Downgrade after a crash is not supported. The redo log was created with MariaDB 10.2.19.

With the second test, which uses MySQL 5.7’s backup-unsafe but crash-safe TRUNCATE TABLE, the MariaDB 10.2.18 server would parse and apply the old-format redo log and the ib_*_*_trunc.log just fine. That test is restarting the server several times. I ran the test twice; first, switching to 10.2.18 on the first restart, and second, switching to 10.2.18 on the second restart and on subsequent restarts, switching between 10.2.18 and this 10.2.19 revision.

Marko Mäkelä added a comment - 2018-10-11 05:12 Buildbot seems to be OK with the change. I conducted a manual test of crash-downgrading to mariadb-10.2.18: ./mtr --manual-gdb innodb.truncate_crash ./mtr --manual-gdb innodb_zip.wl6501_crash_3 Once the server is killed by the test, switch to the 10.2.18 executable (using the file command in GDB) and restart ( run in GDB). With the first test (which uses the backup-safe mechanism), the InnoDB in MariaDB Server 10.2.18 would refuse to start up, after emitting the following message to the error log: 2018-10-11 7:58:30 140737330922240 [ERROR] InnoDB: Downgrade after a crash is not supported. The redo log was created with MariaDB 10.2.19. With the second test, which uses MySQL 5.7’s backup-unsafe but crash-safe TRUNCATE TABLE , the MariaDB 10.2.18 server would parse and apply the old-format redo log and the ib_*_*_trunc.log just fine. That test is restarting the server several times. I ran the test twice; first, switching to 10.2.18 on the first restart, and second, switching to 10.2.18 on the second restart and on subsequent restarts, switching between 10.2.18 and this 10.2.19 revision.

Marko Mäkelä added a comment - 2018-10-17 14:37

The default value for the 10.2-specific parameter will be innodb_safe_truncate=ON.

Marko Mäkelä added a comment - 2018-10-17 14:37 The default value for the 10.2-specific parameter will be innodb_safe_truncate=ON .

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2017-08-17 14:12

Updated:: 2023-12-22 08:38

Resolved:: 2018-09-07 19:24

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.