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

InnoDB assertion with result != FTS_INVALID on DELETE IGNORE with FK cascades on tables with FULLTEXT indexes

    XMLWordPrintable

Details

    • Can result in hang or crash

    Description

      Problem:
      ========
      DELETE IGNORE on a parent table crashes the server when an ON DELETE CASCADE
      foreign key leads to a child table with a FULLTEXT index. The crash happens if the cascaded
      delete of the child row fails with an error that IGNORE suppresses.
      The failure here is a RESTRICT foreign key from a grandchild table.
      It then happens again on a later parent row that cascades into the same child row

      Test case:
      =========

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (id INT PRIMARY KEY, a VARCHAR(10), KEY(a)) ENGINE=InnoDB;
       
      CREATE TABLE t2 (id INT PRIMARY KEY, a VARCHAR(10), b VARCHAR(10),
        KEY(a), KEY(b), FULLTEXT(a),
        FOREIGN KEY (a) REFERENCES t1(a) ON DELETE CASCADE) ENGINE=InnoDB;
       
      CREATE TABLE t3 (id INT PRIMARY KEY, b VARCHAR(10), KEY(b),
        FOREIGN KEY (b) REFERENCES t2(b) ON DELETE RESTRICT) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES (1,'xxx'),(2,'xxx');
      INSERT INTO t2 VALUES (1,'xxx','k');
      INSERT INTO t3 VALUES (1,'k');
       
      DELETE IGNORE FROM t1;
      SELECT * FROM t1;
      SELECT * FROM t2 WHERE MATCH(a) AGAINST('xxx');
       
      DROP TABLE t3, t2, t1;
      

      Analysis:
      ==========
      1) When a cascade is processed, row_ins_foreign_check_on_constraint()
      calls fts_trx_add_op(..., FTS_DELETE) for the child row if the foreign
      key affects a fulltext column. This happens before the outcome of the
      cascaded delete is known.
      2) The cascaded delete then fails because of the RESTRICT constraint
      3) row_update_for_mysql_using_upd_graph() passes the error to row_mysql_handle_errors(). That function rolls back the data changes
      to the internal savepoint with trx->rollback(savept). It does not touch
      the FTS bookkeeping in trx->fts_trx.
      4) The FTS_DELETE recorded for the child row stays in trx->fts_trx->last_stmt,
      even though the row delete was rolled back.
      5) The next parent row cascades into the same child row and records
      FTS_DELETE again. The FTS state table in fts_trx_row_get_new_state() has no
      valid transition from D on event D. It returns FTS_INVALID, and the assertion fails.

      This is to merge the mysql commit@ac91b1506af34e19b1cb936691310b9268bc0dd8

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              thiru Thirunarayanan Balathandayuthapani
              Thirunarayanan Balathandayuthapani Thirunarayanan Balathandayuthapani
              Marko Mäkelä Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.