FK-error /usr/bin/pt-online-schema-change line 9531

Reported by yvonne on 2012-12-26
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

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.

yvonne (mysql-girl) wrote :

I also tried option --alter-foreign-keys-method=none
with the same result ie it runs fine in dry-run but execute fails with the same error as listed in the bug

Daniel Nichter (daniel-nichter) wrote :

What does SHOW GLOBAL VARIABLES LIKE '%UNIQ%' ; say? Have you tried adding "foreign_key_checks=0" to --set-vars?

tags: added: foreign-keys pt-online-schema-change
Changed in percona-toolkit:
status: New → Triaged
yvonne (mysql-girl) wrote :

YES - it worked

adding --set-vars="foreign_key_checks=0"
took care of it
thx

 pt-online-schema-change -uxxxxxxxx -pxxxxx h=127.0.0.1,D=sku,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 --set-vars="foreign_key_checks=0"
No foreign keys reference `sku`.`subscription_elements`; ignoring --alter-foreign-keys-method.
Altering `sku`.`subscription_elements`...
Creating new table...
Created new table sku._subscription_elements_new OK.
Altering new table...
Altered `sku`.`_subscription_elements_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 1803540 rows...
Copying `sku`.`subscription_elements`: 16% 02:30 remain
Copying `sku`.`subscription_elements`: 28% 02:26 remain
Copying `sku`.`subscription_elements`: 39% 02:15 remain
Copying `sku`.`subscription_elements`: 50% 01:59 remain
Copying `sku`.`subscription_elements`: 64% 01:23 remain
Copying `sku`.`subscription_elements`: 75% 00:59 remain
Copying `sku`.`subscription_elements`: 87% 00:29 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `sku`.`_subscription_elements_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `sku`.`subscription_elements`.

Daniel Nichter (daniel-nichter) wrote :

Since "--set-vars foreign_key_checks=0" worked, I'll close this bug. For the record, pt-osc doesn't do any special handling of foreign keys, i.e. checking constraints, updating child tables, etc. because that would be extremely complex to implement and prone to not working right.

Changed in percona-toolkit:
status: Triaged → Invalid
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers