Merge "dont compare unique constraint and index sigs to each other" into main

3 files changed, 89 insertions, 5 deletions

diff --git a/alembic/autogenerate/compare.py b/alembic/autogenerate/compare.py
index b489328..5727891 100644
--- a/alembic/autogenerate/compare.py
+++ b/alembic/autogenerate/compare.py
@@ -452,7 +452,9 @@ class _uq_constraint_sig(_constraint_sig):
     def __init__(self, const: UniqueConstraint) -> None:
         self.const = const
         self.name = const.name
-        self.sig = tuple(sorted([col.name for col in const.columns]))
+        self.sig = ("UNIQUE_CONSTRAINT",) + tuple(
+            sorted([col.name for col in const.columns])
+        )
 
     @property
     def column_names(self) -> List[str]:
@@ -465,7 +467,7 @@ class _ix_constraint_sig(_constraint_sig):
     def __init__(self, const: Index, impl: DefaultImpl) -> None:
         self.const = const
         self.name = const.name
-        self.sig = impl.create_index_sig(const)
+        self.sig = ("INDEX",) + impl.create_index_sig(const)
         self.is_unique = bool(const.unique)
 
     def md_name_to_sql_name(self, context: AutogenContext) -> Optional[str]:
@@ -807,11 +809,11 @@ def _compare_indexes_and_uniques(
         if not conn_obj.is_index and conn_obj.sig in unnamed_metadata_uniques:
             continue
         elif removed_name in doubled_constraints:
+            conn_uq, conn_idx = doubled_constraints[removed_name]
             if (
-                conn_obj.sig not in metadata_indexes_by_sig
-                and conn_obj.sig not in metadata_uniques_by_sig
+                conn_idx.sig not in metadata_indexes_by_sig
+                and conn_uq.sig not in metadata_uniques_by_sig
             ):
-                conn_uq, conn_idx = doubled_constraints[removed_name]
                 obj_removed(conn_uq)
                 obj_removed(conn_idx)
         else:
diff --git a/docs/build/unreleased/1240.rst b/docs/build/unreleased/1240.rst
new file mode 100644
index 0000000..19cfd81
--- /dev/null
+++ b/docs/build/unreleased/1240.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, mysql, regression
+    :tickets: 1240
+
+    Fixed regression caused by :ticket:`1166` released in version 1.10.0 which
+    caused MySQL unique constraints with multiple columns to not compare
+    correctly within autogenerate, due to different sorting rules on unique
+    constraints vs. indexes, which in MySQL are shared constructs.
diff --git a/tests/test_autogen_indexes.py b/tests/test_autogen_indexes.py
index f697e5a..abefd66 100644
--- a/tests/test_autogen_indexes.py
+++ b/tests/test_autogen_indexes.py
@@ -463,6 +463,80 @@ class AutogenerateUniqueIndexTest(AutogenFixtureTest, TestBase):
         eq_(diffs, [])
 
     @config.requirements.unique_constraint_reflection
+    def test_nothing_changed_cols_unsorted(self):
+        """test #1240
+
+
+        MySQL doubles unique constraints as indexes, so we need to make
+        sure we aren't comparing index sigs to unique constraint sigs,
+        which we were doing previously by mistake.  As their signatures
+        were compatible, things "worked" but once index sigs changed
+        col name sorting order, it broke.
+
+        """
+
+        m1 = MetaData()
+        m2 = MetaData()
+
+        Table(
+            "nothing_changed",
+            m1,
+            Column("id", Integer, primary_key=True),
+            Column("sid", Integer, nullable=False),
+            Column("label", String(30), nullable=False),
+            Column("fid", Integer, nullable=False),
+            UniqueConstraint("sid", "label"),
+            UniqueConstraint("sid", "fid"),
+        )
+
+        Table(
+            "nothing_changed",
+            m2,
+            Column("id", Integer, primary_key=True),
+            Column("sid", Integer, nullable=False),
+            Column("label", String(30), nullable=False),
+            Column("fid", Integer, nullable=False),
+            UniqueConstraint("sid", "label"),
+            UniqueConstraint("sid", "fid"),
+        )
+
+        diffs = self._fixture(m1, m2)
+        eq_(diffs, [])
+
+    @config.requirements.unique_constraint_reflection
+    @config.requirements.reports_unnamed_constraints
+    def test_remove_uq_constraint(self):
+        """supplementary test for codepath in #1240"""
+
+        m1 = MetaData()
+        m2 = MetaData()
+
+        Table(
+            "something_changed",
+            m1,
+            Column("id", Integer, primary_key=True),
+            Column("sid", Integer, nullable=False),
+            Column("label", String(30), nullable=False),
+            Column("fid", Integer, nullable=False),
+            UniqueConstraint("sid", "label"),
+            UniqueConstraint("sid", "fid"),
+        )
+
+        Table(
+            "something_changed",
+            m2,
+            Column("id", Integer, primary_key=True),
+            Column("sid", Integer, nullable=False),
+            Column("label", String(30), nullable=False),
+            Column("fid", Integer, nullable=False),
+            UniqueConstraint("sid", "fid"),
+        )
+
+        diffs = self._fixture(m1, m2)
+        assert len(diffs) == 1
+        assert diffs[0][0] in ("remove_index", "remove_constraint")
+
+    @config.requirements.unique_constraint_reflection
     def test_uq_casing_convention_changed_so_put_drops_first(self):
         m1 = MetaData()
         m2 = MetaData()