MySQL Bugs: #113970: DatabaseMetaData#getTables produces duplicates

Bug #113970	DatabaseMetaData#getTables produces duplicates
Submitted:	13 Feb 2024 6:34	Modified:	23 Jul 12:54
Reporter:	Jin Kwon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
DatabaseMetaData#getTables produces duplicates.

Two sets of duplicate tables are here.
(Only the `REMARKS` values are different.)

Table(super=AbstractMetadataType(super=com.github.jinahya.database.metadata.bind.Table@65c39a55, unmappedValues={}),
    tableCat=null,
    tableSchem=null,
    tableName=replication_asynchronous_connection_failover,
    tableType=SYSTEM TABLE,
    remarks=The source configuration details,
    typeCat=null, typeSchem=null, typeName=null, selfReferencingColName=null, refGeneration=null),

Table(super=AbstractMetadataType(super=com.github.jinahya.database.metadata.bind.Table@65c39a55, unmappedValues={}),
    tableCat=null,
    tableSchem=null,
    tableName=replication_asynchronous_connection_failover,
    tableType=SYSTEM TABLE,
    remarks=,
    typeCat=null, typeSchem=null, typeName=null, selfReferencingColName=null, refGeneration=null),

Table(super=AbstractMetadataType(super=com.github.jinahya.database.metadata.bind.Table@6a8d1d75, unmappedValues={}),
    tableCat=null,
    tableSchem=null,
    tableName=replication_asynchronous_connection_failover_managed,
    tableType=SYSTEM TABLE,
    remarks=The managed source configuration details,
    typeCat=null, typeSchem=null, typeName=null, selfReferencingColName=null, refGeneration=null),

Table(super=AbstractMetadataType(super=com.github.jinahya.database.metadata.bind.Table@6a8d1d75, unmappedValues={}),
    tableCat=null,
    tableSchem=null,
    tableName=replication_asynchronous_connection_failover_managed,
    tableType=SYSTEM TABLE,
    remarks=,
    typeCat=null, typeSchem=null, typeName=null, selfReferencingColName=null, refGeneration=null),

How to repeat:
Invoke `DatabaseMetaData#getTables(null, null, "%", null)`.

Suggested fix:
Suppress duplicates

Hello Jin Kwon,

Thank you for the report and feedback.

regards,
Umesh

I have verified for now, I could be wrong but this might turn out to be an expected behavior, if you closely look at the tables from both mysql and performance_schema, below tables are present in both the schema:

mysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
.
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
.
+------------------------------------------------------+
38 rows in set (0.00 sec)

mysql> show tables from performance_schema;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
.
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
.
+------------------------------------------------------+
111 rows in set (0.00 sec)

Only diff being the table level COMMENT missing in performance_schema tables(above).

The the problem will be the `TABLE_CAT` and/or `TABLE_SCHEM` which either should have the different value(`mysql`, `performance_schema`).

Posted by developer:
 
Hi Jin Kwon,

Could you please provide a more thorough testcase, including the configuration properties you used in your connection, and any relevant information about the MySQL server you are connecting to? I am unable to reproduce this bug.

As Umesh stated, there are no duplicates, you should see the following 4 tables:
mysql.replication_asynchronous_connection_failover
mysql.replication_asynchronous_connection_failover
performance_schema.replication_asynchronous_connection_failover_managed
performance_schema.replication_asynchronous_connection_failover_managed

Indeed, this is also what I see when I simply run DatabaseMetaData.getTables(null, null, "%", null). Now, as you noticed, the actual issue seems to be that both TABLE_CAT and TABLE_SCHEM are set to null. One of these two columns should contain the database the table belongs to. However I cannot find a way to get both of these to be null as you reported. Any further information you have would be greatly appreciated.

Regards,
Axyoan

Hi Jin Kwon,

As previously explained, there are no duplicates. The reason your library shows `tableCat` and `tableSchem` as null is outside the scope of Connector/J, as `DatabaseMetaData.getTables()` returns those values correctly.

Also note that MySQL Connector/J supports only one of these at a time. For more details, please refer to the `databaseTerm` connection option:
https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-connection.html#cj-conn-p...

Closing as "Not a Bug".