Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-890

getImportedKeys/getTables regression returning an empty resultset for null/empty catalog

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            diego dupin Diego Dupin
            Reporter:
            amohammedalfresco Abdul Mohammed
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration