set identifier length for MySQL constraints to 64

The rule to limit index names to 64 also applies to all
DDL names, such as those coming from naming conventions.
Add another limiting variable for constraint names and
create test cases against all constraint types.

Additionally, codified in the test suite MySQL's lack of
support for naming of a FOREIGN KEY constraint after
the name was given, which apparently assigns the name to an
associated KEY but not the constraint itself, until MySQL 8
and MariaDB 10.5 which appear to have resolved the
behavior.  However it's not clear how Alembic hasn't had
issues reported with this so far.

Fixed long-lived bug in MySQL dialect where the maximum identifier length
of 255 was too long for names of all types of constraints, not just
indexes, all of which have a size limit of 64. As metadata naming
conventions can create too-long names in this area, apply the limit to the
identifier generator within the DDL compiler.

Fixes: #5898
Change-Id: I79549474845dc29922275cf13321c07598dcea08

1 files changed, 7 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 353de2c48..3925b251d 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -4793,13 +4793,19 @@ class IdentifierPreparer(object):
             name = constraint.name
 
         if isinstance(name, elements._truncated_label):
+            # calculate these at format time so that ad-hoc changes
+            # to dialect.max_identifier_length etc. can be reflected
+            # as IdentifierPreparer is long lived
             if constraint.__visit_name__ == "index":
                 max_ = (
                     self.dialect.max_index_name_length
                     or self.dialect.max_identifier_length
                 )
             else:
-                max_ = self.dialect.max_identifier_length
+                max_ = (
+                    self.dialect.max_constraint_name_length
+                    or self.dialect.max_identifier_length
+                )
             if len(name) > max_:
                 name = name[0 : max_ - 8] + "_" + util.md5_hex(name)[-4:]
         else: