mysql_user or mysql_database can not connect to the database

[doganoo]

:speaking_head: Foreword

Thank you for providing this awesome cookbook.

:ghost: Brief Description

When trying to use the mysql_database and mysql_user resources, Chef throws error with "Can't connect to local MySQL server through socket '/var/run/mysql/mysqld.sock".

Please notice I am not trying to connect on the shell within the Vagrant Box.

The Cookbook:

credentials = {
'user': 'keestash',
'password': 'keestash',
'config_name': 'default'
}

mysql_config 'default' do
  source 'my_cnf.erb'
  instance 'default'
  notifies :restart, "mysql_service[default]"
  action :create
end

mysql_service 'default' do
  port '3306'
  version '8.0'
  initial_root_password credentials['password']
  action [:create, :start]
end

# Create a database
# mysql_database 'keestash' do
#   host 'localhost'
#   user 'root'
#   socket '/var/run/mysql/mysqld.sock'
#   password credentials['password']
#   action :create
# end

mysql_user 'keestash' do
  ctrl_password credentials['password']
  password credentials['password']
  host '%'
  action :create
end

The my_cnf.erb

# Chef generated my.cnf for instance mysql

[client]
socket                         = /var/run/mysql/mysqld.sock

The kitchen.yml:

---
driver:
  name: vagrant

## The forwarded_port port feature lets you connect to ports on the VM guest via
## localhost on the host.
## see also: https://www.vagrantup.com/docs/networking/forwarded_ports

#  network:
#    - ["forwarded_port", {guest: 80, host: 8080}]

provisioner:
  name: chef_zero

  ## product_name and product_version specifies a specific Chef product and version to install.
  ## see the Chef documentation for more details: https://docs.chef.io/workstation/config_yml_kitchen/
  #  product_name: chef
  #  product_version: 17

verifier:
  name: inspec

platforms:
  - name: ubuntu-20.04

max_retries: 3
multiple_converge: 3
wait_for_retry: 600

suites:
  - name: default
    verifier:
      inspec_tests:
        - test/integration/default
    attributes:

attributes/apparmor.rb:

default[:apparmor][:disable] = true

:pancakes:

Cookbook Version: 11.0.0

Chef Workstation version: 21.11.679 Chef Infra Client version: 17.7.29 Chef InSpec version: 4.49.0 Chef CLI version: 5.4.2 Chef Habitat version: 1.6.420 Test Kitchen version: 3.1.1 Cookstyle version: 7.25.9

Steps To Reproduce

Steps to reproduce the behavior:

1. Add mysql cookbook to policyfile
2. create mysql cookbook
3. add instructions (see above)
4. run kitchen converge or kitchen converge ubuntu

:police_car: Expected behavior

A running MySQL instance with the given user/database

Error:

      * mysql_user[keestash] action create[2021-12-17T09:15:46+00:00] FATAL: mysql failed executing this SQL statement:
       SELECT User,Host FROM mysql.user WHERE User='keestash' AND Host='%';
       [2021-12-17T09:15:46+00:00] FATAL: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysqld.sock' (2)

           ================================================================================
           Error executing action `create` on resource 'mysql_user[keestash]'
           ================================================================================

           RuntimeError
           ------------
           SQL ERROR

           Cookbook Trace: (most recent call first)
           ----------------------------------------
           /tmp/kitchen/cache/cookbooks/mysql/libraries/helpers.rb:373:in `execute_sql'
           /tmp/kitchen/cache/cookbooks/mysql/resources/mysql_user.rb:60:in `block in class_from_file'

           Resource Declaration:
           ---------------------
           # In /tmp/kitchen/cache/cookbooks/keestash/recipes/mysql.rb

            33: mysql_user 'keestash' do
            34:   ctrl_password credentials['password']
            35:   password credentials['password']
            36:   host '%'
            37:   action :create
            38: end
            39: 

           Compiled Resource:
           ------------------
           # Declared in /tmp/kitchen/cache/cookbooks/keestash/recipes/mysql.rb:33:in `from_file'

           mysql_user("keestash") do
             action [:create]
             default_guard_interpreter :default
             declared_type :mysql_user
             cookbook_name "keestash"
             recipe_name "mysql"
             password "*sensitive value suppressed*"
             host "%"
             privileges [:all]
           end

           System Info:
           ------------
           chef_version=17.8.25
           platform=ubuntu
           platform_version=20.04
           ruby=ruby 3.0.3p157 (2021-11-24 revision 3fb7d2cadc) [x86_64-linux]
           program_name=/opt/chef/bin/chef-client
           executable=/opt/chef/bin/chef-client

Please let me know if you need additional information. Thank you in advance!

[juanmalm]

Same issue here

[jhou4]

I ran into this issue too. The resources should add a property: ctrl_socket or something like that to allow user to specify the socket. A workaround can be changing 'localhost' to '127.0.0.1' so mysql client connect via tcp.

[dkd-kaehm]

Some news for this topic? How to solve this issue on MySQL 8.0?

[doganoo]

@dkd-kaehm don't think so, I did not get any feedback since I created the issue

[dkd-kaehm]

@doganoo Thanks for reply.

Following helped me:

after mysql_service -> start the server is not fully initialized and rejects the connection for mysql_database, mysql_user resources, so i added sleep 2 command to give a little bit more time for subsequent actions:

mysql_service 'default' do
  version mysql_version
  mysqld_options('default-authentication-plugin' => 'mysql_native_password')
  initial_root_password root_password
  action %i[create start]
end

# This does the trick for https://github.com/sous-chefs/mysql/issues/674
execute 'sleep' do
  command 'sleep 2'
end

IMHO the mysql_service -> start action should wait for mysql service is really ready.

[dmytroshevchenko-cartera]

Here is two issues - incorrect socket location to use for MySQL8 (Tested on Rocky8 mysql-community-server-8.0.34) and bug inside resource when compiling 'ctrl' hash. Please see screenshot, this is from recipe with one 'mysql_user' resource. This is one operation but compare Control Hash and SQL for first and second requests.

mysql_user node['mysql']['admin']['username'] do
  action [:create, :grant]
  password node['mysql']['admin']['password']
  database_name '*'
  host 'localhost'
  privileges [:all]
  grant_option true
end

Function 'run_query' https://github.com/sous-chefs/mysql/blob/main/resources/mysql_user.rb#L67C7-L67C16 force to use host and port in the Control Hash, this leads to use cookbook default incorrect socket file in the 'sql_command_string' function https://github.com/sous-chefs/mysql/blob/main/libraries/helpers.rb#L360. In the same time 'load_current_value' function do not send host and port if they are not set https://github.com/sous-chefs/mysql/blob/main/resources/mysql_user.rb#L57-L58 and incorrect socket file path ommited in the result shell command, and it working fine.