Access Denied Error on CentOS 7

[gangadhar01a]

I am using the following resource which has installed and started the required mysql version. Unfortunately, I am getting Access Denied error when I tried to access mysql as a root.

"mysql_service 'default' do
initial_root_password 'abc123'
bind_address '0.0.0.0'
port '3306'
version '5.7'
action [:create, :start]
end"

[root@rundeck-test cookbooks]# mysql -S /var/run/mysql-default/mysqld.sock -u root -h localhost -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[gangadhar01a]

I t looks like chef doesn't created the "root" account with the mentioned password. Not sure how I what parameters in chef recipe can help to create the root account.
using chef cookbook 8.0.1

[gangadhar01a]

I have updated metadata.rb file to use latest mysql chef cookbook (8.5.1). Two things which I have noticed

1. I am unable to access the mysql without password. (this means the chef cookbook tried to set the password)

2. I am unable to use the password which I have provided in recipe.

[root@rundeck-test cookbooks]# mysql -S /var/run/mysql-default/mysqld.sock -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@rundeck-test cookbooks]# mysql -S /var/run/mysql-default/mysqld.sock -u root -p

Enter password: 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[gsomoza]

Same thing happens to me on Ubuntu 18.04

[ramereth]

@gangadhar01a @gsomoza is this still happening with the current release of this cookbook?

[cgunther]

I think I'm hitting the same issue on Ubuntu 18.04 using v10.0.2 of the cookbook.

What stands out in the Chef output on a fresh machine is the last line and the fact that it's skipped:

* mysql_service[default] action create
  * mysql_server_installation_package[default] action install
    * apt_package[mysql-server-5.7] action install
      - install version 5.7.33-0ubuntu0.18.04.1 of package mysql-server-5.7
    * apt_package[perl-Sys-Hostname-Long] action nothing (skipped due to action :nothing)
    * execute[Initial DB setup script] action nothing (skipped due to action :nothing)

I haven't fully dug into this yet, but it's seeming the file whose existence is checked might already been there just from installing the package, so then the setup script never runs:

mysql/libraries/mysql_service_base.rb

Line 95 in 844036b

not_if "/usr/bin/test -f #{new_resource.data_dir}/mysql/user.frm"

The could be related to using a service instance name of "default", which then looks in /var/lib/mysql for mysql/user.frm, which the package may install, tricking the cookbook into thinking it already did it's setup.

I'll see if I can dig in and clarify this further to find a fix.

[gsomoza]

Weird, it's working for me on version 10.0.2 right now. We're not using a DB setup script though.

MySQL Cookbook version: 10.0.2
Ubuntu Box version: 202005.12.0 (18.04)
MySQL version installed: 5.7.33-0ubuntu0.18.04.1

UPDATE: oh wait, forgot to enable the installation of mysql-server 🙂. Will test that now.
UPDATE2: I'm actually getting the following on the latest version - but probably related to something else?

* mysql_service_manager_systemd[default] action create[2021-03-10T13:40:23+00:00] INFO: Processing mysql_service_manager_systemd[default] action create (/tmp/kitchen/cache/cookbooks/mysql/libraries/mysql_service.rb line 51)
       
             
             ================================================================================
             Error executing action `create` on resource 'mysql_service_manager_systemd[default]'
             ================================================================================
             
             NoMethodError
             -------------
             undefined method `[]' for nil:NilClass
             
             Cookbook Trace:
             ---------------
             /tmp/kitchen/cache/cookbooks/mysql/libraries/mysql_service_base.rb:126:in `configure_apparmor'
             /tmp/kitchen/cache/cookbooks/mysql/libraries/mysql_service_manager_systemd.rb:15:in `block in <class:MysqlServiceManagerSystemd>'
             
             Resource Declaration:
             ---------------------
             # In /tmp/kitchen/cache/cookbooks/mysql/libraries/mysql_service.rb
             
       51:           svc = mysql_service_manager(new_resource.name, &block)
       52:         when 'sysvinit'
             
             Compiled Resource:
             ------------------
             # Declared in /tmp/kitchen/cache/cookbooks/mysql/libraries/mysql_service.rb:51:in `svc_manager'
             
             mysql_service_manager_systemd("default") do
        action [:create]
        retries 0
        retry_delay 2
        default_guard_interpreter :default
        declared_type :mysql_service_manager
        cookbook_name "magento2"
        version "5.7"
        initial_root_password "changeme"
        port "3306"
        instance "default"
        include_dir "/etc/mysql/conf.d"
        data_dir "/var/lib/mysql"
        run_user "mysql"
        run_group "mysql"
             end
             
             System Info:
             ------------
             chef_version=12.22.5
             platform=ubuntu
             platform_version=18.04
             ruby=ruby 2.3.6p384 (2017-12-14 revision 61254) [x86_64-linux]
             program_name=chef-client worker: ppid=1724;start=13:35:39;
             executable=/opt/chef/bin/chef-client

[cgunther]

That error is likely coming from the end of the conditional here:

mysql/libraries/mysql_service_base.rb

Line 126 in f7c92af

return if ::File.exist?('/.dockerenv') || ::File.exist?('/.dockerinit') || !platform?('ubuntu') || node['apparmor']['disable']

I hit a similar issue as it seemed like node['apparmor'] was nil for me, so it fails checking for the disable key in nil. As a simple fix, I added the following to my recipe before calling mysql_service:

node.default['apparmor']['disable'] = true

But I agree I think that's a bit tangential to this main issue.

Also to clarify, I'm not intentionally using a DB setup script, that's just a side effect of the mysql_service resource.

[ramereth]

This looks like the same issue reported in #660 but I was unable to replicate it on my end. Can you provide some example code so I can try and do that on my end?

[cgunther]

I would agree @gsomoza hit the same issue in #660, but I think that's unrelated to the original issue reported by @gangadhar01a which @gsomoza confirmed back in May then I hit yesterday, which I believe boils down to the initial DB setup not being run, potentially when the instance is named "default", which might be because the package install creates the file this cookbook is looking for to determine if it already initialized the DB or not.

[cgunther]

Digging further into this, I'm pretty sure I quoted the wrong output line before, this is where setting the root password is skipped when the instance name is "default" on a fresh machine:

* bash[default initial records] action run (skipped due to not_if)

As I linked to before, I'm pretty sure the package install is creating the file that we're checking to see if "default initial records" needs to run, and as a result, it doesn't run, so doesn't set/change the root password, hence @gangadhar01a 's initial issue, likely. It's almost like if the package is installed, you'd first have to completely clean up the server it creates initially, then proceed with whatever service is specified by the recipe, treating a service named "default" more as a fresh start, rather than applying over the package installed service.

[ramereth]

@cgunther I just released a few fixes, can you see if this problem still exists with the latest version released?

[cgunther]

I think I fixed my problem, but unrelated to your recent fixes.

Digging further into this, I think my trouble was that at least on Ubuntu 18.04/MySQL 5.7, the root user seems to be set up out-of-the-box using the auth_socket plugin, which means so long as you connect via the root OS user, the connection is permitted. The root password doesn't seem to matter, so long as the OS user matches, passing a password or not to the mysql command makes no difference, seems you can even pass an incorrect password and the connection is still permitted just because the OS user matched the MySQL user.

I still think the cookbook isn't actually setting the root password (at least when the instance is named "default"), but I think that's moot because the auth_socket plugin doesn't seem to involve passwords for authentication.

Thanks for going on this adventure with me, though.