Bug #33594 DatabaseMetaData misbehaviour when server side cursors are enabled
Submitted: 31 Dec 2007 8:43 Modified: 25 Feb 2008 12:12
Reporter: Andrea Aime Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.5 OS:Windows
Assigned to: CPU Architecture:Any
Tags: DataBaseMetaData useCursorFetch

[31 Dec 2007 8:43] Andrea Aime
Description:
When using cursor fetch the table metadata returns the data type name in place of the column name.

How to repeat:
Given the following table:

mysql> describe road;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| fid   | varchar(255) | NO   | PRI |         |       |
| id    | int(11)      | YES  |     | NULL    |       |
| geom  | linestring   | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

And a connection using the extra parameters "?useCursorFetch=true&defaultFetchSize=100" we find that database metadata extraction misbehaves in that the column type is returned when the column name is requested, that is, the following code:

DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet tableInfo = dbMetaData.getColumns(null, null, "road", "%");
tableInfo.next();
String colName = tableInfo.getString("NAME_COLUMN");

returns "varchar(255)" instead of "fid".

Removing the above parameters from the connection string restores normal behaviour.
[3 Jan 2008 11:05] Tonci Grgin
Test case

Attachment: TestBug33594.java (text/java), 3.55 KiB.

[3 Jan 2008 11:13] Tonci Grgin
Hi Andrea and thanks for your report. This is more of an issue of useInformationSchema true/false as when useInformationSchema is true all works as expected (please see attached test case). As you are probably using MySQL server > 5.0.7 useInformationSchema=True is a legitimate workaround (and not too complicated) so please lower the Severity to S3 or S2 at most. Verified using latest c/J 5.1 sources.

Output of attached test case:
useInformationSchema = true
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
Connected to 5.0.54-max-nt-log

Time: 0,375

OK (1 test)

----------------
useInformationSchema = false
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
Connected to 5.0.54-max-nt-log
F
Time: 0,422
There was 1 failure:
1) testBug33594(testsuite.simple.TestBug33594)junit.framework.ComparisonFailure: expected:<fid> but was:<varchar(255)>
	at testsuite.simple.TestBug33594.testBug33594(TestBug33594.java:48)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at testsuite.simple.TestBug33594.main(TestBug33594.java:68)

FAILURES!!!
Tests run: 1,  Failures: 1,  Errors: 0
[3 Jan 2008 11:16] Andrea Aime
Hum, does appending InformationSchema=True to the jdbc url causes any problem with older versions of mysql? The code I'm writing is a library (GeoTools) not an application with a specific target db, so whatever I do must work across the board with all versions of mysql.
[3 Jan 2008 11:20] Tonci Grgin
Andrea, alas yes it does... See my test case and previous comment (VersionMeetsMinimum). Changing triage to "Partial workaround". Thanks for lowering severity.
[3 Jan 2008 11:40] Andrea Aime
I don't understand that versionMeetsMinimum comment... that is a method defined somewhere in one of your test base classes. So you say I should find that code, check the version, and apply the extra param only if version if >= 5.0.7?
[3 Jan 2008 11:42] Tonci Grgin
Andrea, true. Please get c/J sources so you can look it up.
[3 Jan 2008 15:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40511
[4 Jan 2008 22:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40588
[25 Feb 2008 12:12] MC Brown
A note has been added to the 5.1.6 changelog: 

Using server side cursors and cursor fetch, the table metadata information would return the data type name instead of the column name.