MySQL Bugs: #51013: conn.getMetaData( ).getProcedures(conn.getCatalog(),null,"%") returns empty

Bug #51013	conn.getMetaData( ).getProcedures(conn.getCatalog(),null,"%") returns empty
Submitted:	9 Feb 2010 5:45	Modified:	8 Mar 2010 8:18
Reporter:	Wenjie Tu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.10	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I've submitted a bug: http://bugs.mysql.com/bug.php?id=49578 to track this issue but found it's unfortuatelly concluded as "Can't repeat". So I have to recreate new bug entry to provide more details.

MySQL 5.1.32-community is installed on Windows2003. there are several store procedures in "test" DB.

create a connection(JDBC Driver is mysql-connector-java-5.1.10-bin.jar), say conn, with "jdbc:mysql://dbserver:3306/TEST" database url. (Please notice it's "TEST" here, not "test").

conn.getMetaData( ).getProcedures(conn.getCatalog(),null,"%") just returns empty.
However, conn.getMetaData( ).getTables(conn.getCatalog( ), null, "%", null ) can return expected tables. And I noticed conn.getCatalog() returns "TEST" and conn.getMetaData( ).getProcedures("test",null,"%") can return all store procedures. 

How to repeat:
Run the following code snippet:

		System.out.println( "java.vm.version         : "
				+ System.getProperty( "java.vm.version" ) );
		System.out.println( "java.vm.vendor          : "
				+ System.getProperty( "java.vm.vendor" ) );
		System.out.println( "java.runtime.version    : "
				+ System.getProperty( "java.runtime.version" ) );
		System.out.println( "os.name                 : "
				+ System.getProperty( "os.name" ) );
		System.out.println( "os.version              : "
				+ System.getProperty( "os.version " ) );
		System.out.println( "sun.management.compiler : "
				+ System.getProperty( "sun.management.compiler" ) );
		Properties props = new Properties( );
                //getProcedures works well if set "useInformationSchema" true 
		//props.put( "useInformationSchema", "true" );
		props.put( "user", "xxx" );
		props.put( "password", "xxx" );

		Class.forName( "com.mysql.jdbc.Driver" );
		Connection conn = DriverManager.getConnection( "jdbc:mysql://dbserver/TEST",
				props );
		System.out.println( "conn.getCatalog(): " + conn.getCatalog( ) );
		ResultSet rs = conn.getMetaData( ).getTables( conn.getCatalog( ), null, "%", null );
		boolean b = rs.next( );
		if ( b )
		{
			System.out.println( "Succeed in listing tables" );
		}
		else
		{
			System.out.println( "Fail to list tables" );
		}
		rs.close( );
		rs = conn.getMetaData( ).getProcedures( conn.getCatalog( ), null, "%" );
		b = rs.next( );
		if ( b )
		{
			System.out.println( "Succeed in listing procedures" );
		}
		else
		{
			System.out.println( "Fail to list procedures" );
		}
		rs.close( );
		conn.close( );

Make sure database url is "jdbc:mysql://dbserver:3306/TEST". not repeatable if database url is "jdbc:mysql://dbserver:3306/test". and following is my output:
-------------------------------
java.vm.version         : 1.5.0_07-b03
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_07-b03
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
conn.getCatalog(): TEST
Succeed in listing tables
Fail to list procedures
-------------------------------

And I found that when I set "useInformationSchema" true when create connection, 
conn.getCatalog() still returns "TEST", but conn.getMetaData( ).getProcedures("test",null,"%") can return all
store procedures. 

I'll also upload System Variables on my mysql server.

System Variables of my Mysql Server

Attachment: System Variables of MySQL 5.1.32-community.csv (application/vnd.ms-excel, text), 7.25 KiB.

Sorry following paragraph in "How to repeat" block:
And I found that when I set "useInformationSchema" true when create connection, 
conn.getCatalog() still returns "TEST", but conn.getMetaData(
).getProcedures("test",null,"%") can return all
store procedures. 

should be: 

And I found that when I set "useInformationSchema" true when create connection, 
conn.getCatalog() still returns "TEST", but conn.getMetaData(
).getProcedures(conn.getCatalog(),null,"%") can return all
store procedures.

Wenjie, let me get his straight. You want me to pass *non-existent* database in connection string, right? As database *is* "test" and you're passing "TEST".

Wenjie, I tested with "TEST" in connection string, and then again with "TEST" in conn.srting and "JSHOP". Can't find any problems...

.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.
Done.
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler
Connected to 5.1.32-community-log
conn.GetCatalog :TEST
jshop
jshop
Time: 0,45
OK (1 test)
-------------------------------
JSHOP
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.
Done.
Connected to 5.1.32-community-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler
conn.GetCatalog :TEST
jshop
jshop
Time: 0,323
OK (1 test)

Server version: 5.1.32-community-log MySQL Community Server (GPL)
mysql> show variables like "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+

Tables in jshop are InnoDB.

Please try repeating with latest c/J 5.1 snapshot from http://downloads.mysql.com/snapshots.php.

Problems detected reported in Bug#51022.

Tonci,

I'm suprised that it's not reprocuable at your side. I made a test against the latest c/J 5.1 snapshot mysql-connector-java-5.1-nightly-20100308 and found this issue too:
When conn url is "jdbc:mysql://dbserver:3306/TEST", succceded in listing tables, failed to list procedures. 
However if url is "jdbc:mysql://dbserver:3306/TEST?useInformationSchema=true", succeeded in listing both tables and procedures.

"useInformationSchema" is always set true during your test?

Any update?

Lin, I really have no idea what you guys are talking about here... All my tests pass and I have absolutely no problems in this part of code (useInformationSchema=false), especially since I rewrote it almost completely in past few months.

Hey Tonci,

Thank you for the updating! We have found the root cause of problem: we are using a commercial Data Direct driver which wraps mySql Connector/J. We believe something wrong in it to make problem happen. Using a mySql Connector/J driver directly, everything simply works fine.

I appreciate your time!

Lin

No problem Lin. Glad things work now.