Uploaded image for project: 'Opencast'
  1. MH-10974

Foreign key constraints on mh_user_settings and mh_series_property have no unique counterpiece

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Fixed and reviewed
    • Affects versions: 2.0.0
    • Fix versions: 2.0.2
    • Components: Backend Software
    • Labels:
      None
    • Severity:
      Incorrect Documentation
    • Steps to reproduce:
      Hide
      While transfering the MySQL DDL (https://bitbucket.org/opencast-community/matterhorn/src/564c1f251d525b67f600e7fe244edd422bf170f2/docs/scripts/ddl/mysql5.sql?at=r%2F2.0.x) and Upgrade Script (https://bitbucket.org/opencast-community/matterhorn/src/564c1f251d525b67f600e7fe244edd422bf170f2/docs/upgrade/1.6_to_2.0/mysql5.sql?at=r/2.0.x) to Postgres, I noticed two problems with foreign keys:

      The create table statement for table mh_user_settings contains contraints with foreign keys. 1:1 transferred to Postgres, this create table statement says:

      CREATE TABLE "mh_user_settings" (
        "id" bigint NOT NULL,
        "setting_key" character varying(255) NOT NULL,
        "setting_value" text NOT NULL,
        "username" character varying(128) NOT NULL,
        "organization" character varying(128) NOT NULL,
        PRIMARY KEY ("id"),
        CONSTRAINT "FK_mh_user_setting_username" FOREIGN KEY ("username") REFERENCES "mh_user" ("username"),
        CONSTRAINT "FK_mh_user_setting_org" FOREIGN KEY ("organization") REFERENCES "mh_user" ("organization")
      );

      When I run this comment, Postgres tells me:

      ERROR: there is no unique constraint matching given keys for referenced table "mh_user"

      This is because the mh_user table has a unique contraint on (username, organization), but not on username.


      A similar problem happens with the mh_series_property table:

      The create statement, 1:1 transferred to Postgres, is:

      CREATE TABLE "mh_series_property" (
        "organization" character varying(128) NOT NULL,
        "series" character varying(128) NOT NULL,
        "name" character varying(255) NOT NULL,
        "value" text,
        PRIMARY KEY ("organization", "series", "name")
      -- PRIMARY KEY ("organization", "series", "name"),
      -- CONSTRAINT "FK_mh_series_property_series" FOREIGN KEY ("series") REFERENCES "mh_series" ("id") ON DELETE CASCADE
      );

      Postgres gives me again an error for the same reason:

      ERROR: there is no unique constraint matching given keys for referenced table "mh_series"

      ---------------------------------------------------

      As no-one else had this problem before with MySQL, I assume that MySQL does not care about the fact of uniqueness. However, I am bringing this up as release blocker so that anyone with more insight has the chance to check if this might produce data inconsistencies.

      ---------------------------------------------------

      As both tables are also created in the upgrade script, the change should also be applied there.
      Show
      While transfering the MySQL DDL ( https://bitbucket.org/opencast-community/matterhorn/src/564c1f251d525b67f600e7fe244edd422bf170f2/docs/scripts/ddl/mysql5.sql?at=r%2F2.0.x ) and Upgrade Script ( https://bitbucket.org/opencast-community/matterhorn/src/564c1f251d525b67f600e7fe244edd422bf170f2/docs/upgrade/1.6_to_2.0/mysql5.sql?at=r/2.0.x ) to Postgres, I noticed two problems with foreign keys: The create table statement for table mh_user_settings contains contraints with foreign keys. 1:1 transferred to Postgres, this create table statement says: CREATE TABLE "mh_user_settings" (   "id" bigint NOT NULL,   "setting_key" character varying(255) NOT NULL,   "setting_value" text NOT NULL,   "username" character varying(128) NOT NULL,   "organization" character varying(128) NOT NULL,   PRIMARY KEY ("id"),   CONSTRAINT "FK_mh_user_setting_username" FOREIGN KEY ("username") REFERENCES "mh_user" ("username"),   CONSTRAINT "FK_mh_user_setting_org" FOREIGN KEY ("organization") REFERENCES "mh_user" ("organization") ); When I run this comment, Postgres tells me: ERROR: there is no unique constraint matching given keys for referenced table "mh_user" This is because the mh_user table has a unique contraint on (username, organization), but not on username. A similar problem happens with the mh_series_property table: The create statement, 1:1 transferred to Postgres, is: CREATE TABLE "mh_series_property" (   "organization" character varying(128) NOT NULL,   "series" character varying(128) NOT NULL,   "name" character varying(255) NOT NULL,   "value" text,   PRIMARY KEY ("organization", "series", "name") -- PRIMARY KEY ("organization", "series", "name"), -- CONSTRAINT "FK_mh_series_property_series" FOREIGN KEY ("series") REFERENCES "mh_series" ("id") ON DELETE CASCADE ); Postgres gives me again an error for the same reason: ERROR: there is no unique constraint matching given keys for referenced table "mh_series" --------------------------------------------------- As no-one else had this problem before with MySQL, I assume that MySQL does not care about the fact of uniqueness. However, I am bringing this up as release blocker so that anyone with more insight has the chance to check if this might produce data inconsistencies. --------------------------------------------------- As both tables are also created in the upgrade script, the change should also be applied there.
    • Tags (folksonomy):

      TestRail: Results

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                lrohner Lukas Rohner
                Reporter:
                abias Alexander Bias
              • Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  TestRail: Cases