Jeff Sexton

Tuesday, February 23, 2016

MySQL Issue Caused by Query That Returns a Large Result

I was getting an odd exception thrown from some EJBs that came, in my code, originally from PreparedStatement.executeQuery().

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure


Exceptions came from trying to free the connection after it had become invalid. The true root of the trouble was running out of memory, then abruptly closing the connection. Even though the exception was handled and closed connection returned to the pool, there was memory not freed someplace; in the JDBC driver it seems. That was the real problem.

The queries were unusual in that the result set would contain a relatively large number of rows. This is not a good practice of course, but I only have a handful of these cases, for good reasons.

One would expect the driver to use a server side cursor. It doesn't. So the solution was to tell the MySQL driver that the results would be scanned forward only, etc. so as to conserve memory.

PreparedStatement stmt;
...
stmt = conn.prepareStatement("select * from MyLargeTable",
java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = stmt.executeQuery();

Problem solved.












Post a Comment
3D modeling Advertising Air Canada Airline Alfa Romeo Spider Touring Gran Sport Analog signal Android Anomalies and Alternative Science Apache Apollo Astoria Augmented reality Aurora Famous Fighters auto-awesome Automobile Autos Barack Obama Batman Beards Beer Bell System Berkshire Hathaway Bigfoot Bird Toys Birds Blogger Books Build Management Business and Economy Business Process Execution Language Byte-order mark Canadian Carrot Cats Christmas Civil Defense CNN Cockatiels Collections Crows Dear Jane Debian Diabetes Digital Living Network Alliance Digital television Disney Doll House Dow Jones Industrial Average Duesenburg SJ Roadster Durham University E-mail address ebauche Economics EJB Energy development Enterprise JavaBean ESP Facebook Fedora Filesharing Finance Ford Fossil fuel Garfield James Abram Garfield Minus Garfield Glassfish Global warming Golden Arches Goofy Google Google Buzz Google Docs Google Lively Google Photos Google Reader Google Wave Google+ Greenhouse gas Half-Life 2 Helbros High-definition television History Hybrid electric vehicle IBM Inner city Instagram Insulin Investing Irony J.C. Penny Jane Austen Java Java Architecture for XML Binding JDBC Jeff's! Jim Davis joe the plumber John McCain Karma Kay Thompson Kermit the Frog Kids and Teens LA Auto Show Larry King Laser Logging Lowry Sexton Mark Cuban Market trends McDonald Meier and Frank Microsoft Microsoft Windows Models Monkey monsters Moon MOUNT HOOD Music Music industry Muxtape MySQL NetBeans Netflix Nintendo Nissan Cube Norm Coleman Nuclear fallout Nuclear warfare Office Depot Open ESB Oracle Corporation Pacific Ocean Packard Boattail Pearl District Pearl District Portland Oregon Philip K Dick photography PlayStation 3 Pocher Pokémon HeartGold and SoulSilver Politics Portal Portland Portland Development Commission Presidents Pride and Prejudice Programming Projects Radio Recording Industry Association of America Renewable energy RIAA Robot Chicken Rock-paper-scissors Sarcasm Science fiction film Serbia Service-oriented architecture Shopping Slide Rule Social Security Social Studies Society6 Spirit of St. Louis SQL Stanford Hospital Star Wars Starbucks Stock market Strip search Sun Microsystems T-Mobile TechCrunch Technical ThinkGeek Toaster Total Recall Transportation Security Administration Unicode United States United States Department of Homeland Security Universal Plug and Play Unknown Primates Vegetable garden Video game Vintage Images Vintage Vintage! Virtual world Volvo C70 Wall Street Warren Buffett watches We Can Remember It for You Wholesale Web service Web Services Description Language Wii Windows 7 Windows Phone 7 Windows Vista Windows XP X-Files X-ray vision XML XML Schema YouTube Yugo Zima