MySQL views reflected without all keywords

locally, a MySQL view reflected works from data reported like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`scott`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `x`.`a` AS `a`,`x`.`b` AS `b` from `x`

that is, it's injecting every term we see at http://dev.mysql.com/doc/refman/5.7/en/create-view.html into the output.

However we have an amazon RDS user reporting that this is failing for one environment, and it is likely that this env. has some MySQL version that isn't putting all those keywords in there.

The patch below includes a fix for this as well as a test, though my local MySQL version doesn't reproduce the reported failing case:

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 9887464..12405db 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2804,7 +2804,7 @@ class MySQLDialect(default.DefaultDialect):
             schema, table_name))
         sql = self._show_create_table(connection, None, charset,
                                       full_name=full_name)
-        if sql.startswith('CREATE ALGORITHM'):
+        if re.match(r'CREATE (?:ALGORITHM)?.* VIEW', sql):
             # Adapt views to something table-like.
             columns = self._describe_table(connection, None, charset,
                                            full_name=full_name)
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index a288762..b8cbea8 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -397,6 +397,37 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         finally:
             meta.drop_all()

+    @testing.provide_metadata
+    def test_view_reflection(self):
+        Table('x', self.metadata, Column('a', Integer), Column('b', String(50)))
+        self.metadata.create_all()
+
+        with testing.db.connect() as conn:
+            conn.execute("CREATE VIEW v1 AS SELECT * FROM x")
+            conn.execute(
+                "CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM x")
+            conn.execute(
+                "CREATE ALGORITHM=UNDEFINED VIEW v3 AS SELECT * FROM x")
+            conn.execute(
+                "CREATE DEFINER=CURRENT_USER VIEW v4 AS SELECT * FROM x")
+
+        @event.listens_for(self.metadata, "before_drop")
+        def cleanup(*arg, **kw):
+            with testing.db.connect() as conn:
+                for v in ['v1', 'v2', 'v3', 'v4']:
+                    conn.execute("DROP VIEW %s" % v)
+
+        insp = inspect(testing.db)
+        for v in ['v1', 'v2', 'v3', 'v4']:
+            eq_(
+                [
+                    (col['name'], col['type'].__class__)
+                    for col in insp.get_columns(v)
+                ],
+                [('a', mysql.INTEGER), ('b', mysql.VARCHAR)]
+            )
+
+
     @testing.exclude('mysql', '<', (5, 0, 0), 'no information_schema support')
     def test_system_views(self):
         dialect = testing.db.dialect

Comments (2)