PK AUTO_INCREMENTを比較的後から安全に追加する

another_

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD dummy_uuid VARCHAR(36) DEFAULT NULL UNIQUE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (3, UUID());
Query OK, 1 row affected (0.00 sec)

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     dummy_uuid
1       NULL
2       NULL
3       1e928842-2432-11ed-98fc-fa163f8f0da3
# server: 1:
num     dummy_uuid
2       NULL
1       NULL
3       1e928842-2432-11ed-98fc-fa163f8f0da3

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     dummy_uuid
1       32567c85-2432-11ed-98fc-fa163f8f0da3
2       NULL
3       1e928842-2432-11ed-98fc-fa163f8f0da3
# server: 1:
num     dummy_uuid
2       NULL
1       32567c85-2432-11ed-98fc-fa163f8f0da3
3       1e928842-2432-11ed-98fc-fa163f8f0da3

$ ./m -e "UPDATE d1.t1 SET dummy_uuid = UUID() WHERE dummy_uuid IS NULL LIMIT 1"

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     dummy_uuid
1       32567c85-2432-11ed-98fc-fa163f8f0da3
2       362f3804-2432-11ed-98fc-fa163f8f0da3
3       1e928842-2432-11ed-98fc-fa163f8f0da3
# server: 1:
num     dummy_uuid
2       362f3804-2432-11ed-98fc-fa163f8f0da3
1       32567c85-2432-11ed-98fc-fa163f8f0da3
3       1e928842-2432-11ed-98fc-fa163f8f0da3

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD PRIMARY KEY (dummy_uuid);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 DROP PRIMARY KEY, ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     dummy_uuid      id
3       1e928842-2432-11ed-98fc-fa163f8f0da3    1
1       32567c85-2432-11ed-98fc-fa163f8f0da3    2
2       362f3804-2432-11ed-98fc-fa163f8f0da3    3
# server: 1:
num     dummy_uuid      id
3       1e928842-2432-11ed-98fc-fa163f8f0da3    1
1       32567c85-2432-11ed-98fc-fa163f8f0da3    2
2       362f3804-2432-11ed-98fc-fa163f8f0da3    3



master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 DROP dummy_uuid;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
3       1
1       2
2       3
# server: 1:
num     id
3       1
1       2
2       3

gistfile1.txt

master [localhost] {msandbox} ((none)) > LOCK TABLE d1.t1 WRITE;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ORDER BY num;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
2       2
# server: 1:
num     id
1       1
2       2

gistfile2.txt

### Need binlog_format = ROW
CREATE TABLE d1.t1_new LIKE d1.t1;
ALTER TABLE d1.t1_new ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
LOCK TABLE d1.t1 WRITE, d1.t1_new WRITE;
INSERT INTO d1.t1_new (num) SELECT num FROM d1.t1;
RENAME TABLE d1.t1 TO d1.t1_old, d1.t1_new TO d1.t1;
UNLOCK TABLES;