Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MySQL 5.7.8 Mysql::Error: SELECT command denied to user 'rails'@'localhost' for table 'session_variables': SHOW VARIABLES WHERE Variable_name = 'character_set_client' (ActiveRecord::StatementInvalid) #21108

Closed
yahonda opened this issue Aug 3, 2015 · 2 comments
Closed

MySQL 5.7.8 Mysql::Error: SELECT command denied to user 'rails'@'localhost' for table 'session_variables': SHOW VARIABLES WHERE Variable_name = 'character_set_client' (ActiveRecord::StatementInvalid) #21108

yahonda opened this issue Aug 3, 2015 · 2 comments

Comments

@yahonda
Copy link
Member

yahonda commented Aug 3, 2015

Once #21107 workaround implemented, rake test_mysql shows this error when tested with MySQL 5.7.8

Mysql::Error: SELECT command denied to user 'rails'@'localhost' for table 'session_variables': SHOW VARIABLES WHERE Variable_name = 'character_set_client' (ActiveRecord::StatementInvalid)

It is easy to reproduce as follows.

$ mysql -urails
mysql> SHOW VARIABLES WHERE Variable_name = 'character_set_client' ;
ERROR 1142 (42000): SELECT command denied to user 'rails'@'localhost' for table 'session_variables'
mysql>

```ruby
$ rake test_mysql
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:317:in `query': Mysql::Error: SELECT command denied to user 'rails'@'localhost' for table 'session_variables': SHOW VARIABLES WHERE Variable_name = 'character_set_client' (ActiveRecord::StatementInvalid)
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:317:in `block in exec_without_stmt'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:509:in `block in log'
        from /home/yahonda/git/rails/activesupport/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:503:in `log'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:316:in `exec_without_stmt'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:233:in `exec_query'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:225:in `client_encoding'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:460:in `set_field_encoding'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:575:in `block (2 levels) in columns'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:101:in `block in each_hash'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:99:in `each_hash'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:99:in `each_hash'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:574:in `each'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:574:in `map'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:574:in `block in columns'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:346:in `execute_and_free'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:573:in `columns'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/schema_cache.rb:51:in `columns'
        from /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/schema_cache.rb:57:in `columns_hash'
        from /home/yahonda/git/rails/activerecord/lib/active_record/model_schema.rb:311:in `load_schema!'
        from /home/yahonda/git/rails/activerecord/lib/active_record/attributes.rb:231:in `load_schema!'
        from /home/yahonda/git/rails/activerecord/lib/active_record/attribute_decorators.rb:28:in `load_schema!'
        from /home/yahonda/git/rails/activerecord/lib/active_record/model_schema.rb:306:in `load_schema'
        from /home/yahonda/git/rails/activerecord/lib/active_record/model_schema.rb:234:in `attribute_types'
        from /home/yahonda/git/rails/activerecord/lib/active_record/model_schema.rb:239:in `type_for_attribute'
        from /home/yahonda/git/rails/activerecord/lib/active_record/table_metadata.rb:28:in `type'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/predicate_builder.rb:111:in `block in create_binds_for_hash'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/predicate_builder.rb:100:in `each'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/predicate_builder.rb:100:in `create_binds_for_hash'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/predicate_builder.rb:33:in `create_binds'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/where_clause_factory.rb:19:in `build'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/query_methods.rb:566:in `where!'
        from /home/yahonda/git/rails/activerecord/lib/active_record/relation/query_methods.rb:556:in `where'
        from /home/yahonda/git/rails/activerecord/lib/active_record/querying.rb:10:in `where'
        from /home/yahonda/git/rails/activerecord/test/models/developer.rb:137:in `<class:CallableDeveloperCalledDavid>'
        from /home/yahonda/git/rails/activerecord/test/models/developer.rb:135:in `<top (required)>'
        from /home/yahonda/git/rails/activesupport/lib/active_support/dependencies.rb:302:in `require'
        from /home/yahonda/git/rails/activesupport/lib/active_support/dependencies.rb:302:in `block in require'
        from /home/yahonda/git/rails/activesupport/lib/active_support/dependencies.rb:268:in `load_dependency'
        from /home/yahonda/git/rails/activesupport/lib/active_support/dependencies.rb:302:in `require'
        from /home/yahonda/git/rails/activerecord/test/cases/relations_test.rb:9:in `<top (required)>'
        from /home/yahonda/.rvm/rubies/ruby-2.2.2/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /home/yahonda/.rvm/rubies/ruby-2.2.2/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /home/yahonda/.rvm/rubies/ruby-2.2.2/lib/ruby/2.2.0/rake/rake_test_loader.rb:15:in `block in <main>'
        from /home/yahonda/.rvm/rubies/ruby-2.2.2/lib/ruby/2.2.0/rake/rake_test_loader.rb:4:in `select'
        from /home/yahonda/.rvm/rubies/ruby-2.2.2/lib/ruby/2.2.0/rake/rake_test_loader.rb:4:in `<main>'
rake aborted!
  • Workaround is setting show_compatibility_56 = on in /etc/my.cnf and restart mysql server.
@yahonda yahonda mentioned this issue Aug 21, 2015
Merged
jbbarth pushed a commit to redmine/redmine that referenced this issue Aug 29, 2015
travis: allow test failures on MySQL 5.7
70c6181 
<pre>
ActiveRecord::StatementInvalid: Mysql2::Error:
  Table 'performance_schema.session_variables' doesn't exist:
  SHOW VARIABLES LIKE 'character_set_database'
</pre>

Refer:

* rails/rails#21108
* rails/rails#21318

git-svn-id: http://svn.redmine.org/redmine/trunk@14525 e93f8b46-1217-0410-a6f0-8f06a7374b81
jbbarth pushed a commit to redmine/redmine that referenced this issue Aug 29, 2015
Merged r14525 from trunk to 3.1-stable
225669d 
travis: allow test failures on MySQL 5.7

<pre>
ActiveRecord::StatementInvalid: Mysql2::Error:
  Table 'performance_schema.session_variables' doesn't exist:
  SHOW VARIABLES LIKE 'character_set_database'
</pre>

Refer:

* rails/rails#21108
* rails/rails#21318

git-svn-id: http://svn.redmine.org/redmine/branches/3.1-stable@14526 e93f8b46-1217-0410-a6f0-8f06a7374b81
@cthiel
Copy link

cthiel commented Nov 23, 2015

This seems to be an issue with mysql 5.7.9 being the default in homebrew these days and rails migrations not working correctly, since it seems to be missing from 4-2-stable branch.

@yahonda yahonda mentioned this issue Nov 23, 2015
Merged
@danielrhodes
Copy link
Contributor

I ran into this problem after upgrading my homebrew MySQL. The problem here is that if you are upgrading from an older version of MySQL, some tables that the new MySQL uses will be out-of-date/non-existent. I was able to solve this by running:

mysql_upgrade -u root -p --force

After that, everything worked as normal.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@cthiel @rafaelfranca @yahonda @danielrhodes