MySQL Bugs: #56590: Partitions: fulltext searches fail

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

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

To be more specific: I think there should either be a separate error message or a more detailed one.

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.

The limitation is documented at http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html.

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.

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