Bug #94223 java.sql.DatabaseMetaData.getColumns returns invalid data in some cases.
Submitted: 6 Feb 2019 16:24 Modified: 11 Jan 2020 18:28
Reporter: James Payne Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.15 OS:CentOS (release 7.6.1810 (Core))
Assigned to: CPU Architecture:x86 (VM)

[6 Feb 2019 16:24] James Payne
Description:
The function with the following specification does not return correct metadata.  When schemaPattern is provided (i.e. not empty and not null), it is not filtering the metadata by schema, and not reporting schema in the metadata.

ResultSet java.sql.DatabaseMetaData.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException

Additional Information:
I have tried setting useInformationSchema=true (though it should default to that when connection to the database server, as it is also 8.0.15), with no change in behavior.

This used to work with version 5.7 and earlier.

How to repeat:
Set up a data source who's user has access to multiple schemas.  Make sure that at least two of the schemas have tables with the same name.  Call the specified function as the example below illustrates.

String schema = "someschema";
String tableName = "sometable";
Connection conn = /* Open Connection */;
ResultSet columns = conn.getMetaData().getColumns(null, schema, tableName, null);

The resulting metadata contains null where schema should be, and contains column sets for all tables with the supplied table name that the user has access to regardless of schema.  This results in a super set of columns in the metadata.

Suggested fix:
As per specification for this function, use the supplied schemaPattern to filter metadata, and report the schema name in the resulting metadata.
[7 Feb 2019 6:58] MySQL Verification Team
Hello James,

Thank you for the report.

regards,
Umesh
[15 Feb 2019 12:00] Alexander Soklakov
Actually, it was never working in c/J, schemaPattern is ignored. See, for example, https://forums.mysql.com/read.php?39,137564,137629#msg-137629

But since MySQL is getting more consistent on that matter and calls databases a schemas, c/J needs to move to the same direction and accept schemaPattern as a database name pattern.
[30 Oct 2019 20:32] Gabe Gates
I'm having the same issue. 

My user has access to multiple schemas which contain the same table names. Initially, I was using the "mysql-connector-java-8.0.13.jar", I upgraded to use the "mysql-connector-java-8.0.18.jar" but I still had the same results.

My MySQL version is 8.0.11

I am providing the database schema into my JDBC connection URL.

My stack trace from the .13 jar indicated a suspect query that returned my database table for all three schemas.

java.lang.Throwable
	at com.mysql.cj.util.LogUtils.expandProfilerEventIfNecessary(LogUtils.java:53)
	at com.mysql.cj.log.StandardLogger.logInternal(StandardLogger.java:283)
	at com.mysql.cj.log.StandardLogger.logInfo(StandardLogger.java:186)
	at com.mysql.cj.log.LoggingProfilerEventHandler.consumeEvent(LoggingProfilerEventHandler.java:45)
	at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1094)
	at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1153)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:951)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:70)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getTables(DatabaseMetaDataUsingInfoSchema.java:847)

The query:

SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'UNPROTECTED_SESSION' ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME

This may not be where the underlying code is getting mixed up, but it lead me down the path for a temporary solution to my problem. My temporary solution is to use a user with access to only one schema.