pt-online-schema multiple changes

Polyglot Projects Percona Toolkit
1

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!

2

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);

3

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)

4

See [URL=“shell - What does ` (backquote/backtick) mean in commands? - Unix & Linux Stack Exchange”]http://unix.stackexchange.com/questi...k-mean-in-bash[/URL]

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