pt-online-schema multiple changes

[khannvin]

Hi,

I want to perform below tasks for a INNODB table.

can pt-online-schema help me to run below commands in one go?

1. ALTER table foo ENGINE=MyISAM;

2. ALTER TABLE foo MODIFY date datetime NOT NULL;

3. ALTER TABLE foo DROP PRIMARY KEY, ADD PRIMARY KEY (id,date);

4. ALTER TABLE foo PARTITION BY RANGE (MOD(TO_DAYS(date),2))(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
   PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM);

Thanks!

[niljoshi]

Hi,

Yes, you can use below alter command with pt-online-schema-change utility. http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch ema-change.html

ALTER TABLE foo ENGINE=MyISAM, DROP PRIMARY KEY, ADD PRIMARY KEY (id,date), MODIFY date datetime NOT NULL PARTITION BY RANGE (MOD(TO_DAYS(date),2))(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM);

[khannvin]

Thanks Nil for your reply. I already implemented the command.

command is given below:

pt-online-schema-change --execute --print --alter “ENGINE=MyISAM, DROP PRIMARY KEY, ADD PRIMARY KEY (id,date) PARTITION BY RANGE ( MOD(TO_DAYS(date),2))(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM)” D=test,t=foo

Note: script was throwing error with (id,date)

[r4z0r]

See http://unix.stackexchange.com/questi…k-mean-in-bash

The id part gets interpreted as ‘id’ command (http://linux.about.com/library/cmd/blcmdl1_id.htm). To work around this, remove the backticks from your ALTER clause