SQLAlchemy and MySQL - Broken pipe

[thesamet]

Hi,

I am using SQLAlchemy with TurboGears. SQLAlchemy version is 0.2.8.

After few minutes of inactivity, when a query happens, I get the
following traceback:

File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 226, in
select
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 232,
in select_whereclause
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 305,
in _select_statement
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 267,
in instances
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line
114, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
246, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
270, in execute_clauseelement
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
286, in execute_compiled
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
282, in proxy
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
316, in _execute_raw
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
504, in log
File "build/bdist.linux-i686/egg/sqlalchemy/util.py", line 100, in
write
IOError: [Errno 32] Broken pipe

In my development server I get the following after inactivity:

File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 226, in
select
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 232,
in select_whereclause
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 305,
in _select_statement
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 267,
in instances
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line
114, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
246, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
270, in execute_clauseelement
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
286, in execute_compiled
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
282, in proxy
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
321, in _execute_raw
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line
340, in _execute
SQLError: (OperationalError) (2006, 'MySQL server has gone away')
'SELECT channels.hash AS channels_hash, channels.name AS channels_name,
channels.ip AS channels_ip, channels.sample_updated AS
channels_sample_updated, channels.channel_id AS channels_channel_id,
channels.stat_id AS channels_stat_id, channels.create_date AS
channels_create_date \nFROM channels, stats \nWHERE channels.stat_id =
stats.stat_id ORDER BY stats.downloads DESC \n LIMIT 10' []

I believe these are related since they both happen only after some
timeout. What could it be?

Thx,
Nadav

[Michael Bayer]

mysql has two configurable settings "wait_timeout" and
"interactive_timeout" which will affect mysql closing a connection that
has had no activity for a number of seconds. I think these default to
8 hours, so a few minutes its pretty fast.

Once you determine what those are set to, you can create your engine
with a "pool_recycle" value that is lower than the interactive_timeout
value. this timeout will be checked at connection checkout time where
the underlying connection will be recycled.

[thesamet]

Both variabless are set to 28800 (8 hours). It turns out that the first
traceback exception
was actually from the log function. It seems that I able to avoid it by
turning off sqlalchemy's
echo.

The 'MySQL server has gone away' is probably the real issue. In mysql's
reference
(http://dev.mysql.com/doc/refman/5.0/en/gone-away.html) they suggest
several
possible causes for this. One of them is that the mysql server is
crashing. According to it's uptime
this is not the case.

The second option there is:

You tried to run a query after closing the connection to the server.
This indicates a logic error in the application that should be
corrected.

Could it be the case?

[Michael Bayer]

On Oct 6, 2006, at 5:21 AM, thesamet wrote:

>
> You tried to run a query after closing the connection to the server.
> This indicates a logic error in the application that should be
> corrected.
>
> Could it be the case?

not really....we dont really close connections on too many occasions
except for when its recycling.