LP #1727137: pt-online-schema change fails if foreign key name is too long
General
Escalation
General
Escalation
Description
Environment
None
Attachments
1
- 04 Dec 2018, 01:48 PM
Smart Checklist
Activity
Show:
Dhruv Raj SIngh Rathore December 4, 2018 at 1:50 PM
the code clearly only adds __ to foreign key name and does not remove the last character if it exceeds the max length
Dhruv Raj SIngh Rathore December 4, 2018 at 1:48 PM
Lalit Choudhary December 4, 2018 at 1:42 PM
Hi @Dhruv Raj SIngh Rathore,
Validated as described. pt-online-schema-change 3.0.12
Reproduciable testcase:
create table base_statushistory (
id int(11) auto_increment not null,
PRIMARY KEY (id)) engine=InnoDB;
CREATE TABLE `fsm_supporteddocsforrejection` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status_history_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fsm_supporteddocsforrejection_e0b206d8` (`status_history_id`),
CONSTRAINT `fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id` FOREIGN KEY (`status_history_id`) REFERENCES `base_statushistory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
$ pt-online-schema-change --alter='add column test varchar(30) default null' --alter-foreign-keys-method='rebuild_constraints' --execute --print D=test,t=base_statushistory,u=msandbox,p=msandbox --socket=/tmp/mysql_sandbox5723.sock
No slaves found. See --recursion-method if host lalit-ThinkPad-T480 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
Child tables:
`test`.`fsm_supporteddocsforrejection` (approx. 1 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `test`.`base_statushistory`...
Creating new table...
CREATE TABLE `test`.`_base_statushistory_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Created new table test._base_statushistory_new OK.
Altering new table...
ALTER TABLE `test`.`_base_statushistory_new` add column test varchar(30) default null
Altered `test`.`_base_statushistory_new` OK.
2018-12-04T19:05:30 Creating triggers...
2018-12-04T19:05:30 Created triggers OK.
2018-12-04T19:05:30 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_base_statushistory_new` (`id`) SELECT `id` FROM `test`.`base_statushistory` LOCK IN SHARE MODE /*pt-online-schema-change 31080 copy table*/
2018-12-04T19:05:30 Copied rows OK.
2018-12-04T19:05:30 Analyzing new table...
2018-12-04T19:05:30 Swapping tables...
RENAME TABLE `test`.`base_statushistory` TO `test`.`_base_statushistory_old`, `test`.`_base_statushistory_new` TO `test`.`base_statushistory`
2018-12-04T19:05:30 Swapped original and new tables OK.
2018-12-04T19:05:30 Rebuilding foreign key constraints...
ALTER TABLE `test`.`fsm_supporteddocsforrejection` DROP FOREIGN KEY `fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id`, ADD CONSTRAINT `_fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id` FOREIGN KEY (`status_history_id`) REFERENCES `test`.`base_statushistory` (`id`)
2018-12-04T19:05:30 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_base_statushistory_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_base_statushistory_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_base_statushistory_ins`
2018-12-04T19:05:30 Dropped triggers OK.
Altered `test`.`base_statushistory` but there were errors or warnings.
Error updating foreign key constraints: 2018-12-04T19:05:30 DBD::mysql::db do failed: Identifier name '_fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id' is too long [for Statement "ALTER TABLE `test`.`fsm_supporteddocsforrejection` DROP FOREIGN KEY `fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id`, ADD CONSTRAINT `_fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id` FOREIGN KEY (`status_history_id`) REFERENCES `test`.`base_statushistory` (`id`)"] at /home/lalit/perl5/bin/pt-online-schema-change line 10909.
mysql [localhost] {msandbox} (test) > show create table fsm_supporteddocsforrejection\G
*************************** 1. row ***************************
Table: fsm_supporteddocsforrejection
Create Table: CREATE TABLE `fsm_supporteddocsforrejection` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status_history_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fsm_supporteddocsforrejection_e0b206d8` (`status_history_id`),
CONSTRAINT `fsm__status_history_id_1a912e00de447938_fk_base_statushistory_id` FOREIGN KEY (`status_history_id`) REFERENCES `_base_statushistory_old` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Dhruv Raj SIngh Rathore December 4, 2018 at 11:58 AM
I am using pt-online-schema-change 3.0.12
Lalit Choudhary December 4, 2018 at 11:54 AM
@Dhruv Raj SIngh Rathore , thank you for the update.
what is the version of pt-online-schema-change you are using?
pt-online-schema-change --version
Reopened
Details
Details
Assignee
Unassigned
UnassignedReporter
lpjirasyncPriority
LowAffects versions
Smart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created January 24, 2018 at 9:46 PM
Updated January 13, 2024 at 8:50 PM
Resolved December 4, 2018 at 1:43 PM
**Reported in Launchpad by Aleksandr Kuzminsky last update 25-10-2017 02:39:44
MySQL poses a limit on max length of foreign key name.
pt-osc renames foreign key by adding a prefix. If the initial FK name is as long as the limit renaming the FK will fail. pt-osc subsequently fails as well.
After the failure pt-osc leaves tables in broken state. The table is renamed but foreign keys refer to the _old table.