Simon Krenger

Since Google shut down its Reader service, I am a regular user of the Tiny Tiny RSS reader. Having my own RSS reader installation gives me more power regarding my privacy and the services I am using. Consider me a happy user.

However, there are some issued regarding full UTF8 support when using MySQL. When a feed uses UTF8 emoijs, tt-rss will throw up and report an error. Unfortunately, this only manifests itself with log entries like this:

PHP Fatal error: Query INSERT INTO ttrss_error_log
[..]
/usr/share/webapps/tt-rss/classes/db/mysqli.php on line 33

After searching for a solution, I encountered this entry in the forums for tt-rss (Most commands in this post are straight from this forum post, all credit goes to “tsimmons”).

The problem is, MySQL does not support UTF8 emojis when the table character set is UTF8. Imagine that: The UTF8 character set does not support full UTF8! It turns out that this is a common problem when using MySQL.

So to fix our problem, we need to make sure MySQL uses the UTF8MB4 character set instead of UTF8.

In regards to Tiny Tiny RSS, this can be accomplished using the following commands. First, alter the default character set and collation to UTF8MB4:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Then, convert all the tables where you might need to store UTF emoji characters. This will lead to another problem, also described in the forum post. Since the index key for a field can only contain 767 bytes (or 191 UTF8MB4 characters), alter the index accordingly:

ALTER TABLE `ttrss_entries` 
DROP INDEX `guid`,
DROP INDEX `ttrss_entries_guid_index`;

ALTER TABLE `ttrss_entries` 
ADD UNIQUE INDEX `guid` (`guid`(191) ASC),
ADD INDEX `ttrss_entries_guid_index` (`guid`(191) ASC);

Now we can convert the tables:

ALTER TABLE ttrss_entries CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ttrss_error_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ttrss_archived_feeds CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ttrss_archived_feeds CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ttrss_enclosures CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ttrss_feedbrowser_cache CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Remember to change the database character set to UTF8MB4 in your config.php. Now all your emoji characters should be entered correctly.