Bug #68622 Fulltext parser not used for InnoDB
Submitted: 10 Mar 2013 11:30 Modified: 11 Mar 2013 12:36
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[10 Mar 2013 11:30] Daniël van Eeden
Description:
The configured fulltext parser plugin seems to be ignored for InnoDB tables.

How to repeat:
Use the simple_parser plugin as explained on:
http://dev.mysql.com/doc/refman/5.6/en/writing-full-text-plugins.html
And then try to use it on a InnoDB table.

mysql [localhost] {msandbox} (test) > show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `c` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `c` (`c`) /*!50100 WITH PARSER `simple_parser` */ 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t;
+-------------------------------------------------+
| c                                               |
+-------------------------------------------------+
| latin1_general_cs is a case-sensitive collation |
| I'd like a case of oranges                      |
| this is sensitive information                   |
| another row                                     |
| yet another row                                 |
+-------------------------------------------------+
5 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > optimize table t;
+--------+----------+----------+----------+
| Table  | Op       | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.t | optimize | status   | OK       |
+--------+----------+----------+----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > \! ./my isam_ftdump -c data/test/t 0
        2            0.4054651 a
        2            0.4054651 another
        1            1.3862944 case
        1            1.3862944 case-sensitive
        1            1.3862944 collation
        1            1.3862944 i'd
        1            1.3862944 information
        2            0.4054651 is
        1            1.3862944 latin1_general_cs
        1            1.3862944 like
        1            1.3862944 of
        1            1.3862944 oranges
        2            0.4054651 row
        1            1.3862944 sensitive
        1            1.3862944 this
        1            1.3862944 yet
mysql [localhost] {msandbox} (test) > alter table t engine=innodb;
Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > optimize table t;
+--------+----------+----------+----------+
| Table  | Op       | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.t | optimize | status   | OK       |
+--------+----------+----------+----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_ft_aux_table='test/t';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from information_schema.innodb_ft_index_table;
+-------------------+--------------+-------------+-----------+--------+----------+
| WORD              | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------------------+--------------+-------------+-----------+--------+----------+
| another           |            4 |           5 |         2 |      4 |        0 |
| another           |            4 |           5 |         2 |      5 |        4 |
| case              |            1 |           2 |         2 |      1 |       23 |
| case              |            1 |           2 |         2 |      2 |       11 |
| collation         |            1 |           1 |         1 |      1 |       38 |
| information       |            3 |           3 |         1 |      3 |       18 |
| latin1_general_cs |            1 |           1 |         1 |      1 |        0 |
| like              |            2 |           2 |         1 |      2 |        4 |
| oranges           |            2 |           2 |         1 |      2 |       19 |
| row               |            4 |           5 |         2 |      4 |        8 |
| row               |            4 |           5 |         2 |      5 |       12 |
| sensitive         |            1 |           3 |         2 |      1 |       28 |
| sensitive         |            1 |           3 |         2 |      3 |        8 |
| yet               |            5 |           5 |         1 |      5 |        0 |
+-------------------+--------------+-------------+-----------+--------+----------+
14 rows in set (0.01 sec)

mysql [localhost] {msandbox} (test) > select * from t where match(c) against('of');
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t where match(c) against('i\'d');
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > alter table t engine=myisam;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > select * from t where match(c) against('of');
+----------------------------+
| c                          |
+----------------------------+
| I'd like a case of oranges |
+----------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t where match(c) against('i\'d');
+----------------------------+
| c                          |
+----------------------------+
| I'd like a case of oranges |
+----------------------------+
1 row in set (0.00 sec)
[11 Mar 2013 12:36] Erlend Dahl
Looks like a duplicate of

  Bug#62004 InnoDB fulltext search not calling into fulltext parser plugins