Broken pipe with mysql

[Álvaro J. Iradier]

As seen in this thread:

http://groups.google.com/group/web2py/browse_thread/thread/c6653dadbea77f0d/06ed6fe085eae33c

I'm getting "broken pipe" messages when mysql is restarted, and under
other circunstances.

Going to a shell, I tried doing a query, for example:

>>> db(db.location).select()
<gluon.dal.Rows object at 0xa2056ec>

everything goes ok. Then I restart Mysql, and running the same query I
get:

>>> db(db.location).select()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 5382, in
select
return self.db._adapter.select(self.query,fields,attributes)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1159, in
select
rows = response(sql)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1149, in
response
self.execute(sql)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1234, in
execute
return self.log_execute(*a, **b)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1229, in
log_execute
ret = self.cursor.execute(*a,**b)
File "/var/www/web2py_klnetcenter/gluon/contrib/pymysql/cursors.py",
line 108, in execute
self.errorhandler(self, exc, value)
File "/var/www/web2py_klnetcenter/gluon/contrib/pymysql/
connections.py", line 184, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (2013, 'Lost connection to MySQL server during
query')

trying to run it again, I only get:

>>> db(db.location).select()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 5382, in
select
return self.db._adapter.select(self.query,fields,attributes)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1159, in
select
rows = response(sql)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1149, in
response
self.execute(sql)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1234, in
execute
return self.log_execute(*a, **b)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1229, in
log_execute
ret = self.cursor.execute(*a,**b)
File "/var/www/web2py_klnetcenter/gluon/contrib/pymysql/cursors.py",
line 108, in execute
self.errorhandler(self, exc, value)
File "/var/www/web2py_klnetcenter/gluon/contrib/pymysql/
connections.py", line 182, in defaulterrorhandler
raise Error(errorclass, errorvalue)
Error: (<class 'socket.error'>, error(32, 'Broken pipe'))

forever!. However, if I do:

>>> db._adapter.connection.ping()
True
>>> db(db.location).select()
<gluon.dal.Rows object at 0xa20596c>

so it looks like pinging does a reconnect.

If the connection is closed manually, it won't reconnect either:

>>> db(db.location).select()
<gluon.dal.Rows object at 0xa205a0c>
>>> db._adapter.connection.close()
>>> print db(db.location).select()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 5382, in
select
return self.db._adapter.select(self.query,fields,attributes)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1159, in
select
rows = response(sql)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1149, in
response
self.execute(sql)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1234, in
execute
return self.log_execute(*a, **b)
File "/var/www/web2py_klnetcenter/gluon/dal.py", line 1229, in
log_execute
ret = self.cursor.execute(*a,**b)
File "/var/www/web2py_klnetcenter/gluon/contrib/pymysql/cursors.py",
line 108, in execute
self.errorhandler(self, exc, value)
File "/var/www/web2py_klnetcenter/gluon/contrib/pymysql/
connections.py", line 184, in defaulterrorhandler
raise errorclass, errorvalue
InterfaceError: (0, '')

forever again!

Is this the expected behavior? Is web2py or the adapter not detecting
it is not connected and not trying to reconnect unless you ping the
connection?

I think this needs fixing. I have a long running shell process, and
every night I find it stopped working and "broken pipe" errors are all
around... I'm trying to workaround it pinging the connection, but I
think that's not a good fix...

Greets.

[Massimo Di Pierro]

This presents logical problems. It just should not happen. Edit the
mysql config file and change the timeout. If the database is
restarted, web2py needs restarting because there is no mechanism in
the database connection API to notify web2py (or python) that a
connection lost.

On Jun 14, 4:06 pm, Álvaro J. Iradier <airad...@gmail.com> wrote:
> As seen in this thread:
>

> http://groups.google.com/group/web2py/browse_thread/thread/c6653dadbe...

[Álvaro J. Iradier]

I agree there's not way to notify web2py that the connection is lost,
but isn't it possible to detect that the connection broke after the
error is raised, and try to reconnect?

Thanks.

On 15 jun, 03:25, Massimo Di Pierro <massimo.dipie...@gmail.com>
wrote:

[Massimo Di Pierro]

Consider this code:

1) connect
2) start transaction
3) insert a record
4) select
5) insert another record
6) close transaction

If the connection is lost between 4 and 5 and you reconnect what
should the workflow be? Jump to 1? Jump to 6? Should 5 be executed if
3 was not committed?

I do not believe this reconnection can be automated because depends on
your workflow. Connections should not break.

[ron_m]

Unless step 6 is reached and completed if the client closed then I would expect the database server to detect the closed connection and rollback on its own since there is no way to recover the state built on that connection or if the database server is restarted then the database server should perform startup recovery and rollback anything that isn't completed but the client still needs to clean up.

What this smells like is a connection pool will hold open connections for reuse, the database server is restarted because of a maintenance script and now the pool has stale connections. When one of these pooled connections is given to a user task the connection should be detected as stale in the pool logic and a reconnect attempted before handing it over to the user.

What Alvaro describes is similar but it looks like the DAL won't initiate a new connection after a server restart unless a ping is performed. Is it possible there is state being held in the DAL or the mysqldb driver that says there is a connection but is not cleared to allow an attempt to get a fresh open on the database?

[Álvaro J. Iradier]

I agree with Ron. If connection is broken between 4 and 5, I would
expect the transaction to be rollback (it was not commited), and an
error raised. Probably that request will fail, but at least the
connection is not dropped and not recoverable.

I guess in a server restart or shutdown, the server will probably (or
should) wait until active connections are finished, and stop accepting
new connections meanwhile. So the problem is likely to happen when
there are idle connections in the pool, and the server is restarted.
The connnections in the pool are invalid and can't be used anymore,
but they won't recover, as web2py won't notice they are
disconnected...

Greets.

[Alexia Death]

This is still an issue. If for whaterver reason db backend closed the connection my shell running daemon script will be stuck in 100% cpu consuming exception loop and no way to make the model re-establish the db connection even if I do catch the exception. As an ugly workarund I could drop out of python altogether and restart the daemon script with shell tools, but this is so not nice and makes killing the daemon politely just that much harder...

Best,
Alexia

[Richard Vézina]

Hello Alexia,

This is a pretty old thread : https://groups.google.com/d/msg/web2py/Iu36WNoWkX4/bgIob2R6UjsJ

I suggest you to open another one... And tell us more on the issue you experiment, we don't have enough informaiton (traceback, setup, web2py version, os, etc) to help. Since 2011 web2py has change a lot. you may face or not the same issue, I don't know, but we need more info to make sure.

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.