pt-online-schema-change does not support multiple alters in single operation

[JC]

pt-online-schema-change 2.2.19

Per the documentation:

```
--alter
type: string

The schema modification, without the ALTER TABLE keywords. You can perform multiple modifications to the table by specifying them with commas.
```

However after testing this and looking at the code it doesn't appear that the tool actually parses the alter option into multiple statements. Sample output below. Likely problem line: https://github.com/percona/percona-toolkit/blob/2.2/bin/pt-online-schema-change#L9039

```
# pt-online-schema-change --print --execute --alter "CHANGE content_type content_type BLOB, CHANGE content_type content_type varchar(255) CHARACTER SET utf8" D=app_live,t=avatars
No slaves found. See --recursion-method if host uadb1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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
Altering `app_live`.`avatars`...
Creating new table...
CREATE TABLE `app_live`.`_avatars_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `content_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `thumbnail` varchar(255) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_avatars_on_profile_id` (`profile_id`),
  KEY `index_avatars_on_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=770710 DEFAULT CHARSET=latin1
Created new table app_live._avatars_new OK.
Altering new table...
ALTER TABLE `app_live`.`_avatars_new` CHANGE content_type content_type BLOB, CHANGE content_type content_type varchar(255)
2016-11-15T20:07:49 Dropping new table...
DROP TABLE IF EXISTS `app_live`.`_avatars_new`;
2016-11-15T20:07:49 Dropped new table OK.
`app_live`.`avatars` was not altered.
Error altering new table `app_live`.`_avatars_new`: DBD::mysql::db do failed: Unknown column 'content_type' in '_avatars_new' [for Statement "ALTER TABLE `app_live`.`_avatars_new` CHANGE content_type content_type BLOB, CHANGE content_type content_type varchar(255)"] at /usr/bin/pt-online-schema-change line 9035.
```

Not relevant but yes the column has already been converted to varchar(255) and utf-8 by a previous run with multiple executions of pt-online-schema-change.

[Carlos Salguero (carlos-salguero)]

First: There is an error on what you are trying to do: Unknown column 'content_type' in '_avatars_new'

Even that, I've tried to reproduce the issue but it works as expected:

mysql> use sakila;
Database changed
mysql> describe actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

bin/pt-online-schema-change --host 127.0.0.1 --port 12345 --user msandbox --password=msandbox --alter "ADD COLUMN c1 INT, ADD COLUMN c2 INT" --alter-foreign-keys-method=auto --execute D=sakila,t=actor

mysql> describe actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| c1 | int(11) | YES | | NULL | |
| c2 | int(11) | YES | | NULL | |
+-------------+----------------------+------+-----+-------------------+-----------------------------+

[JC (jcotton1123)]

I can do some further testing but you can see from the create table statement that the column 'content_type' does indeed exist.

[JC (jcotton1123)]

I tracked the issue down to modifying the same column twice.

pt-online-schema-change --execute --alter "CHANGE thumbnail thumbnail BLOB, CHANGE thumbnail thumbnail varchar(255) CHARACTER SET utf8" D=app_live,t=avatars

will throw an error about the column missing.

I am not going to file a new bug.

[Carlos Salguero (carlos-salguero)]

It is not a bug. online-schema-change works by creating a new table and you are trying to change the field content_type to a 2 different types at the same time.

[JC (jcotton1123)]

Understood and why I am not creating a bug report. However, it doesn’t seem that crazy. I am not asking it to change a column to “2 different types at the same time”. I am asking it to complete to type changes on the temporary table its constructing so that the application effectively only sees a single type change. I understand that the tool may not work like that however.

> On Nov 15, 2016, at 3:09 PM, Carlos Salguero <email address hidden> wrote:
>
> It is not a bug. online-schema-change works by creating a new table and
> you are trying to change the field content_type to a 2 different types
> at the same time.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1642038
>
> Title:
> pt-online-schema-change does not support multiple alters in single
> operation
>
> Status in Percona Toolkit:
> Invalid
>
> Bug description:
> pt-online-schema-change 2.2.19
>
> Per the documentation:
>
> ```
> --alter
> type: string
>
> The schema modification, without the ALTER TABLE keywords. You can perform multiple modifications to the table by specifying them with commas.
> ```
>
> However after testing this and looking at the code it doesn't appear
> that the tool actually parses the alter option into multiple
> statements. Sample output below. Likely problem line:
> https://github.com/percona/percona-toolkit/blob/2.2/bin/pt-online-
> schema-change#L9039
>
> ```
> # pt-online-schema-change --print --execute --alter "CHANGE content_type content_type BLOB, CHANGE content_type content_type varchar(255) CHARACTER SET utf8" D=app_live,t=avatars
> No slaves found. See --recursion-method if host uadb1 has slaves.
> Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
> 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
> Altering `app_live`.`avatars`...
> Creating new table...
> CREATE TABLE `app_live`.`_avatars_new` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `parent_id` int(11) DEFAULT NULL,
> `content_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
> `filename` varchar(255) DEFAULT NULL,
> `thumbnail` varchar(255) DEFAULT NULL,
> `size` int(11) DEFAULT NULL,
> `width` int(11) DEFAULT NULL,
> `height` int(11) DEFAULT NULL,
> `created_at` datetime DEFAULT NULL,
> `updated_at` datetime DEFAULT NULL,
> `profile_id` int(11) DEFAULT NULL,
> PRIMARY KEY (`id`),
> KEY `index_avatars_on_profile_id` (`profile_id`),
> KEY `index_avatars_on_parent_id` (`parent_id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=770710 DEFAULT CHARSET=latin1
> Created new table app_live._avatars_new OK.
> Altering new table...
> ALTER TABLE `app_live`.`_avatars_new` CHANGE content_type content_type BLOB, CHANGE content_type content_type varchar(255)
> 2016-11-15T20:07:49 Dropping new table...
> DROP TABLE IF EXISTS `app_live`.`_avatars_new`;
> 2016-11-15T20:07:49 Dropped new table OK.
> `app_live`.`avatars` was not altered.
> Error alter...

[Shahriyar Rzayev (rzayev-sehriyar)]

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