By ramones79 on

After enabling Views core module I get alot of MySQL server has gone away errors. Still the site is working, but very unpleasant with these errors all over the pages, and its long long lines of it.

for example:
Warning: MySQL server has gone away query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', '%message in %file on line %line.', 'a:4:{s:6:\"%error\";s:12:\"user warning\";s:8:\"%message\";s:172236:\"MySQL server has gone away\nquery: UPDATE cache_update SET data = 'a:14:{s:13:\\"advanced_help\\";a:10:{s:5:\\"title\\";s:13:\\"Advanced help\\";s:10:\\"short_name\\";s:13:\\"advanced_help\\";s:10:\\"dc:creator\\";s:13:\\"merlinofchaos\\";s:11:\\"api_version\\";s:3:\\"6.x\\";s:17:\\"recommended_major\\";s:1:\\"1\\";s:16:\\"supported_majors\\";s:1:\\"1\\";s:13:\\"default_major\\";s:1:\\"1\\";s:14:\\& in /home/slluspq/public_html/topvisia.net/bulvision/includes/database.mysql.inc on line 128

this is just the first paragraph of many to follow when a page is loaded.

I've read about that error - it seems that it appears if you overload the MySQL server, but I don't believe Views is such a consuming module? And besides, I have SWF tools and FlowPlayer 3 for a video on the front page - and it works fine, the problem is only when I turn on Views module .
I'm on Drupal 6 the latest stable (6.12) and Views 2.6, enabled PHP5 for the Drupal folder and subfolders.

What is the problem with Views? Any suggestions to fix this?

Comments

ramones79’s picture

ramones79 commented

I forgot to include the website URL http://www.topvisia.net/bulvision/
By the way it looks like the anonymous view of the website does not generate the error. It happens only when I browse the Admin section.

zbricoleur’s picture

zbricoleur commented

No, it is the combination of Views and max_allowed_packet being too small that is causing your problem. Been there, done that.

ramones79’s picture

ramones79 commented

zbricoleur - how can I workaround that ? Could you please help?
I contacted with my hosting provider and they suggested that I need to change "persistent connection" to MySQL to disabled. But I don't know where such an option resides within Drupal. Is it part of the UI admin section or I need to change some conf. file? I was also told, that the most likely reason for the error is that Drupal probably makes too many connections to the MySQL without to close them so it reaches the limit of connections to the DB and the MySQL cuts them off.

Btw there is a Joomla based website on the same hosting with nearly 18-20 mb database, but never faced such errors. So I wonder how my 2-3 mb Drupal DB can cause this. I'm sure there is some workaround to this, I need to tweak some settings in Drupal for connection with MySQL, but don't know where to do that.

I really hope someone can help me, please

ramones79’s picture

ramones79 commented

Just received this data from my hosting provider:

max_allowed_packet 1048576
max_user_connections 20

hope this helps too

ramones79’s picture

ramones79 commented

Ok I fixed the problem (by myself as usual).
I am posting here the 2 possible solutions for anybody who has the same problem, I hope this will be of help to somebody :

1. Quick approach (not recommended since it will leave you with no module update information)
Just go to Admin -> Site Building -> Modules and disable the 'Update status' module. Voala :) No more nasty MySQL warnings and error messages.

Anyway you will be left without actual information on any new updates or new major versions for your Drupal and modules.

2. The real fix (requires hacking of 2 Drupal core files)
Here is the real solution, it will require you to get hands dirty, but will fix your problem.

First make a backup of these two files - just download them to some directory so you have them just in case:
includes/database.mysql.inc
includes/database.mysqli.inc

Now you need to edit these two files (I edit them directly on the FTP server, but you can make additional copies locally, edit them, save and then upload back)

Add the following lines of code (on a new line, as explained):
In includes/database.mysql.inc, at the end of function db_connect(), under the "SET NAMES" line:

mysql_query('SET SESSION wait_timeout = 60', $connection);

In includes/database.mysqli.inc, at the end of function db_connect(), under the "SET NAMES" line:

mysqli_query($connection, 'SET SESSION wait_timeout = 60');

Now you should get no more MySQL errors.
If you increase the number of istalled modules on your Drupal you may need to increase the timeout from 60 to a bigger value, for example 90, but I seriously doubt it will be ever needed.

The idea of all this is that the update module will require more and more time to check for updates for the increasing number of installed modules on your Drupal site. At some point this time will exceed your server's default value for timeout and this causes MySQL "server gone away" errors.
On many webhosting companies' servers this timeout is set to a very low value (too conservative) and that's why so many people are facing this problem.

3. For some people it turns out, that increasing the max_allowed_packet in MySQL fixes the problem. This was not my case, but maybe yours.
By default MySQL has max_allowed_packet of 1 MB. According to many people (and stated in the official Drupal requirements) it needs to be at least 16MB and optimal 32MB.
Ask your webhosting provider to increase this for you and see if it helps. If they deny to do so - you should check this:
http://drupal.org/node/379976 - Max packet module

4. Additionaly there are some opinions, that the cache system of Drupal is rather low effective, because the cache uses MySQL. So some people suggests that it is a good idea to force Drupal to use file based cache system, instead of the MySQL based one.
So you can check this module: http://drupal.org/project/fastpath_fscache

Hope this information will be useful to you. :)

ramones79’s picture

ramones79 commented

Credits goes to cog.rusty ( http://drupal.org/user/33697 ) and this thread maybe additionally useful to you, so check it out: http://drupal.org/node/259580
:)

ramones79’s picture

ramones79 commented

By the way upgrading your Drupal core may overwrite your fix so you may need to re-fix after an upgrade. Just to keep that in mind.

kenorb’s picture

kenorb commented

You may try to use db_tweaks module to increase your packet size.

ramones79’s picture

ramones79 commented

I did tried it before come in to the solution. It didn't work in my case. I think because of the server settings. But they guys from the hosting company were good enough to rise max_allowed_packet from 1 MB to 32 MB

But anyway that wasn't the real problem. It was not max_allowed_packet problem in my case. It was wait_timeout.