Error: SQL server gone away?

[rackem]

Occasionally, I receive the following error.

Error loading module MySQL server has gone away SQL=SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle, m.params, mm.menuid FROM btxz_modules AS m LEFT JOIN btxz_modules_menu AS mm ON mm.moduleid = m.id LEFT JOIN btxz_extensions AS e ON e.element = m.module AND e.client_id = m.client_id WHERE m.published = 1 AND e.enabled = 1 AND (m.publish_up = '0000-00-00 00:00:00' OR m.publish_up <= '2012-07-22 21:39:40') AND (m.publish_down = '0000-00-00 00:00:00' OR m.publish_down >= '2012-07-22 21:39:40') AND m.access IN (1,1,2,3,5) AND m.client_id = 0 AND (mm.menuid = 216 OR mm.menuid <= 0) ORDER BY m.position, m.ordering

Seems to happen the first time I load a Fabrik list in a session. A refresh makes everything work fine. Anybody else seeing this?

 

[rob]

Not recently no. This generally happens when a query takes too long to load
Do you have J cache on? If so when you turn it off does the error occur each time the page is loaded?

You could try increasing the MySQL timeout from from MySQL configuration file (usually in /etc/my.ini or /etc/mysql/my.ini)

 

[rackem]

Thanks for the reply Rob. I had J cache off. Not sure if it matters, but I had F! debug on for a while as we worked through some issues.

Like I said, it is intermittent but happens enough to make me concerned. Regular users of the site would find the error quite alarming and probably would not try a simple refresh to fix it. Soon after my initial post, it happened after I had been using the site front-end for some time so the first load comment I made would seem to be proven incorrect. Perhaps it is an issue with my host? Sometimes page loads take a really long time for no apparent reason.

I turned my J cache on and F debug off and will continue to monitor the situation. I'll just leave this thread open for the time being as well.

 

[cheesegrits]

I've not seen that myself, or seen anyone else report it. And that's a J! query, not Fabrik, and afaict from my site, happens early in the J! page build, before we get involved.

-- hugh

 

[rackem]

After more searching, this appears to be an issue with my host or my database so I will close this thread. Thanks for your comments!

 

[rackem]

Well, I am re-opening this thread as this issue has gotten much worse, all leads have fizzled out, and I am completely baffled. This is only happening with the Fabrik content on my site. I will try to summarize everything with this issue in case anyone else has experienced this and can provide any assistance.

I am having problems when I try to access any of my Fabrik lists. It seems to happen most often when going to the content for the first time after not viewing the site for a while ( > 30 minutes?). Then once the page is refreshed, the site works fine. Only started happening relatively recently (< 1 month). It was very intermittent at first (once a week) but now the frequency has increased to where it happens very often (several times a day or more) as described.

The issue is that the page takes a long time to load and then I receive the following error:

?Error loading module MySQL server has gone away SQL=SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle, m.params, mm.menuid FROM btxz_modules AS m LEFT JOIN btxz_modules_menu AS mm ON mm.moduleid = m.id LEFT JOIN btxz_extensions AS e ON e.element = m.module AND e.client_id = m.client_id WHERE m.published = 1 AND e.enabled = 1 AND (m.publish_up = '0000-00-00 00:00:00' OR m.publish_up <= '2012-07-30 12:37:24') AND (m.publish_down = '0000-00-00 00:00:00' OR m.publish_down >= '2012-07-30 12:37:24') AND m.access IN (1,1,4,5) AND m.client_id = 0 AND (mm.menuid = 274 OR mm.menuid <= 0) ORDER BY m.position, m.ordering?

It is odd I agree Hugh that this is a Joomla query, not Fabrik.

Lately, I?ve also seen this error on occasion "Fatal error: Call to a member function get() on a non-object in /home/mypoolst/public_html/components/com_content/router.php on line 27
I?ve also noticed the page load just hang.

Here are things I?ve tried or otherwise noted:

This has happened in IE9 and Chrome but haven?t tried Firefox. All of my lists are accessed through menu links. I have had it occur when trying to view from the front or back end.

Tried clearing browser cache - no change
Tried clearing Joomla cache - no change
Tried all three Joomla cache settings - no change
Turned on Gzip in Joomla Configuration - no change

Seeing as how no one else is reporting the issue, I wondered if my database got corrupted somehow so I tried checking, repairing, and optimizing my database. All tables were OK except that ?..._finder_tokens? and ?_finder_tokens_aggregate? had a note that the storage engine for the table doesn't support these actions. Is that a clue?

