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

Bug #49578	conn.getMetaData( ).getProcedures(conn.getCatalog(),null,"%") returns empty
Submitted:	10 Dec 2009 10:13	Modified:	9 Feb 2010 5:57
Reporter:	Wenjie Tu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.7	OS:	Windows
Assigned to:		CPU Architecture:	Any

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

create a connection, say conn, with "jdbc:mysql://shanghai:3306/TEST" database url.

conn.getMetaData( ).getProcedures(conn.getCatalog(),null,"%") just returns empty. However, conn.getMetaData( ).getTables(conn.getCatalog( ), null, "%", new String[]{"TABLE_TYPE"} ) 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:
as Description

Hi Wenjie and thanks for your report.

Since you're working on windows, please see the value of lower_case_table_names variable as described in 
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_lower_case_tabl...
and
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html.

Inform me of result.

Also, please do not file bug against long EOL-ed c/J, use more recent ones.

Our lower_case_table_names variable value is 1.
And I tested against mysql-connector-java-5.1.10-bin.jar, same issue is observed.

Wenjie, there are several settings that come to play here so please attach full but compact test case with connection string and note the following:
  o What is the MySQL server version
  o What is the value of "nullCatalogMeansCurrent" variable
  o What is the value of "useInformationSchema" variable
  o Does you user have access to mysql schema

Ok, I tested with similar setup on my Windows box using latest c/J sources. The problem can not be repeated:

	public void testBug49578() throws Exception {
		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"));
		if (versionMeetsMinimum(5, 0, 7)) {
            Properties props = new Properties();
            props.put("useInformationSchema", "false"); //tested both but running with root user who can select from mysql.proc, both work 
            //props.put("nullCatalogMeansCurrent", "true");
            Connection conn1 = null;
			try {
                conn1 = getConnectionWithProps(props);
                System.out.println("conn.GetCatalog :" + conn1.getCatalog());

                this.rs = conn1.getMetaData().getProcedures(conn1.getCatalog(),null,"%");
                this.rs.first();
                System.out.println(this.rs.getString(1));
                this.rs.close();

                this.rs = conn1.getMetaData().getProcedures("jshop",null,"%");
                this.rs.first();
                System.out.println(this.rs.getString(1));
                this.rs.close();
                
			} finally {
				closeMemberJDBCResources();
			}
		}					
	} 

jshop is the database that has some SP's in it.

Output:
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 :jshop
jshop
jshop

JVM settings and Conn string:
-Xmx1024M -XX:+UseParallelGC -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:xx/jshop?user=xx&password=xx&autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useUnicode=true&characterSetResults=utf8&useInformationSchema=true&useServerPrepStmts=true&cacheResultSetMetadata=true&useAffectedRows=false&traceProtocol=false

Tonci,

What if change your connect url to jdbc:mysql://localhost:xx/JSHOP ?

I don't how to reopen this bug, so have to create another entry: http://bugs.mysql.com/bug.php?id=51013 to provide more details

Continued in Bug#51013.