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

Triggers dropped after pt-osc usage

jirisulajirisula Current User Role Novice
Good day,
I'm facing strange issue, not for a first time. Sometimes it appears, sometimes it does not. I'm expecting it's related to "drop_swap" method. Table stats_table2 is having 3 triggers.
pt-online-schema-change --execute --print --user=root --ask-pass --slave-user=<usr> --slave-password=<pw> --max-load "Threads_running=100" --max-lag 5 --preserve-triggers --nodrop-old-table --new-table-name "%T_techdb" --nocheck-unique-key-change --set-vars=sql_mode=\'NO_ENGINE_SUBSTITUTION\\,STRICT_TRANS_TABLES\' --recursion-method=processlist --nocheck-replication-filters --alter-foreign-keys-method=auto --alter 'CHANGE `last_update` `last_update` datetime(6) NULL AFTER `updated`;' h=127.0.0.1,D=database,t=stats_table2
after running, all looks mint:
2020-05-26T12:14:38 Copied rows OK.
2020-05-26T12:14:38 Max rows for the rebuild_constraints method: 45018
Determining the method to update foreign keys...
2020-05-26T12:14:38   `database`.`event_stats_table`: too many rows: 44141423; must use drop_swap
2020-05-26T12:14:38 Skipping triggers creation since --no-swap-tables was specified along with --drop-new-table
2020-05-26T12:14:38 Drop-swapping tables...
2020-05-26T12:14:38 Analyzing new table...
SET foreign_key_checks=0
DROP TABLE IF EXISTS `database`.`stats_table2`
RENAME TABLE `database`.`stats_table2_techdb` TO `database`.`stats_table2`
2020-05-26T12:14:39 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2020-05-26T12:14:39 Dropping triggers...
DROP TRIGGER IF EXISTS `database`.`pt_osc_datacore_stats_table2_del`
DROP TRIGGER IF EXISTS `database`.`pt_osc_datacore_stats_table2_upd`
DROP TRIGGER IF EXISTS `database`.`pt_osc_datacore_stats_table2_ins`
2020-05-26T12:14:39 Dropped triggers OK.
Successfully altered `database`.`stats_table2`.
At this moment table stats_table2 is not having 3 triggers. I'm always dumping triggers before running pt-osc - and in case of loosing triggers I'm importing them back again.
Anyone faced similar behaviour, am I missing something in parameters? Any advice is highly appreciated! Thanks!

Best Answer

Answers

  • jirisulajirisula Current User Role Novice
    Thanks! Will try my best to cut related tables/dataset from our system! Appreciating quick answer!
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.