Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-144

Constraint name is too long

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.0.3
    • Fix Version/s: 3.0.4
    • Component/s: None
    • Labels:
      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

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              carlos.salguero Carlos Salguero
              Reporter:
              carlos.salguero Carlos Salguero
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: