Mysql server has gone away error - Error 2006

computerzworld

Hello All,
I am having a select query that is combination of 7 select query joined by union. It is dealing with large amount of data .

Here is basic syntax of my query.

select fieldId1,fieldId2 from table1 where....
union 
select fieldId1,fieldId2 from table1 where...
union
select fieldId1,fieldId2 from table1 where...

So when I am running that script many times the script execution get stopped due to mysql server gone away error. I tried to reconnect the connection by pinging mysql server like this..

if(!mysql_ping($connect))
{
        mysql_connect('host','user','pass');
        mysql_select_db('db_name');
}

then also i am getting the same error. So is there any way so that I can resolve this error programatically? Please help me. Thanks in advance.

Weedpacket

Why on earth would you need to write the query like that in the first place? Why can't you combine the WHERE clauses?

computerzworld

sorry I have written wrong query... it is having different tables....

select fieldId1,fieldId2 from table1 where....
union 
select fieldId1,fieldId2 from table2 where...
union
select fieldId1,fieldId2 from table3 where...

johanafm

What do you consider a "large amount of data"? Are the fields in your where clauses properly indexed? Why do you need to run a union on 7 tables, what are the tables and what fields do they contain?

computerzworld

actually its a database of sms sending site. So it is having millions of data and query takes more than 1-2 mins and connection time out occurs.

johanafm

Getting a small subset of rows from several million rows should be done in less than a second anyway. Questions left to answer from my last post

Are the fields in your where clauses properly indexed? Why do you need to run a union on 7 tables, what are the tables and what fields do they contain?

From a DB performance perspective, you're not dealing with "a lot of data", not unless you want to retrieve it all at once.

Do you actually need to retriev all those rows? At once? Sure enough, it might take a minute to retrieve one million rows depending on several factors. But considering how rarely you'd want to do that, I doubt this is the issue.
And if you really do need to do this, while increasing connection timeout is not an option, then you could always grab all data in chunks of 500k rows per request, and send 10-100 requests or whatever is needed to get it all.

computerzworld

yeah thats right but the case happens here is that one query is very large & its execution takes more than 60 seconds so mysql leaves connection & i am getting this error even though i have used mysql_ping command to check & reconnect whether connection is alive or not , it is not re connecting to the database somehow. So is there any other way to do this or I am doing some wrong thing?

johanafm

My lsat post above actually had more than one point of interest. I'll refrain from requoting the previous post once more (as I did the last time). If you do answer my questions, I may be able to help you. Else, go with the help that has been provided so far.