MySQL server has gone away

[MajorHeadache]

Got this after upgrading from 9.0.6.1 to 9.1.2.1

I'm getting emails all day. During peak times, I might get 50 an hour, during slow times, I might get one / hour or less.

The messages are 99% like this:

Code::

On /Forums/viewforum/f=8.html While executing query "SELECT g.group_id, g.group_name, g.group_single_user FROM moo_bbgroups AS g INNER JOIN moo_bbuser_group AS ug ON (ug.group_id=g.group_id AND ug.user_id=10 AND ug.user_pending=0)" the following error occured: MySQL server has gone away In: /home/mysite/public_html/includes/classes/cpg_member.php on line: 88 Guest information: User id: Username: Admin: No IP: 0.0.0.0 Host: 0.0.0.0

Always that same query, always that same line of the same php file. The location changes. Also maybe worth noting, the IP and Host are always 0.0.0.0, even when the location is reading a private message, which indicates it's a member.

I have verified that during this time, MySQL server is indeed up and serving other sites on the box. It's running on the same box as the site too.

Rarely though, during one of these "outages", I will get an email that DOES have the host and IP correct, but the query and php file are different, for example:

Code::

On /Forums/viewforum/f=151.html While executing query "SELECT * FROM moo_banner WHERE type='0' AND active='1' ORDER BY RAND() LIMIT 0,1" the following error occured: MySQL server has gone away In: /home/mysite/public_html/includes/functions/display.php on line: 326 Guest information: User id: 1 Username: Anonymous Admin: No IP: 76.190.yy.xx Host: cpe-76-190-yy-xx.woh.res.rr.com

Couple things that may be related or not:

in phpmyadmin, when I look at the _session table, the IP is garbled. For example: Lđ/ is whats showing on one. many are blank or just have one letter or symbol.

I had the collation problem. My solution has been to edit specific tables to change it from swedish to utf8)general_ci using the drop down menu. I changed enough to not get error messages but I haven't changed them all. And even the ones I do change still show swedish under Row Statistics. I can't figure out how to edit that, or if I even need to.

I think I read somewhere there is some change that should be made to the themes to make them compatible to 9.1.2.1. I can't find that thread anymore and since i haven't had any outward errors, I'm not attributing anything to this. (But maybe I'm wrong) Whatever the change is, I haven't made it.

Does any of this make any sense?

[NanoCaiordo]

Ip garbled is ok since we save it as variable binary length.

0.0.0.0 could be due a bug on 64bit OS (probably fixed already, I say "probably" because we do not have a 64bit OS where to test it and be sure that its fixed) or other little issues like table/field collation and other bugs related to RDBMS.

So are you saying that changing collation to your table fixed the MySQL server has gone away error?

[MajorHeadache]

No, I"m not saying collation caused the error. I'm saying I have this MySQL problem and I'm looking for the reason. I also have these other little issues which may, or may not be related.

[Mel]

dragonflycms.org/Wiki/id=58.html#4d

[MajorHeadache]

I can appreciate that rule, however, it is my suspicion that something on my site, whether it's the script, a mondule, or the SQL data, is causing the problem. I was hoping it might ring a bell with someone.

[NanoCaiordo]

DId you start getting the error immediatly after you updated to 9.1.2.1 OR days later?

Do you know if your ISP have updated MySQL on the same period?
Any other possible clue cna help us to found the issue.

[MajorHeadache]

What happened was immediately after the upgrade I started getting the collation error, which I fixed in a half-assed way by only changing the tables that were mentioned in the error messages.

The next day Apache was upgraded from 1.3 to 2.? and PHP was upgraded from 4 to 5. It was after that that I first saw this specific error. My first thought was it was the server issues, but my server guy keeps pointing to the site.

I would pay good money to find someone who knows BOTH server configuration AND DragonflyCMS to take a look at my situation and make whatever changes are necessary.

[Mel]

major,

Nano is trying to help you find the answers. He will be back on later this afternoon his time, when he gets a chance, he will try to help you.

We spoke about it earlier, so please do not fret, we will come up with something.

[MajorHeadache]

Thanks!

[MajorHeadache]

I don't know if this is a factor or not, but one thing I've also noticed with debug on:

This query is awfully slow:

blocks/block-Who_where.php

* 0.9891 - LINE 21: SELECT s.uname, s.module, s.url, u.user_allow_viewonline FROM moo_session AS s LEFT JOIN moo_users AS u ON u.username=s.uname WHERE guest=0 OR guest=2 ORDER BY s.uname
* 0.0002 - LINE 44: SELECT host_addr, uname, module, url, guest FROM moo_session WHERE guest=1 OR guest=3 ORDER BY guest, host_addr

That's with 43 members online.

[NanoCaiordo]

I'm aware that in some circumstances this could happen, I'm working on it already from few weeks.
For a couple of days I'll dedicate all my free time on this annoying issue and I'll be back to this topic.

On the mean time go to includes/db/sql.php and around line 84 you should have the send_mail function. Comment this line so you will not have your email inbox full of emails

Code::

//if (!send_mail($mailer_message .....

[NanoCaiordo]

I've sent you a pm and there is my email included. Could you please forward 10 random sql error emails to my email please?

[MajorHeadache]

I've just come across something regarding the binary IP addresses. I have two sites both running 9.1.2.1. One is a clean install and one is upgraded from 9.0.6.1. What I've just realized is that teh table structure on the older site for ip_address is varchar(8), while the new site has it as varbinary(17). My understanding is if I change the structure on the old site, it will wipe out all the old data.

It is the case that my IP addresses are getting logged incorrectly. Most visitors show as 0.0.0.0 and members IPs seem normal, but checking the facts shows they are in fact wrong.

So, how can I fix this, and if possible, maintain my old data?

PS. upgrading to 9.2 isn't an option until many more modules and themes have been ported, and to be honest, some of them I doubt will ever be ported.

[NanoCaiordo]

MajorHeadache wrote:

upgrading to 9.2 isn't an option until many more modules and themes have been ported, and to be honest, some of them I doubt will ever be ported.

Does your site use all modules and themes availble to download ... just kidding

Something that NO ONE noticed, but been written many times already, is that by enabling CPG_DEBUG the system will bridge old functions to the new one making your site working as it should but trowning warnings.

This is done to help everyone to visually see where the patches needs to be applied at modules and themes level.

As we did helped many users already, we can help you too.

Again, modules needs upgrade only if it works with ips addresses, otherwise it will be 100% compatible.

[NanoCaiordo]

I've been able to fix this annoying issue, and guess what ... DragonflyCMS is not the source.