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:
Category:Connector / J Severity:S5 (Performance)
Version:mysql-connector-java 5.1.18 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2012 14:30] Stefan Müller
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", 

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.
[12 Mar 2012 15:13] Valeriy Kravchuk
Can you, please, turn on general query log on server and send it's content after running your test, to prove your point?
[12 Mar 2012 15:14] Tonci Grgin
Stefan, an actual code you're using, especially the connect options might prove important too.
[12 Mar 2012 16:49] Mark Matthews
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.
[13 Mar 2012 10:43] Tonci Grgin
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.

useServerPrepStmts	Use server-side prepared statements if the server supports them?
[13 Mar 2012 14:01] Stefan Müller
Creates and fills a large table

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

[13 Mar 2012 14:01] Stefan Müller
Reproduces the OutOfMemory error

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

[13 Mar 2012 14:07] Stefan Müller
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?
[13 Mar 2012 14:34] Tonci Grgin
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.
[15 Mar 2012 17:03] Tonci Grgin
Pushed up to revision 1134.
[22 Mar 2012 19:24] John Russell
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.