MySQL Bugs: #31187: DatabaseMetaData.getColumns(null, null, table, "%") throws table doesn't exist

Bug #31187	DatabaseMetaData.getColumns(null, null, table, "%") throws table doesn't exist
Submitted:	25 Sep 2007 15:31	Modified:	2 May 2015 1:15
Reporter:	Richard Holland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.14, 5.0.8	OS:	Linux
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	Contribution, DatabaseMetaData, jdbc

Description:
This applies when using nullCatalogMeansCurrent=false. Under these circumstances, calls to getTables() with null catalog and schema return all tables in all catalogs. You would expect calls to getColumns() with null catalog and null schema to return all columns from every matching table in any database, or no rows at all if no columns on any table in any database match the criteria.

Calling getColumns(null, null, table, "%") where table is a specific table name will fail with "Table 'x.y' doesn't exist" on the first table it finds which doesn't exist in every single database on the server.

This also affects the current 5.1 download.

How to repeat:
Given three databases:

  create database a;
  create database b;
  create database c;

And two tables - one of which is duplicated in all databases, the other is in b and c only:

  use database a;
  create table test1 (hello int);
  use database b;
  create table test1 (hello int);
  create table test2 (hoho int);
  use database c;
  create table test1 (hello int);
  create table test2 (hoho int);

If I connect using JDBC and nullCatalogMeansCurrent set to "false" in the connection properties and issue this call:

   DatabaseMetaData dmd = conn.getDatabaseMetaData();
   ResultSet dbTblCols = dmd.getColumns(null, null, "test1", "%");

I would expect to get the list of all columns in table test1, three times - once each for databases a, b and c. 

This works.

If I repeat the call using table test2 instead I get "Table 'a.test2' doesn't exist". It's right, but it shouldn't care. It should just return the columns from the test2 table in databases b and c instead.

This is a bug. If the table doesn't exist in a particular database, the call to getColumns() shouldn't attempt to analyse it. It should return no results for that database.

Suggested fix:
In the source for the method getColumns(), it has an IterateBlock with catalogStr as a parameter. This block contains two calls to getTables(). Throughout the block, all catalogs are referenced by catalogStr.getString() EXCEPT the two calls to getTables() which use the non-iterated catalog value.

By changing the two getTables() calls to use catalogStr.getString() it will only check tables that actually exist, thus solving the problem.

On a related note, there is a call later to s2b(catalog) which returns the same catalog value as passed in. This is not correct - it should return the catalog value discovered for each column. This can be achieved by changing it to s2b(catalogStr.toString())

I have tested all these changes on my local copy and it works exactly as the JDBC API suggests.

Thank you for the report.

I can not repeat described behaviour in myself environment. Additionally there is no method java.sql.Conection.getDatabaseMetaData() Please provide working example and indicate framework do you use.

Problem seems to be in the framework.

I meant to type conn.getMetaData() in the example, which returns a DatabaseMetaData object.

I am not using any framework. My application (for testing purposes) is a single class, running with the latest 3.1 ConnectorJ in the classpath, using the 1.4.2 Sun Java SDK against a MySQL 4 server running on a remote server. Connections are obtained using calls to connect() on an instance of com.mysql.jdbc.Driver. Both client and server machines are running Linux distros (server is RedHat, client is Ubuntu).

I can repeat this consistently using the example I provided. All you have to do to see it is run the getColumns() method of DatabaseMetaData with null as catalog and null as schema, and provide the call with a table name and column name. If there are multiple catalogs on the server and the specified table does not exist in every one of them, then you will see the exception thrown.

Note that you should use the nullCatalogMeansCurrent=false connection property otherwise it doesn't search all catalogs, only the one it is connected to. If it happens that the catalog you are connected to contains the table and this setting is not set, you will not see the problem occur. However this setting makes no difference if the current connection catalog doesn't contain the table - you'll see the exception anyway.

What you should get is if the specified table occurs in three different catalogs out of say five, you should get three rows returned for the matching column. If it doesn't exist in any, you should get no results. Instead, you get an exception if the number of table occurrences does not match the number of catalogs.

Also if you consider the logic of the code of getColumns() in 3.1 (and 5.0 where it is the same code) you can see it is flawed - the block iterator iterates over catalog names but doesn't use them consistently to restrict it's search operations (calls to getTable()) or alter the values returned for each row (the TABLE_CAT ResultSet entries). This is illogical and should obviously be so upon close inspection and consideration of what the method is intended to do according to the API definition.

Haven't heard anything for a few days. Are you having any luck finding the bug?

test case

Attachment: bug31187.java (text/plain), 1.33 KiB.

Thank you for the feedback.

Verified as described.

Sorry for the delay: I was travelling.

This is a patch to com/mysql/jdbc/DatabaseMetaData.java that fixes the issue for me:

1892c1892
<                                                       tables = getTables(catalog, schemaPattern, "%",
---
>                                                       tables = getTables(catalogStr.toString(), schemaPattern, "%",
1916c1916
<                                                       tables = getTables(catalog, schemaPattern,
---
>                                                       tables = getTables(catalogStr.toString(), schemaPattern,
2014c2014
<                                                               rowVal[0] = s2b(catalog); // TABLE_CAT
---
>                                                               rowVal[0] = s2b(catalogStr.toString()); // TABLE_CAT

Hello Richard - 

In order for us to continue the process of reviewing your contribution to MySQL - We need you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this one, and others in the future.

Thank you ! 

Liz Drachnik  - Program Manager - MySQL

Don't know how this didn't get marked fixed, but it was back in October 2007 - see http://bazaar.launchpad.net/~mark-mysql/connectorj/5.1/revision/629

Added the following entry to the Connector/J 5.1.6 changelog:

"When nullCatalogMeansCurrent was set to false, calls to getColumns() with null catalog and schema returned all tables in all catalogs, instead of all columns from every matching table from any database."