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

[Siddhant]

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 .

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-738