[DOCS] pt-online-schema-change does not allow any ALTER for a table with a non-unique index only, while manual does not explain this
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Frank Cizmich |
Bug Description
This problem was originally reported to me by Gillian Gunson.
Manual for the tool (http://
Tested with recent version 2.2.6 as follows:
[openxs@chief 5.6]$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'socket';
+------
| Variable_name | Value |
+------
| socket | /tmp/mysql.sock |
+------
1 row in set (0.00 sec)
mysql> CREATE TABLE `t1` ( `id` int(11) NOT NULL, `txt` varchar(20) DEFAULT NULL,
-> KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.57 sec)
mysql> INSERT INTO t1 VALUES (1, ''), (2, 'abcd');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
Now, let's try to ALTER with pt-online-
[openxs@chief 5.6]$ cd ~/bzr2/
[openxs@chief percona-
No slaves found. See --recursion-method if host chief has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2014-01-16T09:08:33 Dropping new table...
2014-01-16T09:08:34 Dropped new table OK.
`test`.`t1` was not altered.
The new table index PRIMARY would be used for the DELETE trigger, but it uses column pkid which does not exist in the original table and the original table does not have a PRIMARY KEY or a unique index to use for the DELETE trigger.
[openxs@chief percona-
pt-online-
It seems reasonable limitation, so, please, document it explicitly.
Related branches
- Daniel Nichter: Approve
-
Diff: 33 lines (+16/-0)1 file modifiedbin/pt-online-schema-change (+16/-0)
Changed in percona-toolkit: | |
status: | New → Confirmed |
Changed in percona-toolkit: | |
status: | Triaged → In Progress |
tags: | added: docs pt-online-schema-change |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
Actually, hardly pt-online- schema- change can do ANY change to the table that does not have PRIMARY or UNIQUE KEY, but http:// www.percona. com/doc/ percona- toolkit/ 2.2/pt- online- schema- change. html does not say this.
Simple checks:
[openxs@chief percona- toolkit- 2.2]$ bin/pt- online- schema- change -S /tmp/mysql.sock -uroot --alter "engine=MyISAM" --execute --chunk-index id D=test,t=t1 foreign_ keys, 10, 1 toolkit- 2.2]$ bin/pt- online- schema- change -S /tmp/mysql.sock -uroot --alter "add column c1 int" --execute --chunk-index id D=test,t=t1 foreign_ keys, 10, 1 toolkit- 2.2]$ bin/pt- online- schema- change -S /tmp/mysql.sock -uroot --alter "add column c1 int" --execute D=test,t=t1 foreign_ keys, 10, 1 toolkit- 2.2]$ bin/pt- online- schema- change -S /tmp/mysql.sock -uroot --alter "drop column txt" --nocheck-alter --execute D=test,t=t1 foreign_ keys, 10, 1
No slaves found. See --recursion-method if host chief has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2014-01-16T09:23:56 Dropping new table...
2014-01-16T09:23:56 Dropped new table OK.
`test`.`t1` was not altered.
The new table `test`.`_t1_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
[openxs@chief percona-
No slaves found. See --recursion-method if host chief has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2014-01-16T09:27:13 Dropping new table...
2014-01-16T09:27:13 Dropped new table OK.
`test`.`t1` was not altered.
The new table `test`.`_t1_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
[openxs@chief percona-
No slaves found. See --recursion-method if host chief has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2014-01-16T09:27:19 Dropping new table...
2014-01-16T09:27:19 Dropped new table OK.
`test`.`t1` was not altered.
The new table `test`.`_t1_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
[openxs@chief percona-
No slaves found. See --recursion-method if host chief has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Creating new table...
Created new ...