MySQL Bugs: #64621: PreparedStatement.getMetaData() executes the Query internally

Bug #64621	PreparedStatement.getMetaData() executes the Query internally
Submitted:	12 Mar 2012 14:30	Modified:	15 Mar 2012 17:03
Reporter:	Stefan Müller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	mysql-connector-java 5.1.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Apparently getting the MetaData of a PreparedStatement internally executes the query. This seems unnecessary expensive as the information in the ResultSetMetaData can be derived from the query and the DB schema only.

Actually for me its a S1 problem since it causes an OutOfMemoryException if the ResultSet is large. The usual work-around for large result sets, setting the fetch size to INTEGER.MIN_VALUE, doesn't work for getMetaData(). 

How to repeat:
Create an arbitrary table.
Fill it with several GB of arbitrary data.

statement = connection.prepareStatement(
  "SELECT * FROM mytable", 
   ResultSet.TYPE_FORWARD_ONLY,
   ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(INTEGER.MIN_VALUE);

statement.getMetaData();  // this will throw OutOfMemoryException

Suggested fix:
Ideally getMetaData() would not execute the query. 

As a quick work-around it would be nice if that query would at least respect the fetch-size hint. That's still very inefficient, but at least it would not fail.

Can you, please, turn on general query log on server and send it's content after running your test, to prove your point?

Stefan, an actual code you're using, especially the connect options might prove important too.

The JDBC driver *shouldn't* be retrieving the entire result set, because it's setting the max rows to retrieve zero rows, so there may be a bug there. However, realize, there is no easy way to get this data from MySQL as it stands today, because it doesn't return this information, and it's not as simple as just looking at the tables and columns because that would require a full-blown parser for the query client-side.

Stefan, I still do not see your test case attached... So my best guess is that you need to add props.put("useServerPrepStmts","true"); to your code and the problem should be solved.

http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html
useServerPrepStmts	Use server-side prepared statements if the server supports them?

Creates and fills a large table

Attachment: CreateTable.java (text/x-java), 2.12 KiB.

Reproduces the OutOfMemory error

Attachment: ReproduceError.java (text/x-java), 1.05 KiB.

I've uploaded two java files, one to create the table and one to reproduce the error. It needs to be quite a large table or the OOM does not appear (otherwise its just slow).

Activating "useServerPrepStmts" does the trick. Thanks!
No OOM and no performance degradation.

Are there any drawbacks from activating this option?

Thank you Stefan.

As for drawbacks of using this option, there are none. As a matter of fact, this is the preferred way. We discouraged it's use long ago while the code is server was still flaky. Now that it's stable you should definitely use SS PS for your work.

Pushed up to revision 1134.

Added to changelog for 5.1.19: 

setMaxRows was not correctly processed during metadata collection for
prepared statements, causing the entire result set to be fetched and
possibly leading to an out-of-memory error.