DRYRUN works but not EXECUTE WHY??
even though I set
mysql> show variables like '%UNIQ%' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| unique_checks | OFF |
+---------------+-------+
Server version: 5.5.28-rel29.1-log Percona Server with XtraDB (GPL), Release rel29.1, Revision 334
pt-online-schema-change --version
pt-online-schema-change 2.1.7
pt-online-schema-change -uYYYYYYY -pXXXXXXX h=127.0.0.1,D=u ,t=subscription_elements -P3306 --alter "ADD COLUMN country_id TINYINT(3) UNSIGNED default 228 NOT NULL, ADD COLUMN language_id TINYINT(3) UNSIGNED default 44 NOT NULL, ADD CONSTRAINT fk_se_country_1 FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT fk_se_language_1 FOREIGN KEY (language_id) REFERENCES language(language_id) ON DELETE NO ACTION ON UPDATE NO ACTION" --alter-foreign-keys-method=auto --dry-run
No foreign keys reference `u`.`subscription_elements`; ignoring --alter-foreign-keys-method.
Starting a dry run. `u`.`subscription_elements` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table u._subscription_elements_new OK.
Altering new table...
Altered `u`.`_subscription_elements_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
Dropping new table...
Dropped new table OK.
Dry run complete. `u`.`subscription_elements` was not altered.
pt-online-schema-change -uYYYYYY -pXXXXXX h=127.0.0.1,D=u,t=subscription_elements -P3306 --alter "ADD COLUMN country_id TINYINT(3) UNSIGNED default 228 NOT NULL, ADD COLUMN language_id TINYINT(3) UNSIGNED default 44 NOT NULL, ADD CONSTRAINT fk_se_country_1 FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT fk_se_language_1 FOREIGN KEY (language_id) REFERENCES language(language_id) ON DELETE NO ACTION ON UPDATE NO ACTION" --alter-foreign-keys-method=auto --execute
No foreign keys reference `u`.`subscription_elements`; ignoring --alter-foreign-keys-method.
Altering `u`.`subscription_elements`...
Creating new table...
Created new table u._subscription_elements_new OK.
Altering new table...
Altered `u`.`_subscription_elements_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 2195264 rows...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`u`.`subscription_elements` was not altered.
Error copying rows from `u`.`subscription_elements` to `u`.`_subscription_elements_new`: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`u`.`_subscription_elements_new`, CONSTRAINT `_fk_subscription_elements_subscriptions1` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`subscription_id`) ON DELETE NO ACTION ON ) [for Statement "INSERT LOW_PRIORITY IGNORE INTO `u`.`_subscription_elements_new` (`id`, `subscription_id`, `u_id`, `price`, `status`, `currency`, `qty`, `term_begin_date`, `term_end_date`, `cancel_request_date`, `payment_date`, `pay_cycle_unit`, `comments`, `last_txn_id`, `created_on`, `updated_on`, `message_ts`, `change_request_date`) SELECT `id`, `subscription_id`, `u_id`, `price`, `status`, `currency`, `qty`, `term_begin_date`, `term_end_date`, `cancel_request_date`, `payment_date`, `pay_cycle_unit`, `comments`, `last_txn_id`, `created_on`, `updated_on`, `message_ts`, `change_request_date` FROM `u`.`subscription_elements` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) /*pt-online-schema-change 24686 copy nibble*/" with ParamValues: 1='31257', 0='20703'] at /usr/bin/pt-online-schema-change line 9531.
I also tried option --alter-
with the same result ie it runs fine in dry-run but execute fails with the same error as listed in the bug