PSA for any sites on MySQL 8.0.29: Data corruption, incl. backups

[PaulB]

This isn't directly a XenForo issue, but it will affect any XenForo sites using MySQL 8.0.29. As far as I'm aware, MariaDB is not affected. Only 8.0.29 is affected; older and newer versions are in the clear. MySQL 8.0.29 was released 2022-04-26, so if you haven't updated since then, you're probably not affected. 8.0.30 was released on 2022-07-26 and fixes the issue, although the process of upgrading from 8.0.29 to 8.0.30 can potentially cause irrecoverable data loss if the redo logs aren't empty during the upgrade.

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:

1. Columns are added to or dropped from a table while running MySQL 8.0.29.
2. That table has row modifications (INSERT, DELETE, etc.).
3. The row modifications are written to the redo log.
4. A crash recovery is performed.

Crash recoveries are often performed when restoring from backups taken at the filesystem level, e.g. when restoring cloud-based snapshots using tools such as AWS RDS. MySQL 8.0.29 writes incorrect data to the redo log that causes permanent data corruption if the redo log is ever played back. This means that any snapshots taken on 8.0.29 should be considered broken unless you can confirm that the redo log is empty.

If you're already running 8.0.29, you'll need to review the limited public info about the issue and determine the correct action to avoid corruption. Simply upgrading to 8.0.30 as you normally would may still leave you with problems, especially 8.0.29 isn't shut down cleanly during the upgrade process.

The following query will identify any affected tables:

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

If that query returns any results on 8.0.29, you should assume that your redo logs are corrupt and will cause irreversible data corruption if replayed during crash recovery. You may be able to resolve the issue by using OPTIMIZE TABLE on the affected tables, but you'll need to fully assess your infrastructure and determine whether that is the correction action, as it's not without risks in this scenario.

 

[eva2000]

Wow, thanks for the heads up. Sounds like a complete pain to deal with if using the affected version. These days I only use MariaDB 10.x but still dabble in 8.0 for some.

 

[eva2000]

As far as I'm aware, MariaDB is not affected.

Interesting as MariaDB also supports ALGORITHM=INSTANT since 10.3.2+ apparently https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-instant-alter-algorithm/

 

[PaulB]

Interesting as MariaDB also supports ALGORITHM=INSTANT since 10.3.2+ apparently https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-instant-alter-algorithm/

The bugs at the bottom of this page point out some discrepancies between MySQL’s implementation and MariaDB’s implementation. MariaDB also appears to have had their fair share of corruption bugs, but they’ve already been patched because they introduced INSTANT support for most DDL operations prior to MySQL, so they’ve had more time to catch and fix bugs.

Here’s a notable difference for anyone trying to work around potential INSTANT bugs in either DBMS. MySQL has the following to say about the ALGORITHM parameter:

Specifying an ALGORITHM clause requires the operation to use the specified algorithm for clauses and storage engines that support it, or fail with an error otherwise.

However, a comment in a MariaDB bug report points out that the behavior there is different, and the docs confirm that that:

The ALGORITHM clause can be used to specify the least efficient algorithm that the user is willing to accept. It is supported by the ALTER TABLE and CREATE INDEX statements.

This means the syntax for avoiding an INSTANT operation differs between MariaDB and MySQL. In MariaDB, the only way to avoid INSTANT appears to be to force a table re-build, which is a bit unintuitive, but it should have the same result; you’ll just need to add FORCE to the ALTER statement, effectively limiting the algorithm options to INPLACE and COPY.

 

[Baby Community]

Wow, thanks for the heads up. Sounds like a complete pain to deal with if using the affected version. These days I only use MariaDB 10.x but still dabble in 8.0 for some.

Mariadb smells like roses...





I'm going to migrate mysql server to the latest version of mariadb 10 very soon.

 

[PaulB]

I'm going to migrate mysql server to the latest version of mariadb 10 very soon.

Just make sure you make proper backups both before and after you upgrade. And remember: a backup that you haven't tested isn't a backup.

 

[Baby Community]

Just make sure you make proper backups both before and after you upgrade. And remember: a backup that you haven't tested isn't a backup.

I have backed up many times. I changed server. I have restored the backup I have taken many times. Thanks for the warning though. On my previous server, I did the mariadb phb and server setup myself.

 

[eva2000]

However, a comment in a MariaDB bug report points out that the behavior there is different, and the docs confirm that that:

Indeed interesting

In MariaDB 10.3 and later, the above operation would actually use the INSTANT algorithm, because the ADD COLUMN operation supports the INSTANT algorithm, and the INSTANT algorithm is more efficient than the INPLACE algorithm.

and https://mariadb.com/kb/en/innodb-online-ddl-overview/#specifying-an-alter-algorithm

 

[Baby Community]

Indeed interesting

and https://mariadb.com/kb/en/innodb-online-ddl-overview/#specifying-an-alter-algorithm

I had installed mariadb 10.3 on my previous server, then the site was xenforo2.2. I didn't see a problem

 

[Baby Community]

Anyone using mariadb 10.3 or mariadb 10.4 with XenForo 2.2.10 Patch 1? Is there any problem?
should i migrate the server to mariadb10.4

 

[MattW]

Anyone using mariadb 10.3 or mariadb 10.4 with XenForo 2.2.10 Patch 1? Is there any problem?
should i migrate the server to mariadb10.4

Using it fine here with 10.6

 

[Onlyme]

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

I had 2 result from this, xf user & usergroup tables. Was already on mysql 8.0.30 tho.

OPTIMIZE TABLE seems to have done the trick.

Why doesn't mysqlcheck find any issues?

 

[Baby Community]

now i upgraded mysql to mariadb10.3.. I already tested it on my previous server. It was very fast and very good… nights for updates are always my preference.

 

[PaulB]

Why doesn't mysqlcheck find any issues?

The name is a bit of misnomer with InnoDB. You can use it to resolve this issue, but you have to find the affected tables first yourself and pass them to mysqlcheck -o--e.g., mysqlcheck -o xenforo table_1 table_2 table_3. All this does is run OPTIMIZE TABLE, though.