Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.0.3
-
None
-
None
Description
pt-online-schema-change is adding an underscore to the new table constraint's name, and it's causing the problem when FK name length is already 64 characters long.
It's happening in the latest version 2.2.15.
Example output (made with `--dry-run` option):
Error creating new table: DBD::mysql::db do failed: Identifier name '_suresc_formulary_id_551efe76791cde00_fk_surescripts_formulary_id' is too long [for Statement "CREATE TABLE `chronometer_production`.`_surescripts_formularydrug_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`syn_id` int(11) DEFAULT NULL,
`pkg_product_id` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
`status` int(11) NOT NULL,
`relative_cost` int(11) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`formulary_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `surescripts_formularydrug_cfb5099b` (`syn_id`),
KEY `surescripts_formularydrug_83b60bd0` (`pkg_product_id`),
KEY `surescripts_formularydrug_618299a8` (`formulary_id`),
CONSTRAINT `_suresc_formulary_id_551efe76791cde00_fk_surescripts_formulary_id` FOREIGN KEY (`formulary_id`) REFERENCES `surescripts_formulary` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=410072014 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED"] at /usr/bin/pt-online-schema-change line 9890.
I realize that the FK name is too long, but it's generated automatically by our software framework, so we don't have any control over it.
It might be a better idea to use UUIDs or something similar to generate new constraint names.
Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1491674