Mysql chef database recipe not working in large file

Question

"MySQL server is gone." Hm.

I use a stroller and a chef to set up my virtual development environment. I am almost there, but in the last step the chef fails while trying to execute my external db_setup.sql file. I can execute the same SSH'ing script on a virtual server, and it installs without difficulty.

This is my problem code (in cookbooks / database / recipies / mysql.rb):

# Query a database from a sql script on disk mysql_database 'run script' do database_name 'my_db' connection mysql_connection_info retries 3 sql { ::File.open('/vagrant/db_setup.sql').read } action :query end 

The file is 6.9mb, and the error I get when starting the vagrant provision is:

 ==> default: [2014-08-24T16:04:53-07:00] ERROR: mysql_database[run script] (database::mysql line 50) had an error: Mysql::Error: MySQL server has gone away 

For what it's worth, when I replace the db_setup.sql file with a smaller, simpler file that just creates a few empty tables, it runs without difficulty.

Any suggestions? Thank you in advance!

Answer 1

Many, many things can cause this error . In my experience, this is usually an SQL file larger than 1 MB, or wait_timeout on your my.cnf servers is set very low (sixty seconds), you need to configure it correctly. When deploying my.cnf, try wait_timeout 86400 and max_allowed_packet is “enough” to import this file.

For example:

  [mysqld] wait_timeout = 86400 max_allowed_packet = 1GB 

PS. I would not recommend this high parameter in actual production.

Answer 2

I worked on this issue by forcing the chef to use the mysql command-line tool instead of using Ruby to read the .sql file.