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. ( [url]https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html[/url] )
I use MySQL 5.7.16 community.
Best Regards,
Hyuk Lee