Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
2.7.3
Description
I would like report an issue encountered with mariadb-java-client version 2.7.3 where getting foreign keys via getImportedKeysUsingInformationSchema is returning null values which up until 2.7.2 is still working as expected.
I work for Alfresco Software and we create a table as:
2021-06-23 13:05:32,264 DEBUG [domain.schema.SchemaBootstrap] [main] Executing statement: CREATE TABLE alf_access_control_entry |
(
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
version BIGINT NOT NULL,
|
permission_id BIGINT NOT NULL,
|
authority_id BIGINT NOT NULL,
|
allowed BIT NOT NULL,
|
applies INTEGER NOT NULL,
|
context_id BIGINT,
|
PRIMARY KEY (id),
|
UNIQUE KEY permission_id (permission_id, authority_id, allowed, applies),
|
KEY fk_alf_ace_ctx (context_id),
|
KEY fk_alf_ace_perm (permission_id),
|
KEY fk_alf_ace_auth (authority_id),
|
CONSTRAINT fk_alf_ace_auth FOREIGN KEY (authority_id) REFERENCES alf_authority (id),
|
CONSTRAINT fk_alf_ace_ctx FOREIGN KEY (context_id) REFERENCES alf_ace_context (id),
|
CONSTRAINT fk_alf_ace_perm FOREIGN KEY (permission_id) REFERENCES alf_permission (id)
|
) ENGINE=InnoDB
|
with it's schema as:
<table name="alf_access_control_entry"> |
<columns>
|
<column name="id" order="1"> |
<type>bigint</type>
|
<nullable>false</nullable> |
<autoincrement>true</autoincrement> |
</column>
|
<column name="version" order="2"> |
<type>bigint</type>
|
<nullable>false</nullable> |
<autoincrement>false</autoincrement> |
</column>
|
<column name="permission_id" order="3"> |
<type>bigint</type>
|
<nullable>false</nullable> |
<autoincrement>false</autoincrement> |
</column>
|
<column name="authority_id" order="4"> |
<type>bigint</type>
|
<nullable>false</nullable> |
<autoincrement>false</autoincrement> |
</column>
|
<column name="allowed" order="5"> |
<type>bit</type>
|
<nullable>false</nullable> |
<autoincrement>false</autoincrement> |
</column>
|
<column name="applies" order="6"> |
<type>int</type> |
<nullable>false</nullable> |
<autoincrement>false</autoincrement> |
</column>
|
<column name="context_id" order="7"> |
<type>bigint</type>
|
<nullable>true</nullable> |
<autoincrement>false</autoincrement> |
</column>
|
</columns>
|
<primarykey name="PRIMARY"> |
<columnnames>
|
<columnname order="1">id</columnname> |
</columnnames>
|
</primarykey>
|
<foreignkeys>
|
<foreignkey name="fk_alf_ace_auth"> |
<localcolumn>authority_id</localcolumn>
|
<targettable>alf_authority</targettable>
|
<targetcolumn>id</targetcolumn>
|
</foreignkey>
|
<foreignkey name="fk_alf_ace_ctx"> |
<localcolumn>context_id</localcolumn>
|
<targettable>alf_ace_context</targettable>
|
<targetcolumn>id</targetcolumn>
|
</foreignkey>
|
<foreignkey name="fk_alf_ace_perm"> |
<localcolumn>permission_id</localcolumn>
|
<targettable>alf_permission</targettable>
|
<targetcolumn>id</targetcolumn>
|
</foreignkey>
|
</foreignkeys>
|
<indexes>
|
<index name="permission_id" unique="true"> |
<columnnames>
|
<columnname>permission_id</columnname>
|
<columnname>authority_id</columnname>
|
<columnname>allowed</columnname>
|
<columnname>applies</columnname>
|
</columnnames>
|
</index>
|
<index name="fk_alf_ace_ctx" unique="false"> |
<columnnames>
|
<columnname>context_id</columnname>
|
</columnnames>
|
</index>
|
<index name="fk_alf_ace_perm" unique="false"> |
<columnnames>
|
<columnname>permission_id</columnname>
|
</columnnames>
|
</index>
|
<index name="fk_alf_ace_auth" unique="false"> |
<columnnames>
|
<columnname>authority_id</columnname>
|
</columnnames>
|
</index>
|
</indexes>
|
</table>
|
and use java.sql.DatabaseMetaData interface's getImportedKeys method which is implemented by mariadb-java-client:2.7.3's getImportedKeys and subsequently it uses getImportedKeysUsingInformationSchema.
I believe a change due to CONJ-880 may have broken how foreign keys are returned and this commit seems to be related to it.
When the schema is checked against the database, it seems all the foreign keys are returned as null and debugging hinted me below issue:
Difference: missing foreign key from database, expected at path: .alf_access_control_entry.fk_alf_ace_auth
|
Difference: missing foreign key from database, expected at path: .alf_access_control_entry.fk_alf_ace_ctx
|
Difference: missing foreign key from database, expected at path: .alf_access_control_entry.fk_alf_ace_perm
|
I couldn't find a working solution or a workaround to fix this. However, I also noted in your next release 3.0.0-alpha this problem is fixed and foreign keys are listed as normal. However, I wanted to report this issue explicitly for 2.7.3.
I used MariaDB server versions: 10.2.18 and 10.3.4 using MariaDB's docker images to reproduce this issue.