[Feature Request] Skip some columns when copying rows to new table.

[Matt]

Hi.
Thanks for your useful tools, especially pt-online-schema-change.
I have a small feature request which really useful when not to copy some columns to new table.

Let's say we have below table and some rows.

CREATE TABLE test (id int primary key, fd1 char(1) not null default 'Y');
==> and this table's most of row have 'Y' value on fd1 column.

On this table, If I want to change "DEFAULT 'N'" on fd1 column and want to set 'N' to fd1 column of all rows.
This is possible when I use ALTER TABLE command like this.
==> ALTER TABLE test DROP fd1, ADD fd1 CHAR(1) NOT NULL DEFAULT 'N';

But, with pt-online-schema-change, this is not possible.

So, pt-online-schema-change tool skip some columns when copy rows or create trigger, then this request could be accomplished,
If pt-online-schema-change tool support option lIke below example. ^^

shell> pt-online-schema-change --skip-copy-columns="fd1" ...

I believe you can easily implement it. And some people really want to this feature.
Thanks again.

[Matt (brew74)]

I modified pt-online-schema-change tool a little bit.
And you can see that from blog site.

http://kakaodbe.blogspot.kr/2014/04/pt-online-schema-change-modified-pt.html

[Nilnandan Joshi (nilnandan-joshi)]

Verified. If we run those statements with pt-osc, it will change the column default value but will not set new value. With ALTER TABLE, it will change the column default values and set the new default value to all records.

mysql> CREATE TABLE test (id int primary key, fd1 char(1) not null default 'Y');
Query OK, 0 rows affected (0.12 sec)
mysql> select * from test;
+----+-----+
| id | fd1 |
+----+-----+
| 1 | Y |
| 2 | Y |
| 3 | Y |
| 4 | Y |
| 5 | Y |
+----+-----+
5 rows in set (0.00 sec)

root@localhost:/var/lib/mysql# pt-online-schema-change --alter "DROP fd1, ADD fd1 CHAR(1) NOT NULL DEFAULT 'N'" h=127.0.0.1,u=root,p=root,D=test,t=test --execute
No slaves found. See --recursion-method if host localhost 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`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
2014-08-26T13:33:40 Creating triggers...
2014-08-26T13:33:40 Created triggers OK.
2014-08-26T13:33:40 Copying approximately 5 rows...
2014-08-26T13:33:40 Copied rows OK.
2014-08-26T13:33:40 Swapping tables...
2014-08-26T13:33:41 Swapped original and new tables OK.
2014-08-26T13:33:41 Dropping old table...
2014-08-26T13:33:41 Dropped old table `test`.`_test_old` OK.
2014-08-26T13:33:41 Dropping triggers...
2014-08-26T13:33:41 Dropped triggers OK.
Successfully altered `test`.`test`.
root@localhost:/var/lib/mysql#

root@localhost:/var/lib/mysql# mysql -uroot -p
Enter password:
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `fd1` char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from test;
+----+-----+
| id | fd1 |
+----+-----+
| 1 | Y |
| 2 | Y |
| 3 | Y |
| 4 | Y |
| 5 | Y |
+----+-----+
5 rows in set (0.00 sec)

mysql> ALTER TABLE test DROP fd1, ADD fd1 CHAR(1) NOT NULL DEFAULT 'N';
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+-----+
| id | fd1 |
+----+-----+
| 1 | N |
| 2 | N |
| 3 | N |
| 4 | N |
| 5 | N |
+----+-----+
5 rows in set (0.00 sec)

mysql>

[Shahriyar Rzayev (rzayev-sehriyar)]

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