Rob, I contacted my host (Site Ground) but they said they were unable to provide any assistance and simply referred me back here or to switch to another extension. I am on Shared Hosting so I can't increase my MySQL timeout. They didn't think this was the issue as my site is quite small and low traffic. Of course it could be everyone else on my server but the "reproducible" nature of the issue suggests it is something on my site.

Can anyone please help or provide any ideas of things to try? This is a showstopper for my site as visitors simply won?t accept it as is. I have searched Google, my host, and the Joomla forums without success. I have not made any significant structural changes to my website.

Here are links to my Fabrik content:
http://www.mypoolstats.com/index.php/competition
http://www.mypoolstats.com/index.php/wi-player-ranking/list/14
http://www.mypoolstats.com/index.php?Itemid=116

 

[troester]

First try:hit
It seems to be very reproducible; I've used firefox and your link http://www.mypoolstats.com/index.php/competition
clicking the "Venue" it's loading very long and than I get

• JUser: :_load: Unable to load user with ID: 43
• Error loading module MySQL server has gone away ....

I know Hugh is struggeling with this JUser error...

Are you using more than one fabrik connection?
Did you check + save the connection settings? (Did you copy your site?)

 

[jfquestiaux]

What I would do is to install the site locally (on WAMP or MAMP) using Akeeba backup for the transfer to see if it is a server related error.

 

[rackem]

JUser: :_load: Unable to load user with ID: 43

I haven't seen this one yet. I use User ID 43 to display example data to guest users on some of my lists but not all.

Are you using more than one fabrik connection?

No, only one connection is used (the original database)

Did you check + save the connection settings? (Did you copy your site?)

I just tried this but still receive the error. This site is not a copy

What I would do is to install the site locally (on WAMP or MAMP) using Akeeba backup for the transfer to see if it is a server related error.

I backup and install to my local test rig (XAMP) to experiment with new ideas and have never received this error. Last did this about 2 weeks ago but not since things got really bad. Just to note I just transfer from my live to development but not the other way.

This is really confusing because there are clues that would seem to point to my server, Joomla, and/or Fabrik.

 

[felixkat]

Well personally I think it's your host, seems to be a few complaints on their website about it this sort of problem. I don't think your site is particulary complex so I'm pretty sure the forums would be filled with this error if a problem existed.

There are so many factors involved when using a shared server that it's often hard to pin point the problem, especially when the host don't want you to find it.

If you want to test on another live host I'm more than happy to supply you some space to play with. If you don't have any issues then at least you can go back to your host and tell them clearly it's their problem.

 

[rackem]

Not sure if it helps, but here is my last post with my host and their response.

2012-07-30 12:31pm by mypoolst - Hello David,
Thanks for answering my questions. Would you please review my website logs to identify the source of the problem? Many of the sources that I have reviewed in regards to this problem point the the host as the best place to go in resolving the problem. I have gone through several forums of the applications I am using and none are reporting this issue. If you identify the actual query that is causing the problem or tell me how to do it, then I have something to work with. As Dimo mentioned earlier, this is a very small site that takes very little in terms of resources.

On the other hand, the server itself may be the problem? Most of the time, the site loads and functions just fine. This is an intermitent error that occurs after a page takes a long time to load. I was noticing these delays since I started with SiteGround. Back then, the page would load eventually just fine but now I am receiving these errors after the delay.

Chris

2012-07-30 01:26pm by David.S - I investigated the case closely but I am afraid that I wont be able to tell you anything new at this point.

Yes, the reported queries appear to be generated by Joomla but the problem occurs only when the scripts of the extension are loaded in the execution line of the application. Which makes me believe that they are stalling the execution of the application.

I am not sure what forums have you checked so far but a simple Google search for "Fabrik slow loading" produces more than 2 million results. The two top results are for topics in their official forums in which you can read the complaints of other people with very similar to your problem:

http://fabrikar.com/forums/showthread.php?t=10041

http://fabrikar.com/forums/showthread.php?t=22833

The website is small, I can agree, but the thing is that one can generate a slow query even with a database which has only table with only column and only one record inside of it.

I can assure you that the server environment is not the problem in this case. The average server load has been quite low for the last 72 hours and there were no peaks in the server load. We have been working with Joomla for years and we are constantly tweaking further our custom-made server-side software to provide better and better experience to our Joomla users.

