Description:
The call to method DatabaseMetaData.getImportedKeys in JDBC Driver can return multiple rows for the same foreign key.
I was able to reproduce this problem on version 8.0.26, while on previous 8.0.25 everything worked fine.
This is most likely a regression of https://bugs.mysql.com/bug.php?id=95280 which changed the query that fetches foreign keys.
How to repeat:
I've created a repo which demonstrates how the problem can be reproduced. https://github.com/badgersow/mysql-duplicate-fk-bug . I'm also copying those steps here (see below).
Create the database in MySQL 8 with 2 tables, one of them has a FK to another one. It's crucial that the second table has also a UNIQUE key.
CREATE DATABASE IF NOT EXISTS dbdemo;
USE dbdemo;
CREATE TABLE IF NOT EXISTS `AO_9412A1_AOUSER` (
`CREATED` datetime DEFAULT NULL,
`ID` bigint NOT NULL AUTO_INCREMENT,
`LAST_READ_NOTIFICATION_ID` bigint DEFAULT '0',
`TASK_ORDERING` longtext COLLATE utf8mb4_bin,
`UPDATED` datetime DEFAULT NULL,
`USERNAME` varchar(255) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `U_AO_9412A1_AOUSER_USERNAME` (`USERNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE IF NOT EXISTS `AO_9412A1_USER_APP_LINK` (
`APPLICATION_LINK_ID` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`AUTH_VERIFIED` tinyint(1) DEFAULT NULL,
`CREATED` datetime DEFAULT NULL,
`ID` bigint NOT NULL AUTO_INCREMENT,
`UPDATED` datetime DEFAULT NULL,
`USER_ID` bigint DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `fk_ao_9412a1_user_app_link_user_id` (`USER_ID`),
KEY `index_ao_9412a1_use643533071` (`APPLICATION_LINK_ID`),
CONSTRAINT `fk_ao_9412a1_user_app_link_user_id` FOREIGN KEY (`USER_ID`) REFERENCES `AO_9412A1_AOUSER` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
The following unit test will fail on JDBC Connector 8.0.26, because the database will return the
import org.junit.After;
import org.junit.Assert;
import org.junit.Assume;
import org.junit.Before;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class DuplicateFKTest {
private Connection connection;
@Before
public void setup() throws Exception {
connection = getMySqlConnection();
}
@After
public void tearDown() throws Exception {
connection.close();
}
/**
* Please setup the running DB with necessary tables
*/
@Test
public void testForeignKeyIsNotDuplicated() throws Exception {
DatabaseMetaData meta = connection.getMetaData();
ResultSet rs = meta.getImportedKeys(connection.getCatalog(), null, "AO_9412A1_USER_APP_LINK");
int numberOfFk = 0;
while (rs.next()) {
String fkTableName = rs.getString("FKTABLE_NAME");
String fkColumnName = rs.getString("FKCOLUMN_NAME");
int fkSequence = rs.getInt("KEY_SEQ");
numberOfFk++;
// Additional logging
System.out.println("getExportedKeys(): index=" + numberOfFk);
System.out.println("getExportedKeys(): fkTableName=" + fkTableName);
System.out.println("getExportedKeys(): fkColumnName=" + fkColumnName);
System.out.println("getExportedKeys(): fkSequence=" + fkSequence);
System.out.println();
}
Assume.assumeTrue("There should be at least one FK. " +
"If this fails, please make sure you set up the DB and tables correctly. See README.md for more details.",
numberOfFk >= 1);
Assert.assertEquals("There should be exactly one FK", numberOfFk, 1);
}
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);
return DriverManager.getConnection(url, username, password);
}
}
Suggested fix:
The query which is used to fetch the foreign keys, and it's result is:
SELECT DISTINCT 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 1
ELSE 1 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 1
ELSE 1 END AS DELETE_RULE,
A.CONSTRAINT_NAME AS FK_NAME,
TC.CONSTRAINT_NAME 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)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON (A.REFERENCED_TABLE_SCHEMA = TC.TABLE_SCHEMA AND A.REFERENCED_TABLE_NAME = TC.TABLE_NAME AND
TC.CONSTRAINT_TYPE IN ('UNIQUE', 'PRIMARY KEY'))
WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND A.TABLE_SCHEMA = 'dbdemo'
AND A.TABLE_NAME = 'AO_9412A1_USER_APP_LINK'
AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL
ORDER BY A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, A.ORDINAL_POSITION;
+-------------+---------------+------------------+---------------+-------------+---------------+-------------------------+---------------+---------+-------------+-------------+------------------------------------+-----------------------------+---------------+
| PKTABLE_CAT | PKTABLE_SCHEM | PKTABLE_NAME | PKCOLUMN_NAME | FKTABLE_CAT | FKTABLE_SCHEM | FKTABLE_NAME | FKCOLUMN_NAME | KEY_SEQ | UPDATE_RULE | DELETE_RULE | FK_NAME | PK_NAME | DEFERRABILITY |
+-------------+---------------+------------------+---------------+-------------+---------------+-------------------------+---------------+---------+-------------+-------------+------------------------------------+-----------------------------+---------------+
| dbdemo | NULL | AO_9412A1_AOUSER | ID | dbdemo | NULL | AO_9412A1_USER_APP_LINK | USER_ID | 1 | 1 | 1 | fk_ao_9412a1_user_app_link_user_id | PRIMARY | 7 |
| dbdemo | NULL | AO_9412A1_AOUSER | ID | dbdemo | NULL | AO_9412A1_USER_APP_LINK | USER_ID | 1 | 1 | 1 | fk_ao_9412a1_user_app_link_user_id | U_AO_9412A1_AOUSER_USERNAME | 7 |
+-------------+---------------+------------------+---------------+-------------+---------------+-------------------------+---------------+---------+-------------+-------------+------------------------------------+-----------------------------+---------------+
2 rows in set (0.01 sec)
The problematic part of the query is the left join to TABLE_CONSTRAINTS table. If the table has multiple of them, it will generate the duplicate rows for the foreign key. For example, this was my contents of TABLE_CONSTRAINTS:
mysql> select * from information_schema.table_constraints where table_schema = 'dbdemo';
+--------------------+-------------------+------------------------------------+--------------+-------------------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+------------------------------------+--------------+-------------------------+-----------------+----------+
| def | dbdemo | PRIMARY | dbdemo | AO_9412A1_AOUSER | PRIMARY KEY | YES |
| def | dbdemo | U_AO_9412A1_AOUSER_USERNAME | dbdemo | AO_9412A1_AOUSER | UNIQUE | YES |
| def | dbdemo | PRIMARY | dbdemo | AO_9412A1_USER_APP_LINK | PRIMARY KEY | YES |
| def | dbdemo | fk_ao_9412a1_user_app_link_user_id | dbdemo | AO_9412A1_USER_APP_LINK | FOREIGN KEY | YES |
+--------------------+-------------------+------------------------------------+--------------+-------------------------+-----------------+----------+
4 rows in set (0.00 sec)
The fix to the bug is to get rid of the LEFT JOIN. It's broken anyway, because it tries to get the constraint name of the table, but there is not enough information if that constraint is actually involved in the FK or not.
