Announcement

Announcement Module
Collapse
No announcement yet.

pt-online-schema multiple changes

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema multiple changes

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

    Comment


    • #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`)

      Comment


      • #4
        Hi,

        Can you please provide full log of that command?

        Comment


        • #5
          Hi,

          Below is the output:

          pt-online-schema-change --password=uk86xYz --execute --print --alter "ENGINE=MyISAM, MODIFY date datetime NOT NULL,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

          Altering `test`.`foo`...
          Creating new table...
          CREATE TABLE `test`.`_foo_new` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `date` datetime DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `indx_date` (`date`)
          ) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
          Created new table test._foo_new OK.
          Altering new table...
          ALTER TABLE `test`.`_foo_new` ENGINE=MyISAM, MODIFY date datetime NOT NULL,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)
          Altered `test`.`_foo_new` OK.
          Creating triggers...
          CREATE TRIGGER `pt_osc_test_foo_del` AFTER DELETE ON `test`.`foo` FOR EACH ROW DELETE IGNORE FROM `test`.`_foo_new` WHERE `test`.`_foo_new`.`id` <=> OLD.`id` AND `test`.`_foo_new`.`date` <=> OLD.`date`
          CREATE TRIGGER `pt_osc_test_foo_upd` AFTER UPDATE ON `test`.`foo` FOR EACH ROW REPLACE INTO `test`.`_foo_new` (`id`, `date`) VALUES (NEW.`id`, NEW.`date`)
          CREATE TRIGGER `pt_osc_test_foo_ins` AFTER INSERT ON `test`.`foo` FOR EACH ROW REPLACE INTO `test`.`_foo_new` (`id`, `date`) VALUES (NEW.`id`, NEW.`date`)
          Created triggers OK.
          Copying approximately 1116 rows...
          INSERT LOW_PRIORITY IGNORE INTO `test`.`_foo_new` (`id`, `date`) SELECT `id`, `date` FROM `test`.`foo` /*pt-online-schema-change 24549 copy table*/
          Copied rows OK.
          Swapping tables...
          RENAME TABLE `test`.`foo` TO `test`.`_foo_old`, `test`.`_foo_new` TO `test`.`foo`
          Swapped original and new tables OK.
          Dropping old table...
          DROP TABLE IF EXISTS `test`.`_foo_old`
          Dropped old table `test`.`_foo_old` OK.
          Dropping triggers...
          DROP TRIGGER IF EXISTS `test`.`pt_osc_test_foo_del`;
          DROP TRIGGER IF EXISTS `test`.`pt_osc_test_foo_upd`;
          DROP TRIGGER IF EXISTS `test`.`pt_osc_test_foo_ins`;
          Dropped triggers OK.
          Successfully altered `test`.`foo`.

          Comment


          • #6
            Hi,

            I'm not seeing any error in above output so where you are getting it?

            Comment


            • #7
              Its the correct command which i have implemented. If you use (`id`,`date`) then it will throw error due to special character.

              Comment


              • #8
                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
                Last edited by r4z0r; 06-11-2013, 06:59 PM.

                Comment

                Working...
                X