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

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

ashoknixashoknix EntrantCurrent User Role Beginner
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

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    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
  • ashoknixashoknix Entrant Current User Role Beginner
    | 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 |
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.