8

This is the error I am suddenly getting.

Additional uncaught exception thrown while handling exception.

Original
PDOException: SQLSTATE[70100]: Unknown error: 1317 Query execution was interrupted...my query

Additional
PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away...my query

After doing some research it sounds like a custom module, which I have made, is attempting a query which is too big. A solution that seems to work for a lot of people is this:

  1. Open my.ini
  2. Change "max_allowed_packet" from "1m" to "16m" (or larger)
  3. Save my.ini and restart MySQL

However, I am on a shared server, so I cannot edit my.ini, and although my provider has confirmed to me that the value is set to 1M, they won't increase it.

I also tried adding this: ini_set('mysqli.reconnect', 'on'); to settings.php. It didn't solve the problem.

Does anyone know of a another solution, or work around?

Thanks.

8

Shared hosting companies do not give away the SUPER privilege. If you have it, you can easily set max_allowed_packet to 512M by just running this :

mysql> SET GLOBAL max_allowed_packet = 1024 * 1024 * 512;

No restart is required. Then, all incoming connections thereafter can have a 512M packet.

You cannot do this:

mysql> SET max_allowed_packet = 1024 * 1024 * 512;

because you get

ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

The only alternative I can think of is to switch to Amazon EC2 and install MySQL there. That way, you can have free rein to edit my.cnf/my.ini and have the SUPER privilege.

0

I asked my provider to increase the value, but they would not. I can understand why, they are a business and they want me to pay 29.99 a month for a dedicated server, rather than 4.99 for a shared server.

However, there is a solution: identify the query which is too big and break it up into two or more parts. I split my problem query in half: problem solved. Inefficient - yes - but it's better than my site not working at all or having to pay the extra.

  • Please keep in mind what I said about MySQL Packets ( superuser.com/a/273833/76764 ) The usual suspects that can clog a MySQL packet are big TEXT/BLOB fields. So you may want to think about compressing your TEXT/BLOB fields instead jerry rigging code. – RolandoMySQLDBA May 9 '13 at 18:13
  • Yes, maybe that can be the problem, but although my SELECT query was relatively complex, the result set returned was just a few hundred characters of text. – 24ma13wg May 9 '13 at 18:17
  • 2
    You may start running into problems with the cache tables doing INSERTs that are pretty big. Your solution won't work here, or any other place where Drupal is doing the query. – mpdonadio May 9 '13 at 18:17
  • It isn't an INSERT, just a SELECT query with about 25 joins. All done with the query builder. Do you know why this would cause such an error? Doing the INSERT via a form submit handler isn't a problem. – 24ma13wg May 9 '13 at 18:23
0

Problem is because max_allowed_packet, you set it with bigger value.

How to fix this problem(if you are in windows and using xamp else find my.ini file)
Go to xampp\mysql\bin
Open my.ini
Change "max_allowed_packet" from "1m" to "16m"
Save my.ini
Now restart MySql through the XAMPP control panel.

Found this solution here. http://minorpoint.blogspot.com/2007/09/mysql-server-has-gone-away.html

Not the answer you're looking for? Browse other questions tagged or ask your own question.