mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
2c567b2fa3
- This patch does the following: git revert --no-commit673243c893git revert --no-commit6c669b9586git revert --no-commitbacaf2d4f4git checkout HEAD mysql-test git revert --no-commit1fd7d3a9adAbove command reverts MDEV-29277, MDEV-25581, MDEV-29342. When binlog is enabled, trasaction takes a lot of time to do sync operation on innodb fts table. This leads to block of other transaction commit. To avoid this failure, remove the fulltext sync operation during transaction commit. So reverted MDEV-25581 related patches. We filed MDEV-31105 to avoid the memory consumption problem during fulltext sync operation.
157 lines
4.9 KiB
Text
157 lines
4.9 KiB
Text
connect con1,localhost,root,,;
|
|
connection default;
|
|
# Case 1: Test select and insert(row in both disk and cache)
|
|
CREATE TABLE t1 (
|
|
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
FULLTEXT(title)
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO t1(title) VALUES('mysql');
|
|
INSERT INTO t1(title) VALUES('database');
|
|
connection con1;
|
|
SET @old_dbug = @@SESSION.debug_dbug;
|
|
SET debug_dbug = '+d,fts_instrument_sync_debug';
|
|
SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected';
|
|
INSERT INTO t1(title) VALUES('mysql database');
|
|
connection default;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR written';
|
|
SET GLOBAL innodb_ft_aux_table="test/t1";
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
database 2 3 2 2 0
|
|
database 2 3 2 3 6
|
|
mysql 1 3 2 1 0
|
|
mysql 1 3 2 3 0
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
SET GLOBAL innodb_ft_aux_table=default;
|
|
SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
|
|
FTS_DOC_ID title
|
|
1 mysql
|
|
2 database
|
|
SET DEBUG_SYNC= 'now SIGNAL selected';
|
|
connection con1;
|
|
SET @old_dbug = @@SESSION.debug_dbug;
|
|
SET GLOBAL innodb_ft_aux_table="test/t1";
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
database 2 3 2 2 0
|
|
database 2 3 2 3 6
|
|
mysql 1 3 2 1 0
|
|
mysql 1 3 2 3 0
|
|
SET GLOBAL innodb_ft_aux_table=default;
|
|
SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
|
|
FTS_DOC_ID title
|
|
3 mysql database
|
|
1 mysql
|
|
2 database
|
|
connection default;
|
|
DROP TABLE t1;
|
|
# Case 2: Test insert and insert(sync)
|
|
CREATE TABLE t1 (
|
|
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
FULLTEXT(title)
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO t1(title) VALUES('mysql');
|
|
INSERT INTO t1(title) VALUES('database');
|
|
connection con1;
|
|
SET debug_dbug = '+d,fts_instrument_sync_debug';
|
|
SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted';
|
|
INSERT INTO t1(title) VALUES('mysql database');
|
|
connection default;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR written';
|
|
INSERT INTO t1(title) VALUES('mysql database');
|
|
SET DEBUG_SYNC= 'now SIGNAL inserted';
|
|
connection con1;
|
|
SET debug_dbug = @old_dbug;
|
|
SET GLOBAL innodb_ft_aux_table="test/t1";
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
database 2 3 2 2 0
|
|
database 2 3 2 3 6
|
|
database 4 4 1 4 6
|
|
mysql 1 4 3 1 0
|
|
mysql 1 4 3 3 0
|
|
mysql 1 4 3 4 0
|
|
SET GLOBAL innodb_ft_aux_table=default;
|
|
SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
|
|
FTS_DOC_ID title
|
|
3 mysql database
|
|
4 mysql database
|
|
1 mysql
|
|
2 database
|
|
connection default;
|
|
disconnect con1;
|
|
DROP TABLE t1;
|
|
# Case 3: Test insert crash recovery
|
|
CREATE TABLE t1 (
|
|
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
FULLTEXT(title)
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO t1(title) VALUES('database');
|
|
SET debug_dbug = '+d,fts_instrument_sync_debug,fts_write_node_crash';
|
|
INSERT INTO t1(title) VALUES('mysql');
|
|
ERROR HY000: Lost connection to server during query
|
|
# restart
|
|
After restart
|
|
SELECT title FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
|
|
title
|
|
database
|
|
SET @old_dbug = @@SESSION.debug_dbug;
|
|
SET debug_dbug = '+d,fts_instrument_sync_debug';
|
|
INSERT INTO t1(title) VALUES('mysql');
|
|
SET debug_dbug = @old_dbug;
|
|
SELECT title FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
|
|
title
|
|
database
|
|
mysql
|
|
DROP TABLE t1;
|
|
# Case 4: Test sync commit & rollback in background
|
|
CREATE TABLE t1(
|
|
id INT AUTO_INCREMENT,
|
|
title VARCHAR(100),
|
|
FULLTEXT(title),
|
|
PRIMARY KEY(id)) ENGINE=InnoDB;
|
|
SET debug_dbug = '+d,fts_instrument_sync';
|
|
INSERT INTO t1(title) VALUES('mysql');
|
|
SET debug_dbug = @old_dbug;
|
|
# restart
|
|
SET @old_global_dbug = @@GLOBAL.debug_dbug;
|
|
SET @old_dbug = @@SESSION.debug_dbug;
|
|
SET GLOBAL debug_dbug='+d,fts_instrument_sync,fts_instrument_sync_interrupted';
|
|
INSERT INTO t1(title) VALUES('database');
|
|
SET GLOBAL debug_dbug = @old_global_dbug;
|
|
SET debug_dbug = '+d,fts_instrument_sync_debug';
|
|
INSERT INTO t1(title) VALUES('good');
|
|
SET debug_dbug = @old_dbug;
|
|
SET GLOBAL innodb_ft_aux_table="test/t1";
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
database 2 2 1 2 0
|
|
good 3 3 1 3 0
|
|
mysql 1 1 1 1 0
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
|
|
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
|
|
SET GLOBAL innodb_ft_aux_table=default;
|
|
SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good');
|
|
id title
|
|
1 mysql
|
|
2 database
|
|
3 good
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-26273 InnoDB fts DDL fails when
|
|
# innodb_force_recovery is set to 2
|
|
#
|
|
# restart: --innodb_force_recovery=2
|
|
CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED KEY,
|
|
f1 CHAR(200)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX(f1);
|
|
DROP TABLE t1;
|
|
# restart
|