Error: SQL server gone away?

[rackem]

Here is a screenshot from phpMyAdmin showing this table. I wonder if the "table_type" part of this query is the problem. It is looking for "view" but "BASE TABLE" is all that is available?

 

[jfquestiaux]

This is my settings for MySQL on my servers (with around 30 Joomla sites).

The main difference concerns the connect_timeout (60 sec vs 20 sec on yours) and the allow_persistent on "on".

Since some of your queries are around 20 sec, the problem comes probably from there. Since you can't change that parameter, there is not much you can do except if there is a reason why those queries are so slow.

I found more and more performance problems with hostings providers in the recent years, so I decided to run my sites on my own servers, which is probably not a good solution for you if you have only one hobby site.
I have a colleague managing a few Joomla sites who is quite happy with Bravehost (I think it's a Canadian based hosting company).

 

[felixkat]

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.

This was the settings I mentioned on Skype the other night. Can you let me know the PHP version as well on your current server.

I'll send you the details of an alternative host on Skype. You already know it works as you are already using it.

Not sure why my site isn't using the mysqli connection, it is on my sites. This can actually be changed in the backend but Joomla suggestion doing it at installation, so it was probably the Akeeba Kickstart that set it to mysql. I'll look into this though as this could be a flaw with their setup.

 

[felixkat]

Another factor is that I believe your host is using a seperate mysql server to the webserver. Setup correctly this would be the correct route to take, however for a shared web host it would mean dramitically reducing their timeouts and not allowing persistent connection which is what you are seeing.

 

[jfquestiaux]

I confirm that by default Joomla installer proposes "mysqli" as default while kickstart.php (from Akkeba) proposes "mysql" (at leasts with the old kickstart version I use), so you have to not forget to change it.

Regarding the mysql server, I think all hosting companies have their mysql server separated from the web server, but that has no bearing on the timeout value. The host I used in the past allows persistent connections and has a timeout of 60 secs. And I am quite sure this is a seperate server. However, it allows only 8 links and persistent, which does slow down the accesses.

 

[rackem]

This is my settings for MySQL on my servers (with around 30 Joomla sites).

The main difference concerns the connect_timeout (60 sec vs 20 sec on yours) and the allow_persistent on "on".

Since some of your queries are around 20 sec, the problem comes probably from there. Since you can't change that parameter, there is not much you can do except if there is a reason why those queries are so slow.

After putting a finger on the slow queries, the puzzle pieces are coming together. I agree switching to a host with less restrictive parameters would work around the problem.

However, a 20 second query seems very excessive. Will this 20 gradually grow until it exceeds the 60 second limit? I think I saw this loading delay on felixkat's server so that would say it is something with my site. I think I will dig more to see what I can find. Perhaps it will save me the trouble of switching hosts - it will be my first switch so I don't know how bad that sort of thing is to do.

This was the settings I mentioned on Skype the other night. Can you let me know the PHP version as well on your current server.

Yes, this had slipped my mind in all of my confusion. I didn't mean to ignore it sorry. My PHP version is 5.3.14.

I'll send you the details of an alternative host on Skype. You already know it works as you are already using it.

Great! When we discussed it I wasn't sure if it was also shared hosting or a dedicated server.

Not sure why my site isn't using the mysqli connection, it is on my sites. This can actually be changed in the backend but Joomla suggestion doing it at installation, so it was probably the Akeeba Kickstart that set it to mysql. I'll look into this though as this could be a flaw with their setup.

This might have been just me, I was trying different things to get it to connect to the database.

Another factor is that I believe your host is using a seperate mysql server to the webserver. Setup correctly this would be the correct route to take, however for a shared web host it would mean dramitically reducing their timeouts and not allowing persistent connection which is what you are seeing.

BTW, I realized I was mistaken about the database server host name not being localhost. I discovered this as I was reading through the settings, it is localhost. I had it confused with the FTP server name.

 

[felixkat]

I confirm that by default Joomla installer proposes "mysqli" as default while kickstart.php (from Akkeba) proposes "mysql" (at leasts with the old kickstart version I use), so you have to not forget to change it.

Great thanks for confirming that.

Regarding the mysql server, I think all hosting companies have their mysql server separated from the web server, but that has no bearing on the timeout value

Not all, but it varies between hosters, my hoster provide both solutions depending on what you want. For small sites such as blogs there are really no benefits to having a seperate mysql server. The point is some hosters limit 500 clients per box whilst others have 1000 or more per box. The more clients per box, the more profit but they will have to be strict about settings to allow balance between everyone. Persistent connections will have to be a minimum otherwise potentially you could overload the system or run out of connection, (in worse case sceanrio).

The thing is Rackem may have had a 30 second time out last week and there may have been an overload on the system, so the host simply reduces it to 20 seconds. They would never tell you this though.

 

[rackem]

Regarding the slow query

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

Does anyone know when this query occurs? I have two databases visible in phpMyAdmin: "INFORMATION_SCHEMA" and "mypoolst_v1". "mypoolst_v1" is the database Joomla points to. What is INFORMATION_SCHEMA?

Like I mentioned earlier, there is nothing for this query to find as "table_type = view" is not present. I thought perhaps a "view" was created after the page was first accessed but this does not appear to be the case.

 

[felixkat]

However, a 20 second query seems very excessive. Will this 20 gradually grow until it exceeds the 60 second limit? I think I saw this loading delay on felixkat's server so that would say it is something with my site. I think I will dig more to see what I can find.

The first thing to check would be indexes and whether you can add any to improve performance. More is not always better though.

If you have had any delays on my server though just let me know and I'll check through the logs. Oh and while I think about it, you may want to put a robots.txt file in the root so my server doesn't start stealing your search results.... lol. Shout me on Skype if you want me to do this though.

 

[troester]

INFORMATION_SCHEMA contains the MySQL information.
MySQL views have table_type = 'view'.

I don't know why (or if) Fabrik is searching for mps_events as a view (and I can't imagine why such a simple query should need about 20 seconds).

