Urgent response to difficult error

Anon

Hi there,

My error is as follows:

I have a page that uses the MySQL db heavily. It has a header, a sidebar, and a main content. All three components use the db, but in this particular page the main content sends about 15-20 large queries to the database.

The error is that the sidebar and header work fine and display DB information. But in the main content either of the following happens:

All the content returns an error, and none of hte queries are valid. The error returns when PHP tries to access the data in the queries, as if the queries themselves were invalid.
SOME of the content will show up, and the rest will be the same as #1. This randomly happens, and will resume #1 on and off. This happens much less. It will be sequential as well, in that the top queries work and the bottom ones don't.

These errors occur randomly, and sometimes stay for hours, and sometimes dissapear once you refresh. Usually, I have noticed, it happens when the DB is first accessed after a long period of no use. In this case, simply refreshing fixes the problem. But when it occurs when the DB is already in use, refreshing does nothing.

I have full control over everything, and have increased the max_query etc variables. I don't think it is the DB itself, and if it is it must be a very rare variable because all the ones I have read about have already been altered to ridiculously large ammounts.

Please let me know if you have encountered this problem before and/or if you have a suggestion.

When I print the MySQL error, it is "Mysql server has gone away". That's a pretty unhelpful error, and changing the DB variables (which has been the most popular proposed solution) does nothing. Maybe I am changing the wrong variables?

Thanks a lot.

Decius.

junebug

well, what are all these queries? you should look at making some joins along the way and cuting down the number of queries you send. not just because of the errors but because it will make your page load faster.

josephferris

The MySQL Documentation states two points of interest in regards to your problem. Both relate to the same "MySQL Server has gone away" error message.

The first, is in regards to not connecting to the server after a while and needing to refresh the page. MySQL has a default timeout of eight hours, as set in the wait_timeout variable.

Secondly, the error can be caused if a SQL Query is too large, or more uncommonly, if it is incorrect.

As Chris recommended, try streamlining the queries, if possible. Also, try running them directly against the database to test them. It is not to unheard of to mangle a SQL Script when embedding it in PHP - I think we all have done it. 😉

Post back and let us know what you can come up with.

Thanks,

Joseph

Anon

Well, the problem is that I have run each and every query in the DB via telnet and it works fine.

Also, half the time, even the PHP page loads fine. So it is impossible that the query itself is wrong.

And the queries source from multiple tables, that have nothing to do with each other. Therefore even if one query got mangled, the others should work fine.

It seems to be the Mysql server itself, or that specific connection ID that closes.

josephferris

How large are the tables that you are querying? Are there are large number of records, or do they contain large amounts of data?

Also, are you only returning what you need from the query? What I guess I am really getting at.... When the query does run successfully, how long does it take?

The reason I ask is that I am wondering if it is sometimes taking longer for the query to return a result than PHP will wait for it. I don't know how, but maybe the error message is due to a timeout and the reason that it works sometimes and not others is that the query execution speeds are so close to that timeout value.

If you want to try, see if you could time the execution speed of the query. The default timeout from the PHP interpreter is set at 30 secs. Maybe you could try raising it and see if that has an effect.

Joseph