6

After accessing my web app using:
- Python 2.7
- the Bottle micro framework v. 0.10.6
- Apache 2.2.22
- mod_wsgi
- on Ubuntu Server 12.04 64bit; I'm receiving this error after several hours:

OperationalError: (2006, 'MySQL server has gone away')

I'm using MySQL - the native one included in Python. It usually happens when I don't access the server. I've tried closing all the connections, which I do, using this:

cursor.close()
db.close()

where db is the standard MySQLdb.Connection() call.

The my.cnf file looks something like this:

key_buffer      = 16M
max_allowed_packet  = 128M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

It is the default configuration file except max_allowed_packet is 128M instead of 16M.

The queries to the database are quite simple, at most they retrieve approximately 100 records.

Can anyone help me fix this? One idea I did have was use try/except but I'm not sure if that would actually work.

Thanks in advance,

Jamie

Update: try/except calls didn't work.

Jamie
  • 115
  • 1
  • 3
  • 9
  • Check this one: http://stackoverflow.com/questions/207981/how-to-enable-mysql-client-auto-re-connect-with-mysqldb – Jakub M. Nov 22 '12 at 07:53

4 Answers4

25

This is MySQL error, not Python's.

The list of possible causes and possible solutions is here: MySQL 5.5 Reference Manual: C.5.2.9. MySQL server has gone away.

Possible causes include:

  • 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.
  • A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host.
  • You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).
  • You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
  • You also get a lost connection if you are sending a packet 16MB or larger if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around.
  • and so on...

In other words, there are plenty of possible causes. Go through that list and check every possible cause.

Tadeck
  • 132,510
  • 28
  • 152
  • 198
  • Thanks for the quick reply. I've read through that document to no avail, most of it doesn't apply. The only thing I could think of was the automatic reconnection part ,which goes back to my try/except idea. Unless there's another way to automatically reconnect using python – Jamie Sep 16 '12 at 03:48
  • 1
    Ok the automatic reconnection did end up working. Thanks for your help, that was easy! – Jamie Sep 16 '12 at 03:49
  • Actually the solution didn't work. There's still an error after I left the app for about 24 hours. I think a new question would be how do I create automatic reconnections using Python? – Jamie Sep 18 '12 at 04:58
  • @Jamie: Have you tried all the points from the list I have linked? If not, exactly which points you were not able to fix, in addition to automated reconnections? – Tadeck Sep 18 '12 at 05:38
  • Just this clause: You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0). Thanks for your help, Tadeck. How would I go about setting it to automatically reconnect? – Jamie Sep 18 '12 at 06:12
  • @Jamie: There is at least one solution for this specific issue: http://stackoverflow.com/questions/207981/how-to-enable-mysql-client-auto-re-connect-with-mysqldb. Anyway, nice job on going through all the elements on the list - the last one that is left must be true. Btw. how did you eliminate the possibility of finding a MySQL's bug? ;) – Tadeck Sep 18 '12 at 06:52
  • 1
    @Tedeck Your answer help me a lot and save at least 1 day of my time . Thank you so much . – mehdi May 24 '13 at 14:32
0

Make sure you are not trying to commit to a closed MySqldb object

joseph
  • 940
  • 10
  • 19
0

An answer to a (very closely related) question has been posted here: https://stackoverflow.com/a/982873/209532

It relates directly to the MySQLdb driver (MySQL-python (unmaintained) and mysqlclient (maintained fork)), but the approach is the the same for other driver the does not support automatic reconnect.

chjortlund
  • 3,613
  • 2
  • 31
  • 30
-3

For me this was fixed using 

MySQLdb.connect("127.0.0.1","root","","db" )

instead of 

MySQLdb.connect("localhost","root","","db" )

and then

df.to_sql('df',sql_cnxn,flavor='mysql',if_exists='replace', chunksize=100)
citynorman
  • 4,918
  • 3
  • 38
  • 39