My recommendation in this case is to address this problem with the developers of the extension. If they are unable to help you, you can either consider using an alternative extension or hire a professional developer.

Best Regards,
David S.
SiteGround Technical Support Team

(From above)

Yes, the reported queries appear to be generated by Joomla but the problem occurs only when the scripts of the extension are loaded in the execution line of the application. Which makes me believe that they are stalling the execution of the application.

Not sure how to respond to that. I haven't ruled out my host by any means but without data I am stuck on that front.

Well personally I think it's your host, seems to be a few complaints on their website about it this sort of problem. I don't think your site is particulary complex so I'm pretty sure the forums would be filled with this error if a problem existed.

Agreed but I find the behavior suspicious enough to not rule out anything at this point. Not that I claim to be an expert by any means. First and foremost, the people in this forum are been absolutely wonderful to work with so I wanted to raise the question. Thanks again to all!

If you want to test on another live host I'm more than happy to supply you some space to play with. If you don't have any issues then at least you can go back to your host and tell them clearly it's their problem.

Thank you for your generous offer. That would be so awesome. I would like to give it a try.

 

[felixkat]

WARNING WARNING RANT IN PROGRESS!!!!

I am not sure what forums have you checked so far but a simple Google search for "Fabrik slow loading" produces more than 2 million results.

Rubbish and what a complete idiot for suggesting that to you.

"Fabrik Slow Loading" in quotes produce zero results, Fabrik Slow Loading without the quotes do produce 2 million results but only the first five are related to fabrik.

The results are using all keywords, so there are 2 millions results on the words 'slow' and 'loading' and 'Fabrik' hence one of the links being a youtube link for

DekadentFabrik "Slow Vanitas Slow" LIVE, La Fabrika 2012 - YouTube <-- Doesn't look like Hugh or Rob to me!!



In addition the first five entries are there due to some maintenance that needs to be done on the fabrikar website, which I believe Hugh will deal with at some point. This is the fact that you sometimes see results for fabrikar.com, www.fabrikar.com liquid.fabrikar.com..... etc etc, but I'm sidetracking.


THIS is a more realistic result which is under 50!! Not all of these are related to form\list performance either. In addition to the two posts he points to are for Fabrik 2.x which makes it irrelevant anyway.

The two top results are for topics in their official forums in which you can read the complaints of other people with very similar to your problem:

Complaints? How about using the term support requests. How is slow loading related to SQL server gone away? Slow loading means it's slow but it does actually load eventually whereas I believe you are receiving an aborted error, (although without seeing I cannot confirm this).


So let see the first post the guy refers to:-


6 Tables, 1st table 15 elements, 100,000 records linking to two dbjoins with 5000 records. Does that sound like your poolstats at the moment?


Then a few posts down Hugh says.

We've achieved some significant improvement (page load time down from about 20 mins to 4 or 5 seconds), but still some odd behavior with the dropdown selections.

Does that sound like a problem with Fabrik?


Okay rant over and my apologies for it, I really do get angry though when so called technical support people fill you with BS without showing you any evidence and clearly don't know what they are on about.

Thank you for your generous offer. That would be so awesome. I would like to give it a try.

Sure I'll arrange that for you. To make it clear though, I'm not saying this couldn't possibly be a Fabrik problem but the fact remains is nobody else has had this issue and I wouldn't expect this sort of problem on a smaller site in development.

Either way though I wouldn't also expect a host to provide you with incorrect information!!!


I'll PM you some details.

 

[rackem]

Good rant! I feel pretty much the same way. The "proof" was irrelevant and obsolete at best. I knew it wasn't going good when my support ticket was "closed" after they basically said "I don't know and don't see anything, good day."

However as a relative newcomer to web design, I am stuck between knowing enough to think that and not knowing the right questions to ask or actions needed to actually fix it. Patience and persistence in all things I guess.

I really appreciate the helping hands on this. I will look for your PM.

 

[jfquestiaux]

Since you said you had this site tested locally without showing this problem leads to think the problem comes from the server. Anothr test on Felixat server should confirm this.

 

[felixkat]

I totally feel for you and unfortuently there are too many companies, not just web hosts who target people who may not have the exact knowledge to know what to ask for.

Your not alone, an old host I used to use did the same to me a few years ago.

I just find it frustrating when your webserver is probably hosting 500 sites that they can simply say, it's not our problem.

Further research has led me to believe this happens in Joomla frequently which clearly represents it as a Joomla related issue and not Fabrik. Whether the Joomla issue is down to overloaded shared hosting I cannot answer.

