[CONJ-890] getImportedKeys/getTables regression returning an empty resultset for null/empty catalog Created: 2021-06-23  Updated: 2021-06-29  Resolved: 2021-06-29

Status: Closed
Project: MariaDB Connector/J
Component/s: metadata
Affects Version/s: 2.7.3
Fix Version/s: 2.7.4

Type: Bug Priority: Critical
Reporter: Abdul Mohammed Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: metadata


 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.

 Comments   
Comment by Diego Dupin [ 2021-06-29 ]

issue reproduced.
in 2.7.3, DatabaseMetaData getImportedKeys implementation has a regression when setting catalog to null or empty value, returning an empty resultset in place of rows for each foreign keys.

Generated at Thu Feb 08 03:19:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.