[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

[Valerii Kravchuk]

This problem was originally reported to me by Gillian Gunson.

Manual for the tool (http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html) does NOT explain that it can not add a new column that is a primary key to the table that has only non-unique keys, because trigger(s) can not actually identify the row in the original table properly to copy change to the new table.

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-schema-change:

[openxs@chief 5.6]$ cd ~/bzr2/percona-toolkit-2.2/

[openxs@chief percona-toolkit-2.2]$ bin/pt-online-schema-change -S /tmp/mysql.sock -uroot --alter "add column pkid int not null auto_increment primary key" --execute --chunk-index id D=test,t=t1
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_foreign_keys, 10, 1
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-toolkit-2.2]$ bin/pt-online-schema-change --version
pt-online-schema-change 2.2.6

It seems reasonable limitation, so, please, document it explicitly.

[Valerii Kravchuk (valerii-kravchuk)]

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
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_foreign_keys, 10, 1
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-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
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_foreign_keys, 10, 1
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-toolkit-2.2]$ bin/pt-online-schema-change -S /tmp/mysql.sock -uroot --alter "add column c1 int" --execute D=test,t=t1
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_foreign_keys, 10, 1
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-toolkit-2.2]$ bin/pt-online-schema-change -S /tmp/mysql.sock -uroot --alter "drop column txt" --nocheck-alter --execute D=test,t=t1
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_foreign_keys, 10, 1
Altering `test`.`t1`...
Creating new table...
Created new ...

[Frank Cizmich (frank-cizmich)]

I agree this should be documented.

Here is a proposed text:

In almost all cases a PRIMARY KEY or UNIQUE INDEX needs to be present in the table. This is necessary because the tool creates a DELETE trigger to keep the new table updated while the process is running.
A notable exception is when a PRIMARY or UNIQUE index is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger.

[Frank Cizmich (frank-cizmich)]

Docs were modified to emphasize this limitation and also explains why it is so.

[Shahriyar Rzayev (rzayev-sehriyar)]

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