Re: MySQL reconnection
Probably an omission. If you can send a backtrace with that error message, I'll take a look. Sure (from hoptoad - is sequel 3.19.1 because it is git HEAD that I've packaged as gem): Sequel::DatabaseError: Mysql::Error: Lost connection to MySQL server during query ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:200:in `query' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:200:in `_execute' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/database/ logging.rb:28:in `log_yield' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:200:in `_execute' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:184:in `execute' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/ connection_pool/threaded.rb:84:in `hold' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/database/ connecting.rb:226:in `synchronize' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:184:in `execute' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/dataset/ actions.rb:541:in `execute' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:440:in `execute' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/adapters/ mysql.rb:366:in `fetch_rows' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/dataset/ actions.rb:123:in `each' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/dataset/ actions.rb:449:in `single_record' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/dataset/ actions.rb:457:in `single_value' ... /vendor/bundle/ruby/1.8/gems/sequel-3.19.1/lib/sequel/dataset/ actions.rb:200:in `get' ... -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Jan 11, 2:10 am, Roland Swingler roland.swing...@gmail.com wrote: Probably an omission. If you can send a backtrace with that error message, I'll take a look. Sure (from hoptoad - is sequel 3.19.1 because it is git HEAD that I've packaged as gem): Sequel::DatabaseError: Mysql::Error: Lost connection to MySQL server during query https://github.com/jeremyevans/sequel/commit/152def0cf06faa87c412ef58585e10e2448e8f27 Jeremy -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
Hmm, So I have now tried both the DB.disconnect in the passenger block, and attempting to use the after_connect proc to reconnect automatically, but neither of these help, so I don't think it can be because (or only because) of issues to do with passenger forking. I'm going to try setting the read_timeout value to a higher number and see if that has any impact, but I'm at a bit of a loss at the moment. R On Jan 7, 7:21 pm, Aman Gupta themastermi...@gmail.com wrote: On Fri, Jan 7, 2011 at 2:54 AM, Roland Swingler roland.swing...@gmail.comwrote: This code looks like you are attempting to disconnect after the fork, instead of before forking. Also, you don't need to call Sequel.connect again, just DB.disconnect should be enough. But make sure it is called before forking, not after. Ah - so is reconnecting to the DB handled for me by the connection pool as soon as I try to use the Database object (I tried in the console and it seems to work that way)? The problem here is that I don't think I have any way of running code before fork is called, because it isn't a Daemon I'm writing myself, but Passenger. As far as I can tell from the passenger documentation, this is the only point at which I can hook into the passenger forking/ spawning method. I am using DB.disconnect inside PhusionPassenger.on_event(:starting_worker_process) without any issues. Aman I'm probably being really dense, but why is it important to disconnect before rather than after the fork? AFAIK, the connection won't actually ever be used in the parent process (a passenger ApplicationSpawner), only in child processes - does this make a difference as to whether it matters whether you disconnect pre/post fork? Maybe this is more of a issue for the passenger mailing list. @Aaron don't forget that there may be a stateful firewall that is expiring state and denying packets (thus causing the disconnect). I don't think this is the problem because I have a rails app working on the same setup which works fine. Thanks for everyone's help so far, much appreciated. Cheers, Roland -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.comsequel-talk%2bunsubscr...@googlegr oups.com . For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en. -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Mon, Jan 10, 2011 at 2:36 AM, Roland Swingler
roland.swing...@gmail.comwrote:
Hmm,
So I have now tried both the DB.disconnect in the passenger block, and
attempting to use the after_connect proc to reconnect automatically,
but neither of these help, so I don't think it can be because (or only
because) of issues to do with passenger forking.
This definitely works, as I'm using it and it fixes the error you're seeing.
What file did you add the passenger block to?
Perhaps you have multiple connections open to mysql? You could try something
like:
ObjectSpace.each_object(Sequel::Database){ |d| d.disconnect }
or
Sequel::DATABASES.each{ |d| d.disconnect }
Aman
I'm going to try setting the read_timeout value to a higher number and
see if that has any impact, but I'm at a bit of a loss at the moment.
R
On Jan 7, 7:21 pm, Aman Gupta themastermi...@gmail.com wrote:
On Fri, Jan 7, 2011 at 2:54 AM, Roland Swingler
roland.swing...@gmail.comwrote:
This code looks like you are attempting to disconnect after the fork,
instead of before forking. Also, you don't need to call
Sequel.connect again, just DB.disconnect should be enough. But make
sure it is called before forking, not after.
Ah - so is reconnecting to the DB handled for me by the connection
pool as soon as I try to use the Database object (I tried in the
console and it seems to work that way)?
The problem here is that I don't think I have any way of running code
before fork is called, because it isn't a Daemon I'm writing myself,
but Passenger. As far as I can tell from the passenger documentation,
this is the only point at which I can hook into the passenger forking/
spawning method.
I am using DB.disconnect
inside PhusionPassenger.on_event(:starting_worker_process) without any
issues.
Aman
I'm probably being really dense, but why is it important to disconnect
before rather than after the fork?
AFAIK, the connection won't actually ever be used in the parent
process (a passenger ApplicationSpawner), only in child processes -
does this make a difference as to whether it matters whether you
disconnect pre/post fork?
Maybe this is more of a issue for the passenger mailing list.
@Aaron
don't forget that there may be a stateful
firewall that is expiring state and denying packets (thus causing the
disconnect).
I don't think this is the problem because I have a rails app working
on the same setup which works fine.
Thanks for everyone's help so far, much appreciated.
Cheers,
Roland
--
You received this message because you are subscribed to the Google
Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.comsequel-talk%2bunsubscr...@googlegroups.com
sequel-talk%2bunsubscr...@googlegr oups.com
.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
--
You received this message because you are subscribed to the Google Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.comsequel-talk%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
--
You received this message because you are subscribed to the Google Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
This definitely works, as I'm using it and it fixes the error you're seeing.
What file did you add the passenger block to?
I've added it to an environment file which is pretty much the first
thing I'm requiring in my config.ru
I've also tried switching passenger to use the conservative spawning
method and am still getting the same problem, so this again makes me
think the problem is nothing to do with passenger because nothing
would be reusing the connection any more.
I'm getting this problem on random requests - some of which hardly
return any data, so it doesn't make sense that it would be caused by
read timeouts because there were too many rows to process.
Perhaps you have multiple connections open to mysql? You could try something
like:
I've got two connections to two different databases - but I'm
disconnecting both of them in the block. Maybe having two connections
is part of the problem (they're both to the same mysql server)?
Sequel::DATABASES.each{ |d| d.disconnect }
Maybe I should try this just to make definitely sure I'm disconnecting
everything.
Another thing that puzzles me is that the mysql adapter doesn't
include this Lost connection during query message in the set of
Disconnect error messages (https://github.com/jeremyevans/sequel/
blob/master/lib/sequel/adapters/mysql.rb line 89) - I don't know if
this is on purpose or an omission.
--
You received this message because you are subscribed to the Google Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Jan 10, 2:15 pm, Roland Swingler roland.swing...@gmail.com wrote:
This definitely works, as I'm using it and it fixes the error you're seeing.
What file did you add the passenger block to?
I've added it to an environment file which is pretty much the first
thing I'm requiring in my config.ru
I've also tried switching passenger to use the conservative spawning
method and am still getting the same problem, so this again makes me
think the problem is nothing to do with passenger because nothing
would be reusing the connection any more.
I'm getting this problem on random requests - some of which hardly
return any data, so it doesn't make sense that it would be caused by
read timeouts because there were too many rows to process.
Perhaps you have multiple connections open to mysql? You could try something
like:
I've got two connections to two different databases - but I'm
disconnecting both of them in the block. Maybe having two connections
is part of the problem (they're both to the same mysql server)?
Sequel::DATABASES.each{ |d| d.disconnect }
Maybe I should try this just to make definitely sure I'm disconnecting
everything.
Another thing that puzzles me is that the mysql adapter doesn't
include this Lost connection during query message in the set of
Disconnect error messages (https://github.com/jeremyevans/sequel/
blob/master/lib/sequel/adapters/mysql.rb line 89) - I don't know if
this is on purpose or an omission.
Probably an omission. If you can send a backtrace with that error
message, I'll take a look.
Jeremy
--
You received this message because you are subscribed to the Google Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Fri, Jan 7, 2011 at 2:54 AM, Roland Swingler roland.swing...@gmail.comwrote: This code looks like you are attempting to disconnect after the fork, instead of before forking. Also, you don't need to call Sequel.connect again, just DB.disconnect should be enough. But make sure it is called before forking, not after. Ah - so is reconnecting to the DB handled for me by the connection pool as soon as I try to use the Database object (I tried in the console and it seems to work that way)? The problem here is that I don't think I have any way of running code before fork is called, because it isn't a Daemon I'm writing myself, but Passenger. As far as I can tell from the passenger documentation, this is the only point at which I can hook into the passenger forking/ spawning method. I am using DB.disconnect inside PhusionPassenger.on_event(:starting_worker_process) without any issues. Aman I'm probably being really dense, but why is it important to disconnect before rather than after the fork? AFAIK, the connection won't actually ever be used in the parent process (a passenger ApplicationSpawner), only in child processes - does this make a difference as to whether it matters whether you disconnect pre/post fork? Maybe this is more of a issue for the passenger mailing list. @Aaron don't forget that there may be a stateful firewall that is expiring state and denying packets (thus causing the disconnect). I don't think this is the problem because I have a rails app working on the same setup which works fine. Thanks for everyone's help so far, much appreciated. Cheers, Roland -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.comsequel-talk%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en. -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Jan 6, 7:58 am, Roland Swingler roland.swing...@gmail.com wrote:
Hi,
I'm occasionally getting Sequel::DatabaseError: Mysql::Error: Lost
connection to MySQL server during query
when running with sequel + sinatra + passenger 3.
Reading around there seem to be two possibilities for this:
* It is just a standard timeout for mysql
* It is caused by the connection being used simultaneously.
I can't see a reconnect option in the mysql adapter like in
activerecord, despite
thishttp://code.google.com/p/ruby-sequel/issues/detail?id=26
saying it is fixed in trunk, nor can I see the MYSQL_OPT_RECONNECT
being used anywhere, so is it the case that sequel cannot currently
set this option? If so, will you welcome a patch to support this?
No. The implicit reconnection feature in the mysql driver is turned
off. Sequel will detect disconnects and remove that connection from
the pool (raising a DatabaseDisconnectError), and new connections will
automatically be created as needed.
Implicit reconnection causes problems with caching prepared
statements, which is one of the reasons that Sequel turns it off.
Another reason is that implicit reconnection can cause the same
statement to be executed twice, leading to problems with non-
idempotent statements.
Note that you can certainly turn implicit reconnection on if you want
to:
DB = Sequel.mysql(..., :after_connect=proc{|c| c.reconnect = true})
The other possibility is that this is caused by passenger spawning new
workers or somesuch. The db connection is currently kept in a global
DB constant, which is defined in the config.ru file - should I instead
be connecting every time a request comes through (seems unlikely) or
performing some voodoo to reset the connection when passenger spawns
new workers somehow?
In terms of forking, as long as you disconnect all database
connections before forking, you should be OK. If you are only getting
the disconnect because the connections aren't being used, have a cron
job use the connection to do a simple DB query often enough so that
you don't hit the timeout.
Jeremy
--
You received this message because you are subscribed to the Google Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
If you are only getting the disconnect because the connections aren't being used I had a look and the default wait time sequel defines is a month, so I don't think this is the cause of the problems Another reason is that implicit reconnection can cause the same statement to be executed twice, leading to problems with non- idempotent statements. I didn't know that - do you know if that is that a general mysql issue or just an issue with Sequel? (wondering whether I should be removing the reconnect: true option from my various ActiveRecord-related projects) In terms of forking, as long as you disconnect all database connections before forking, you should be OK. I've had a look through the passenger docs, and am now doing something like this in my config.ru if defined?(PhusionPassenger) PhusionPassenger.on_event(:starting_worker_process) do |forked| if forked # We're in smart spawning mode, need to reopoen DB connections DB.disconnect DB = Sequel.connect(...) end end end A bit annoying, because I'll get warnings about redefining constants, but I'm hoping this will work... -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Jan 6, 8:29 am, Roland Swingler roland.swing...@gmail.com wrote: If you are only getting the disconnect because the connections aren't being used I had a look and the default wait time sequel defines is a month, so I don't think this is the cause of the problems OK. If you are getting the disconnects right away, it's probably because you opened the connections before forking and have multiple processes using them. Another reason is that implicit reconnection can cause the same statement to be executed twice, leading to problems with non- idempotent statements. I didn't know that - do you know if that is that a general mysql issue or just an issue with Sequel? (wondering whether I should be removing the reconnect: true option from my various ActiveRecord-related projects) AFAIK, it's a general issue with how implicit reconnection has to work. The fact that it's turned off by default should mean something. In terms of forking, as long as you disconnect all database connections before forking, you should be OK. I've had a look through the passenger docs, and am now doing something like this in my config.ru if defined?(PhusionPassenger) PhusionPassenger.on_event(:starting_worker_process) do |forked| if forked # We're in smart spawning mode, need to reopoen DB connections DB.disconnect DB = Sequel.connect(...) end end end This code looks like you are attempting to disconnect after the fork, instead of before forking. Also, you don't need to call Sequel.connect again, just DB.disconnect should be enough. But make sure it is called before forking, not after. Jeremy -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
If the database is hosted on a different box (and sometimes even if it's on the same host), don't forget that there may be a stateful firewall that is expiring state and denying packets (thus causing the disconnect). I've had to adapt a bunch of my long-running applications to catch the MySQL disconnect and retry because of such things. For one Sinatra app, I added a middleware class to wrap the app and automagically restart it, something like: class DBRetryWrapper def initialize(app, maxtries=5) @app = app @maxtries = maxtries end def call(env) tries = 0 begin @app.call(env) rescue Sequel::DatabaseDisconnectError, Mysql::Error = e if /server has gone away/.match(e.message) tries += 1 raise e if tries @maxtries retry end raise e end end end Aaron out. -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Jan 6, 3:44 pm, Aaron D. Gifford astound...@gmail.com wrote:
If the database is hosted on a different box (and sometimes even if
it's on the same host), don't forget that there may be a stateful
firewall that is expiring state and denying packets (thus causing the
disconnect).
I've had to adapt a bunch of my long-running applications to catch the
MySQL disconnect and retry because of such things.
For one Sinatra app, I added a middleware class to wrap the app and
automagically restart it, something like:
class DBRetryWrapper
def initialize(app, maxtries=5)
@app = app
@maxtries = maxtries
end
def call(env)
tries = 0
begin
@app.call(env)
rescue Sequel::DatabaseDisconnectError, Mysql::Error = e
if /server has gone away/.match(e.message)
tries += 1
raise e if tries @maxtries
retry
end
raise e
end
end
end
Just note that you need to be careful doing that, as it has problems
unless your actions are idempotent. Example:
@app = Proc{|e| DB[:a].insert(:b=params[:c]); DB[:d].first}
If the second query fails with a disconnect error, you'll end up
inserting into table a twice (or more).
Jeremy
--
You received this message because you are subscribed to the Google Groups
sequel-talk group.
To post to this group, send email to sequel-t...@googlegroups.com.
To unsubscribe from this group, send email to
sequel-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
Re: MySQL reconnection
On Jan 6, 9:13 am, Jeremy Evans jeremyeva...@gmail.com wrote: Another reason is that implicit reconnection can cause the same statement to be executed twice, leading to problems with non- idempotent statements. I didn't know that - do you know if that is that a general mysql issue or just an issue with Sequel? (wondering whether I should be removing the reconnect: true option from my various ActiveRecord-related projects) AFAIK, it's a general issue with how implicit reconnection has to work. The fact that it's turned off by default should mean something. I forgot to mention earlier that implicitly reconnection probably doesn't deal well with transactions for obvious reasons. Basically, a database connection has state, and implicitly reconnecting creates a new connection with different state, but there's no way for Sequel (or other database libraries) to know that the state was changed. Jeremy -- You received this message because you are subscribed to the Google Groups sequel-talk group. To post to this group, send email to sequel-t...@googlegroups.com. To unsubscribe from this group, send email to sequel-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
