2

"MySQL server has gone away". Hmm.

I am using vagrant and chef to setup my virtual development environment. I'm almost there, but on the last step chef fails when attempting to execute my external db_setup.sql file. I can execute this same script by SSH'ing into the virtual server and it installs without difficulty.

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

# 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 receive when I run 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 executes without difficulty.

Any suggestions? Thank you in advance!

Brian FitzGerald
  • 3,041
  • 3
  • 28
  • 38

2 Answers2

1

Many, many things can cause this error. In my experience it's usually an SQL file of over 1MB, or, the wait_timeout in your servers my.cnf is set very low (sixty seconds) needs properly tuned. When deploying my.cnf try a wait_timeout 86400 and a max_allowed_packet of "enough" to import that file.

For example:

 [mysqld]
 wait_timeout = 86400
 max_allowed_packet = 1GB

PS. I would not recommend this high of parameters in actual production.

Brian McManus
  • 56
  • 1
  • This would make sense, but if that were the issue, would I be able to (I can) ssh into the box and process the script from the command line? – Brian FitzGerald Aug 25 '14 at 23:51
  • For what it's worth, I did make these changes to my.cnf and restarted server and the provisioning process still failed. To confirm, I also ssh'ed into the server and ran `source db_setup.sql` and everything ran successfully. Any thoughts? Thanks again – Brian FitzGerald Aug 25 '14 at 23:59
0

I worked around this issue by getting Chef to leverage the mysql command line tool instead of using Ruby to read the .sql file.

Community
  • 1
  • 1
Michael Kropat
  • 14,557
  • 12
  • 70
  • 91