Wednesday, January 23, 2008

MySQL Streaming Result Set, Part II

When I get streaming result sets from mysql, I sometimes have to perform operations that take minutes to complete. Occasionally, I get Can not read response from server. As I've learned, this is due to the mysql net_write_timeout setting. If you wait more than this many seconds between reads of the streaming result set, mysql may close the connection. However, setting net_write_timeout to a large value globally (such as in the my.cnf file) may be dangerous. Fortunately, there is a Connector/J property which will set net_write_timeout to a specified value only for streaming result sets. That property is netTimeoutForStreamingResults. By default, it is set to 600 seconds. To change it, you will need to be using Connector/J 5.1.0 or later. We set this value in our connection url.


Jonno said...

"may be dangerous" why?

Jason said...

If you're running a large site and requests start taking 100x longer and they don't short circuit because you set too long of a timeout, your entire site may come to a grinding halt.

David O'Meara said...

I'm hoping this solves our issue but great work, thanks!