Nerd alert: I’m posting this in case anyone finds themselves with the same issue and needs a fix; if you don’t care about databases feel free to skip this one 🤓
For a recent post I wanted to use an emoji. I also wanted to use emojis in a post ages ago and wasn’t able to… but WordPress supports emoji! So, what was up with that?
As it turns out, my database encoding and charsets were all over the shop. This is understandable; when I first created this site I knew little about databases and the default charset for most MySQL databases (at least those created in phpMyAdmin) was latin and the collation was set to latin1_swedish_ci.
I remember changing these to utf8 a long time ago, but that was before utf8mb4 (a better implementation of utf8 in MySQL; the previous one was limited). I also only changed my database and tables, not each individual column.
In order to get proper emoji support happening I wanted to update everything to utf8mb4. I’m not a whiz with SQL queries, so I ended up just going through each table and changing the encoding and collation. Sequel Pro makes this pretty easy and only slightly tedious.
As I was doing that I noticed that some columns (those with data types of varchar, enum, and also the post_content and post_title columns) kept their encoding despite the change to the table. I guess that’s so the data doesn’t get messed up, which is good, but annoying for my purposes.
An article from Atlassian , MySQL Collation Repair, was great for fixing most of the columns, but for post_content and post_title I kept getting the following error.
Column ‘post_content’ cannot be part of FULLTEXT index
I’m not sure where I found the following code, but basically you have to change any column that are in the FULLTEXT index at the same time, otherwise they won’t budge. The following queries worked for me.
ALTER TABLE `wp_posts`
MODIFY COLUMN `post_content` longtext CHARACTER SET binary,
MODIFY COLUMN `post_title` text CHARACTER SET binary;
ALTER TABLE `wp_posts`
DEFAULT CHARSET=utf8mb4,
MODIFY COLUMN `post_content` longtext CHARACTER SET utf8mb4,
MODIFY COLUMN `post_title` text CHARACTER SET utf8mb4;Side note: props to this post for making me smile. While the solutions proposed didn’t quite fit my situation the alternate title of The things we do to store U+1F4A9 PILE OF POO (💩) correctly was bang on!
Leave a Reply