[chef] Re: Why I need to set mysql root password MANUAL when using mysql recipes(opscode) in my cookbook?

[DV < >]

Not sure what's causing this error for you, but here's how I install MySQL in my recipe. I'm using Chef server, not Chef solo.

In my cookbook's metadata.rb:

depends "database"

depends "mysql"

In my cookbook's recipe (note that I didn't put these under any "if" statement):

# Install MySQL

include_recipe "mysql::server"

include_recipe "mysql::client"

include_recipe "database::mysql"

# Create database

mysql_connection_info = {:host => 'localhost', :username => 'root', :password => node[:mysql][:server_root_password]}

mysql_database 'mydb' do

connection mysql_connection_info

action :create

end

# Create tables

mysql_database 'mydb' do

connection mysql_connection_info

sql { ::File.open("#{$mydir}/mytables.sql").read }

action :query

end

# Create user

mysql_database_user 'myuser' do

connection mysql_connection_info

password 'mypass'

action :create

end

# Grant privileges to user

mysql_database_user 'myuser' do

connection mysql_connection_info

password 'mypass'

database_name 'mydb'

host '%'

privileges [:all]

action :grant

end

Hope this helps.

On Mon, Oct 21, 2013 at 6:43 AM, Vladimir Skubriev < " target="_blank"> > wrote:

I created a redmine cookbook, that setups redmine.

First of all my cookbook installing mysql and creating a apporative database redmine:

See my first recipe in a runlist of redmine server names database.rb:

# Setup mysql client packages - need for mysql::server recipe
include_recipe "mysql::client"

# If mysql server is not installed.
if ! ::File.exists?("/usr/sbin/mysqld") then

    # Fix bug in recipe mainline. When we use credmine::purgeall and the rerun chef-client error occurs: "No such file or directory"
    directory "/etc/mysql" do
        owner "mysql"
        group "mysql"
        mode 0700
        action :create
    end

    # Setup security
    node.set['mysql']['remove_test_database'] = true

    # Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
    node.set['mysql']['remove_anonymous_users'] = true

    # If true Sets root access from '%'. If false deletes any non-localhost root users.
    node.set['mysql']['allow_remote_root'] = true

    # Setup mysql server by security parameters seted in credmine::_loaddatabags
    include_recipe "mysql::server"

    # Prepare chef ruby environment(install some gems) for using LWRP at this machine
    include_recipe "database::mysql"

    # Restart mysql server with new parameters.
    service "mysql" do
      action [ :enable, :restart ]
    end

end

# Using database cookbook idemponent LWRP's
mysql_connection_info = {:host => "localhost", :username => "root", :password => node['mysql']['server_root_password']}

# Using database cookbook ::: to create redmine database
mysql_database node.run_state['redmine-mysql-name'] do
  connection mysql_connection_info
  action :create
end
------------------

At the last action mysql_database node.run_state['redmine-mysql-name'] do recipe stops with error:

[2013-10-21T13:38:34+00:00] INFO: Processing mysql_database[redmine] action create (credmine::database line 42)

================================================================================
Error executing action `create` on resource 'mysql_database[redmine]'
================================================================================

Mysql::Error
------------
Access denied for user 'root'@'localhost' (using password: YES)

Cookbook Trace:
---------------
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:83:in `new'
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:83:in `db'
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:78:in `exists?'
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:36:in `action_create'

Resource Declaration:
---------------------
# In /var/chef/cache/cookbooks/credmine/recipes/database.rb

 42: mysql_database node.run_state['redmine-mysql-name'] do
 43:   connection mysql_connection_info
 44:   action :create
 45: end
 46:

Compiled Resource:
------------------
# Declared in /var/chef/cache/cookbooks/credmine/recipes/database.rb:42:in `from_file'

mysql_database("redmine") do
  provider Chef::Provider::Database::Mysql
  action [:create]
  retries 0
  retry_delay 2
  database_name "redmine"
  cookbook_name "credmine"
  recipe_name "database"
  connection {:host=>"localhost", :username=>"root", :password=>"ahgdfyasrbiywerfybweufy"}
end

[2013-10-21T13:38:35+00:00] ERROR: Running exception handlers
[2013-10-21T13:38:35+00:00] FATAL: Saving node information to /var/chef/cache/failed-run-data.json
[2013-10-21T13:38:35+00:00] ERROR: Exception handlers complete
[2013-10-21T13:38:35+00:00] FATAL: Stacktrace dumped to /var/chef/cache/chef-stacktrace.out
[2013-10-21T13:38:35+00:00] FATAL: Mysql::Error: mysql_database[redmine] (credmine::database line 42) had an error: Mysql::Error: Access denied for user 'root'@'localhost' (using password: YES)

I changed password in log to (ahgdfyasrbiywerfybweufy) of course instead of true pass.

And I need to insert my own reset password code before mysql_database node.run_state['redmine-mysql-name'] do
Like this:

# TODO may be off this recipe
bash "change_mysql_root_password" do
  cwd "/tmp"
  code <<-EOH
                mysql -u root --password=#{node['mysql']['server_root_password']} mysql -e 'show databases;'> /dev/null
                if ! [ $? -eq 0 ]; then
                    service mysql stop; if ! [ $? -eq 0 ]; then exit 1000; fi;
                    sleep 3
                    mysqld_safe --skip-grant-tables --socket=/tmp/mysqld_safe.socket --pid-file=/tmp/mysqld_safe.pid >/dev/null &
                    echo "use mysql;" > /tmp/mysql_flush_root.sql
                    echo "update user set password=PASSWORD('#{node['mysql']['server_root_password']}') where User='root'; " >> /tmp/mysql_flush_root.sql
                    # Update privileges in memmory, because if we don't do this old password be valied instead of new.
                    echo "flush privileges;" >> /tmp/mysql_flush_root.sql
                    sleep 5
                    mysql --socket=/tmp/mysqld_safe.socket < /tmp/mysql_flush_root.sql
                    rm /tmp/mysql_flush_root.sql
                    kill -TERM $(cat /tmp/mysqld_safe.pid);
                    sleep 2
                    service mysql start
                else exit 0;
                fi
  EOH
end
------
This recipe verifies the password that I set into a variable node['mysql']['server_root_password'] earlier in _loaddatabags recipe. Which is running before this database creation of course.

Well I am not understand how I can check why lwrp don't setup a mysql with needed password and don't use this smart hack ))) ?




--
Best regards,

CVision Lab System Administrator
Vladmir Skubriev

--
Best regards, Dmitriy V.