SQLAlchemy fails for MySQL 5.7.8 with a security error

[Joseph Schorr]

Apologies if this has already been addressed; a search did not find any references.

On MySQL 5.7.8, trying to connect via SQLAlchemy results in the following exception:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1142, u"SELECT command denied to user 'testuser'@'192.168.59.3' for table 'session_variables'") [SQL: "SHOW VARIABLES LIKE 'sql_mode'"]

This exception is raised because of a recent security change made by Oracle to MySQL. Details on the issue and some possible solutions can be found here: http://code.openark.org/blog/mysql/baffling-5-7-globalstatus-variables-issues-unclean-migration-path

Is there a recommended approach to working around this issue?

[Mike Bayer]

most disturbingly, the docs right here: https://dev.mysql.com/doc/refman/5.7/en/show-variables.html which have a big note about the new / but going away "show compatibility" flag at the top, leads right into the second paragraph:

SHOW VARIABLES shows the values of MySQL system variables (see Section 5.1.4, “Server System Variables”). This statement does not require any privilege. It requires only the ability to connect to the server.

which is obviously a lie. the blog post you refer to at http://code.openark.org/blog/mysql/baffling-5-7-globalstatus-variables-issues-unclean-migration-path is one of very few articles about this change as of yet, but I'm not able to follow the author's fairly hysterical style of writing. Rails seems like they want to just replace this with a SELECT from @@, I guess they are trying to support DBs that have this flag set that way, but...I disagree with that.

Looking at https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56, it appears that they've reversed themselves on this decision:

When show_compatibility_56=OFF, the SHOW VARIABLES and SHOW STATUS statements display rows from the Performance Schema global_variables, session_variables, global_status, and session_status tables.

As of MySQL 5.7.9, those tables are world readable and accessible without the SELECT privilege, which means that SELECT is not needed to use the SHOW statements, either. Before MySQL 5.7.9, the SELECT privilege is required to access those Performance Schema tables, either directly, or indirectly through the SHOW statements.

but...the current release is 5.7.8.

So I'd advise setting show_compatibility_56 = on in /etc/my.cnf . I'm guessing I'm going to hear about this more, though I don't yet see anything I want to do in SQLA (at most, I'd put some docs in). The reason I don't even want to put docs in is because, I've found that in the past I tend to write the best docs on these database gotchas out of anyone, and then people googling for info find our docs for it even though they're not using SQLAlchemy. Oracle would appear to have flat out broken the SHOW VARIABLES statement, and this is going to break a lot more than replication. We'll see how much this gets reported.

[Mike Bayer]

going to stay quiet about Oracle breaking the world for everyone until the information becomes more widespread.

[Mike Bayer]

Also, you can just do the GRANT, simple enough: "GRANT SELECT ON performance_schema.global_variables TO '...' ", as long as you're using 5.7.8.

[Joseph Schorr]

Would the grant be allowed with the non-root user? If so, that seems a good workaround.

[Mike Bayer]

to actually run the grant you need administrator privileges

[Joseph Schorr]

Right, which also allows the original call to work. I guess, for now, we'll have to have customers either set the flag manually or add the grant manually.