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

pt-online-schema-change add auto_increment pk

RyanERyanE EntrantCurrent User Role Participant
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/en/innodb-create-index-overview.html )
I use MySQL 5.7.16 community.


Best Regards,
Hyuk Lee

Comments

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.