[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

Bug #1269695 reported by Valerii Kravchuk
10
This bug affects 2 people
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://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.

Changed in percona-toolkit:
status: New → Confirmed
Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :
Download full text (3.4 KiB)

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 ...

Read more...

summary: - [DOCS] pt-online-schema-change does not allow to add primary key for a
- table with a non-unique index only, while manual does not explain this
+ [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
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

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.

Changed in percona-toolkit:
assignee: nobody → Frank Cizmich (frank-cizmich)
status: Confirmed → Triaged
importance: Undecided → Medium
milestone: none → 2.2.12
Changed in percona-toolkit:
status: Triaged → In Progress
tags: added: docs pt-online-schema-change
Changed in percona-toolkit:
status: In Progress → Fix Committed
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

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

Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers