Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1634

pt-osc --alter-foreign-keys-method=drop_swap doesn't work with MariaDB 10.x

    XMLWordPrintable

    Details

      Description

      Hello,

      Recently noticed that ps-osc fails with MariaDB 10.x.  It throws the same error as if one was running MySQL 8.0:

       

      Running MariaDB 10.2.17.

      Just upgraded to Percona Toolkit 3.0.12-1.el6.

      Ran:

      pt-online-schema-change --user=root --password=********** --execute --alter-foreign-keys-method=auto --alter "MODIFY ..." D=xxxxx,t=yyyyy,h=localhost

      Output:

      No slaves found. See --recursion-method if host hostnameXYZ has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      Operation, tries, wait:
      analyze_table, 10, 1
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
      Child tables:
      `xxxxx`.`t1` (approx. 1511207 rows)
      `xxxxx`.`t2` (approx. 147803 rows)
      `xxxxx`.`t3` (approx. 401 rows)
      `xxxxx`.`t4` (approx. 2015760 rows)
      Will automatically choose the method to update foreign keys.
      Altering `xxxxx`.`yyyyy`...
      Creating new table...
      Created new table xxxxx._yyyyy_new OK.
      Altering new table...
      Altered `xxxxx`.`_yyyyy_new` OK.
      2018-11-02T14:42:25 Creating triggers...
      2018-11-02T14:42:25 Created triggers OK.
      2018-11-02T14:42:25 Copying approximately 1168528 rows...
      Copying `xxxxx`.`yyyyy`: 68% 00:13 remain
      2018-11-02T14:43:33 Copied rows OK.
      2018-11-02T14:43:33 Max rows for the rebuild_constraints method: 46038
      Determining the method to update foreign keys...
      2018-11-02T14:43:33 `xxxxx`.`t1`: too many rows: 1511207; must use drop_swap
      2018-11-02T14:43:33 Dropping triggers...
      2018-11-02T14:43:34 Dropped triggers OK.
      Not dropping the new table `xxxxx`.`_yyyyy_new` because --swap-tables failed. To drop the new table, execute:
      DROP TABLE IF EXISTS `xxxxx`.`_yyyyy_new`;
      `xxxxx`.`yyyyy` was not altered.
      --alter-foreign-keys-method=drop_swap doesn't work with MySQL 8.0+
      See https://bugs.mysql.com/bug.php?id=89441 at /usr/bin/pt-online-schema-change line 9823.

       

      This seems somewhat related to:

      https://bugs.mysql.com/bug.php?id=89441
      https://jira.percona.com/browse/PT-1569

      However, it seems that the version is being read wrong, or being confused with MySQL 8.0.

      Current work-around is to run with: --alter-foreign-keys-method=rebuild_constraints

      Thank you.

       

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            ccalender Chris Calender
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist