MySQL error: "Column 'columnname' cannot be part of FULLTEXT index"

Question

Recently I changed a bunch of columns to utf8_general_ci (the default UTF-8 collation) but when attempting to change a particular column, I received the MySQL error:

Column 'node_content' cannot be part of FULLTEXT index

In looking through docs, it appears that MySQL has a problem with FULLTEXT indexes on some multi-byte charsets such as UCS-2, but that it should work on UTF-8.

I'm on the latest stable MySQL 5.0.x release (5.0.77 I believe).

Historian

Answer 1

Oops, so I have found the answer to my problem:

All columns of a FULLTEXT index must have not only the same character set but also the same collation.

My FULLTEXT index had utf8_unicode_ci on one of its columns, and utf8_general_ci on its other columns.

Historian

Answer 2

Just to add to Thomas's good advice: And to sort things out in PHPMyAdmin you have to change the characterset for all columns AT THE SAME TIME.

Just wasted half a day trying again and again to change the columns one at a time and continually getting the error message about the FULLTEXT index.

Lockage

I believe that would be a MySQL limitation rather than a phpMyAdmin one - you'd have to change all of them in the same ALTER command because you can't have mixed collations in a FULLTEXT index, even if it's just for a "little while". – Historian

Answer 3

For DBeaver/database tool users.

When you use interface to modify more than one column, the tool generate commands like this :

ALTER TABLE databaseName.tableName MODIFY COLUMN columnName1 text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE databaseName.tableName MODIFY COLUMN columnName2 varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

This is not working because you must modify the charsets at the same time.

So, you have to change it manually, in one command :

ALTER TABLE databaseName.tableName 
MODIFY COLUMN columnName1 text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
MODIFY COLUMN columnName2 text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

utf8 or utf8mb4 ? See here.

Macpherson