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

FK fields cannot be used anymore in generated columns

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3
    • N/A
    • None
    • Docker 10.5.22 official image

    Description

      Since 10.5.22, our migrations are not running anymore with the following error message:

      SQLSTATE[HY000]: General error: 1901 Function or expression 'variant_listing_config' cannot be used in the CHECK clause of `variant_listing_config`"

      When I remove the FK constraint it works again.

      Our migration steps simplified

      DROP TABLE IF EXISTS  `t1`;
       
      CREATE TABLE `t1` (
                                 `id` binary(16) NOT NULL,
                                 `configurator_group_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`configurator_group_config`)),
                                 `main_variant_id` binary(16) DEFAULT NULL,
                                 `display_parent` tinyint(1) DEFAULT NULL,
                                 PRIMARY KEY (`id`),
                                 CONSTRAINT `fk.t1.main_variant_id` FOREIGN KEY (`main_variant_id`) REFERENCES `t1` (`id`) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
       
      ALTER TABLE `t1`
          ADD COLUMN `variant_listing_config` JSON
              GENERATED ALWAYS AS (
                  CASE
                      WHEN `display_parent` IS NOT NULL OR `main_variant_id` IS NOT NULL OR
                           `configurator_group_config` IS NOT NULL
                          THEN (JSON_OBJECT('displayParent', `display_parent`, 'mainVariantId', LOWER(HEX(`main_variant_id`)),
                                            'configuratorGroupConfig', JSON_EXTRACT(`configurator_group_config`, '$')))
                      END) VIRTUAL
      ;
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              shyim Soner Sayakci
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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