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

``pt-online-schema-change`` declined to handle tables because of foreign keys even when there were no foreign keys with some MariaDB 10.2 and MySQL 8 versions.

Details

    Description

      I have a MariaDB 10.2 table without foreign keys and also no foreign keys pointing to it. When I run the following command I get an error:

      pt-online-schema-change --print --alter "CHANGE COLUMN type type ENUM('multi-contract') CHARACTER SET 'utf8' NOT NULL" D=db1,t=t1 --user abc --host localhost --statistics --ask-pass --dry-run{{Enter MySQL password: }}
      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}}
      There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See https://bugs.mysql.com/bug.php?id=96145
      Since pt-online-schema change needs to rename the old <-> new tables as the final step, and the requested table has FKs, it cannot be executed under the current MySQL version

      }}{{SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 't1';
      Empty set (0.01 sec)

      SELECT TABLE_NAME,
      {{ -> COLUMN_NAME,}}
      {{ -> CONSTRAINT_NAME,}}
      {{ -> REFERENCED_TABLE_NAME,}}
      {{ -> REFERENCED_COLUMN_NAME}}
      {{ -> FROM KEY_COLUMN_USAGE}}
      {{ -> WHERE TABLE_SCHEMA = "db1" }}
      {{ -> AND TABLE_NAME = "t1" }}
      {{ -> AND REFERENCED_COLUMN_NAME IS NOT NULL;}}
      Empty set (0.00 sec)

       

      SHOW VARIABLES LIKE 'version%';
      ---------------------------------------------------------+
      | Variable_name | Value |
      ---------------------------------------------------------+
      | version | 10.2.25-MariaDB-log |
      | version_comment | SUSE package |
      | version_compile_machine | x86_64 |
      | version_compile_os | Linux |
      | version_malloc_library | system |
      | version_ssl_library | OpenSSL 1.1.0i-fips 14 Aug 2018 |
      ---------------------------------------------------------+
      6 rows in set (0.00 sec)

       

      Attachments

        Activity

          People

            carlos.salguero Carlos Salguero (Inactive)
            Trueman1 Jens
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist