Skip to content

MySQL views reflected without all keywords #3613

Closed
@sqlalchemy-bot

Description

@sqlalchemy-bot
Collaborator

Migrated issue, originally created by Michael Bayer (@zzzeek)

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

Activity

sqlalchemy-bot

sqlalchemy-bot commented on Dec 22, 2015

@sqlalchemy-bot
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

  • An adjustment to the regular expression used to parse MySQL views,
    such that we no longer assume the "ALGORITHM" keyword is present in
    the reflected view source, as some users have reported this not being
    present in some Amazon RDS environments.
    fixes MySQL views reflected without all keywords #3613

8c54b14

sqlalchemy-bot

sqlalchemy-bot commented on Dec 22, 2015

@sqlalchemy-bot
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

  • An adjustment to the regular expression used to parse MySQL views,
    such that we no longer assume the "ALGORITHM" keyword is present in
    the reflected view source, as some users have reported this not being
    present in some Amazon RDS environments.
    fixes MySQL views reflected without all keywords #3613

(cherry picked from commit 8c54b14)

40cc8f4

sqlalchemy-bot

sqlalchemy-bot commented on Dec 22, 2015

@sqlalchemy-bot
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
added this to the 1.0.xx milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingmysql

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions

          MySQL views reflected without all keywords · Issue #3613 · sqlalchemy/sqlalchemy