Bug #56590 Partitions: fulltext searches fail
Submitted: 6 Sep 2010 12:05 Modified: 25 Oct 2011 16:48
Reporter: Mark Stier Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:Any OS:Any
Assigned to: CPU Architecture:Any
Tags: fulltext, myisam, partitions
Triage: Triaged: D4 (Minor)

[6 Sep 2010 12:05] Mark Stier
Description:
The incompatibility between MYISAM PARTITIONING and FULLTEXT indexes is not documented and the error message is extremely misleading and leading to a waste of time for users because it leads one to search in the wrong place.

Related to bug #13448.

The documentation should be extended in that regard, too. Please replace the line "Full-text searches are supported for MyISAM tables only." in "11.9.5. Full-Text Restrictions" with the line "Full-text searches are supported for non-partitioned MyISAM tables only."

How to repeat:
mysql> CREATE FULLTEXT INDEX msg ON m (msg);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
[6 Sep 2010 12:06] Mark Stier
To be more specific: I think there should either be a separate error message or a more detailed one.
[6 Sep 2010 12:49] Valeriy Kravchuk
Verified with 5.1.50:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> ) engine=MyISAM
    -> PARTITION BY HASH(id) PARTITIONS 6;
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

Indeed, misleading error message.
[25 Oct 2011 16:02] Jon Stephens
The limitation is documented at http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html.
[25 Oct 2011 16:47] Jon Stephens
Documented as follows in the 5.6.4 changelog:

       Error 1214 (ER_TABLE_CANT_HANDLE_FT
       (http://dev.mysql.com/doc/refman/5.6/en/error_er_table_cant_handle_ft.html#error_er_table_c...)), 
       given when trying to use a FULLTEXT index with a partitioned
       table, displayed the misleading text -The used table type
       doesn't support FULLTEXT indexes- was misleading and has
       been replaced with Error 1752
       (ER_FULLTEXT_NOT_SUPPORTED_WITH_PARTITIONING
       (http://dev.mysql.com/doc/refman/5.6/en/error_er_fulltext_not_supported_with_partitioning.h...)) 
       which shows the more accurate -FULLTEXT index is not supported for 
       partitioned tables-.

Also added a note about the limitation to dev.mysql.com/doc/en/fulltext-restrictions.html (5.1, 5.5., 5.6 versions).

Closed.
[25 Oct 2011 16:48] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html