Ticket #872 (new defect)

Opened 2 years ago

Last modified 6 months ago

MySQLdb/gears does not reconnect if mysql drops connection

Reported by: eastham@gmail.com Assigned to: anonymous
Priority: high Milestone: 1.1
Component: SQLObject Version: 1.0.1
Severity: major Keywords: mysql
Cc:

Description

At present, TG is permanently hosed if you lose the mysql connection. This can happen if the connection times out, or the server is restarted. This is not going to fly in a production environment, or in one where your hosting provider does dumb things to the database settings.

On Mark Ramm's request, I am filing this ticket so that once MySQLdb exposes an auto-reconnect interface, SQLObject/Gears can make use of it.

I have submitted a patch to the MySQLdb sourceforge that exposes the interface. Hopefully they will add it soon. Link:http://sourceforge.net/tracker/index.php?func=detail&aid;=1483074&group;_id=22307&atid;=374934

Here's a thread on this topic, which includes a quicker hardcoded workaround (also in the MySQLdb code -- I couldn't find any other way to do it):

http://groups.google.com/group/turbogears/browse_frm/thread/4f0ae451e72b4717/7cfd7592010b4959?lnk=arm#7cfd7592010b4959

Change History

05/16/06 12:57:53 changed by jvanasco@gmail.com

Just to add - postgres exhibits a similar problem ( at least with sessions ) -- but seems to reconnect after an invalid request

A workaround I found that seems to be working, is to use tg scheduler as such: 

import psycopg2 
def get_db_for_sessions():
    dsn = get('sessions.postgres.dsn')
    return psycopg2.connect(dsn) 

def _session_db_ping():
    print "_session_db_ping"
    x= get_db_for_sessions()
   
add_interval_task( action=_session_db_ping, taskname='_session_db_ping', initialdelay=0, interval=270 )

that pings the db by calling a connect every 4.5 minutes -- it would probably work with mysql

08/25/06 23:28:26 changed by jorge.vargas

I have move this to the SO bug tracker ticket 1547004,http://tinyurl.com/nqqyv if you have new information on this please post it there.

I'm not closing this bug until that one is closed.

09/22/06 20:26:53 changed by jorge.vargas

  • milestone changed from 0.9 to 1.0.

10/06/06 00:14:34 changed by stickystyle

The same issue exists in SA, it is referenced in this ticket http://www.sqlalchemy.org/trac/ticket/121

01/12/07 06:27:54 changed by alberto

  • milestone changed from 1.0 to 1.1.

01/31/07 16:36:38 changed by jorge.vargas

I have talk to Oleg about this several times someone track it to the driver code and I believe in the latest release it should work.

as for the SA code it seems to be commited based on the ticket.

in both cases please test and report back here.

03/28/07 12:42:01 changed by alberto

  • milestone changed from 1.1 to __unclassified__.

Batch moved into unclassified from 1.1 to properly track progress on the later

03/30/07 12:17:45 changed by anseljh

  • keywords set to mysql.
  • version changed from 0.9a5 to 1.0.1.
  • milestone changed from __unclassified__ to 1.0.2.

Hello... I just ran into this bug and, between the two bugs on this Trac, bugs on other trackers, and multiple mailing list threads, I'm rather confused on the current status. I would really appreciate it if someone could post an "executive summary" of this bug's status. If it's fixed, exactly which package(s) fix it? I think this would be helpful for other people too and perhaps worthy of a TG wiki page. It should also be a priority to make sure the next TG release includes/requires the fixed package(s).

As for me, I get a 500 error with a "MySQL server has gone away" exception in the morning if I leave my TG app running overnight. This is obviously not going to fly; it's a blocker. :(

I'm running this on Fedora Core 6's stock TurboGears 1.0.1 yum/RPM packages:

  • TG 1.0.1
  • MySQLdb 1.2.1
  • SQLObject 0.7.2
  • MySQL 5.0.27

Thanks a lot!

P.S. I'm changing the version for this bug from 0.9a5 to 1.0.1 since I confirmed it with 1.0.1. I'm also changing the milestone from unclassified to 1.0.2. Feel free to change as appropriate, but I think those make sense.

05/01/07 12:56:57 changed by alberto

  • milestone changed from 1.0.2 to 1.0.3.

07/01/07 18:14:05 changed by faide

  • milestone changed from 1.0.3 to 1.1.

11/25/07 19:09:59 changed by Chris Arndt

Both the SQLObject and the SQLAlchemy bugs referenced above are now closed. The closing message to the SQLObject bug indicates that you need MySQLdb 1.2.2+ and MySQL 5.0.x.

Anyway, this isn't really a TurboGears bug, is it? Should we close this?

11/26/07 11:24:11 changed by anseljh

My sense is that this bug (no matter what the source) has been enough of a showstopper that TG should provide a workaround if the underlying bugs are in other packages. Therefore, I propose not closing this until either (a) we have a verified fix in the underlying packages, or (b) provided a good workaround.

I'll make sure I have the latest packages, will disable the workaround I've been using, and will post the results back here (hopefully tonight?).

11/26/07 14:33:15 changed by Chris Arndt

Good, sounds like a plan! But make sure you don't keep the db connection alive by testing it periodically ;-)

Take your time, this ticket has been open for so long, another few days won't hurt.