Non-Centmin Mod MySQL 8.0.29 Data Corruption Bug Heads up

eva2000 · Aug 2, 2022

PaulB on XF forums posted a heads up for a potential MySQL 8.0.29 data corruption bug PSA for any sites on MySQL 8.0.29: Data corruption, incl. backups. Centmin Mod uses MariaDB 10.x so isn't impacted by this bug but I'd imagine some users might be running MySQL servers using MySQL 8.0 servers on non-Centmin Mod servers.

A data corruption issue is likely to arise on MySQL servers running 8.0.29 due to a regression. The corruption happens when the following occur in succession:

Columns are added to or dropped from a table while running MySQL 8.0.29.

That table has row modifications (INSERT, DELETE, etc.).

The row modifications are written to the redo log.

A crash recovery is performed.

Click to expand...

details Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns - Percona Database Performance Blog

Oracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it as part of the –prepare step to bring the backup to a consistent state.
Click to expand...

eva2000 · Aug 2, 2022

Though it seems MariaDB 10.3.2+ and higher also support ALTER tables with ALGORITHM=INSTANT too with MariaDB 10.4+ like 8.0.29 - allowing adding columns in any position in a database table InnoDB Online DDL Operations with the INSTANT Alter Algorithm.

In MariaDB 10.3.2 and later, InnoDB supports adding columns to a table with ALGORITHM set to INSTANT if the new column is the last column in the table. See MDEV-11369 for more information. If the table has a hidden FTS_DOC_ID column is present, then this is not supported.

In MariaDB 10.4 and later, InnoDB supports adding columns to a table with ALGORITHM set to INSTANT, regardless of where in the column list the new column is added.

When this operation is performed with ALGORITHM set to INSTANT, the tablespace file will have a non-canonical storage format. See Non-canonical Storage Format Caused by Some Operations for more information.

With the exception of adding an auto-increment column, this operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to NONE. When this strategy is used, all concurrent DML is permitted.
Click to expand...

MariaDB also highlights known bugs InnoDB Online DDL Operations with the INSTANT Alter Algorithm
There are some known bugs that could lead to issues when an InnoDB DDL operation is performed using the INSTANT algorithm. This algorithm will usually be chosen by default if the operation supports the algorithm.

The effect of many of these bugs is that the table seems to forget that its tablespace file is in the non-canonical storage format.

If you are concerned that a table may be affected by one of these bugs, then your best option would be to normalize the table structure. This can be done by rebuilding the table. For example:
Code (Text):
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab FORCE;
Query OK, 0 rows affected (0.008 sec)
If you are concerned about these bugs, and you want to perform an operation that supports the INSTANT algorithm, but you want to avoid using that algorithm, then you can set the algorithm to INPLACE and add the FORCE keyword to the ALTER TABLE statement:
Code (Text):
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD COLUMN c varchar(50), FORCE;
Click to expand...
InnoDB Online DDL Operations with the INSTANT Alter Algorithm

Some operations cause a table's tablespace file to use a non-canonical storage format when the INSTANT algorithm is used. The affected operations include:

Adding a column.

Dropping a column.

Reordering columns.

Click to expand...

Default alter_algorithm in MariaDB is INPLACE Server System Variables but seems that isn't how the variable works ALTER TABLE

From
InnoDB Online DDL Overview

The set of alter algorithms can be considered as a hierarchy. The hierarchy is ranked in the following order, with least efficient algorithm at the top, and most efficient algorithm at the bottom:

COPY

INPLACE

NOCOPY

INSTANT

When a user specifies an alter algorithm for a DDL operation, MariaDB does not necessarily use that specific algorithm for the operation. It interprets the choice in the following way:

If the user specifies COPY, then InnoDB uses the COPY algorithm.

If the user specifies any other algorithm, then InnoDB interprets that choice as the least efficient algorithm that the user is willing to accept. This means that if the user specifies INPLACE, then InnoDB will use the most efficient algorithm supported by the specific operation from the set (INPLACE, NOCOPY, INSTANT). Likewise, if the user specifies NOCOPY, then InnoDB will use the most efficient algorithm supported by the specific operation from the set (NOCOPY, INSTANT).

There is also a special value that can be specified:

If the user specifies DEFAULT, then InnoDB uses its default choice for the operation. The default choice is to use the most efficient algorithm supported by the operation. The default choice will also be used if no algorithm is specified. Therefore, if you want InnoDB to use the most efficient algorithm supported by an operation, then you usually do not have to explicitly specify any algorithm at all.

Click to expand...

Log in / Register

Forums

Welcome to Centmin Mod Community

Non-Centmin Mod MySQL 8.0.29 Data Corruption Bug Heads up

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Welcome to Centmin Mod Community

Non-Centmin Mod MySQL 8.0.29 Data Corruption Bug Heads up

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

.