search

brianmario / mysql2

A modern, simple and very fast Mysql library for Ruby - binding to libmysql
http://github.com/brianmario/mysql2
MIT License
2.25k stars 550 forks source link

MemSQL compatibilty #836

Closed tgeselle closed 7 years ago

tgeselle commented 7 years ago

I'm trying to configure a new Rails 5.0.2 project with MemSQL 5.7.4. I'm using the gem mysql in version 0.4.5. I also use a small model called Report that I created manually in my MemSQL's DB.

I have this issue with every request I try to send to MemSQL:

rails db:migrate
rails aborted!
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.' at line 1
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:85:in `_query'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:85:in `block in query'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:84:in `handle_interrupt'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:84:in `query'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:882:in `configure_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:119:in `configure_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:45:in `initialize'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:26:in `new'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:26:in `mysql2_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:729:in `new_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:773:in `checkout_new_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:752:in `try_to_checkout_new_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:713:in `acquire_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:490:in `checkout'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:364:in `connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:883:in `retrieve_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_handling.rb:128:in `retrieve_connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_handling.rb:91:in `connection'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/migration.rb:1100:in `initialize'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/migration.rb:1006:in `new'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/migration.rb:1006:in `up'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/migration.rb:984:in `migrate'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands/rake_proxy.rb:14:in `block in run_rake_task'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands/rake_proxy.rb:11:in `run_rake_task'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands/commands_tasks.rb:51:in `run_command!'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands.rb:18:in `<top (required)>'
/Users/tgeselle/Documents/Projets/amazon_reporting/bin/rails:9:in `require'
/Users/tgeselle/Documents/Projets/amazon_reporting/bin/rails:9:in `<top (required)>'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/lib/spring/client/rails.rb:28:in `load'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/lib/spring/client/rails.rb:28:in `call'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/lib/spring/client/command.rb:7:in `call'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/lib/spring/client.rb:30:in `run'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/bin/spring:49:in `<top (required)>'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/lib/spring/binstub.rb:31:in `load'
/Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/spring-2.0.1/lib/spring/binstub.rb:31:in `<top (required)>'
/Users/tgeselle/Documents/Projets/amazon_reporting/bin/spring:15:in `require'
/Users/tgeselle/Documents/Projets/amazon_reporting/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Report.all
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.' at line 1
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:85:in `_query'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:85:in `block in query'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:84:in `handle_interrupt'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.1/lib/mysql2/client.rb:84:in `query'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:882:in `configure_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:119:in `configure_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:45:in `initialize'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:26:in `new'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:26:in `mysql2_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:729:in `new_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:773:in `checkout_new_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:752:in `try_to_checkout_new_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:713:in `acquire_connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:490:in `checkout'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:364:in `connection'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activerecord-5.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:883:in `retrieve_connection'
... 14 levels...
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands/console_helper.rb:9:in `start'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands/commands_tasks.rb:78:in `console'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/railties-5.0.2/lib/rails/commands.rb:18:in `<top (required)>'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:293:in `require'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:293:in `block in require'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:259:in `load_dependency'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:293:in `require'
    from /Users/tgeselle/Documents/Projets/amazon_reporting/bin/rails:9:in `<top (required)>'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:287:in `load'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:287:in `block in load'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:259:in `load_dependency'
    from /Users/tgeselle/.rvm/gems/ruby-2.4.1/gems/activesupport-5.0.2/lib/active_support/dependencies.rb:287:in `load'
    from /Users/tgeselle/.rvm/rubies/ruby-2.4.1/lib/ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require'
    from /Users/tgeselle/.rvm/rubies/ruby-2.4.1/lib/ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require'
    from -e:1:in `<main>'
sodabrew commented 7 years ago

This is really a Rails issue - take a look at the configure_connection method in the Active Record abstract mysql adapter: https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L808

MemSQL certainly claims the ability to work with Rails, and suggests mysql2 as the preferred adapter: https://docs.memsql.com/v5.7/docs/how-to-connect-to-memsql#section-ruby-on-rails

I think you may get this working by adding sql_mode: '' in your database.yml. I have a hunch that an empty value will resolve the issue.

tgeselle commented 7 years ago

@sodabrew Thanks for your help. It works by adding this to the database.yml file:

variables:
  sql_mode: ''

Here's an example of my database.yml (it might helps people who have the same issue as me):

default: &default
adapter: mysql2
pool: 5
username: root
password:
host: 127.0.0.1
socket: '/memsql-ops/installs/master-3306/memsql.sock'
variables:
  sql_mode: ''