mysql gone away problem with Mysql5

[TomLesters]

Someone reported on quixote list: mysql gone away problem with Mysql5 and there is a new
MYSQL_OPT_RECONNECT parameter for myql_options() of mysql C API,

Has this been fixed?

original post see:
http://mail.mems-exchange.org/durusmail/quixote-users/5367/

---

From: Bo Yang

I had exactly the same problem after upgrading to mysql 5. After 2
weeks of wild goose chasing, I think I found the problem.

from http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html :

"... The reconnect flag in the MYSQL structure is set to 0 by

mysql_real_connect().
Only those client programs which did not explicitly set this
flag to 0 or 1 after
mysql_real_connect() experience a change. Having automatic
reconnection
enabled by default was considered too dangerous (due to the fact
that table
locks, temporary tables, user variables, and session variables
are lost after
reconnection). "

this change happened in 5.0.3. after 5.0.13 there's a new
MYSQL_OPT_RECONNECT parameter for myql_options() of mysql C API, but
I can't figure out how to set it in MysqlDB.

my guess is one or few of the quixote processes idled for too long,
mysql connection timed out, there's no auto-reconnect, and you get
the "'MySQL server has gone away" messages.

this is what I do now: set wait_timeout in my.cnf to 12 hours, and
use cron to restart my quixote processes every 12 hours. far from
elegant, but better than hacking MysqlDB myself.

• bo

http://blog.douban.net

On Jan 18, 2006, at 5:39 AM, Mike Orr wrote:

> I moved my Quixote applications to a new Linux server and upgraded to
> MySQL 5, and suddenly now things hang up sometimes with 'MySQL server
> has gone away' exceptions. The MySQL manual suggests several possible
> reasons (timeout on client thread, large query size/result size, MySQL
> server restarting itself), but I was wondering whether anyone else is
> getting this in a Quixote context, especially with session2. Using
> Quixote 2.4, scgi 1.9, session2 0.6, Apache 2.0.54, MySQL 5.0.16,
> Linux Gentoo 2005.1.
>
> The MySQL server is responding fine; I just have to restart the
> Quixote application. But MySQL shows three idle connections from
> Quixote sleeping. Is something in Quixote or session2 using
> persistent connections?
>
> --
> Mike Orr <sluggoster@gmail.com>
> (mso@oz.net address is semi-reliable)
> _______
> Quixote-users mailing list
> Quixote-users@mems-exchange.org
> http://mail.mems-exchange.org/mailman/listinfo/quixote-users

[Andy Dustman]

If you are using transactions, transparent automatic reconnections are a really bad idea.

Support for changing this (would go in the connect method) has not been added.

Also see:

http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html

I suspect if you put "reconnect" in a defaults file, that will enable automatic reconnections.

[Andy Dustman]

It doesn't appear that putting reconnect (or reconnect = yes) changes things:

Python 2.4.2 (#1, Oct 14 2005, 01:36:01)
[GCC 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0, pie-8.7.8)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> db=MySQLdb.connect(db='test',read_default_file="~/.my.cnf")
>>> db.ping()
>>> db.thread_id()
2331
>>> db.kill(2331)
>>> db.ping()
Traceback (most recent call last):
File "<stdin>", line 1, in ?
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')
>>> db.get_server_info()
'5.0.18-log'