`initialize_database` runs every time (at least with mysql 8)

[pdohertybcov]

:speaking_head: Foreword

Thank for taking the time to fill this bug report fully. Without it we may not be able to fix the bug, and the issue may be closed without resolution.

:ghost: Brief Description

initialize_database runs every time (at least with mysql 8)

:pancakes: Cookbook version

10.0.2

:woman_cook: Chef-Infra Version

16.10.8

:tophat: Platform details

CentOS 7.9

Steps To Reproduce

Setup a service, using mysql 8

mysql_service 'foo' do
  port '3306'
  version '8.0'
  bind_address '127.0.0.1'
  data_dir node['foo']['mysql']['bar']
  initial_root_password node['foo']['mysql']['root_password']
  error_log node['foo']['mysql']['error_log']
  action [:create, :start]
end

On every run of chef-client, it will try and run the initialize database step:

    * bash[foo initial records] action run
      - execute "bash"

and you'll see a message like this in the mysql error log

2021-02-26T16:52:05.499384Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.23) initializing of server in progress as process 18946
2021-02-26T16:52:05.502231Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2021-02-26T16:52:05.502281Z 0 [ERROR] [MY-013236] [Server] The designated data directory /opt/mysql/ is unusable. You can remove all files that the server added to it.
2021-02-26T16:52:05.502416Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-02-26T16:52:05.502829Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.

:police_car: Expected behavior

This step should be skipped. It's only supposed to run once.

:heavy_plus_sign: Additional context

The step has a not_if options added to it: https://github.com/sous-chefs/mysql/blob/master/libraries/mysql_service_base.rb#L89-L98 not_if "/usr/bin/test -f #{new_resource.data_dir}/mysql/user.frm"

For reasons I haven't yet discovered, this file doesn't exist on my system. Creating it causes the expected behavior, and the initialize_database resource is skipped after the first run. I suspect this may be a difference in mysql 5.7 and 8.0

[ramereth]

@pdohertybcov I'm curious what you have set for data_dir and error_log in case that has any reason for it to fail. I see you're referencing some attributes in your example above.

[pdohertybcov]

The service is actually starting and working correctly, I only noticed this issue while reviewing the logs, and making sure chef wasn't doing unnecessary back and forth changes, like sometimes happens. But it doesn't seem great that the cookbook is trying to initialize the DB on every run, but failing due to the built-in mysql safeguards. Hopefully I'll have a chance to troubleshoot a little more next week, but I thought I'd open the issue, in case it was just obvious to someone with more direct experience where that user.frm file comes from, and why it's used to determine if the initialization should run or not. The actual service definition and attributes look like this:

mysql_service 'foo' do
  port '3306'
  version '8.0'
  bind_address '127.0.0.1'
  socket node['foo']['mysql']['socket']
  data_dir node['foo']['mysql']['data_dir']
  error_log node['foo']['mysql']['error_log']
  initial_root_password node['foo']['mysql']['root_password']
  action [:create, :start]
end

default['foo']['mysql']['root_password'] = ''
default['foo']['mysql']['socket'] = '/var/run/mysql-foo/mysqld.sock'
default['foo']['mysql']['data_dir'] = '/opt/mysql'
default['foo']['mysql']['error_log'] = '/var/log/mysql-foo/error.log'

Thanks!

[ramereth]

@pdohertybcov I'm able to replicate this on my end and will work on a PR which fixes it soon.

[ramereth]

@pdohertybcov I believe #663 fixes this. Can you please confirm?

[pdohertybcov]

Excellent. This fix works for me. I tested it in my environment and the initialization step only ran the first time, and didn't run again after that.

Thanks for the quick patch.