[LRUG] MySql connection problems

[Ed James (Alt)]

Wow, thanks for the detailed response Oskar. I will try this and come back to you to confirm what happened. 

-- 
Ed James (Spam)
Sent with Sparrow (http://www.sparrowmailapp.com/?sig)


On Monday, 4 March 2013 at 16:32, Oskar Pearson wrote:

> Hi Ed
> 
> > Hi all
> > 
> > I'm wondering if anyone has seen this issue before. Any help would be greatly appreciated. There are a few threads on Stack Overflow, but most of them are marked as duplicates of the supposedly solved thread, which just points to the MySql docs.
> > 
> > I've removed part of the query, but it's a very straight-forward statement, with a single join. Our db is properly indexed.
> 
> 
> I've had a similar problem before. This happens if MYSQL is set up to terminate idle connections.
> 
> The timeline is something like this (assuming a 6000 second idle timeout in MYSQL):
> 
> 0 - user hits rails app, rails app opens connection to db. Response returned to user.
> <time passes>
> 6000 - mysql closes the db connection. Rails doesn't notice.
> 6010 - user hits rails app, rails app tries to use existing db to query db. Query returns an error, and rails returns an error to the user. Rails also invalidates the connection.
> 6010 - user retries the app. The app opens new connection to db, and everything works.
> 
> > We're trying to upgrade to Ubuntu 12.04 and we're getting intermittent connection failures. When we upgrade to Ubuntu 12.04, we get this error at seemingly random intervals in the Rails 2.3 app:
> > 
> > ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query: SELECT users.id, users.last_login_ip, users.account_id, accounts.account_type_id FROM `users` INNER JOIN `accounts` ON `accounts`.id = `users`.account_id WHERE (...) ORDER BY users.id
> > 
> > Libraries used in tested apps and OS
> 
> If it's the same problem, there are multiple ways to resolve it. http://stackoverflow.com/questions/100631/mysql-server-has-gone-away-with-rails seems relevant and helpful.
> 
> 1) Remove the timeout in your mysql config. I prefer not to go this way, but that's just my preference. I could think of multiple reasons the filedescriptor could become invalid, and I'd prefer that rails handled it if possible.
> 
> 2) If you don't want to do (1) or can't do (1):
> 
> Add the reconnect: true flag in database.yml:
> 
> production:
> adapter: mysql
> database: ...
> pool: 10
> username: ...
> password: ...
> timeout: 5000 # NB, in my opinion
> reconnect: true # NB
> 
> My theory is that if you set this to less seconds than the mysql timeout, rails will consider the FH invalid and re-connect. This isn't mentioned in the S/O thread, but it worked for me (TM).
> 
> With those two options, the timeline would look like this:
> 
> 0 - user hits rails app, rails app opens connection to db. Response returned to user.
> <time passes>
> 6000 - mysql closes the db connection. Rails doesn't notice since it's not threaded or have callbacks.
> 6010 - user hits rails app
> * Rails app checks db filehandles, considers the filehandle invalid (as 6010 is > 5000) and considers it closed / closes it.
> * Rails opens new connection.
> * User gets successful response.
> 
> 
> Also - in my long-running cron-type-processes (eg items that watch for new entries in the db) I had to add this in each loop - which will automatically reconnect if necessary. There's some mention on StackOverflow of doing that in ActionController, which would mean that the dbs are validated with each hit. That could potentially slow things down on a busy server though - since I think it issues a query to the db to check it's available. Doing that would add some latency to each hit.
> 
> 
> Here's the fragment of my long-running script:
> while True
> ActiveRecord::Base.verify_active_connections!
> ..sleep over here...
> ...
> 
> 
> 
> 3) You could try and catch ActiveRecord::StatementInvalid and retry it - but this gets to be a pain. There are some ideas here in the StackOverflow thread I've mentioned above.
> 
> 
> 
> Oskar 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20130304/62d46b93/attachment.html>