One thing you can check in #_fabrik_lists:
what is the the "isview" setting in the record of your mps_events list (last entry in parameters)

 

[rackem]

One thing you can check in #_fabrik_lists:
what is the the "isview" setting in the record of your mps_events list (last entry in parameters)

The last entry in parameters is: "isview":0

I don't know if this is a clue or not but it takes a long time to view the information_schema through phpMyAdmin. I click on my site's db and it appears in a second or two. information_schema took between 15-30 seconds to display.

I ran the "slow" query through phpMyAdmin and it returned zero results after 0.38 seconds. I then went to my site and all content loaded just fine. I can't say that is a valid test though. I'll wait for a bit and then try running the query without having a database selected.

Is it possible that mySQL is getting hung up just connecting to information_schema?

The first thing to check would be indexes and whether you can add any to improve performance. More is not always better though.

I know I haven't done anything with indexes so I will have to look into this.

 

[cheesegrits]

Hmmmm. this is a well understood issue in Fabrik, and I'm not sure why you are hitting this problem.

If you look at the code in ./components/com_fabrik/models/list.php, around line 3714 (latest git), you'll find the isView() method. This is how we work out if a List is a "real" table, or is actually a view, which we need to know in various places in the code.

In there, you'll see this comment:

		/* $$$ hugh - because querying INFORMATION_SCHEMA can be very slow (like minutes!) on
		 * a shared host, I made a small change.  The edit table view now adds a hidden 'isview'
		 * param, defaulting to -1 on new tables.  So the following code should only ever execute
		 * one time, when a new table is saved.

So if things are working right, we should only ever query INFORMATION_SCHEMA once for any given list, when that list is created. From then on, once 'isview' has been set in the List params, we simply use that, rather than querying INFORMATION_SCHEMA, to decide if this list is a view or a real table.

BTW, the reason INFORMATION_SCHEMA access can be so slow is that it is the one table in a MySQL database which is shared by ALL users of the database. So on shared hosts, which may have thousands of domains on them, things can get very slow. Or (even worse) if your shared server uses a shared back-end MySQL, i.e. a single MySQL server, serving multiple physical servers, each supporting thousands of domains ... access to INFORMATION_SCHEMA can get wedged up in huge query queues.

Anyway ... like I said, that code, which is the only place we access the schema, should NOT be invoked if your "isview" parameter is set to 0, which you say it is.

So ... to get any further with this, I may need to get on your site to debug it.

-- hugh

 

[felixkat]

I know I haven't done anything with indexes so I will have to look into this.

Fabrik will put indexes on what it thinks is required though, just thought I'd better say. Just saying there maybe some areas where it could be improved with additional ones.

Re the information_schema, just seems unusual that anything would refer to that, not with what you are doing anyway.

You may need to clarify something with your host as their logs may be putting you off track.

The information_schema contains every table within the mysql database, this means yours and everybody else who uses that server. It not only contain each and every table but all the other information that mysql needs to store, so this is columns, user permissions.... look at it as the master database for all.

The information_schema will only allow you to see your own information though through the permissions set up by your host.

My reckoning is that they are showing you their logs which uses the information_schema as a reference.

 

[felixkat]

I do think we are focusing in the wrong direction though. I went on your site earlier and it took about 15-16 seconds to load up one page AND displayed the error.

I then pressed F5 a few times, left it 10-15 seconds, refreshed....and so on and it was fine after that.

So in my view it's clearly a timeout issue with the mysql server. It is also my view that something is seriously wrong if it takes 15-16 second to reconnect.... but of course they can eliminate that if you upgrade.

 

[cheesegrits]

Hmmm I think I may have found the problem, which seems to be a very elementary coding error.

Can you try changing line 3719 (again, that's in the latest github code), so the test is for >=, not just >, and reads:

		if (!is_null($isView) && (int) $isView >= 0)
		{
			return $isView;
		}

... see if that fixes your problem? In J! debug mode, you should then see almost no time in the profile between "getAsFields: starting to test if a view" and "getAsFields: end of view test".

-- hugh

 

[cheesegrits]

PS, just to be clear, so you don't have to dig back through my previous posts, the file you need is ./components/com_fabrik/models/list.php.

-- hugh

 

[rackem]

I made the change. The issue really only occurred after the site "rested" so trying to be patient to test...

In J! debug mode, you should then see almost no time in the profile between "getAsFields: starting to test if a view" and "getAsFields: end of view test".

Not sure what you mean by this.

 

[felixkat]

Wow so this has been an interesting thread, but I have to finish it off with yet another long thread.

I spoke to Rackem on Skype so he is fully up to date why this one character caused a problem.

I think Hugh summarised the cause of the issue an through a conversation with him I have put together a final summary, (to save him a job!!)


When Fabrik loads a list \ table, (same applies for Fabrik 2.x), it needs to know whether it is a physical mysql table or a mysql view.

The current way it does this is to do a query to the INFORMATION_SCHEMA, once it receives the result it stores the value in the lists parameters, (isview).

Next time Fabrik loads up that list it uses the stored 'isview' parameter so it doesn't need to re-query the INFORMATION_SCHEMA again.

Unfortunetly due to a minor bug it was still querying the INFORMATION_SCHEMA which Hugh's suggested fix should now resolve.


The question is, why has this only been effecting Rackem's server?.... well all of the above really posts really. It still stands that his host seem to be using restrictive settings but on the positive side it discovered this bug... lol

The reason why it was only causing this issue on the first query is due to mysql's cache. When a list was displayed for the first time after inactivity Fabrik performed the query to the INFORMATION_SCHEMA. Whilst this information stays in mysqls cache it doesn't need to perform the query again for the answer.

Once the query has been purged from the cache it will once again query INFORMATION_SCHEMA, if activity is high on the server, the error message is displayed.

Now it does need to be clear that the word bug should be used very lightly in this situation. The only reason this bit of code was introduced was because another user was hosted on a similiar setup, i.e a very inefficient mysql setup.

This is also leads me to believe that this is a corner case because nobody else could re-create this issue. I can only assume that most of us have more efficient setups.

This is not anyway through fault of Rackem's though as you just don't expect these sort of things to happen. Most would like to assume you are getting your money's worth with shared hosting but unfortuently this is not always the case.

So before we go down the road of 'My Host is better than your Host' I would like to thank everyones input on this one.

Thanks also Hugh for resolving the issue and thanks Rackem for your patience.


Please post back to say it's all working, close this thread and bury it... lol

 

[rackem]

Well, after testing this a few times, I think that fix did it! ;D

Thank you felixkat for putting together this excellent summary. It is really neat to see how all of the puzzle pieces fit together to illustrate what was going on.

What an adventure! Not to mention a learning experience into the dark and steamy inner workings of a website. You all have my heartfelt gratitude for your time, patience, and willingness to help as we worked through this. I will need to click "thanks" after every post in this thread! Also a big thank you to felixkat for letting me borrow some live server space to test.

There were several twists and turns along the way. And, to my surprise, it turned out my situation was a "corner case" after all. I am just glad a fix was found and now others will not have to retrace these steps.

Error: SQL server gone away --> RIP

 

[rackem]

@Hugh: Just a friendly reminder to please commit this change to Github when you return