How to connect to MySQL via Ruby's "mysql2" gem?

Question

I installed a fresh MySQL instance and tried connecting to the instance via MySQL Workbench (using the IP address, default port, username / password). The connection was successful. So these values are correct.

I then created a new db schema "newDB" and granted all privileges to the same user for newDB.

Next, I'm using the following ruby code to connect to this MySQL instance.

require 'fileutils'
require 'logger'
require 'sequel'
require 'mysql2'

DATABASE_URL = 'mysql2://The.IP.Address:3306'

DB_OPTIONS = { encoding: 'utf8', sql_log_level: :debug, adapter: "mysql2",  database: "newDB", password: 'password123', username: 'someUser', host:'The.IP.Address', port:'3306'  }

database_connection = Sequel.connect(DATABASE_URL, DB_OPTIONS.merge(logger: database_logger))

However, this connection fails. Here is the stacktrace:

13: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/core.rb:124:in `connect'
        12: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:57:in `connect'
        11: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:57:in `new'
        10: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/database/misc.rb:169:in `initialize'
         9: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:278:in `test_connection'
         8: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:269:in `synchronize'
         7: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/connection_pool/threaded.rb:91:in `hold'
         6: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/connection_pool/threaded.rb:139:in `acquire'
         5: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/connection_pool/threaded.rb:209:in `assign_connection'
         4: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/connection_pool.rb:122:in `make_new'
         3: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/adapters/mysql2.rb:43:in `connect'
         2: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/sequel-5.44.0/lib/sequel/adapters/mysql2.rb:43:in `new'
         1: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/mysql2-0.5.3-x64-mingw32/lib/mysql2/client.rb:90:in `initialize'
C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/mysql2-0.5.3-x64-mingw32/lib/mysql2/client.rb:90:in `connect': Mysql2::Erro (Sequel::DatabaseConnectionError)sha2_password' cannot be loaded: The specified module could not be found.

How do I fix this error? I seem to have provided the correct values for hostname, username etc.

score 0 · Answer 1 · answered May 28 '21 at 10:04

I finally solved this problem. There were multiple problems:

MySQL used the newer sha256 authentication by default. This causes issues when we supply the password directly as text.

The solution#1 was to create a new user and use native_authentication during creation.

SQL error still remains the same even though we're one step closer. The next problem is that SQL searches for the user someUser@% .

A quick fix is to create a user with that name (repeat #1).

Another issue is was SQL expects the user to have certain administrative roles as well, and not just schema privileges (thanks MySQL!).

So you need to provide administrative roles as well to the user.

How to connect to MySQL via Ruby's "mysql2" gem?

1 Answers1