Comments on: Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/ Thu, 28 Dec 2023 23:32:25 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Vishal Saxena https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10974163 Mon, 11 Sep 2023 18:36:55 +0000 https://www.percona.com/blog/?p=81836#comment-10974163 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.

]]> By: Vishal Saxena https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10974162 Mon, 11 Sep 2023 18:03:18 +0000 https://www.percona.com/blog/?p=81836#comment-10974162 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.

]]> By: Marcelo Altmann https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10974011 Mon, 13 Feb 2023 18:33:20 +0000 https://www.percona.com/blog/?p=81836#comment-10974011 Hi Randy.

Percona will only consider INSTANT an stable algorithm as part of 8.0.32 release.

]]> By: Randy Fay https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10974010 Mon, 13 Feb 2023 18:05:08 +0000 https://www.percona.com/blog/?p=81836#comment-10974010 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…

]]> By: John Galanek https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973835 Wed, 23 Nov 2022 14:40:10 +0000 https://www.percona.com/blog/?p=81836#comment-10973835 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:

  1. Has anyone been in a similar situation? What steps did you, (should I) take to get whole again?
  2. Will a MySQL server restart sufficiently empty the redo logs so I can proceed with a MySQL version upgrade?
  3. If not what should I do?
  4. Will optimizing my tables remove this corruption?
  5. If so should I optimize before or after the mysql upgrade?
  6. My total dump file is 30GB, should I optimize just the bad tables? Probably..
  7. What other resources or advice can you offer?
]]> By: Marcelo Altmann https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973712 Wed, 10 Aug 2022 14:23:45 +0000 https://www.percona.com/blog/?p=81836#comment-10973712 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.

]]> By: Marcelo Altmann https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973711 Wed, 10 Aug 2022 14:22:25 +0000 https://www.percona.com/blog/?p=81836#comment-10973711 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.

]]> By: Arda Beyazoglu https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973710 Wed, 10 Aug 2022 14:17:18 +0000 https://www.percona.com/blog/?p=81836#comment-10973710 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 ?

]]> By: Arda Beyazoglu https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973709 Wed, 10 Aug 2022 06:18:12 +0000 https://www.percona.com/blog/?p=81836#comment-10973709 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)

]]> By: Joe Horn https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973696 Mon, 01 Aug 2022 20:24:25 +0000 https://www.percona.com/blog/?p=81836#comment-10973696 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.

]]> By: georgelorch https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973678 Wed, 20 Jul 2022 19:39:44 +0000 https://www.percona.com/blog/?p=81836#comment-10973678 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.

]]> By: Randy Fay https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973677 Wed, 20 Jul 2022 18:15:31 +0000 https://www.percona.com/blog/?p=81836#comment-10973677 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.)

]]> By: georgelorch https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973676 Wed, 20 Jul 2022 17:12:47 +0000 https://www.percona.com/blog/?p=81836#comment-10973676 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.

]]> By: Randy Fay https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973675 Wed, 20 Jul 2022 17:04:18 +0000 https://www.percona.com/blog/?p=81836#comment-10973675 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?

]]> By: georgelorch https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973674 Wed, 20 Jul 2022 16:31:31 +0000 https://www.percona.com/blog/?p=81836#comment-10973674 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.

]]> By: Randy Fay https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973673 Wed, 20 Jul 2022 16:05:09 +0000 https://www.percona.com/blog/?p=81836#comment-10973673 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?

]]> By: georgelorch https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973670 Tue, 19 Jul 2022 14:19:57 +0000 https://www.percona.com/blog/?p=81836#comment-10973670 Percona XtraBackup 8.0.29 release is in progress and should be in the repos by end of day today.

]]> By: Pavel Katiushyn https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973669 Tue, 19 Jul 2022 14:03:27 +0000 https://www.percona.com/blog/?p=81836#comment-10973669 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.

]]> By: Randy Fay https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/#comment-10973668 Tue, 19 Jul 2022 13:21:22 +0000 https://www.percona.com/blog/?p=81836#comment-10973668 But xtrabackup 8.0.29 isn’t available yet! Eagerly awaiting it. When will it be released?

]]>