Emergency

pt-online-schema-change add auto_increment pk

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change add auto_increment pk

    Hello,
    I would like to add an auto_increment PK in an existing table without stopping the service.

    The original table looks as below.

    CREATE TABLE `service` (
    `service_id` varchar(40) COLLATE utf8mb4_bin NOT NULL,
    `user_id` varchar(10) COLLATE utf8mb4_bin NOT NULL,
    `device_id` varchar(64) COLLATE utf8mb4_bin NOT NULL,
    PRIMARY KEY(`service_id`),
    KEY `idx_service_001`(`user_id`,`device_id`)
    ) ENGINE=InnoDB;


    And this is the target schema.

    CREATE TABLE `service` (
    `id` int unsigned not null
    `service_id` varchar(40) COLLATE utf8mb4_bin NOT NULL,
    `user_id` varchar(10) COLLATE utf8mb4_bin NOT NULL,
    `device_id` varchar(64) COLLATE utf8mb4_bin NOT NULL,
    PRIMARY KEY(`id`),
    UNIQUE KEY `uk_service_001`(`service_id`),
    KEY `idx_service_001`( `user_id`,`device_id`)
    ) ENGINE=InnoDB;


    I will first create an unique key as below with MySQL OSC.
    create unique index `uk_service_001` on `service`(`service_id`);

    Then add the auto_increment pk column like 'drop primary key, add column id int unsigned not null auto_increment primary key' with pt-osc.
    Do you think if there is any problem with it?
    I choose pt-osc because MySQL OSC blocks DML while adding auto_increment column. ( https://dev.mysql.com/doc/refman/5.7...-overview.html )
    I use MySQL 5.7.16 community.


    Best Regards,
    Hyuk Lee

  • #2
    Hello,

    Please read https://bugs.launchpad.net/percona-toolkit/+bug/1709650.
    pt-online-schema-change sets NO_AUTO_VALUE_ON_ZERO and that might affect adding a new AUTO_INCREMENT index.

    Regards

    Comment


    • #3
      Hello,

      I appreciate your advice.

      Regards

      Comment

      Working...
      X