Not the answer you need?
Register and ask your own question!

pt-online-schema multiple changes

khannvinkhannvin EntrantCurrent User Role Beginner
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!

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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);
  • khannvinkhannvin Entrant Current User Role Beginner
    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`)
  • r4z0rr4z0r Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.