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.

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

mysql_user or mysql_database can not connect to the database #674

Open
doganoo opened this issue Dec 17, 2021 · 6 comments
Open

mysql_user or mysql_database can not connect to the database #674

doganoo opened this issue Dec 17, 2021 · 6 comments

Comments

@doganoo
Copy link

doganoo commented Dec 17, 2021

🗣️ Foreword

Thank you for providing this awesome cookbook.

👻 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

🥞

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

🚓 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
Copy link

Same issue here

@jhou4
Copy link
Contributor

jhou4 commented Feb 7, 2022

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
Copy link

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

@doganoo
Copy link
Author

doganoo commented Jan 23, 2023

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

@dkd-kaehm
Copy link

@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

debug

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

5 participants