Run All SQL Files with Chef

Run all sql files with Chef

There is a misconception here about how Chef compiles resources. You are expecting Chef to execute the command 15 times, but that's not how Chef operates. Chef runs in two phases - the execution phase, and the compilation phase. During the compilation phase (which runs first), the Ruby is evaluated and resources are added to the resource collection. With some exceptions, this phase does not alter the state of the system. So given your recipe:

execute "Run_SQL_Files" do
dirResults = Dir.glob("/tmp/sql_dumps/*.sql")
var = 0
while var < 15 do
var = var + 1
command "mysql --user=root --password=toomba source" + dirResults[var]
# Already tried this also
# command "mysql --user=root --password=toomba < " dirResults[var]
puts dirResults[var]
end
end

That is functionally equivalent to a recipe that was written like this (after the compilation phase completes)"

execute "Run_SQL_Files" do
command "mysql --user=root --password=toomba source /tmp/sql_dumps/15.sql"
end

Notice that Chef is only going to utilize the last value for the command attribute. That is because Chef executes in two phases (as already mentioned).

Using conditional logic and loops inside of a resource definition is almost always going to cause problems. In this example, you need to compile the command outside of the execute resource. Each SQL command you want to execute needs to have it's own execute block. Here's a simple, refactored example:

Dir["/tmp/sql_dumps/*.sql"].each do |path|
execute "run_sql_#{path}" do
command "mysql --user=root --password=toomba < #{path}"
end
end

This will put 15 (assumption from OP) execute resources in the resource collection, and execute them in order.

How to run a batch of sql files in a chef recipe

I found a way to simplify the requirement and execute a batch of scripts.
I created a batch file and added the sql script calls there. It works perfect.
Unfortunately there wasn't a direct way to call an sql script file directly which worked without errors.
You can use the below code snippets if you have a similar requirement.

Inside the batch file I put

ORACLE_HOME\bin\sqlplus /nolog @C:\script_path\myscriptscall.sql

Inside myscriptscall.sql

set verify off
echo on
spool C:\logs_path\scriptlogs.log

@C:\script_path\sqlscript1.sql
@C:\script_path\sqlscript2.sql
@C:\script_path\sqlscript3.sql

spool off
exit;

Make sure to 'exit' after execution else the program will hang.

Chef-solo and vagrant to automatically run an MySql script after install?

For this, I would recommend using Chef since you are already in the Chef/Vagrant env. No need to create a shell script and trigger it from within Chef - it would go against the intended flow of Chef/Vagrant.

You can do something along the lines of (taken and simplified from the MySQL cookbook by OpsCode in the server.rb recipe):

execute "mysql-install-privileges" do
command "mysql -u root -p#{node['mysql']['server_root_password']} < /vagrant/command.sql"
end

You can determine on your own how you want to store the mysql path, username and password.
The general idea is that you define your MySQL commands in a .sql file (saved in your templates or file directory within your cookbook) and then run it with plain MySQL commands.

Hope that gets you on the right path.

Chef notification within each method

You are creating 4 nearly identical resources all named execute[Create_scripts] and when the notification fires from the first cookbook_file resource being updated it finds the last one of them to be notified and runs against test4 (no matter which cookbook_file resource updates).

The fix is to use string interpolation to change the name of the execute resources to be unique and to notify based on that unique name:

directory node['sql_scripts_dir'] do
recursive true
end

node['script_runner']['scripts'].each do |script|
cookbook_file "#{node['sql_scripts_dir']}\\#{script['name']}.sql" do
source "#{script['name']}.sql"
action :create
notifies :run, "execute[create #{script['name']} scripts]", :immediately
end

execute "create #{script['name']} scripts" do
command "sqlcmd -S \"#{script['hostname']}\" -i \"#{node['sql_scripts_dir']}\\#{script['name']}.sql\""
action :nothing
end
end

Note that this is a manifestation of the same issues behind the old CHEF-3694 warning message where what would happen is that all the four execute resources would be merged into one resource via "resource cloning" with the properties of the subsequent resource being "last-writer-wins".

In Chef 13 this was changed to remove resource cloning and the warning, and in most circumstances having two resources named the same thing in the resource collection is totally harmless -- until you try to notify one of those resources. The resource notification system should really warn in this situation rather than silently picking the last resource that matches (but between notifications, subscribes, lazy resolution and now unified_mode that code is very complicated and you only want it to be firing under exactly the right conditions).

store mysql query information chef

If you don't have experience with Ruby, this might be really confusing. There's no way to "return" the result of a provider from a Chef resource. The mysql_database is a Chef::Recipe DSL method that gets translated to Chef::Provider::Database::Mysql at runtime. This provider is defined in the cookbook.

If you take some time to dive into that provider, you'll can see how it executes queries, using the db object. In order to get the results of a query, you'll need to create your own connection object in the recipe and execute a command against it. For example

require 'mysql'
db = ::Mysql.new('host', 'username', 'password', nil, 'port', 'socket') # varies with setup

users = db.query('SELECT * FROM users')

#
# You might need to manipulate the result into a more manageable data
# structure by splitting on a carriage return, etc...
#
# Assume the new object is an Array where each entry is a username.
#

file '/etc/group' do
contents users.join("\n")
end


Related Topics



Leave a reply



Submit