Pt-osc with foreign key using Mysql 8.0 on GCP

Polyglot Projects Percona Toolkit
1

Hi
I’m testing pt-osc against a Mysql instance on Google Cloud Sql, version 8.0.18-google. The table has foreign keys and I have testet with the “–alter-foreign-keys-method auto” but I get an error regarding the drop_swap option.

--alter-foreign-keys-method=drop_swap doesn't work with MySQL 8.0+
See https://bugs.mysql.com/bug.php?id=89441

Regarding the bug report this should be fixed in 8.0.14! I’m not into the details around the problem, so can anyone confirm that this i still a problem with MySQL 8.0+ and will be throughout the 8.0+ life or is this due to the google version?

In the case where I’m forced to use “–alter-foreign-keys-method rebuild_constraints", as stated in the doc, this could take long time for large table. So my question here is, are the altered child tables locked during this process?

Thanks in advance.

1 Like
2

Hi @Storebuddy, honestly, best way to confirm if a bug is fixed is to try it yourself. In theory, google’s 8.0.18 should include fixes from 8.0.14. You should be able to easily clone your instance and test it out on the new clone. Delete clone when done. Should not cost too much for peace of mind vs testing in production.

The child tables will most likely have a READ/SHARED lock imposed on each row during the index rebuild.

1 Like
3

I had the same issue on AWS RDS, running a MySQL 8.0.23 + Percona Toolkit 3.3.1 (latest available)

2021-05-20T09:22:23 fnbluser01.fnbl_picture_of_the_day: too many rows: 61344; must use drop_swap
–alter-foreign-keys-method=drop_swap doesn’t work with MySQL 8.0+
See MySQL Bugs: #89441: Foreign keys constraints ignored after RENAME TABLE

Looking at the Percona Toolkit source code on GitHub:

   if ($vp->cmp('8.0') > -1 && $vp->flavor() !~ m/maria/i && $alter_fk_method eq 'drop_swap') {
       my $msg = "--alter-foreign-keys-method=drop_swap doesn't work with MySQL 8.0+\n".
                 "See https://bugs.mysql.com/bug.php?id=89441";
       _die($msg, INVALID_PARAMETERS);
   }

To me the issue is that the toolkit does not adknowledge that bug is addressed in 8.0.14 and above.

1 Like
4

See [PT-1940] ptsoc dropswap with mysql8: revise rejection - Percona JIRA

1 Like