Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
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
- relates to
-
-
- Closed
-
Activity
I can able to repeat the failure in MySQL-5.7.
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.
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.
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.
Patch is in bb-10.1-thiru. Please take a look whenever it is possible
I cleaned up the test case, to avoid introducing new server restarts (which are slow).
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 rowsINSERT INTO t1 (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...');# Recreate fulltext index to see if everything is OKCREATE 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.incinsert into t1 (title, body) values("mariadb", "database");SELECT * FROM t1WHERE 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 anythingSELECT * FROM t1WHERE MATCH (title,body)AGAINST ('mariadb' IN NATURAL LANGUAGE MODE);drop table t1;