Schema verify warnings about missing indexes after schema upgrade to 4.6

Site Administrators
1

Hi,

We have upgraded the CTA schema from 4.5 to 4.6 on our development instance (using the script here: catalogue/migrations/liquibase/oracle · master · cta / CTA · GitLab).

Using cta-catalogue-schema-verify (from cta-catalogueutils-4.6.1-1.el7.x86_64). It seems to successfully verify the schema, but outputs several warnings for “Column X is part of a foreign key constraint but has no index”:

[root@cta-front02 ~]# cta-catalogue-schema-verify /etc/cta/cta-catalogue.conf
Schema version : 4.6
Checking indexes...
  SUCCESS
Checking tables, columns and constraints...
  SUCCESS
Status of the checking : SUCCESS
  WARNING: Column ARCHIVE_FILE.STORAGE_CLASS_ID is part of a foreign key constraint but has no index
  WARNING: Column FILE_RECYCLE_LOG.STORAGE_CLASS_ID is part of a foreign key constraint but has no index
  WARNING: Column FILE_RECYCLE_LOG.VID is part of a foreign key constraint but has no index
  WARNING: Column REQUESTER_ACTIVITY_MOUNT_RULE.MOUNT_POLICY_NAME is part of a foreign key constraint but has no index
  WARNING: Column REQUESTER_GROUP_MOUNT_RULE.MOUNT_POLICY_NAME is part of a foreign key constraint but has no index
  WARNING: Column REQUESTER_MOUNT_RULE.MOUNT_POLICY_NAME is part of a foreign key constraint but has no index
  WARNING: Column STORAGE_CLASS.VIRTUAL_ORGANIZATION_ID is part of a foreign key constraint but has no index
  WARNING: Column TAPE.LOGICAL_LIBRARY_ID is part of a foreign key constraint but has no index
  WARNING: Column TAPE.MEDIA_TYPE_ID is part of a foreign key constraint but has no index
  WARNING: Column TAPE_POOL.VIRTUAL_ORGANIZATION_ID is part of a foreign key constraint but has no index
[root@cta-front02 ~]#

Is this something to be concerned about? I note from this issue that this is related to some recent work - Check all foreign key references have a full index on both sides of the constraint (#1158) · Issues · cta / CTA · GitLab - so possibly just an improvement in the verification rather than a new problem with our DB.

Thanks,
Tom

2

Hi Tom,

Those warnings are normal in the 4.6 version of the schema. The schema version 10 adds the missing indexes.

Best regards,
Jorge

3

Thanks for confirming!

Have a good weekend,
Tom

4

Hi Tom,

Congratulations on a successful upgrade to v4.6!

We have added a container for the v10 schema upgrade (corresponding to CTA v4.7.0), and (as of yesterday) the container-based upgrade procedure documentation has been updated here:

This one is a complex upgrade, so we strongly recommend executing it on a clone of your production DB to ensure that it goes smoothly, before you upgrade in production.

Let us know how you get on!

Best regards,

Michael