pt-online-schema-change Complains Missing UNIQUE KEY
General
Escalation
General
Escalation
Description
Environment
None
Smart Checklist
Activity
Show:
Lalit Choudhary September 13, 2019 at 8:54 AM
Validated as described
mysql [localhost] {msandbox} (test) > show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`s` varchar(255) DEFAULT NULL,
UNIQUE KEY `s` (`s`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
$ pt-online-schema-change --defaults-file=/home/lalit/sandboxes/msb_10_3_7/my.sandbox.cnf --alter='ENGINE=INNODB DEFAULT CHARSET=utf8mb4' --charset=utf8mb4 --execute h=127.0.0.1,P=10307,u=msandbox,p=msandbox,D=test,t=t
No slaves found. See --recursion-method if host lalit-ThinkPad-T480 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`t`...
Creating new table...
Created new table test._t_new OK.
Altering new table...
Altered `test`.`_t_new` OK.
2019-09-13T14:22:45 Dropping new table...
2019-09-13T14:22:45 Dropped new table OK.
`test`.`t` was not altered.
The new table `test`.`_t_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
Please check you have at least one UNIQUE and NOT NULLABLE index.
pt-online-schema-change seems to complain on missing UNIQUE KEY when in fact it exist.
master [localhost:22614] {msandbox} (db) > DROP TABLE t; Query OK, 0 rows affected (0.010 sec) master [localhost:22614] {msandbox} (db) > CREATE TABLE `t` ( `s` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.017 sec) master [localhost:22614] {msandbox} (db) > SET NAMES latin1; Query OK, 0 rows affected (0.000 sec) master [localhost:22614] {msandbox} (db) > INSERT INTO t VALUES ('株式会社アネム'), ('Huuto lisääjät') ; Query OK, 2 rows affected (0.004 sec) Records: 2 Duplicates: 0 Warnings: 0 master [localhost:22614] {msandbox} (db) > SELECT * FROM t; +-----------------------+ | s | +-----------------------+ | 株式会社アネム | | Huuto lisääjät | +-----------------------+ 2 rows in set (0.000 sec) master [localhost:22614] {msandbox} (db) > ALTER TABLE t ADD UNIQUE KEY (s); Query OK, 0 rows affected (0.021 sec) Records: 0 Duplicates: 0 Warnings: 0revin@u-dell:~$ pt-online-schema-change --defaults-file=/data/msb/rsandbox_10_3_13/master/my.sandbox.cnf --alter='ENGINE=INNODB DEFAULT CHARSET=utf8mb4' --charset=utf8mb4 --execute h=127.0.0.1,P=22614,u=msandbox,p=msandbox,D=db,t=t 1> Cannot connect to A=utf8mb4,F=/data/msb/rsandbox_10_3_13/master/my.sandbox.cnf,P=22615,h=node-2,p=...,u=msandbox No slaves found. See --recursion-method if host u-dell has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `db`.`t`... Creating new table... Created new table db._t_new OK. Altering new table... Altered `db`.`_t_new` OK. 2019-09-12T10:52:34 Dropping new table... 2019-09-12T10:52:34 Dropped new table OK. `db`.`t` was not altered. The new table `db`.`_t_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger. Please check you have at least one UNIQUE and NOT NULLABLE index.