How best to create and fill a database in an idempotent fashion?

Hello list,

I’m writing a receipt which creates a mysql database for my mail server.
I wrote a sql script and call it like this:


root_pass = node[:mysql][:server_root_password]

scheme_path = “/tmp/scheme.sql”

template scheme_path do
mode "600"
source "mysql-scheme.sql.erb"
end

execute “mysql-scheme” do
command "cat #{scheme_path} | mysql -u root -p#{root_pass}"
end

file scheme_path do
action :delete
end


Of course, this worked only the first time, the next chef run mysql
complained the database already exists.

How do you handle similar situations? What’re the pros and cons of
various approaches?

Thanks,
Dmitry

Hi Dmitry,

This snippet from cookbooks/wordpress/recipes/default.rb might give you an idea. Look at the not_if clause.

execute "create #{node[:wordpress][:db][:database]} database" do
command "/usr/bin/mysqladmin -u root -p#{node[:mysql][:server_root_password]} create #{node[:wordpress][:db][:database]}"
not_if do
m = Mysql.new("localhost", "root", node[:mysql][:server_root_password])
m.list_dbs.include?(node[:wordpress][:db][:database])
end
end

Regards,
KJ

Op 1 jul 2010, om 12:19 heeft Dmitry V'yal het volgende geschreven:

Hello list,

I'm writing a receipt which creates a mysql database for my mail server.
I wrote a sql script and call it like this:


root_pass = node[:mysql][:server_root_password]

scheme_path = "/tmp/scheme.sql"

template scheme_path do
mode "600"
source "mysql-scheme.sql.erb"
end

execute "mysql-scheme" do
command "cat #{scheme_path} | mysql -u root -p#{root_pass}"
end

file scheme_path do
action :delete
end


Of course, this worked only the first time, the next chef run mysql complained the database already exists.

How do you handle similar situations? What're the pros and cons of various approaches?

Thanks,
Dmitry

Hello!

On Jul 1, 2010, at 6:09 AM, Klaas Jan Wierenga wrote:

This snippet from cookbooks/wordpress/recipes/default.rb might give you an idea. Look at the not_if clause.

execute "create #{node[:wordpress][:db][:database]} database" do
command "/usr/bin/mysqladmin -u root -p#{node[:mysql][:server_root_password]} create #{node[:wordpress][:db][:database]}"
not_if do
m = Mysql.new("localhost", "root", node[:mysql][:server_root_password])
m.list_dbs.include?(node[:wordpress][:db][:database])
end
end

Also note that this uses the mysql rubygem which needs to be installed earlier in the Chef run by using the compile-time resource creation. The mysql::server recipe used in the wordpress cookbook does this when it includes the mysql::client recipe:

mysql::client -

r = gem_package "mysql" do
action :nothing
end

r.run_action(:install)

wordpress -

include_recipe "mysql::server"
require 'rubygems'
Gem.clear_paths
require 'mysql'

The Gem.clear_paths is significant, so the load path is set up correctly to find the newly installed mysql gem. Your node will also need to be able to compile rubygem native extensions to install the mysql gem.

--
Opscode, Inc
Joshua Timberman, Senior Solutions Engineer
C: 720.334.RUBY E: joshua@opscode.com