Solving "MySQL server has gone away", mysql error 2006 [duplicate]

Question

I have a site at hostgator, using their "Business account". It's cheap, and comes with a private SSL cert. HOWEVER, their MySQL settings are such that any db connection goes stale fairly quickly, and then the hostgator staff try to convince you to use a VPS or dedicated server at multiple times the expense.

I'm creating and deploying WebAPIs, which can have a 'conversation' which exceeds 60 seconds. When this occurs, the db connection handle is stale, causing any db operations after my timeout to fail.

I'm in the process of implementing the following solution, but am curious if anyone else has solved this issue (where they can not modify their my.ini or my.cnf to give MySQL more resources) in a similar or completely different manner.

last part of a hook_update:

$ret = db_query( "UPDATE {my_table} set field1 = '%s', field2 = %d ... WHERE vid - %d", $node->field1, $node->field2, ... $node->vid );
if (!$ret) {
       // get private storage directory for this node:
       $cexStorage    = _cex_front_getCexStore();
       $nodeFilesPath = DRUPALROOT.'/'.$cexStorage.'/'.$node->storeDir;
       $fname         = $nodeFilesPath.'/recover.node'; 
       $readyToWrite  = serialize( $node );
       file_put_contents( $fname, $readyToWrite );
       if (file_exists( $fname )) {
         _dbgReport( 'seems to have worked!' );
       }
    }

Then this is called in my hook_form, right after I determine that node->nid is valid:

function _cex_front_recover_node( &$node ) {
  if (!isset($node->storeDir)) {
     _dbgReport( '**************************** _cex_front_recover_node: no storeDir!!!' );
     return;
  }

  // get private storage directory for this node:
  $cexStorage    = _cex_front_getCexStore();
  $nodeFilesPath = DRUPALROOT.'/'.$cexStorage.'/'.$node->storeDir;
  $fname         = $nodeFilesPath.'/recover.node';
  if (file_exists( $fname )) {
    $recovered = file_get_contents( $fname );
    if ($recovered) {
      // no longer needed:
      unlink( $fname );
      $recoveredNode = unserialize( $recovered );
      // restore the key values I need to proceed with this node:
      $node->stage = $recoveredNode->stage;
      $node->rnid  = $recoveredNode->rnid;
      $node->iris  = $recoveredNode->iris;
      cex_front_update( $node );
    }
    else {
      _dbgReport( '********* failed to read "'.$fname.'"' );
    }
  }
}

I'll be pairing the logic down to only store the key fields I need, but this works! Sure beats upgrading my server at 3x the expense. Anyone else doing something like this?

Answer 1

I had a client on a hosting company that refused to increase the value of max_allowed_packet (2MB), regularly leading to the inability to add info-items to the cache. I solved that by adding compression. See Compress cache data before storing it in the database.

In your case, reducing the resource usage may be done with spreading it out over batch operations.

Answer 2

We had a similar issue where we are using two databases. We would do some work on the first database, then run very long-running processes against the second database, then when we tried to use the first database again, the connection had died.

We resolved this using the mysqli.reconnect setting in php.ini - if you don't have access to that, you can add it to your settings.php. There are some caveats with this setting, such as transactions being partially completed, but these will probably not be relevant to your setup.

Answer 3

This error is usually related to parsing of large amount of data, so database may fail to respond (timeout) as it can't handle such sizes due the given limits, therefore the server timing out and closing the connection.

To fix it, you need to increase further up the value of max_allowed_packet in your my.cnf (e.g. ~/.my.cnf) under [mysqld] section, e.g.

[mysqld]
max_allowed_packet=256M

Try with 256M, if won't help, try increasing further more (e.g. 1G).

In your case, I think max_allowed_packet should be under [mysqld] section specifically, not under [mysqld_safe], so settings are applied to the right component.

See: B.5.2.9 MySQL server has gone away for more detailed information.

Another thing is that in this particular case it happens in shutdown function (which is after Drupal finished the processing of the site), therefore it could be related to some cron or debugging tasks.

---

If your hosting provider refuses increasing max_allowed_packet, you should consider changing to different hosting provider which supports Drupal system requirements.

---

Related:

• ERROR 2006 (HY000): MySQL server has gone away at stackoverflow
• MySQL error 2006: mysql server has gone away at stackoverflow