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

pt-online-schema-change impossible to create triggers

ipalausipalaus EntrantInactive User Role Beginner

I'm trying to alter a table with about ~50m rows, but keeps crashing every time I try to run the command. I've used the tool successfully in other alterations of the same database, so I'm not sure what's going on.
# pt-online-schema-change  --critical-load Threads_running=100 --alter "ADD COLUMN checked_at INT(11) UNSIGNED NOT NULL DEFAULT '0'" u=root,p=XXX,h=,D=YYY,t=objects --execute
No slaves found.  See --recursion-method if host 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 `YYY`.`objects`...
Creating new table...
Created new table YYY._objects_new OK.
Altering new table...
Altered `YYY`.`_objects_new` OK.
2016-02-08T07:36:03 Creating triggers...
2016-02-08T07:46:14 Dropping triggers...
2016-02-08T07:46:14 Dropped triggers OK.
2016-02-08T07:46:14 Dropping new table...
2016-02-08T07:46:14 Dropped new table OK.
`YYY`.`objects` was not altered.
Error creating triggers: 2016-02-08T07:46:14 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "CREATE TRIGGER `pt_osc_yyy_objects_del` AFTER DELETE ON `yyy`.`objects` FOR EACH ROW DELETE IGNORE FROM `yyy`.`_objects_new` WHERE `yyy`.`_objects_new`.`objectid` <=> OLD.`objectid`"] at /usr/bin/pt-online-schema-change line 10583.

# pt-online-schema-change --version
pt-online-schema-change 2.2.16
mysql> SHOW VARIABLES LIKE "%version%";

| Variable_name           | Value                        |
| innodb_version          | 5.6.28                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.28-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
7 rows in set (0.02 sec)

Any possible work around for this?

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.