8

using pymysql connect to mysql, leave the program running for a long time,for example, leave the office at night and come back next morning.during this period,no any operation on this application.now doing a database commit would give this error.

  File "/usr/local/lib/python3.3/site-packages/pymysql/cursors.py", line 117, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.3/site-packages/pymysql/connections.py", line 189, in defaulterrorhandler
    raise errorclass(errorvalue)

pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

restart the web server (tornado),it's ok.why if leave it long time would get this error?

user2003548
  • 4,287
  • 5
  • 24
  • 32
  • 3
    maybe you're hitting the [wait_timeout](http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout) of your mysql server – Gryphius Aug 06 '13 at 09:20
  • possible duplicate of [Lost connection to MySQL server during query](http://stackoverflow.com/questions/1884859/lost-connection-to-mysql-server-during-query) – msw Aug 06 '13 at 09:26
  • seems it's a wait_timeout problem,since the error only came out during a really long time,how to fix this problem?i don't think enlarge it is a good option. – user2003548 Aug 06 '13 at 09:43

1 Answers1

6

The wait_timeout exists for a reason: long-time idle connections are wasteful of limited server resources. You are correct: increasing it is not the right approach.

Fortunately, this is Python which has a robust exception mechanism. I'm not familiar with pymysql but presumably you've got an "open_connection" method somewhere which allows

try:
    cursor.do_something()
except pymysql.err.OperationalError as e:
    if e[0] == 2013: # Lost connection to server 
        # redo open_connection and do_something
    else:
        raise

Since you didn't post any calling code, I can't structure this example to match your application. There things worth noting about the except clause: the first is that they should always be as narrow as possible, in this case there are (presumably) many OperationalErrors and you only know how to deal with 'Lost connection'.

In case it isn't a lost connection exception, you should re-raise it so it doesn't get swallowed. Unless you know how to handle other OperationalErrors, this will get passed up the stack and result in an informative error message which is a reasonable thing to do since your cursor is probably useless anyway by then.

Restarting the web server only fixes the lost connection as an accidental side effect of reinitializing everything; handling the exception within the code is a much more gentle way of accomplishing your goal.

msw
  • 42,753
  • 9
  • 87
  • 112
  • 2
    the connection interface has a ping function,can use ping(reconnect=True) to solve this problem,thanks your solution is right – user2003548 Aug 06 '13 at 12:04