Adding _ to constraints can create issues with constraint name length
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Frank Cizmich |
Bug Description
When using pt-online-
Error creating new table: DBD::mysql::db do failed: Identifier name '_____FK_
Obviously the FK name is borderline silly in length, but when using software frameworks, you don't have much say. However, even on reasonably named constraints, you'll still have a hard limit on how many times you can use pt-online-
Proposed change, toggle between adding and removing underscores to keep the constraint name length fluctuation to one character. On line 9560, change to something like this which will look for leading underscores and strip them out if they exist, otherwise add them:
if ($sql =~ m/^ CONSTRAINT `_+/gm) {
$sql =~ s/^ CONSTRAINT `_+/ CONSTRAINT `/gm;
} else {
$sql =~ s/^ CONSTRAINT `/ CONSTRAINT `_/gm;
}
This would potentially cause issues if no-swap-tables is yes and the old table isn't dropped as it wouldn't be able to get a unique name space.. but could additionally put in a checks for the uniqueness of the constraint and fall back onto the default prepending underscores to the constraint name.
==
Please account for the situation where the current length of the CONSTRAINT
name is exactly 64 characters. Adding a 65th character will flag an error
an the max name length is 64 characters.
Troy.
#
tags: | added: foreign-keys pt-online-schema-change |
Changed in percona-toolkit: | |
status: | New → Triaged |
description: | updated |
Changed in percona-toolkit: | |
importance: | Undecided → Medium |
milestone: | none → 2.2.15 |
Changed in percona-toolkit: | |
status: | Triaged → Fix Committed |
description: | updated |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
Hi there
Has any decision or progress been made with regards to this this feature request? I really need this for my project.
In my production environment we need to OPTIMIZE a table quite often to ensure we do not run out of hard drive space. (Simply deleting unwanted/old entries from the table does not stop the table file to grow indefinitely.) The pt-online- schema- change tool works great, until I hit the FK constraint name length limit after optimizing a number of times.