MySQL server has gone away

Wed, 04/21/2010 - 06:18 - augustin
Project:Programming
Component:Code
Category:support request
Priority:normal
Assigned:Unassigned
Status:closed
Related pages:#50: MySQL server has gone away
Description

I have a php CLI script which takes more than 8 hours, which is longer than the default timeout for the mysql.

As a result, I have the following error by the end of my script:

MySQL server has gone away

I'm finding information about everywhere in the internet. I'll compile what I find in the wiki.

Comments

#1

augustin - 04/21/2010 - 08:11

20.8.11. Controlling Automatic Reconnection Behavior
http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html

In MySQL 5.0, auto-reconnect was enabled by default until MySQL 5.0.3, and disabled by default thereafter. The MYSQL_OPT_RECONNECT option is available as of MySQL 5.0.13.

The problem is that the solution offered implies being able to change the code. I could do that but I'd need to patch Drupal core, which I'd rather not do.

I'm looking for a solution that I could set in my.cnf or in php.ini.

#2

augustin - 04/21/2010 - 08:12

a php.ini setting is available with mysqli.
See #52: Mysql and Mysqli php extensions for server setting questions.

#3

augustin - 04/21/2010 - 08:14

http://drupal.org/node/227445#comment-1042291

This may also help some people: http://www.phpbuilder.com/board/showthread.php?t=10325179
"So, one problem I was getting using mysqli OO was "Error: MySQL server has gone away". I fixed this by enabling the option in the PHP-CLI php.ini to mysqli.reconnect = On. I've also adjusted my my.cnf file to handle bigger datasets. So the file runs, but takes upto 53-54 seconds to run. I think this is due to a timeout and then reconnection. I can't see why PEAR::DB would be faster than OO mysqli."
I wonder if switching from mysqli to mysql in settings.php would help.

#4

augustin - 04/21/2010 - 10:13

Apparently on Ubuntu, the mysqli extensions are installed together with php5-mysql.

So, in my case, I only needed to adapt the settings.php for my Drupal site to use the mysqli instead of mysql.

Also, I had to change the default php.ini:
/etc/php5/cli/php.ini

; Allow or prevent reconnect
mysqli.reconnect = On

#5

augustin - 04/21/2010 - 12:54
Status:active» fixed

The problem is fixed for me, and I put in the wiki the information I found.

#6

robot - 05/10/2010 - 05:39
Status:fixed» closed
Related pages:-50: MySQL server has gone away

Automatically closed -- issue fixed for 2 weeks with no activity.