Unable to save result set warning

[phpknight]

Hi,

 

I am purposely running a query that returns 14 million rows to test the limits of my program.  Once I ask for about 500,000 rows of this result at one time, I get a php warning that says "unable to save result set."  Also, the query fails and the error is "mysql server has gone away."  Which mySQL/php variables might I increase so that the query does not die?

 

Thanks!

[toplay]

You shouldn't think in those terms. Instead, you should be looping and using LIMIT to get and process a chunk of data at a time.

 

i.e.

 

First query:  LIMIT 0, 100000

 

Second query: LIMIT 100000, 100000

 

Third query: LIMIT 200000, 100000

 

etc.

 

 

[Monotoba]

This occurs becuase php cannot reserve enough memory for your result set. You could change the amount of memory that php can access but that approach would result in a massively slow system and would eat up quit a bit of ram from your system. I suggest you stick with the above suggestion and use limit to limit your results to what php can handle with out modification. Your system will run much faster and you will never get an error due to that lack of system resources.

[phpknight]

I understand your suggestions, and in production the program will not work like that.  However, I just want to make sure the capability is there if it gets big.  About the memory issue, I thought of that, but PHP is not throwing an over the memory limit warning or anything.  The script goes to completion, but the result set just does not get saved.