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

FULLTEXT index disables foreign-key enforcement while metadata still reports the constraint

    XMLWordPrintable

Details

    Description

      • Description
        Hi, MariaDB developer.
        Thanks for reading my report.
        Creating a FULLTEXT index on a foreign-key column appears to disable foreign-key enforcement in MariaDB, while information_schema.KEY_COLUMN_USAGE still reports the constraint as active. As a result, MariaDB allows data that violates the foreign key even though the system catalog still claims the constraint exists.
      • Steps to reproduce

        DROP DATABASE IF EXISTS test_fkft;
        CREATE DATABASE test_fkft;
        USE test_fkft;
         
        CREATE TABLE parent (
          c0 varchar(16) NOT NULL,
          PRIMARY KEY (c0)
        ) ENGINE=InnoDB;
         
        CREATE TABLE child (
          id int NOT NULL,
          c0 varchar(16) DEFAULT NULL,
          PRIMARY KEY (id),
          CONSTRAINT fk0 FOREIGN KEY (c0) REFERENCES parent (c0)
        ) ENGINE=InnoDB;
         
        INSERT INTO parent (c0) VALUES ('a');
        INSERT INTO child (id, c0) VALUES (1, 'a');
        CREATE FULLTEXT INDEX ft ON child (c0);
         
        -- Must fail with a foreign-key error. MariaDB accepts it.
        -- MySQL fails with "Cannot add or update a child row: a foreign key constraint fails (`test_fkft`.`child`, CONSTRAINT `fk0` FOREIGN KEY (`c0`) REFERENCES `parent` (`c0`))"
        UPDATE child SET c0 = 'b';
        

      • Expected behavior
        The UPDATE should fail because 'b' does not exist in parent(c0).
        MySQL rejects the same statement with a foreign-key constraint error.
      • Actual behavior
        MariaDB accepts the UPDATE.
        However, the foreign key is still reported in KEY_COLUMN_USAGE:

        -- Catalog still claims fk0. Expected: no such row, or SHOW without CONSTRAINT.
        SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME,
               REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = 'test_fkft'
          AND REFERENCED_TABLE_NAME IS NOT NULL;
        +------------+-----------------+-------------+-----------------------+------------------------+
        | TABLE_NAME | CONSTRAINT_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
        +------------+-----------------+-------------+-----------------------+------------------------+
        | child      | fk0             | c0          | parent                | c0                     |
        +------------+-----------------+-------------+-----------------------+------------------------+
        

        The table now contains an orphaned foreign-key value:

        -- Violation of foreign key constraints
        SELECT child.c0 
        FROM child 
        WHERE NOT EXISTS (SELECT 1 FROM parent WHERE parent.c0 = child.c0);
        +------+
        | c0   |
        +------+
        | b    |
        +------+
        

        Therefore, the catalog reports fk0 as present, but the corresponding referential-integrity constraint is no longer enforced.
        Creating a FULLTEXT index should either preserve foreign-key enforcement or be rejected if the combination is unsupported

      Attachments

        Activity

          People

            thiru Thirunarayanan Balathandayuthapani
            jinhui lai jinhui lai
            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.