Issue on connect timeout

[jsia]

I tried this code

import amysql
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME)

wait for > 10 seconds
cnn.close()

cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME)
Traceback (most recent call last):
File "", line 1, in
RuntimeError: Connection timed out (0)

It produces an error I have to do connect two times for it to have a reconnect

[jskorpan]

Confirmed, I have a repro and a test for it now which will allow me to fix it ASAP

[jskorpan]

The issue seems to be related to using gevent Python and amysql in interactive mode thus making a sleep which doesn't call the gevent reactor.

If I put together a test from this and do gevent.sleep() it all works out. Gonna keep testing

[jsia]

import amysql
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME)

cnn.close()
gevent.sleep() --> did you mean that it should call gevent sleep here before creating a new connection?
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME)

[jsia]

I'm creating a connection pool for this, I think I need to extending the socket time out duration to have an effective connection pooling

[jskorpan]

You should have to call anything but I can't reproduce the issue if I
only relay on non blocking gevent sleep.

If I do time.sleep() (blocking sleep) or simply wait in the python
console (blocking sleep) I get this issue.
We're using the driver without these problems in production at the
moment so I wouldn't be scared of using it.

There's something happening with gevent, it seems as if the first
wait_write for the first connect of the socket lingers around and fires
for the next one. Gonna keep digging

//JT

On 2011-06-01 00:35, jsia wrote:

import amysql
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME)

cnn.close()
gevent.sleep() --> did you mean that it should call gevent sleep here before creating a new connection?
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME)

Jonas Tärnström
Product Manager
• e-mail: [email protected]
• skype: full name "Jonas Tärnström"
• phone: +46 (0)734 231 552

ESN Social Software AB
www.esn.me

[jskorpan]

I've commited a fix for this bug as far as the repro test goes:
def testDoubleConnect(self):
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_DB)
time.sleep(11)
cnn.close()
time.sleep(1)
cnn = amysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_DB)

[yashh]

I am using amysql in production. I implemented a connection pool with amysql, and found that 1 in 50 write requests are failing with RunTimeError Not connected. For now I am having a try except to catch the error and open a new connection to complete the write.

A few things to make amysql better:

• When I install amysql I see a bunch of warnings. Fixing them would make the developer more comfortable to use.
• There is a is_connected (boolean) on the cnn but I think every cnn object should have the server information its connected to, that I way I can call a reconnect() or some other function to retry a Connection Error.

[jsia]

You need to check for the connection if it is still available before you use the connection from the pool. Try to query first then check if it is still usable, if not create a new connection, I think you are experiencing socket timeout, it closes the connection if it has been idle for a period of time

[yashh]

@jsia so you are suggesting me to check if cnn is connected by calling a query ? or a is_connected() method on it ?

if not cnn.is_connected:
    cnn = get_mysql_connection(*self.db_details) # create a new connection

Also is there a way to increase the socket timeout? Because sqlalchemy takes pool_recycle parameter to recycle the connections in the pool.

data = cnn.query("DELETE FROM users WHERE ID=%s", (user_id))
RuntimeError: Not connected

[jsia]

Hi,

What i did was to edit the code Connection.cpp, I think you have to make it configurable if you want flexibility. You can also run a separate tasklet that will monitor your pool or create a map on when was the last time the connection was used, I used the socket id to determine when was the last time the connection was used and set a threshold value, if it surpasses the threshold value then you need to recreate the connection even without checking if its still alive

Thanks,
Jason

---

From: yashh [mailto:[email protected]]
To: [email protected]
Sent: Wed, 06 Jul 2011 02:50:07 +0800
Subject: Re: [amysql] Issue on connect timeout (#1)

@jsia so you are suggesting me to check if cnn is connected by calling a query ? or a is_connected() method on it ?

  if not cnn.is_connected:
      cnn = get_mysql_connection(*self.db_details) # create a new connection

Also is there a way to increase the socket timeout? Because sqlalchemy takes pool_recycle parameter to recycle the connections in the pool.

data = cnn.query("DELETE FROM users WHERE ID=%s", (user_id))
RuntimeError: Not connected

--
Reply to this email directly or view it on GitHub:
https://github.com/esnme/amysql/issues/1#issuecomment-1506245