Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27385

Adding constraint sometimes (non-repeatably) checks historical rows

    XMLWordPrintable

    Details

      Description

      There is an error that I am running across but am unable to repeat on a simple schema. If anyone can point me in the right direction to collect more details, I would be happy to look.

      The problem arises when attempting to add a unique key on two columns with the following:

      set @@system_versioning_alter_history=1;
      ALTER TABLE manufacturer_pn
      ADD CONSTRAINT manufacturer_pn_UN
      UNIQUE KEY (manufacturer, manufacturer_pn);
      

      This results in a duplicate entry error:
      `Duplicate entry 'Yageo-RC0805FR-071ML' for key 'manufacturer_pn_UN'

      In an effort to find the problem, I searched for everything that matched the problematic key:

      SELECT id, manufacturer, manufacturer_pn, ROW_START, ROW_END
      FROM manufacturer_pn
      FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
      WHERE manufacturer_pn LIKE 'RC0805FR-071ML'
      ORDER BY ROW_END;
      

      Results of the command:

      id  |manufacturer|manufacturer_pn|ROW_START          |ROW_END            |
      ----+------------+---------------+-------------------+-------------------+
      5407|Yageo       |RC0805FR-071ML |2021-12-06 10:05:11|2021-12-16 00:13:34|
      5363|Yageo       |RC0805FR-071ML |2021-12-06 10:05:11|2021-12-16 00:13:34|
      5407|Yageo       |RC0805FR-071ML |2021-12-16 00:13:34|2021-12-29 12:03:47|
      5363|Yageo       |RC0805FR-071ML |2021-12-16 00:13:34|2021-12-29 12:22:30|
      5363|Yageo Group |RC0805FR-071ML |2021-12-29 12:22:30|2021-12-29 23:41:56|
      5363|Yageo       |RC0805FR-071ML |2021-12-29 23:41:56|2021-12-29 23:57:01|
      5363|Yageo Test  |RC0805FR-071ML |2021-12-29 23:57:01|2021-12-29 23:57:31|
      

      Note that none of these rows are active, so there should be no violation. This leads me to think that adding the unique constraint is running a check on historical rows, which it shouldn't. Setting `check_constraint_checks` to 0 also has no effect, and adding the key still fails.

      As mentioned, I would be happy to dig deeper to try to find the cause since I can't seem to replicate this issue on table from scratch.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            tgross35 Trevor Gross
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.