[LRUG] MySql connection problems

[Ed James (Alt)]

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.

Thanks in advance.

------

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 (http://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 (http://users.id)

Libraries used in tested apps and OS

Ubuntu 10.04
  mysql-client-5.1 5.1.67-0

Ubuntu 12.04
  mysql-client-5.5 5.5.28-0

RDS production database
  Mysql 5.1.63

Rails 2.3
  rails (2.3.17)
  mysql2 (0.2.18)

Rails 3.2
  rails (3.2.12)
  mysql2 (0.3.11)

Ubuntu 12.04 only comes with Mysql 5.5 packages by they should be 100% compatible (https://help.ubuntu.com/12.04/serverguide/mysql.html)


Debugging

ActiveRecord
I was able to replicate it by executing this query raw from ActiveRecord instance.
Like this:
> ActiveRecord::Base.connection.execute("SELECT users.id (http://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 BYusers.id (http://users.id)")


Although the problem is that error appears only from time to time.
When testing on 2 machines at once I received the error at same time frames. This could mean that the error is dependant on database state ( traffic spikes? ).

Mysql2 gem
We thought that the Mysql2 gem could be to blame, but we could not replicate the issue when executing query from Mysql2 gem instance (i.e. without Rails environment).

Query too large
One of the possible causes by the MySQL documentation is:
You can also get these errors if you send a query to the server that is incorrect or too large.

We tried to shortening the query but got nothing definitive, and this also doesn't explain why our query sometimes succeeds. 

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

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