Index with keyword 'unique' as prefix/suffix considered as unique
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Committed
|
Medium
|
Carlos Salguero |
Bug Description
Version specific bug : pt-online-
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-
======= Table =======
mysql> show create table osc_test\G
*******
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-
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_
Starting a dry run. `percona`
Dry run complete. `percona`
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-
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`
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-
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_
Starting a dry run. `percona`
Dry run complete. `percona`
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-
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`
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|
while($clean =~ /$fields_re/g) {
push @$fields, [ split /\s*,\s*/, $1 ];
}
=======
Can be tackled by using parameter --no-check-
or by downgrading to lower version wherein other tables will be affected by BUG #1545129 for adding unique index .
Changed in percona-toolkit: | |
status: | New → Fix Committed |
importance: | Undecided → Medium |
assignee: | nobody → Carlos Salguero (carlos-salguero) |
milestone: | none → 3.0.5 |
tags: | added: pt200 |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/ /jira.percona. com/browse/ PT-738