Closed
Description
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
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
No branches or pull requests
Activity
sqlalchemy-bot commentedon Dec 22, 2015
Michael Bayer (@zzzeek) wrote:
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 commentedon Dec 22, 2015
Michael Bayer (@zzzeek) wrote:
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 commentedon Dec 22, 2015
Changes by Michael Bayer (@zzzeek):