initial_root_password has no effect on modern MySQL versions

:ghost: Brief Description

When setting up MySQL using the mysql_service block, there is an option called initial_root_password. When setting that to some specific string, I expect to be able to use that string to log into the server later.

In reality, I cannot use the password because the authentication is handled via Socket by default, ever since MySQL 5.7 I believe.

:pancakes: Cookbook version

11.0.5

:woman_cook: Chef-Infra Version

17.10.3

:tophat: Platform details

Ubuntu 20.04 LTS

Steps To Reproduce

In a custom Chef cookbook, use this library and configure as follows:

mysql_service 'default' do
  version '8.0'
  charset 'utf8mb4'
  bind_address '127.0.0.1'
  initial_root_password 'super_strong_password'
  socket '/var/run/mysqld/mysqld.sock'
  action [:create, :start]
end

:police_car: Expected behavior

After Chef finishes, I can log in via mysql -u root -p and then entering the super_strong_password I defined above.

In reality, MySQL just reports Access denied for user 'root'@'localhost'

:heavy_plus_sign: Additional context

When forcing access to the console through sudo mysql, one can see that the server is actually configured to use the auth_socket plugin:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+

As far as setting the default password is concerned, I think it happens here. But this invocation has no effect if auth_socket is used, and MySQL reports a warning along the lines of SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.

To make the password access work, the plugin must be changed upon setting the password. The IDENTIFIED WITH change was once properly introduced in https://github.com/sous-chefs/mysql/commit/9a66e575a16bfd448ed51f3970ad4eacc78e4413, but then got reverted immediately in https://github.com/sous-chefs/mysql/commit/fe39425e99f7fde2e11c82ac4c19ed05921f9e63 for unknown reasons.

I am aware of https://github.com/sous-chefs/mysql/issues/539 but that issue never reached any actual conclusion. I'm curious to know why the revert that I mentioned above happened and if (and why) this is intended behaviour.

I had to throw in the below to use a native password (Mainly just for mocks and tests, I use Aurora so don't actually need this part)

mysql_root_pass = 'YourPassHere'
execute 'Set root password to native authentication' do
  command "mysql -u root -S /run/mysqld/mysqld.sock -e \"ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '#{mysql_root_pass}';\""
  action :run
  sensitive true
end

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

and simple template file looks like templates/mysql_config_extras.erb

[mysqld]
default_authentication_plugin = mysql_native_password

Updates root then sets default for new users in case it comes up again. Wish I didn't have to though, but hey. Only issue I had was that I had to put in a small connection retry loop after defining the service as it runs these immediately after it and the service hasn't fully came up yet.

sous-chefs / mysql