Bug #64959 Fulltext collation creation does not work with mysql 5.6 on linux
Submitted: 12 Apr 2012 19:54 Modified: 26 Apr 2012 4:38
Reporter: A V Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:5.6.6 OS:Linux (Ubuntu)
Assigned to: CPU Architecture:Any

[12 Apr 2012 19:54] A V
Description:
Creating a fulltext collation as above does not work in mysql 5.6 (although it worked fine in 5.1) with either InnoDB or MyISAM tables. I get the following error:

mysql> CREATE TABLE t1 (a TEXT CHARACTER SET latin1 COLLATE latin1_ft_ci, FULLTEXT INDEX(a)) ENGINE=InnoDB;
ERROR 1283 (HY000): Column 'a' cannot be part of FULLTEXT index

How to repeat:
See above.
[12 Apr 2012 19:55] A V
The 'as above' refers to the manual page on adding fulltext collations at
  http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html
[17 Apr 2012 18:51] Sveta Smirnova
Thank you for the report.

Verified as described.
[26 Apr 2012 4:38] Praveenkumar Hulakund
In the example mentioned in link 
"http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html". 
Collation ID is set to "62" for "latin1_fulltext_ci".

    <charset name="latin1">
    ...
    <collation name="latin1_fulltext_ci" id="62"/>
    </charset>

Page "http://dev.mysql.com/doc/refman/5.6/en/adding-collation-choosing-id.html"
has information about how to choose collation ID.

Two points to be noted from above page,

1. To add a collation, you must choose an ID value that is not currently used.

2. Warning:
   Before MySQL 5.5, which provides for a range of user-defined collation IDs,
   you must choose an ID in the range from 1 to 254. In this case, if you 
   upgrade MySQL, you may find that the collation ID you choose has been
   assigned to a collation included in the new MySQL distribution. 
   "In this case, you will need to choose a new value for your own collation."

In 5.5+, Collation ID 62 is already used for the charset "utf16le_bin". And 
"utf16le_bin" is ucs2 charset. One of the member of is set to represent the
same. 

So, using ID 62 again for "latin1_filltext_ci", is updating the collation name
of "utf16le_bin". But value of all other members are as it is. So 
"latin1_filltext_ci" is considered as ucs2 charset. Full-text index is not 
supported on ucs2 charset. Because of this error is reported in 5.6. And ID "62"
is not used in 5.1 so it works fine.

So for a collation,"we must choose an ID value that is not currently used".
And above mentioned link has procedure to choose collation ID and also has 
warning of ID being used by other collation included in new MySQL distribution".

Since the problem is seen because of using already used ID (of ucs2 charset), 
this "DOES NOT" seems looks like BUG. 

Document page "http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html" 
should be referred before referring 
"http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html".
Link to "adding-collation.html" exists in "full-text-adding-collation.html".