This Link Gives a fairly accurate scale of the problem.

All the sites. as expected, from the above link that I clicked on were using Joomla. Some of them actually brought the same error up when I clicked on them. On inspection I couldn't find any using Fabrik, but I did find K2.

Now all these sites with this error message would have only happened when Google searched their site, (your's does .. lol). This means there could be many sites out there with the same problem that google doesn't know about, i.e the message didn't appear at the exact time that google indexed it.

Now it would seem logical to me that a lot of these sites are fairly quiet with a low visitor count. So when Mr Google comes along and indexes the page they get the same symptoms you are seeing when your webpage hasn't been touched for 30 minutes or so. Now Google just sees it as content so indexes it as part of the page.

So it could be a Joomla bug if there is a timing issue with something in the code but I still think that's unlikely for something as simple as loading a webpage.

Be really interesting to see the results on my webserver.

 

[rackem]

Last night we tested my site on felixkat's server. The error has not occurred at all. Same results on a local test server running XAMP. Thank you felixkat for your generosity and assistance!

I presented this information to my host. Their response was basically that it was a problem with my site and I should hire a professional to re-write the code of my application so it was optimized for the database. Of course upgrading to a high-powered server would help too.

So I am back to square one. Which is super frustrating because I chose to stick with a vanilla site precisely to avoid these issues. A small hobby website does not need a dedicated server. And I chose to use Joomla and Fabrik because they were written by professionals designed to make things easy! I need to stop venting right there or this will get long.

This just doesn't make sense. Why would a database query time out in a repeatable fashion like this and work perfectly fine otherwise? If it was my host, it seems that it would be random. But it isn't random. And why now? These errors have only started happening recently. I have a really hard time accepting the answer "it just is" because that really just means there isn't enough information on hand to figure it out.

 

[jfquestiaux]

Can you post the values of the MySQL settings? You should get them in Site > System information - PHP information tab, half way down the page.

If you are looking for reason, you could compare the PHP info of felixcat's server and your current on to find the difference. Maybe that would be a good indication.

You should also consider changing host. There are plenty out there working fine with Joomla (I guess...)

 

[rackem]

Not sure how it happened or if it makes a difference but I have mysqli on my server and mysql on felixkat's server.

Attached are my server's MySQL settings as well as felixkat's.

Differences (my host versus felixkat's)
mysql.allow_persistent: off versus on
mysql.connect_timeout: 20 versus 60
mysql.max_links: 60 versus unlimited

My host doesn't allow changing server settings on shared host. Upgrading to a dedicated server would allow me to set whatever I want. My site is just a hobby site really with a very small user base so it is very difficult to justify higher hosting costs.

I chose my host (Site Ground) as it was recommended on the Joomla website as meeting their requirements. I don't mind switching hosts but will I be jumping from one burning ship to another?

@Felixkat: you said you could recommend another host. Please do as I would like to compare their services and prices.

 

[troester]

I just checked 3 of our Joomla sites on 3 different hosts, all using the smallest hosting package: all are showing felixkat's server settings
mysql.allow_persistent: on
mysql.connect_timeout: 60
mysql.max_links: unlimited

So it's possible that your host is very strict and this is the issue.

But it could also be a problem with a query (I don't know about MySQL internas, I assume the connect_timeout is in seconds and a regular query shouldn't need >20).
If you enable Joomla debug and fabrik debug maybe somebody can find the problem.

 

[rackem]

I turned on Joomla and Fabrik debug. This link will generate the error (under the conditions described above): http://www.mypoolstats.com/index.php/competition

@troester: thanks for posting the server settings

I just asked my host for a list of slow queries logged in the server. Here is their reply:

These are the latest slow queries logged in the server from your site:

Slow queries were found:
Executed 206 min ago for 17 sec on Database --> mypoolst_v1.
SELECT table_name, table_type, engine FROM INFORMATION_SCHEMA.tables WHERE table_name = 'mps_events' AND table_type = 'view' AND table_schema = 'mypoolst_v1
Executed 202 min ago for 21 sec on Database --> mypoolst_v1.
SELECT table_name, table_type, engine FROM INFORMATION_SCHEMA.tables WHERE table_name = 'mps_events' AND table_type = 'view' AND table_schema = 'mypoolst_v1

You can review the server logs for your account in the cPanel -> Log section.

The table "mps_events" is one of my Fabrik tables.