[MDEV-19073] FTS row mismatch after crash recovery Created: 2019-03-28  Updated: 2019-10-25  Resolved: 2019-10-25

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.2.28, 10.1.42, 10.3.19, 10.4.9

Type: Bug Priority: Major
Reporter: Thirunarayanan Balathandayuthapani Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Relates
relates to MDEV-12676 InnoDB FTS duplicate key error Closed

 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.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2019-03-28 ]

I can able to repeat the failure in MySQL-5.6

let $MYSQLD_DATADIR=`select @@datadir`;
 
 
 
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 ...');
 
 
 
# Recreate fulltext index to see if everything is OK
 
CREATE FULLTEXT INDEX idx ON t1 (title,body);
 
 
 
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
 
--shutdown_server
 
--source include/wait_until_disconnected.inc
 
# Do something while server is down
 
--enable_reconnect
 
# Write file to make mysql-test-run.pl start up the server again
 
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
 
--source include/wait_until_connected_again.inc
 
 
 
 
 
insert into t1 (title, body) values("mariadb", "database");
 
 
 
SELECT * FROM t1
 
        WHERE MATCH (title,body)
 
        AGAINST ('mariadb' IN NATURAL LANGUAGE MODE);
 
 
 
# We expect a restart.
 
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
 
 
 
--echo # Quick shutdown and restart server
 
--shutdown_server 0
 
 
 
# Wait for the server to come back up, and reconnect.
 
--enable_reconnect
 
--source include/wait_until_connected_again.inc
 
 
 
# Should return 1 row, but it doesn't return anything
 
SELECT * FROM t1
 
        WHERE MATCH (title,body)
 
        AGAINST ('mariadb' IN NATURAL LANGUAGE MODE);
 
drop table t1;

Comment by Thirunarayanan Balathandayuthapani [ 2019-04-02 ]

I can able to repeat the failure in MySQL-5.7.

Comment by Thirunarayanan Balathandayuthapani [ 2019-04-05 ]

InnoDB FTS writes synced doc id to FTS_CONFIG table. While recovery or initializing FTS index, it reads the doc id from
CONFIG table and tries to do tokenize the remaining doc from the table and insert into the auxiliary table.

Problem is that we store synced doc_id as doc_id + 1. I don't know the reason behind it. While recovery, InnoDB FTS read the
docs which are greater than doc_id. Obviously, we're missing one doc id to insert into aux table during recovery.

Comment by Thirunarayanan Balathandayuthapani [ 2019-04-05 ] 

create table t1(
 
FTS_DOC_ID bigint unsigned auto_increment not null primary key,
 
title varchar(200),
 
body  text)engine=innodb;
 
INSERT INTO t1 (title,body) VALUES
 
('MySQL vs. YourSQL','In the following database comparison ...');
 
CREATE FULLTEXT INDEX idx ON t1 (title,body);
 
# restart
 
insert into t1 (title, body) values('mariadb documentation', 'mariadb');
 
SELECT * FROM t1
 
WHERE MATCH (title,body)
 
AGAINST ('mariadb' IN NATURAL LANGUAGE MODE);
 
FTS_DOC_ID      title   body
 
2       mariadb documentation   mariadb
 
# Kill and restart
 
SELECT * FROM t1
 
WHERE MATCH (title,body)
 
AGAINST ('mariadb' IN NATURAL LANGUAGE MODE);
 
FTS_DOC_ID      title   body
 
drop table t1;

Above test case is used to repeat mysql-5.7 failure.

Comment by Marko Mäkelä [ 2019-04-05 ]

thiru says that this is related to how the FTS_DOC_ID is being stored in the internal CONFIG table. MDEV-12676 fixed a bug in assigning FTS_DOC_ID which has not yet been fixed in MySQL. That could be why the bug repeats differently between MariaDB and MySQL.

Comment by Thirunarayanan Balathandayuthapani [ 2019-10-23 ]

Patch is in bb-10.1-thiru. Please take a look whenever it is possible

Comment by Marko Mäkelä [ 2019-10-25 ]

I cleaned up the test case, to avoid introducing new server restarts (which are slow).

Generated at Thu Feb 08 08:48:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.