MySQL Bugs: #71816: InnoDB Fulltext search doesn't find records when savepoints are involved (2)

Bug #71816	InnoDB Fulltext search doesn't find records when savepoints are involved (2)
Submitted:	24 Feb 2014 13:12	Modified:	16 Jun 2014 14:50
Reporter:	Saverio Miroddi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.6.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	fulltext

Description:
When using savepoints, InnoDB FT search doesn't find records.

This bug could be related to #70333 (which was fixed in 5.6.15), but it's slightly different, and still happens.

If savepoints are required, there are no current workarounds, and the InnoDB FT functionality is completely unusable.

How to repeat:
CREATE TABLE test_bug (
  id int(11) NOT NULL,
  keywords varchar(2048) NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY index_shadow_customers_on_keywords (keywords)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;

SAVEPOINT active_record_1;

INSERT INTO test_bug VALUES( 8, '8 Keyword1 Customer 0-0-0 unknown@email.co.uk Unknown address No city NoCountry' );

RELEASE SAVEPOINT active_record_1;

# none found
#
SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST( 'Keyword1' );

# found!
#
# it's found also if the INSERT INTO is not inside a SAVEPOINT
#
COMMIT;
SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST( 'Keyword1' );

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.17 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE test
Database changed
mysql 5.6 > CREATE TABLE test_bug (
    ->   id int(11) NOT NULL,
    ->   keywords varchar(2048) NOT NULL,
    ->   PRIMARY KEY (id),
    ->   FULLTEXT KEY index_shadow_customers_on_keywords (keywords)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (2.57 sec)

mysql 5.6 > BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > SAVEPOINT active_record_1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > INSERT INTO test_bug VALUES( 8, '8 Keyword1 Customer 0-0-0 unknown@email.co.uk Unknown address No city NoCountry' );
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > RELEASE SAVEPOINT active_record_1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST( 'Keyword1' );
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.6 > COMMIT;
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 > SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST( 'Keyword1' );
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql 5.6 > SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST( 'Keyword1' );
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql 5.6 >

Thanks for the report.

I tried this case without savepoint&release as following:

mysql> CREATE TABLE test_bug (
    ->   id int(11) NOT NULL,
    ->   keywords varchar(2048) NOT NULL,
    ->   PRIMARY KEY (id),
    ->   FULLTEXT KEY index_shadow_customers_on_keywords (keywords)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.32 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_bug VALUES( 8, '8 Keyword1 Customer 0-0-0
    '> unknown@email.co.uk Unknown address No city NoCountry' );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST(
'Keyword1' );
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT COUNT(*) FROM test_bug WHERE MATCH( keywords ) AGAINST(
'Keyword1' );
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

The fulltext tokenization happens when the trx commits, so before the commit,
the trx itself couldn't find the newly modified rows by fulltext search, but
it can find these rows by non-fulltext search. After the commit, these rows
can be found using fulltext search by the following trxs.

So in this case, we couldn't find the row in the first query and find the
row after commit, which is correct. And the savepoint and release should
make no differences in this case.

Due to these reasons, the result looks correct.

I see. Is there some reference in the documentation?

I've looked at the pages:

- https://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html
- https://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-fulltext-index
- https://dev.mysql.com/doc/refman/5.6/en/column-indexes.html

and couldn't find anything related; if there isn't any, I think it would be appropriate to add it.

The following information has been added to the FTS restrictions documentation:

"For "InnoDB", all DML operations ("INSERT", "UPDATE", "DELETE")
involving columns with full-text indexes are processed at transaction
commit time. For example, for an "INSERT" operation, an inserted string is
tokenized and decomposed into individual words. The individual words are
then added to full-text index tables when the transaction is committed. As
a result, full-text searches only return committed data."

https://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html
https://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html

The changes should appear soon, with the next published documentation build.

Thank you for the bug report.