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

FTS row mismatch after crash recovery

    Details

      Description

      The following test case gives the unexpected result after crash recovery.

       
      --source include/have_innodb.inc
      create table t1(
              id int unsigned auto_increment not null primary key,
              title varchar(200),
              body  text)engine=innodb;
       
      # Insert six rows
      INSERT INTO t1 (title,body) VALUES
              ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
              ('How To Use MySQL Well','After you went through a ...'),
              ('Optimizing MySQL','In this tutorial we will show ...'),
              ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
              ('MySQL vs. YourSQL','In the following database comparison ...'),
              ('MySQL Security','When configured properly, MySQL ...');
       
      # Recreate fulltext index to see if everything is OK
      CREATE FULLTEXT INDEX idx ON t1 (title,body);
       
      --source include/restart_mysqld.inc
       
      # This insert will re-initialize the Doc ID counter, it should not crash
      INSERT INTO t1 (title,body) VALUES
              ('MySQL Tutorial','DBMS stands for DataBase ...');
       
      # Should return 3 rows
      SELECT * FROM t1
              WHERE MATCH (title,body)
              AGAINST ('Database' IN NATURAL LANGUAGE MODE);
       
      INSERT INTO t1 (title,body) VALUES
              ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
              ('How To Use MySQL Well','After you went through a ...'),
              ('Optimizing MySQL','In this tutorial we will show ...'),
              ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
              ('MySQL vs. YourSQL','In the following database comparison ...'),
              ('MySQL Security','When configured properly, MySQL ...');
       
      # Should return 5 rows
      SELECT * FROM t1
              WHERE MATCH (title,body)
              AGAINST ('Database' IN NATURAL LANGUAGE MODE);
       
      select count(*) from t1;
       
      select * from information_schema.innodb_sys_tablespaces;
       
      --echo # Kill and restart
      let $shutdown_timeout=0;
      let $restart_parameters="--debug_dbug='+d,ib_log'";
      --source include/restart_mysqld.inc
       
      # Should return 5 rows, But it returns only 4 rows
      SELECT * FROM t1
              WHERE MATCH (title,body)
              AGAINST ('Database' IN NATURAL LANGUAGE MODE);
       
      SET GLOBAL innodb_optimize_fulltext_only=ON;
      OPTIMIZE TABLE t1;
      select count(*) from t1;
       
      # Should return 5 rows, But it returns 4 rows.
      SELECT * FROM t1
              WHERE MATCH (title,body)
              AGAINST ('Database' IN NATURAL LANGUAGE MODE);
       
       
      drop table t1;
      
      

      Redo log part went wrong for committed fts transaction.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                thiru Thirunarayanan Balathandayuthapani
                Reporter:
                thiru Thirunarayanan Balathandayuthapani
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: