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
Rodbc Loses Time Values of Datetime When Result Set Is Large
Writing a Subquery Using Zend Db
Capturing Multiple Error Messages from a Single Statement Inside Try Catch
How to Call Oracle Md5 Hash Function
From What Do SQL Parameters Protect You
Column Reference Is Ambiguous in Postgresql Function
SQL Server Plans:Difference Between Index Scan/Index Seek
Ms SQL Server - When Is a Cursor Good
Django Prefetch_Related with Limit
How to Use Parameters with Rpostgresql (To Insert Data)
Oracle: Function Based Index Selective Uniqueness
SQL Update Woes in Ms Access - Operation Must Use an Updateable Query
Split Function by Comma in SQL Server 2008
Pivot on Multiple Fields and Export from Access
Efficient Way of Getting @@Rowcount from a Query Using Row_Number