5

I'm using activerecord with em_mysql2 under Goliath (eventmachine). The oddest thing is happening with my User model. When I do a POST to /users the first time, it all works just find as expected. When I do a second POST I get an error.

Mysql2::Error: This connection is still waiting for a result, try again once you have the result: INSERT INTO `users` (... and so on ...)

This doesn't happen for any other of my models or routes. I would assume that if the db connection is in a messed up state that I would see the same error on other requests but nope - all the other DB update and GET requests seem to work just fine.

Does anyone understand how it is that this could only happen for my Users model and only for a User.save action? Does active record somehow store the DB connection that it used for doing a Model.save and re-use it?

EDIT:

I somehow failed to mention when I wrote this question that I was using ActiveRecord as the ORM. I also failed to mention that I was asynchronously sending a request to a Mongo database to fetch the user authentication information.

My solution:

It turns out that the only time this error would occur was when the response from Mongo came back before the response from MySQL, which caused the MySQL response to be picked up by a different Fiber than the one that made the request. Since the MySQL2 fiber implementation I was using used the objectID of the fiber to manage the connections, that seems to have caused the issue.

Overall connection pooling in ActiveRecord + MySql2 + Fibers + Goliath wasn't a fully supported config. (There may be some progress since that time though)

radixhound
  • 2,190
  • 2
  • 18
  • 23

1 Answers1

0

Use a connection pool, which comes with em-synchrony. Using only one connection fails here because requests come in from Goliath while a MySQL query is still waiting for a result, as you can't have multiple active queries on a single connection.

Wrap the connection like this instead:

db = EventMachine::Synchrony::ConnectionPool.new(size: 2) do
  Mysql2::EM::Client.new
end

The pool makes sure that requests wait until a connection becomes available, should all connections be in use.

The size of the connection pool needs to be tuned though, depending on what your database can handle and how much traffic you're expecting. I started out with something around 5-10 but it was a relatively low traffic service, at least in the beginning. That made our connection woes go away.

roidrage
  • 2,341
  • 17
  • 17
  • Thanks! Generally that is a reasonable answer to the question. Unfortunately I can't validate it's correctness at this time since I moved to a different project. If someone else can validate, I'd love to accept the answer.. Oh! Are you using ActiveRecord? – radixhound Nov 15 '11 at 22:06
  • Nope, not using ActiveRecord, but Sequel. Pretty similar story there, so I suspect the solution is the same. – roidrage Nov 16 '11 at 16:30
  • I'm going to accept this answer since it's along the right lines. I was making some kind of error with the request fibers and connection pooling regardless. :-) – radixhound Jul 21 '12 at 00:10