DatabaseMetaData.getTables returns empty ResultSet in version 5.5

[GoogleCodeExporter]

What steps will reproduce the problem?

Snippet for listing tables in DB:
Class.forName("com.google.appengine.api.rdbms.AppEngineDriver");
Connection conn = DriverManager.getConnection(url, user, password); 
DatabaseMetaData databaseMetaData = conn.getMetaData();
ResultSet tables = databaseMetaData.getTables(null, null, "TEST_TABLE", 
{"TABLE"});
while (tables.next()){
  System.out.println(tables.getString(1));
}

What is the expected output?
List of tables currently in DB.

What do you see instead?
No output

Please provide any additional information below.
For version 5.1, the above snippet works fine, and squirrel-sql client shows 
tables currently in DB

For version 5.5, squirrel-sql client show empty tables list.

Original issue reported on code.google.com by gwtdevel...@gmail.com on 29 Feb 2012 at 8:06

[GoogleCodeExporter]

Original comment by rclev...@google.com on 1 Mar 2012 at 7:12

[GoogleCodeExporter]

Ok, I've looking into this for quite a while and can't reproduce this issue 
here.  A couple of things.

(a) Squirrel SQL needs to have the catalog set from the dropdown to match the 
user you want to see tables for. Passing null as the catalog to both MySQL and 
Google Cloud SQL means current catalog for DatabaseMetaData.getTables

(b) tables.getString(1) would be returning the catalog, not a table.  I can't 
reproduce this. 

I tested with code like this:

executeUpdate(conn, "CREATE TABLE TEST_TABLE(a INT)");
// Specifying the catalog to skip mysql system tables.
ResultSet rs = md.getTables(null, null, "TEST_TABLE", new String[] { "TABLE" });
assertTrue(rs.next());
Object actualCatalog = rs.getObject(3);
Object actualTableName = rs.getObject(3);
assertEquals(conn.getCatalog(), actualCatalog);
assertEquals("TEST_TABLE", actualTableName);
rs.close();

And it passes both against 5.1 and 5.5.

If you have insights on how to reproduce this issue, please let me know and 
provide exact steps to reproduce this. 

Thanks

Original comment by rclev...@google.com on 2 Mar 2012 at 9:40

• Changed state: Invalid

[GoogleCodeExporter]

It seem DatabaseMetaData works for root user only.

To reproduce:
CREATE DATABASE testdb;
CREATE USER 'testdb'@'localhost' IDENTIFIED BY 'testdb';
GRANT ALL PRIVILEGES ON testdb.* TO testdb@localhost;
USER testdb;
CREATE TABLE TEST_TABLE(a INT);

With above snippet:
1) Open a connection using 'testdb' user
String user = "testdb";
String password = "testdb";
Connection conn = DriverManager.getConnection(url, user, password);
==> Failed at assertTrue(rs.next());

2) Open a connection using 'root' or not specified user
Connection conn = DriverManager.getConnection(url, 'root', null);
or
Connection conn = DriverManager.getConnection(url);
==> Works fine

* Same to squirrel-sql client: 
For v5.5: if login using non-root user, it shows empty tables list

Original comment by gwtdevel...@gmail.com on 3 Mar 2012 at 3:46

[GoogleCodeExporter]

Original comment by rclev...@google.com on 3 Mar 2012 at 5:28

• Changed state: New

[GoogleCodeExporter]

Workaround for now is to explicitly grant permissions on INFORMATION_SCHEMA.  

example:
grant select on information_schema.* to testdb@localhost;

Original comment by rclev...@google.com on 3 Mar 2012 at 5:33

[GoogleCodeExporter]

These issues with the deprecated drivers will not be fixed now that Cloud SQL 
works against the regular MySQL drivers and using the regular drivers is the 
only supported solution.

See https://developers.google.com/cloud-sql/docs/native-mysql-gae

Thank you.

Original comment by rclev...@google.com on 12 Feb 2014 at 8:15

• Changed state: WontFix