MySQL Bugs: #95280: DatabaseMetaData.getImportedKeys returns double the rows

Bug #95280	DatabaseMetaData.getImportedKeys returns double the rows
Submitted:	7 May 2019 13:34	Modified:	21 May 2021 16:21
Reporter:	Chitra Dandin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.15, 8.0.16, 8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DatabaseMetaData, MySQL 8, regression, useInformationSchema

Description:
We switched Mysql Server version from 5.7.25 to 8.0.15. We have been using connector version 8.0.16 for a few weeks now against older Mysql server and everything seemed to work fine.
But now after the server version switch, one of the issues we faced is that
DatabaseMetaData.getImportedKeys(), for a given catalog, null-schema and a table, it returns double the expected number of rows.

If I set useInformationSchema=false, then I get expected results but I believe setting this property to false leads to other issue (as per release notes).

How to repeat:
Given Catalog='test' and Table ='XYZ' the query generated by com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema#getImportedKeys
is 

SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, 
A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,CASE 
WHEN R.UPDATE_RULE='CASCADE' THEN 0 WHEN R.UPDATE_RULE='SET NULL' THEN 2 WHEN R.UPDATE_RULE='SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE='RESTRICT' THEN 1 
WHEN R.UPDATE_RULE='NO ACTION' THEN 3 ELSE 3 END  AS UPDATE_RULE,CASE WHEN R.DELETE_RULE='CASCADE' THEN 0 WHEN R.DELETE_RULE='SET NULL' THEN 2 
WHEN R.DELETE_RULE='SET DEFAULT' THEN 4 WHEN R.DELETE_RULE='RESTRICT' THEN 1 
WHEN R.DELETE_RULE='NO ACTION' THEN 3 ELSE 3 END  AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, 
(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND TABLE_NAME = A.REFERENCED_TABLE_NAME 
AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,7 AS DEFERRABILITY 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A 
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) 
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA)
WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.TABLE_SCHEMA LIKE 'test' AND A.TABLE_NAME='xyz' AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL 
ORDER BY A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, A.ORDINAL_POSITION

Run this query on Mysql Server 8 and notice that every row is duplicated.

Suggested fix:
Fix the query or add Distinct to the select.

Fixed version

Thank you for the bug report. The mentioned query against 8.0.16 server version returns 0 rows.

Sorry for not being clear in the steps. I have used a dummy catalog name 'test' and a dummy table name 'xyz' in the query. You will have to change those to valid values in your schema.

Look for this:

A.TABLE_SCHEMA LIKE 'test' AND A.TABLE_NAME='xyz'

Hello Chitra,

Thank you for the details.
I could not reproduce this issue with Conn/J version 8.0.16, MySQL Server 8.0.16 and dummy schema, java test case.
Could you please provide logical dump of the schema(SQL format), java test case to reproduce this issue at our end? Thank you.

thanks,
Umesh

##
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    DatabaseMetaData meta = conn.getMetaData();

    ResultSet rs = meta.getImportedKeys(conn.getCatalog(), null, "products");
    while (rs.next()) {
      String fkTableName = rs.getString("FKTABLE_NAME");
      String fkColumnName = rs.getString("FKCOLUMN_NAME");
      int fkSequence = rs.getInt("KEY_SEQ");
      System.out.println("getExportedKeys(): fkTableName=" + fkTableName);
      System.out.println("getExportedKeys(): fkColumnName=" + fkColumnName);
      System.out.println("getExportedKeys(): fkSequence=" + fkSequence);
    }

    conn.close();
  }

  public static Connection getMySqlConnection() throws Exception {
    String driver = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://localhost/dbdemo";
    String username = "root";
    String password = "Mysql123!";

    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }

run:
getExportedKeys(): fkTableName=products
getExportedKeys(): fkColumnName=cat_id
getExportedKeys(): fkSequence=1
BUILD SUCCESSFUL (total time: 3 seconds)

- schema

CREATE DATABASE IF NOT EXISTS dbdemo;
USE dbdemo;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories1(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

Hi,
I tried with your schema and I could not reproduce it either. 

But then I realized we have 2 schema with same structure with one of them being empty. We have it like that for 2 reasons:
1. Much faster to update/sync DDL
2. to run DB-tests against

Anyways, coming back to the issue. I created a dbdemo2 schema using your script and then I was able to reproduce my issue. I got 2 results for 'dbdemo' and 'products'.

The problem is in this JOIN with INFORMATION_SCHEMA.TABLE_CONSTRAINTS.
It only uses CONSTRAINT_NAME and TABLE_NAME. If you add TABLE_SCHEMA also to it, that will solve the problem.

Refer to com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema#getImportedKeys Line# 417:

sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) ");

Thank you for the feedback.
With Conn/J 8.0.16 and MySQL Server 8.0.16, below schema and java test case:

CREATE DATABASE IF NOT EXISTS dbdemo;
USE dbdemo;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

CREATE DATABASE IF NOT EXISTS dbdemo2;
USE dbdemo2;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

run:
getExportedKeys(): fkTableName=products
getExportedKeys(): fkColumnName=cat_id
getExportedKeys(): fkSequence=1
getExportedKeys(): fkTableName=products
getExportedKeys(): fkColumnName=cat_id
getExportedKeys(): fkSequence=1
BUILD SUCCESSFUL (total time: 4 seconds)

- With Conn/J 8.0.16 and MySQL Server 5.7.26, below schema and java test case:

CREATE DATABASE IF NOT EXISTS dbdemo;
USE dbdemo;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

CREATE DATABASE IF NOT EXISTS dbdemo2;
USE dbdemo2;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

run:
getExportedKeys(): fkTableName=products
getExportedKeys(): fkColumnName=cat_id
getExportedKeys(): fkSequence=1
BUILD SUCCESSFUL (total time: 2 seconds)

- Looks like regression to me
- C/J 5.1.47, MySQL Server 8.0.16 and below schema - looks fine i.e no duplicates observed

CREATE DATABASE IF NOT EXISTS dbdemo;
USE dbdemo;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

CREATE DATABASE IF NOT EXISTS dbdemo2;
USE dbdemo2;
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

run:
getExportedKeys(): fkTableName=products
getExportedKeys(): fkColumnName=cat_id
getExportedKeys(): fkSequence=1
BUILD SUCCESSFUL (total time: 1 second)

Same problem with MySQL 8.0.18 and connector 8.0.18.

Bug #100665 marked as duplicate of this one

Contribution submitted via Github - Bug #100665 - Suboptimal DatabaseMetaDataUsingInfoSchema#getImportedKeys() (*) Contribution by Miron Balcerzak (Github mironbalcerzak, mysql-connector-j/pull/55): Please see the bug report available at - https://bugs.mysql.com/bug.php?id=100665 The OCA has been completed and I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_474983209.txt (text/plain), 3.19 KiB.

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

"Row were duplicated in the ResultSet returned by the
DatabaseMetaData.getImportedKeys() method. It was due to a faulty query in
the method, which has been corrected by this patch. Thanks to Miron Balcerzak
for contributing to the fix."

I was able to reproduce this problem on MySQL Connector 8.0.26. This issue is marked as fixed there, so I assume something has regressed.

I've prepared a repo with the steps to reproduce the problem. Strangely, everything works fine for 8.0.25, but fails for 8.0.26.

Can somebody please take a look?

https://github.com/badgersow/mysql-duplicate-fk-bug

Hi Efim,

Could you open a new bug report for this issue?

Sure. I've just filed https://bugs.mysql.com/bug.php?id=104641