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.
pt-table-checksum has always had this behavior. In this latest version it is improved because it alternates adding and subtracting
Using an UUID or similar would guarantee uniqueness and has been considered before.
The ugly part is that we'd have to arbitrarily truncate very long names.
This is really a very border case. A dynamically generated name that happens to be exactly the max length allowed!
But I appreciate it's affecting you.
A random id would be the ultimate solution to name conflict, at the cost of readability perhaps.