- changed status to resolved
MySQL views reflected without all keywords
Issue #3613
resolved
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)
-
reporter -
reporter - 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
#3613
(cherry picked from commit 8c54b14b5c0feee41bd9b9032f6b17b2dbd560a9)
→ <<cset 40cc8f44676d>>
- 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
- Log in to comment
#3613→ <<cset 8c54b14b5c0f>>