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

LP #1719085: Index with keyword 'unique' as prefix/suffix considered as unique

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Review
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Siddhant last update 25-09-2017 18:20:01

      Version specific bug : pt-online-schema-change 3.0.4

      After recent related fix for #1545129 (pt-online-schema change eats data on adding a unique index)

      When trying to add INDEX with suffix/prefix having keyword 'unique' in it causes to warn out for "You are trying to add an unique key" error .
      pt-online-schema-change allows to add the column with 'unique' as suffix/prefix but does not allow to add index .

      ======= Table =======

      mysql> show create table osc_test\G

                                                          • 1. row ***************************
                                                            Table: osc_test
                                                            Create Table: CREATE TABLE `osc_test` (
                                                            `id` int(11) NOT NULL AUTO_INCREMENT,
                                                            `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                                            PRIMARY KEY (`id`)
                                                            ) ENGINE=InnoDB DEFAULT CHARSET=latin1

      ========
      Adding index on newly added column with keyword 'unique' as prefix

      $ pt-online-schema-change h=localhost,D=percona,t=osc_test --alter "ADD COLUMN unique_id BIGINT UNSIGNED NOT NULL , ADD INDEX(unique_id)" --dry-run

      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
      Starting a dry run. `percona`.`osc_test` will not be altered. Specify --execute instead of --dry-run to alter the table.
      Dry run complete. `percona`.`osc_test` was not altered.
      You are trying to add an unique key. This can result in data loss if the data is not unique.
      Please read the documentation for the --check-unique-key-change parameter.
      You can check if the column(s) contain duplicate content by running this/these query/queries:

      SELECT IF(COUNT(DISTINCT unique_id) = COUNT,
      'Yes, the desired unique index currently contains only unique values',
      'No, the desired unique index contains duplicated values. There will be data loss'
      ) AS IsThereUniqueness FROM `percona`.`osc_test`;

      Keep in mind that these queries could take a long time and consume a lot of resources .

      ===== 2nd test case ====

      However , even when adding index on other column (here : last_updated) on table reports same warning (here unique keyword is suffixed)

      $ pt-online-schema-change h=localhost,D=percona,t=osc_test --alter "ADD COLUMN id_unique BIGINT UNSIGNED NOT NULL, ADD INDEX(last_updated)" --dry-run

      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
      Starting a dry run. `percona`.`osc_test` will not be altered. Specify --execute instead of --dry-run to alter the table.
      Dry run complete. `percona`.`osc_test` was not altered.
      You are trying to add an unique key. This can result in data loss if the data is not unique.
      Please read the documentation for the --check-unique-key-change parameter.
      You can check if the column(s) contain duplicate content by running this/these query/queries:

      SELECT IF(COUNT(DISTINCT last_update) = COUNT,
      'Yes, the desired unique index currently contains only unique values',
      'No, the desired unique index contains duplicated values. There will be data loss'
      ) AS IsThereUniqueness FROM `percona`.`osc_test`;

      Keep in mind that these queries could take a long time and consume a lot of resources

      =======
      MySQL version : 5.7.17-11-log Percona Server (GPL)

      =======
      Related code :

      my $fields_re = qr/(?:PRIMARY|UNIQUE)\s*(?:INDEX|KEY|)\s*(?:.?)\s((.*?))/i;
      while($clean =~ /$fields_re/g)

      { push @$fields, [ split /\s*,\s*/, $1 ]; }

      =======

      Can be tackled by using parameter --no-check-unique-key-change , for above conditions only .
      or by downgrading to lower version wherein other tables will be affected by BUG #1545129 for adding unique index .

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              lpjirasync lpjirasync (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: