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.
Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column feature in the upcoming Percona Server for MySQL 8.0.29 (check PS-8291 / PS-8292 / PS-8303 for more details) we also raised and provided patches for those issues as a contribution to Community MySQL (see 107613 / 107611 / 107854 for details). Percona XtraBackup 8.0.29 can take backups of Percona Server for MySQL 8.0.29 with tables that have INSTANT ADD/DROP COLUMNS. However, the current version of Community MySQL 8.0.29 still has this flaw, making it unsafe to take backups.
It is impossible for XtraBackup to deal with the corrupted redo log generated by Community MySQL 8.0.29 and, for this reason, XtraBackup 8.0.29 version will not take backups if it detects tables with INSTANT ADD/DROP columns and will create an error with a list of the affected tables and provide instructions to convert them to regular tables.
Please avoid ALTER ADD/DROP COLUMN without an explicit ALGORITHM=INPLACE. The default ALGORITHM is INSTANT, so ALTER TABLE without the ALGORITHM keyword uses the newly added INSTANT algorithm. For example:
Working:
1 | ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INPLACE/COPY; |
Not Working:
1 2 3 | ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INSTANT; ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=DEFAULT; ALTER TABLE tb1 ADD/DROP COLUMN [...]; |
If you already have such tables (see below on how to find such tables), users are advised to run OPTIMIZE TABLE against these tables before taking backups.
Find all tables with INSTANT ADD/DROP COLUMNS:
1 2 3 4 5 6 7 8 9 | mysql> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0; +---------+ | NAME | +---------+ | test/t1 | | test/t2 | | test/t3 | +---------+ 3 rows in set (0.02 sec) |
If this query shows an empty result set, you are all good. Percona XtraBackup will take backups of your MySQL 8.0.29 servers. If not, please run OPTIMIZE TABLE on the list of tables before taking a backup.
Percona XtraBackup error message
If Percona XtraBackup detects that MySQL 8.0.29 server has tables with instant add/drop columns, it aborts with the following error message
1 2 3 4 5 6 7 8 | 2022-07-01T15:18:35.127689+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns 2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption. 2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details. 2022-07-01T15:18:35.127723+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Tables found: 2022-07-01T15:18:35.127730+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t1 2022-07-01T15:18:35.127737+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t2 2022-07-01T15:18:35.127744+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t3 2022-07-01T15:18:35.127752+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue. |
Summary
Algorithm INSTANT is the new default in 8.0.29. If you do not specify an algorithm, all ALTER TABLE ADD/DROP COLUMN statements will use the default algorithm.
The INSTANT algorithm is considered unstable at this point.
Percona XtraBackup will refuse to take backups from MySQL 8.0.29 tables that have been modified using this algorithm. Running OPTIMIZE TABLE on affected tables will bring them back to a safe state.
Percona XtraBackup is able to take backups seamlessly from Percona Server for MySQL, as the corruption issues have been fixed in the upcoming release of Percona Server for MySQL 8.0.29
But xtrabackup 8.0.29 isn’t available yet! Eagerly awaiting it. When will it be released?
Percona XtraBackup 8.0.29 release is in progress and should be in the repos by end of day today.
It is just so bad that such a bug remains in the main Community MySQL release for so long (almost three months).
Especially taking into account that there is no easy way to downgrade.
Could you please update this article to show how to set the default to a usable value on a new database (or in default settings in my.cnf?) That would be a big step forward. I don’t need the INSTANT and it sure sounds like I don’t want it. How do I prevent it from being there?
Hey Randy,
There is no system option to alter the default algorithm of “ALTER TABLE … ADD|DROP COLUMN” in either Oracle MySQL Community or Percona Server for MySQL. You must alter your SQL SYNTAX to explicitly use the “ALGORITHM=INPLACE” modifier. In the upcoming Percona Server 8.0.29 release (coming soon) we have temporarily changed the default algorithm for these “ALTER TABLE” operations to use the “INPLACE” algorithm instead of the unstable “INSTANT” algorithm. We will likely revert this change once the “INPLACE” feature has stabilized, hopefully as early as 8.0.30.
Hmm, that’s application-level changes. So you’d have to change Drupal and TYPO3 and Shopware to get them to change their db creation code. I guess you’re saying that xtrabackup is unusable for most people. Maybe you should lead with that statement in the blog and the info page. Of course maybe it’s MySQL 8.0.29 that’s unusable. But AFAICT xtrabackup 8.0.29 shouldn’t even be released if it requires application-level changes to use it. Am I completely misunderstanding?
Randy, this is not an XtraBackup issue, this is a MySQL Server issue with what it is writing to the InnoDB REDO log when this feature is used, which is unfortunately, the default. If you are using the combination of Percona Server and XtraBackup, you will not encounter this issue. If you are using MySQL Community 8.0.29 and any form of crash recovery (which is what XtraBackup uses during prepare), and this new feature, you will encounter this issue.
I completely understand that we’re talking about a MySQL bug. However, what we have here is a situation where a widely-used backup tool suddenly is unusable with its paired mysql version because of that bug, unless application level changes are undertaken, which is impossible for most people. So just a TL;DR at the top: “You need application-level changes if you want to use xtrabackup 8.0.29 with MySQL 8.0.29” would help people out. That’s the essence of things right? (Sadly, I’ve been waiting for this release for months so that I could update the mysql version in DDEV, https://github.com/drud/ddev. So I went through the considerable build process (requires arm64, so have to build arm64 version of xtrabackup, etc.). Then in testing discover that because of this issue 8.0.29 isn’t going to be usable at all, and it may not be clear when 8.0.30 will work. DDEV uses xtrabackup to do “snapshots”, and has no control of the application-level database/table creation. So since xtrabackups can’t be taken, snapshots can’t work.)
If i understand correct, we can use percona server 8.0.29 and instant add/drop without any problem? Or must we avoid instant algorithm? (because you changed default to inplace)
We have fixed all issues that were open about INSTANT and they are fixed on PS 8.0.29. That does not mean there aren’t others. This is still a new feature and we advise users to use INSTANT with caution.
I see that this is still exactly the same in just-released xtrabackup 8.0.31 with mysql 8.0.31. Is the upstream mysql problem still there, or was it a mistake not to resolve this in xtrabackup 8.0.31? The doc also doesn’t suggest a global way to resolve this…
Hi Randy.
Percona will only consider INSTANT an stable algorithm as part of 8.0.32 release.
Hi , is the instant add/drop column is stable enough now for production use ? if yes then which percona version supports it ?. i tried in PXC 8.0.29 but looks like its not instant and took different times with different number of rows.
It is more than that Randy, basically, if you can not prevent these specific operations from happening, then you should not be on 8.0.29 as there is a direct risk of irrecoverable data corruption and potentially a bricked instance if you ever have to replay the InnoDB Redo log after one of these ALTERs with DMLs on an altered table. Again, if you are using a full combination of Percona Server and XtraBackup, you are all good, but, if you are using MySQL 8.0.29 Community, then there is nothing at all that we can do other than to detect the situation and warn you that we can not possibly take a correct, clean, and consistent backup because of this bug. This is one of the reasons why we currently advise to never upgrade your server instance until after you have upgraded to the corresponding version of Percona XtrabBackup first.
Does this apply to standard crash recovery process (no xtrabackup) when using percona 8.0.29? Basically, can we use algorithm instant with percona version without any problems at all ?
same as above, test INSTANT before using it in prod. Same issues xtrabackup sees during –prepare phase will be seen by the server while performing crash recovery.
As the sample listed, these procedure might help.
1. Find all tables with at lease 1 row versions ( Add/Drop columns with ‘instant’ algo. )
SQL> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
2. Use pt-online-schema-change for optimizing found table(s).
eg. pt-online-schema-change –execute –alter “ENGINE=InnoDB” D=test,t=t1
3. Use xtrabackup.
I’ve previously posted about my issue on serverfault here.
https://serverfault.com/questions/1116092/problems-with-mysql-8-0-29-data-corruption-need-upgrade-path-advice
I’m painted into a corner with this bug. I currently run the community version of MySQL and I don’t have Percona, so my backups are corrupt considering I have about 23 corrupt tables from this bug.
Presently, 2 of my 3 slaves are down and cannot be resynced, they give error 2013 everytime I try to perform a full resync. I have no other viable backups, so my only option is to try to resolve the 23 corrupt tables in my various schema by optimizing them and then upgrading all servers to 8.0.31.
These are my questions:
I just tested instant add column with after clause on PXC 8.0.33 and it does not work as expected. with different number of rows the alter time goes up and never same like empty table.not sure if percona still does ‘inplace’ behind the seen.