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

Index with keyword 'unique' as prefix/suffix considered as unique

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.0.4
    • Fix Version/s: 3.0.5
    • Component/s: None
    • Labels:
      None

      Description

      Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1719085

      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:
                carlos.salguero Carlos Salguero
                Reporter:
                carlos.salguero Carlos Salguero
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: