I'm writing a script that needs to hold a "FLUSH TABLES WITH READ LOCK" query open long enough to destage multiple gigs of memory to the disks so I can take a LVM snapshot of the data. What I'm running into is that the connection will die sometime before the memory gets destaged so the subsequent query to "UNLOCK TABLES" comes back with the following error:
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query').
I've read through the docs and the mailing list and so far haven't seen a good answer as to how to force a connection to stay alive. Any ideas?
Thanks in advance,
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
| 28800 |
+----------------+
1 row in set (0.00 sec)
mysql> set @@wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)
mysql> # wait 5 or more seconds
mysql> select @@wait_timeout;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: NONE
+----------------+
| @@wait_timeout |
+----------------+
| 28800 |
+----------------+
1 row in set (0.00 sec)
mysql>
So execute SET @@wait_timeout=n where n is big enough for you to do your thing. Note that 0 will not mean "forever"; it really means zero. Negative numbers seem to have the same effect.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm writing a script that needs to hold a "FLUSH TABLES WITH READ LOCK" query open long enough to destage multiple gigs of memory to the disks so I can take a LVM snapshot of the data. What I'm running into is that the connection will die sometime before the memory gets destaged so the subsequent query to "UNLOCK TABLES" comes back with the following error:
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query').
I've read through the docs and the mailing list and so far haven't seen a good answer as to how to force a connection to stay alive. Any ideas?
Thanks in advance,
This is not really a MySQLdb question. However:
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
wait_timeout looks like what you want.
mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
| 28800 |
+----------------+
1 row in set (0.00 sec)
mysql> set @@wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)
mysql> # wait 5 or more seconds
mysql> select @@wait_timeout;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: NONE
+----------------+
| @@wait_timeout |
+----------------+
| 28800 |
+----------------+
1 row in set (0.00 sec)
mysql>
So execute SET @@wait_timeout=n where n is big enough for you to do your thing. Note that 0 will not mean "forever"; it really means zero. Negative numbers seem to have the same effect.
Hi,
wouldn't the patch
http://sourceforge.net/forum/forum.php?thread_id=1639447&forum_id=70461
which allows setting the MYSQL_OPT_RECONNECT flag also solve this problem?
Kind regards,
Markus
Thank You - I believe that will work perfectly.
I doubt that. The locks are most likely released when the connection is dropped.