If you’re trying to change this before you have any data in them, you probably need to delete the fulltext index and recreate it again after changing the character set. It looks like the index is called content_ft_idx:
mysql> show index from modx_site_content;
+-------------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| modx_site_content | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| modx_site_content | 1 | alias | 1 | alias | A | NULL | NULL | NULL | YES | BTREE | |
| modx_site_content | 1 | published | 1 | published | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | pub_date | 1 | pub_date | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | unpub_date | 1 | unpub_date | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | parent | 1 | parent | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | isfolder | 1 | isfolder | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | template | 1 | template | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | menuindex | 1 | menuindex | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | searchable | 1 | searchable | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | cacheable | 1 | cacheable | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | hidemenu | 1 | hidemenu | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | class_key | 1 | class_key | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | context_key | 1 | context_key | A | NULL | NULL | NULL | | BTREE | |
| modx_site_content | 1 | content_ft_idx | 1 | pagetitle | NULL | NULL | NULL | NULL | | FULLTEXT | |
| modx_site_content | 1 | content_ft_idx | 2 | longtitle | NULL | NULL | NULL | NULL | | FULLTEXT | |
| modx_site_content | 1 | content_ft_idx | 3 | description | NULL | NULL | NULL | NULL | | FULLTEXT | |
| modx_site_content | 1 | content_ft_idx | 4 | introtext | NULL | NULL | NULL | NULL | YES | FULLTEXT | |
| modx_site_content | 1 | content_ft_idx | 5 | content | NULL | NULL | NULL | NULL | YES | FULLTEXT | |
+-------------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
19 rows in set (0.00 sec)
I would make a full backup of your database first, then try the following:
alter table [your prefix]site_content drop index content_ft_idx;
Then change your character types, and when you’re ready, recreate the index:
create fulltext index content_ft_idx on [your prefix]site_content (pagetitle, longtitle, description, introtext, content);
I’ve never run into your exact issue before, and like Bob said you’re going to have issues if there’s already content in those rows, but this is worth a shot if you don’t.
☆ A M B ☆
☆ A M B ☆