pt-osc does not work if FOREIGN KEY ON DELETE CASCADE ON UPDATE NO ACTION

Polyglot Projects Percona Toolkit
1

Hello,

pt-online-schema-change --alter-foreign-keys-method=rebuild_constraints --alter “ENGINE=INNODB” D=test,t=obs_raw --print --execute

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
No foreign keys reference test.obs_raw; ignoring --alter-foreign-keys-method.
Altering test.obs_raw…
Creating new table…
CREATE TABLE test._obs_raw_new (
obref char(16) NOT NULL,
cation varchar(100) NOT NULL DEFAULT ‘’,
ate varchar(100) NOT NULL DEFAULT ‘’,
gion varchar(50) NOT NULL,
cation_raw varchar(100) DEFAULT ‘’,
ate_raw varchar(100) DEFAULT ‘’,
gion_raw varchar(50) DEFAULT NULL,
ary_raw varchar(100) DEFAULT ‘’,
ost_date_raw char(100) DEFAULT NULL,
expiry_date_raw char(100) DEFAULT NULL,
last_update datetime NOT NULL,
KEY obs_raw_jobs1 (obref),
CONSTRAINT _FK_obs_raw_obs1 FOREIGN KEY (obref) REFERENCES obs (obref) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Created new table test._obs_raw_new OK.
Altering new table…
ALTER TABLE test._obs_raw_new ENGINE=INNODB
Altered test._obs_raw_new OK.
2018-06-25T22:14:28 Dropping new table…
DROP TABLE IF EXISTS test._obs_raw_new;
2018-06-25T22:14:28 Dropped new table OK.
test.obs_raw was not altered.
The new table test._obs_raw_new does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

Can someone guide me ? - I am just trying to optimise my table.

Thanks!
ar

2

Hi

There are no PRIMARY or UNIQUE keys so, there is no way to create the necessary triggers needed by the tool.
How many rows the table has? Which MySQL version are you using?
Maybe you could try using a different tool that doesn’t require triggers like GitHub’s gh-ost.

Regards

3

| obs_raw | CREATE TABLE obs_raw (
obref char(16) NOT NULL,
cation varchar(100) NOT NULL DEFAULT ‘’,
ate varchar(100) NOT NULL DEFAULT ‘’,
gion varchar(50) NOT NULL,
cation_raw varchar(100) DEFAULT ‘’,
ate_raw varchar(100) DEFAULT ‘’,
gion_raw varchar(50) DEFAULT NULL,
ary_raw varchar(100) DEFAULT ‘’,
ost_date_raw char(100) DEFAULT NULL,
expiry_date_raw char(100) DEFAULT NULL,
last_update datetime NOT NULL,
KEY obs_raw_jobs1 (obref),
CONSTRAINT FK_obs_raw_jobs1 FOREIGN KEY (obref) REFERENCES obs (obref) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT |