Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Lost connection to MySQL server during query on 'simple' queries #495

Closed
kakubei opened this issue Mar 10, 2014 · 37 comments
Closed

Lost connection to MySQL server during query on 'simple' queries #495

kakubei opened this issue Mar 10, 2014 · 37 comments
Milestone

Comments

@kakubei
Copy link

@kakubei kakubei commented Mar 10, 2014

Opening up a new issue for this since all the other similar ones are closed. We started seeing lots of these errors after upgrading to Padrino 0.12 and Mysql2 0.3.15. We don't have access to mysql.cnf (hosted on ClearDB) so can't tweak any of the settings there. Reconnect is set to true in our database.rb file.

This is on Heroku and happens (unsurprisingly) during heavy usage.

By "simple" queries, I mean it's nothing crazy, just a number of joins with properly formatted foreign keys which execute pretty quickly in the console.

Any ideas are welcome.

Lost connection to MySQL server during query

Padrino 0.12
Active Record 4.0.2
mysql2 (0.3.15)
puma (2.7.1)

Following is the backtrace:

gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287query
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287block in execute
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:435block in log
gems/activesupport-4.0.2/lib/active_support/notifications/instrumenter.rb:20instrument
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:430log
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287execute
gems/activerecord-4.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:222execute
gems/activerecord-4.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:226exec_query
gems/activerecord-4.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:235select
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract/database_statements.rb:24select_all
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract/query_cache.rb:63select_all
gems/activerecord-4.0.2/lib/active_record/querying.rb:36find_by_sql
gems/activerecord-4.0.2/lib/active_record/relation.rb:585exec_queries
gems/activerecord-4.0.2/lib/active_record/relation.rb:471load
gems/activerecord-4.0.2/lib/active_record/relation.rb:220to_a
gems/activerecord-4.0.2/lib/active_record/relation/finder_methods.rb:315find_take
gems/activerecord-4.0.2/lib/active_record/relation/finder_methods.rb:65take
gems/activerecord-4.0.2/lib/active_record/relation/finder_methods.rb:48find_by
gems/activerecord-4.0.2/lib/active_record/querying.rb:6find_by
/app/app/controllers/books_controller.rb:57block (2 levels) in <top (required)>
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:699call
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:699block in route
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:62[]
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:62block (3 levels) in process_destination_path
gems/sinatra-1.4.4/lib/sinatra/base.rb:976route_eval
gems/newrelic_rpm-3.7.2.192/lib/new_relic/agent/instrumentation/sinatra.rb:133route_eval_with_newrelic
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:62block (2 levels) in process_destination_path
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:62catch
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:62block in process_destination_path
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:37instance_eval
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:37process_destination_path
(eval):613block in call
(eval):602catch
(eval):602call
gems/http_router-0.11.0/lib/http_router.rb:307raw_call
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:1101route!
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:1086block in dispatch!
gems/sinatra-1.4.4/lib/sinatra/base.rb:1049block in invoke
gems/sinatra-1.4.4/lib/sinatra/base.rb:1049catch
gems/sinatra-1.4.4/lib/sinatra/base.rb:1049invoke
gems/padrino-core-0.12.0/lib/padrino-core/application/routing.rb:1084dispatch!
gems/newrelic_rpm-3.7.2.192/lib/new_relic/agent/instrumentation/sinatra.rb:151dispatch_and_notice_errors_with_newrelic
gems/newrelic_rpm-3.7.2.192/lib/new_relic/agent/instrumentation/sinatra.rb:146block in dispatch_with_newrelic
gems/newrelic_rpm-3.7.2.192/lib/new_relic/agent/instrumentation/controller_instrumentation.rb:339perform_action_with_newrelic_trace
gems/newrelic_rpm-3.7.2.192/lib/new_relic/agent/instrumentation/sinatra.rb:143dispatch_with_newrelic
gems/sinatra-1.4.4/lib/sinatra/base.rb:889block in call!
gems/sinatra-1.4.4/lib/sinatra/base.rb:1049block in invoke
gems/sinatra-1.4.4/lib/sinatra/base.rb:1049catch
gems/sinatra-1.4.4/lib/sinatra/base.rb:1049invoke
gems/sinatra-1.4.4/lib/sinatra/base.rb:889call!
gems/sinatra-1.4.4/lib/sinatra/base.rb:877call
gems/newrelic_rpm-3.7.2.192/lib/new_relic/rack/error_collector.rb:55call
gems/newrelic_rpm-3.7.2.192/lib/new_relic/rack/browser_monitoring.rb:27call
gems/newrelic_rpm-3.7.2.192/lib/new_relic/rack/agent_hooks.rb:32call
gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract/connection_pool.rb:626call
gems/rack-protection-1.5.2/lib/rack/protection/xss_header.rb:18call
gems/rack-protection-1.5.2/lib/rack/protection/base.rb:50call
gems/rack-protection-1.5.2/lib/rack/protection/base.rb:50call
gems/rack-protection-1.5.2/lib/rack/protection/json_csrf.rb:18call
gems/rack-protection-1.5.2/lib/rack/protection/base.rb:50call
gems/rack-protection-1.5.2/lib/rack/protection/base.rb:50call
gems/rack-protection-1.5.2/lib/rack/protection/frame_options.rb:31call
gems/rack-1.5.2/lib/rack/head.rb:11call
gems/rack-1.5.2/lib/rack/methodoverride.rb:21call
gems/padrino-core-0.12.0/lib/padrino-core/reloader/rack.rb:22call
gems/rack-1.5.2/lib/rack/session/abstract/id.rb:225context
gems/rack-1.5.2/lib/rack/session/abstract/id.rb:220call
gems/sinatra-1.4.4/lib/sinatra/base.rb:2004call
gems/sinatra-1.4.4/lib/sinatra/base.rb:1469block in call
gems/sinatra-1.4.4/lib/sinatra/base.rb:1778synchronize
gems/sinatra-1.4.4/lib/sinatra/base.rb:1469call
gems/padrino-core-0.12.0/lib/padrino-core/router.rb:82block in call
gems/padrino-core-0.12.0/lib/padrino-core/router.rb:75each
gems/padrino-core-0.12.0/lib/padrino-core/router.rb:75call
gems/rack-1.5.2/lib/rack/urlmap.rb:65block in call
gems/rack-1.5.2/lib/rack/urlmap.rb:50each
gems/rack-1.5.2/lib/rack/urlmap.rb:50call
gems/puma-2.7.1/lib/puma/configuration.rb:68call
gems/puma-2.7.1/lib/puma/server.rb:486handle_request
gems/puma-2.7.1/lib/puma/server.rb:357process_client
gems/puma-2.7.1/lib/puma/server.rb:250block in run
gems/puma-2.7.1/lib/puma/thread_pool.rb:92call
gems/puma-2.7.1/lib/puma/thread_pool.rb:92block in spawn_thread

@WilliamDenniss
Copy link

@WilliamDenniss WilliamDenniss commented Mar 15, 2014

By any chance have you set the reaping_frequency ActiveRecord config? I found that the ActiveRecord connection reaper was causing this error. It's disabled by default in rails, but I enabled it when I following this sample from Heroku https://devcenter.heroku.com/articles/concurrency-and-database-connections#threaded-servers removing those config lines from my code fixed the issue.

See this issue from Rails rails/rails#9907 and my SO answer on this topic: http://stackoverflow.com/a/22423893/72176

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Apr 7, 2014

@kakubei Does this resolve your issue?

@kakubei
Copy link
Author

@kakubei kakubei commented Apr 7, 2014

Sorry, hadn't had a chance to try it. I just changed it to 180 seconds and pushed my changes. I'll let you know. I didn't remove the value because this is Padrino, not Rails, I don't know if removing the value altogether will set it to a default value which might too low.

@WilliamDenniss
Copy link

@WilliamDenniss WilliamDenniss commented Apr 7, 2014

@kakubei if you keep the reaper enabled, I advise you to consider why it's actually needed. If your code is leaking MySQL connections, then I'd suggest fixing the code! Also, MySQL should have its own connection timeouts and handling. So far I have yet to meet anyone who actually found that the reaper fixed more problems than it caused.

Reference: this thread on Linux Questions http://www.linuxquestions.org/questions/showthread.php?p=5143596 I also posted some thoughts on the matter on my blog http://omegadelta.net/2014/03/15/the-rails-grim-reaper/

@kakubei
Copy link
Author

@kakubei kakubei commented Apr 7, 2014

Yes, it would be great to fix the underlying memory issue, however I have not been able to find it. Are you saying that if I remove the reaping_frequency value! no reaping will be done? I thought Heroku suggested it be turned on.

@WilliamDenniss
Copy link

@WilliamDenniss WilliamDenniss commented Apr 7, 2014

At least on rails, if you don't specify a reaping_frequency it means no reaping. Remember that Rails Core decided to disable the reaper by default for good reason (see rails/rails@7150a9d and rails/rails#9907). Yes Heroku suggests it, but doesn't mean they are right.

For Geospike, we didn't have any issues that were solved by the reaper, and we had some big ones (queries being destroyed in long running processes) that were caused by the reaper.

My opinion is that if you have a problem that is solved by the reaper, you are best to try and solve it another way. Or you may have to accept the side effects of the reaper which I believe is why you filed this bug in the first place. Perhaps a slower reaping frequency could be the best of both worlds, but given the reaper's tendency to kill perfectly normal connections, I would be surprised.

@kakubei
Copy link
Author

@kakubei kakubei commented Apr 7, 2014

Thanks @WilliamDenniss I'll give removing it a try.

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Apr 7, 2014

Thank you @kakubei - I just closed the very similar #421, and would love to hear confirmation if the reaping frequency problem is the root cause of this ticket.

@copernicus
Copy link

@copernicus copernicus commented Apr 17, 2014

We are having the exact same issue with Padrino and mysql2 gem. In our case we are using Sequel adapter. There is no reaping_frequency specified anywhere as this is Padrino and not Rails. Anyone have any pointers to how to fix this? This is not OS specific either. Happens on linux and OSX.

thanks!

@WilliamDenniss
Copy link

@WilliamDenniss WilliamDenniss commented Apr 18, 2014

@copernicus, are you using ActiveRecord? Can you elaborate more on your error message?

@copernicus
Copy link

@copernicus copernicus commented Apr 18, 2014

No. I am using Sequel adapter with the mysql2 gem in Padrino 0.12.1. It seems like the connection just goes away after a few seconds. A stack trace:

ERROR -  Mysql2::Error: MySQL server has gone away: SELECT 2010 AS `v` FROM `users` LIMIT 1
Sequel::DatabaseDisconnectError - Mysql2::Error: MySQL server has gone away:
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/mysql2.rb:77:in `query'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/mysql2.rb:77:in `block in _execute'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/database/logging.rb:37:in `log_yield'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/mysql2.rb:77:in `_execute'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:34:in `block in execute'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/database/connecting.rb:229:in `block in synchronize'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/connection_pool/threaded.rb:104:in `hold'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/database/connecting.rb:229:in `synchronize'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:34:in `execute'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/dataset/actions.rb:900:in `execute'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/mysql2.rb:191:in `execute'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/adapters/mysql2.rb:153:in `fetch_rows'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/dataset/actions.rb:139:in `each'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/dataset/actions.rb:639:in `single_record'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/dataset/actions.rb:647:in `single_value'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/dataset/actions.rb:246:in `get'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sequel-4.9.0/lib/sequel/model/plugins.rb:28:in `get'
    /Users/copernicus/Dev/thor/app/app.rb:168:in `block (2 levels) in <class:App>'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/warden-1.2.3/lib/warden/session_serializer.rb:34:in `fetch'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/warden-1.2.3/lib/warden/proxy.rb:212:in `user'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/warden-1.2.3/lib/warden/proxy.rb:144:in `authenticated?'
    /Users/copernicus/Dev/thor/app/controllers/dashboard.rb:6:in `block (2 levels) in <top (required)>'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:59:in `instance_eval'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:59:in `block (2 levels) in process_destination_path'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:59:in `each'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:59:in `block in process_destination_path'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:37:in `instance_eval'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:37:in `process_destination_path'
    (eval):81:in `block in call'
    (eval):70:in `catch'
    (eval):70:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/http_router-0.11.1/lib/http_router.rb:288:in `raw_call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:1142:in `route!'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:1127:in `block in dispatch!'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1058:in `block in invoke'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1058:in `catch'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1058:in `invoke'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/application/routing.rb:1125:in `dispatch!'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:898:in `block in call!'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1058:in `block in invoke'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1058:in `catch'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1058:in `invoke'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:898:in `call!'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:886:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/warden-1.2.3/lib/warden/manager.rb:35:in `block in call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/warden-1.2.3/lib/warden/manager.rb:34:in `catch'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/warden-1.2.3/lib/warden/manager.rb:34:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/session/abstract/id.rb:225:in `context'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/session/abstract/id.rb:220:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-perftools_profiler-0.6.1/lib/rack/perftools_profiler/profiler_middleware.rb:39:in `call_app'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-perftools_profiler-0.6.1/lib/rack/perftools_profiler/call_app_directly.rb:6:in `act'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-perftools_profiler-0.6.1/lib/rack/perftools_profiler/profiler_middleware.rb:28:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-protection-1.5.3/lib/rack/protection/xss_header.rb:18:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-protection-1.5.3/lib/rack/protection/json_csrf.rb:18:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-protection-1.5.3/lib/rack/protection/base.rb:49:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-protection-1.5.3/lib/rack/protection/base.rb:49:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-protection-1.5.3/lib/rack/protection/frame_options.rb:31:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/head.rb:11:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/methodoverride.rb:21:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/reloader/rack.rb:22:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/logger.rb:423:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/show_exceptions.rb:21:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:2014:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1478:in `block in call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1788:in `synchronize'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:1478:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/router.rb:82:in `block in call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/router.rb:75:in `each'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/padrino-core-0.12.1/lib/padrino-core/router.rb:75:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/lint.rb:49:in `_call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/lint.rb:37:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/showexceptions.rb:24:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/commonlogger.rb:33:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/sinatra-1.4.5/lib/sinatra/base.rb:217:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/chunked.rb:43:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/rack-1.5.2/lib/rack/content_length.rb:14:in `call'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:572:in `process_client'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:666:in `worker_loop'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:521:in `spawn_missing_workers'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:140:in `start'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/gems/unicorn-4.8.2/bin/unicorn:126:in `<top (required)>'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/bin/unicorn:23:in `load'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/bin/unicorn:23:in `<main>'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/bin/ruby_executable_hooks:15:in `eval'
    /Users/copernicus/.rvm/gems/ruby-2.1.1@thor/bin/ruby_executable_hooks:15:in `<main>'

@WilliamDenniss
Copy link

@WilliamDenniss WilliamDenniss commented Apr 18, 2014

So this happens 100% of the time? Or at what frequency?

@copernicus
Copy link

@copernicus copernicus commented Apr 18, 2014

Seems to happen for every other query. The first query works fine and when the code for the next query kicks in it throws this error.

@WilliamDenniss
Copy link

@WilliamDenniss WilliamDenniss commented Apr 18, 2014

The reaper cause for the Lost connection to MySQL server during query error message exists in the ActiveRecord gem specifically (and it's not the only possible cause for that error – but when encountered with ActiveRecord I'm highly suspicious). I have suggested the reaper as a possible cause to the original poster (OP) due to the fact the error matches, they are using ActiveRecord, and the reoccurrence rate is intermittent. I wrote more about the reaper here.

Reaper or not, @copernicus this doesn't look like the same issue to me:

  • your MySQL error MySQL server has gone away is different to the OP's Lost connection to MySQL server during query
  • your gemlist and the OPs is quite different (importantly, they are using ActiveRecord but you are not).
  • your error occurrence rate is very reliable where as the OP's is not

If you believe the error is caused by a bug in the mysql2 gem it may be appropriate to open a separate issue.

However, I think you should go back to square-one with your searching on this topic, and specifically search for the MySQL server has gone away message and see if anyone else has some tips on that. Possibly your database, or connection settings in ruby, or your MySQL configuration settings at the server are not correct.

@copernicus
Copy link

@copernicus copernicus commented Apr 18, 2014

Will do. Thanks for the pointers!

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Jun 16, 2014

Is this still an open issue? Can you try mysql2 0.3.16 and see if that changes the situation?

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Jul 29, 2014

Is this still an open issue? Can you try mysql2 0.3.16 and see if that changes the situation?

I'm going to close the issue this week unless there is an ongoing issue that needs bugfix work.

@zymtx5g79k
Copy link

@zymtx5g79k zymtx5g79k commented Jul 29, 2014

I had the same issue. The problem was in shared context for workers in unicorn. Set the option preload_app to false and disable any shared context (memory, sockets, pipes etc.) features.

After open certain number of connections the db does not allow open more connection in some period time. Be sure that each process unit have own connection to db (not shared). It allow to the "process units" close the database connection at the end of work.

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Jul 31, 2014

#530 may be a full resolution to this issue.

@janraasch
Copy link

@janraasch janraasch commented Oct 24, 2014

@kakubei, we were running into a similar issue on our database which is hosted by ClearDB as well. In our case the error

Mysql2::Error: MySQL server has gone away: SELECT  `foobars`.* FROM `foobars`  WHERE `foobars`.`reference` = 'xyz' LIMIT 1

was raised during a long running transaction (code smell...). The ClearDB Team was nice enough to raise their routers timeout (that is the timeout for the whole cluster of databases) to fix this in a timely manner.

@drobune
Copy link

@drobune drobune commented Jun 4, 2015

Same thing has occured on ClearDB.

Activ​eReco​rd::S​tatem​entIn​valid​: Mys​ql2::​Error​: Los​t con​necti​on to​ MySQ​L ser​ver d​uring​ quer​y: SE​LECT ​ `hoge`.* F​ROM `​hoge` ​ WHER​E `hoge`.`fuga_i​d` = ​xxx LIMI​T 1

@iagorl
Copy link

@iagorl iagorl commented Jun 20, 2015

We are having the same problem, anyone could find a fix?

@ahmadhasankhan
Copy link

@ahmadhasankhan ahmadhasankhan commented Aug 4, 2015

I am also facing same issue.
I don't know what happened, app was working fine before a day and suddenly i started getting this issue.

Mysql2::Error: Lost connection to MySQL server during query: SELECT id, locality_id, name FROM `sublocalities`  ORDER BY name
/Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query': Mysql2::Error: Lost connection to MySQL server during query: SELECT id, locality_id, name FROM `sublocalities`  ORDER BY name (ActiveRecord::StatementInvalid)
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `block in execute'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/ar-octopus-0.8.5/lib/octopus/abstract_adapter.rb:15:in `instrument'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/newrelic_rpm-3.12.1.298/lib/new_relic/agent/instrumentation/active_record.rb:59:in `block in log_with_newrelic_instrumentation'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/newrelic_rpm-3.12.1.298/lib/new_relic/agent/method_tracer.rb:73:in `block in trace_execution_scoped'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/newrelic_rpm-3.12.1.298/lib/new_relic/agent/method_tracer_helpers.rb:82:in `trace_execution_scoped'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/newrelic_rpm-3.12.1.298/lib/new_relic/agent/method_tracer.rb:71:in `trace_execution_scoped'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/newrelic_rpm-3.12.1.298/lib/new_relic/agent/instrumentation/active_record.rb:56:in `log_with_newrelic_instrumentation'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `execute'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/mysql2_adapter.rb:213:in `execute'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/mysql2_adapter.rb:217:in `exec_query'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/mysql2_adapter.rb:226:in `select'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/ar-octopus-0.8.5/lib/octopus/proxy.rb:265:in `method_missing'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/querying.rb:38:in `block in find_by_sql'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/explain.rb:41:in `logging_query_plan'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/querying.rb:37:in `find_by_sql'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/relation.rb:171:in `exec_queries'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/relation.rb:160:in `block in to_a'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/explain.rb:41:in `logging_query_plan'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/relation.rb:159:in `to_a'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/relation/finder_methods.rb:159:in `all'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activerecord-3.2.22/lib/active_record/querying.rb:5:in `all'
        from /Users/ahmadhassan/ola/call_center/app/app.rb:16:in `<class:CallCenter>'
        from /Users/ahmadhassan/ola/call_center/app/app.rb:3:in `<top (required)>'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:251:in `require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:251:in `block in require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:236:in `load_dependency'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:251:in `require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/reloader.rb:148:in `safe_load'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:163:in `block in require_dependencies'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:161:in `each'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:161:in `require_dependencies'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/mounter.rb:148:in `locate_app_object'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/mounter.rb:30:in `initialize'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/mounter.rb:221:in `new'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/mounter.rb:221:in `mount'
        from /Users/ahmadhassan/ola/call_center/config/apps.rb:34:in `<top (required)>'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:251:in `require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:251:in `block in require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:236:in `load_dependency'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/activesupport-3.2.22/lib/active_support/dependencies.rb:251:in `require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/reloader.rb:148:in `safe_load'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:163:in `block in require_dependencies'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:161:in `each'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:161:in `require_dependencies'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:70:in `block in load!'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:70:in `each'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/loader.rb:70:in `load!'
        from /Users/ahmadhassan/ola/call_center/config/boot.rb:73:in `<top (required)>'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/cli/base.rb:23:in `require'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/cli/base.rb:23:in `start'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor/task.rb:27:in `run'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor/invocation.rb:120:in `invoke_task'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor.rb:275:in `dispatch'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor/invocation.rb:109:in `invoke'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/lib/padrino-core/cli/base.rb:29:in `s'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor/task.rb:27:in `run'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor/invocation.rb:120:in `invoke_task'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor.rb:275:in `dispatch'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/thor-0.15.4/lib/thor/base.rb:425:in `start'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/gems/padrino-core-0.10.7/bin/padrino:9:in `<top (required)>'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/bin/padrino:23:in `load'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/bin/padrino:23:in `<main>'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/bin/ruby_executable_hooks:15:in `eval'
        from /Users/ahmadhassan/.rvm/gems/ruby-2.0.0-p598/bin/ruby_executable_hooks:15:in `<main>'

@Bodacious
Copy link

@Bodacious Bodacious commented Feb 21, 2016

+1 for this issue.

  • Rails 4.0.0
  • MySQL2 0.3.20
  • ClearDB is the provider

(ActiveRecord::StatementInvalid) "Mysql2::Error: Lost connection to MySQL server during query: SHOW FULL FIELDS FROMsessions"

@oranges13
Copy link

@oranges13 oranges13 commented Apr 6, 2016

I am also having this issue on several web apps with Padrino, not Rails. We're using Sequel as the Database adapter not Active Record.

@CyberFerret
Copy link

@CyberFerret CyberFerret commented Jun 3, 2016

Just came across this same thing using Padrino 13.1, and Sequel as the adapter. It seems if I leave the web app idle for a while and come back to try and do something, it falls over with this error.

@pchaganti
Copy link

@pchaganti pchaganti commented Jun 6, 2016

Seeing the same thing when using Sequel as adapter with latest version of Padrino.

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Jun 6, 2016

I read up on the Sequel adapter - it simply passes through constructor arguments to Mysql2::Client#new -- do you all have reconnect enabled? Are you all on mysql2 0.3.x or 0.4.x now?

@oranges13
Copy link

@oranges13 oranges13 commented Jun 8, 2016

How can we confirm reconnect enabled or not?

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Jun 8, 2016

Reconnect is disabled by default, so if you don't see it, then it's turned off.

This type of disconnect generally occurs when the connection has been left hanging for a while and has timed out, e.g. if there were many minutes or hours between web requests. Reconnect will transparently reconnect without raising an exception if this happens. There is a caveat that any session settings that were set via SQL query will not be applied to the new connection. There's a workaround for that, using the :init_command option.

@oranges13
Copy link

@oranges13 oranges13 commented Jun 8, 2016

So how can we enable it?
On Jun 8, 2016 1:17 PM, "Aaron Stone" notifications@github.com wrote:

Reconnect is disabled by default, so if you don't see it, then it's turned
off.


You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#495 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/AAuFpiEXliUAgobMWbOI-xASaENOh0Xxks5qJvkxgaJpZM4BoNmq
.

@sodabrew
Copy link
Collaborator

@sodabrew sodabrew commented Jun 8, 2016

Here are the options for Mysql2::Client#new which you can pass to Sequel#new: https://github.com/brianmario/mysql2#connection-options

@CyberFerret
Copy link

@CyberFerret CyberFerret commented Jun 19, 2016

I added :reconnect => true to my connection string (Padrino 0.13.1 and Sequel) but still getting the "MySQL has gone away" messages. In testing I assumed it was happening when the app had been idle for many minutes, but in production now, under heavy use, it happens once every 3 or 4 minutes right in the middle of a bunch of constant queries being run (i.e. within seconds of the last successful query).

I've checked the logs for things like memory and CPU usage on my server, as well as connection limits to the database server but they are always well under 10% of max capacity.

@born4new
Copy link

@born4new born4new commented Apr 25, 2017

Any news on this? We're still having these errors with mysql2 0.3.21

@vnazarenko
Copy link

@vnazarenko vnazarenko commented Apr 25, 2017

the same for me

@kln
Copy link

@kln kln commented Aug 3, 2017

I created an initializer file and I try to reconnect when this error occurs. Its works for me.
Some problem with this approach?

# config/initializers/connection_fix.rb

Rails.application.config.after_initialize do
  config  = ActiveRecord::Base.configurations[Rails.env] ||
              Rails.application.config.database_configuration[Rails.env]

  adapter = config['adapter']

  if adapter == "mysql2"
   module ActiveRecord::ConnectionAdapters
     class Mysql2Adapter
       alias_method :execute_without_retry, :execute

       def execute(*args)
         execute_without_retry(*args)
       rescue ActiveRecord::StatementInvalid => e
         if e.message =~ /server has gone away/i ||
            e.message =~ /Lost connection to MySQL server during query/i
           reconnect!
           retry
         else
           raise e
         end
       end
     end
   end
  end

@camertron
Copy link

@camertron camertron commented Jan 7, 2020

@kln your solution is the only one that worked for me. Thanks, and cheers!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests