how to order query execution with opscode chef and database cookbook

Question

I'm looking for some help on defining a priority in my chef recipe.

The recipe is supposed to import a sql dump in a database then execute 2 mysql queries against the database.

I'm using Chef Solo.

The first mysql_database action :query importing the dump works well But the 2 other queries seem to do nothing as it seems that the dump is still importing datas in the DB and the datas are not there when runs. Random execution ?

My recipe:

mysql_database node['wp-dev']['database'] do
    connection ({:host => "localhost", :username => 'root', 
                 :password => node['mysql']['server_root_password']})
    #NOW THE SQL SCRIPT importing the dump - WORKING
    sql { ::File.open("/tmp/database.sql").read }
    #NOW THE 2 QUERIES - Not working at first Run
    sql "UPDATE WORKING QUERY WHEN COPIED IN MYSQL 1"
    sql "UPDATE WORKING QUERY WHEN COPIED IN MYSQL 2"
    action :query
end

I can not figure how to fix this or how I can work with only_if to check that the import is finished before running the queries.

it's really simple queries, I do not think the sql code is the problem, as the generated queries works well when copied/pasted into mysql. — user2793488, Sep 19 '13 at 02:56
I've encountered the same problems. It is caused by a delayed connection close. The queries itself are in order (when using two database resources). I've tried to install a base schema and after that some user data. The second query closed the connection before the first query (question: why they are using 2 connections?) .. This lead to the result that mysql executed the second before the last. However, the mysql_database_provider is calling `close` in the code. Strange problem, until fixed it is unusable. — hek2mgl, Feb 26 '14 at 19:59

score 2 · Answer 1 · answered Nov 06 '13 at 11:54

A solution is to use different mysql_datase blocks with one notifying another when completed.

mysql_database 'insertTestData' do
   connection mysql_connection_info
   database_name node['some']['database']
   sql { ::File.open(/somePath/testData.sql).read }
   action :nothing
end


mysql_database 'createTables' do
   connection mysql_connection_info
   database_name node['some']['database']
   sql { ::File.open(/somePath/tableCreate.sql).read }
   action :query
   notifies :query,"mysql_database[insertTestData]"
end

score 1 · Answer 2 · answered Sep 19 '13 at 12:02

1

You need to create a separate mysql_database block for each sql statement you're executing. You can't have multiple sql queries in the same one.

answered Sep 19 '13 at 12:02

Oin

6,951
2
31
55

2

I did it too, but I'm in the same problem, mysql_database blocks seem to be executed in "parallel". When the first one is executed (dump import) the second try to execute against the database the query but import is not complete. Between, multiple sql statements seems to work too: I have the "executing query" in the log. – user2793488 Sep 19 '13 at 13:44

how to order query execution with opscode chef and database cookbook

2 Answers2