include parsed col length field as integer from mysql index reflection

Added support to MySQL index reflection to correctly reflect the
``mysql_length`` dictionary, which previously was being ignored.

Fixes: #9047
Change-Id: I0a5e27123be68741e12af4464a0fa305052ec36e
(cherry picked from commit 4a31f97824095610cfdbc1ac1180fd8690f9f477)

4 files changed, 110 insertions, 3 deletions

diff --git a/doc/build/changelog/unreleased_14/9047.rst b/doc/build/changelog/unreleased_14/9047.rst
new file mode 100644
index 000000000..74110890e
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/9047.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: usecase, mysql
+    :tickets: 9047
+    :versions: 2.0.0
+
+    Added support to MySQL index reflection to correctly reflect the
+    ``mysql_length`` dictionary, which previously was being ignored.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 17916e404..9948602d3 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -3033,14 +3033,22 @@ class MySQLDialect(default.DefaultDialect):
                 ]
 
             index_d = {}
-            if dialect_options:
-                index_d["dialect_options"] = dialect_options
 
             index_d["name"] = spec["name"]
             index_d["column_names"] = [s[0] for s in spec["columns"]]
+            mysql_length = {
+                s[0]: s[1] for s in spec["columns"] if s[1] is not None
+            }
+            if mysql_length:
+                dialect_options["%s_length" % self.name] = mysql_length
+
             index_d["unique"] = unique
             if flavor:
                 index_d["type"] = flavor
+
+            if dialect_options:
+                index_d["dialect_options"] = dialect_options
+
             indexes.append(index_d)
         return indexes
 
diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py
index fae9a387e..7a4a46b34 100644
--- a/lib/sqlalchemy/dialects/mysql/reflection.py
+++ b/lib/sqlalchemy/dialects/mysql/reflection.py
@@ -322,7 +322,12 @@ class MySQLTableDefinitionParser(object):
     def _parse_keyexprs(self, identifiers):
         """Unpack '"col"(2),"col" ASC'-ish strings into components."""
 
-        return self._re_keyexprs.findall(identifiers)
+        return [
+            (colname, int(length) if length else None, modifiers)
+            for colname, length, modifiers in self._re_keyexprs.findall(
+                identifiers
+            )
+        ]
 
     def _prep_regexes(self):
         """Pre-compile regular expressions."""
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index 529d352a2..a297145ab 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -759,6 +759,93 @@ class ReflectionTest(fixtures.TestBase, AssertsCompiledSQL):
             "CREATE FULLTEXT INDEX textdata_ix ON mytable (textdata)",
         )
 
+    def test_reflect_index_col_length(self, metadata, connection):
+        """test for #9047"""
+
+        tt = Table(
+            "test_table",
+            metadata,
+            Column("signal_type", Integer(), nullable=False),
+            Column("signal_data", String(200), nullable=False),
+            Column("signal_data_2", String(200), nullable=False),
+            Index(
+                "ix_1",
+                "signal_type",
+                "signal_data",
+                mysql_length={"signal_data": 25},
+                mariadb_length={"signal_data": 25},
+            ),
+        )
+        Index(
+            "ix_2",
+            tt.c.signal_type,
+            tt.c.signal_data,
+            tt.c.signal_data_2,
+            mysql_length={"signal_data": 25, "signal_data_2": 10},
+            mariadb_length={"signal_data": 25, "signal_data_2": 10},
+        )
+
+        mysql_length = (
+            "mysql_length"
+            if not connection.dialect.is_mariadb
+            else "mariadb_length"
+        )
+        eq_(
+            {idx.name: idx.kwargs[mysql_length] for idx in tt.indexes},
+            {
+                "ix_1": {"signal_data": 25},
+                "ix_2": {"signal_data": 25, "signal_data_2": 10},
+            },
+        )
+
+        metadata.create_all(connection)
+
+        eq_(
+            sorted(
+                inspect(connection).get_indexes("test_table"),
+                key=lambda rec: rec["name"],
+            ),
+            [
+                {
+                    "name": "ix_1",
+                    "column_names": ["signal_type", "signal_data"],
+                    "unique": False,
+                    "dialect_options": {mysql_length: {"signal_data": 25}},
+                },
+                {
+                    "name": "ix_2",
+                    "column_names": [
+                        "signal_type",
+                        "signal_data",
+                        "signal_data_2",
+                    ],
+                    "unique": False,
+                    "dialect_options": {
+                        mysql_length: {
+                            "signal_data": 25,
+                            "signal_data_2": 10,
+                        }
+                    },
+                },
+            ],
+        )
+
+        new_metadata = MetaData()
+        reflected_table = Table(
+            "test_table", new_metadata, autoload_with=connection
+        )
+
+        eq_(
+            {
+                idx.name: idx.kwargs[mysql_length]
+                for idx in reflected_table.indexes
+            },
+            {
+                "ix_1": {"signal_data": 25},
+                "ix_2": {"signal_data": 25, "signal_data_2": 10},
+            },
+        )
+
     @testing.requires.mysql_ngram_fulltext
     def test_reflect_fulltext_comment(
         self,