Notification email does not support UTF8 emoji

[andrixnet]

When somebody logs a cache, the onwer receives notification email with that log.
If the log text contains UTF8 emojis, they appear in the email as ????? even though the database field for log entry supports them and the log, as it appears on the website shows them correctly.

Example:
https://www.opencaching.ro/viewcache.php?cacheid=675
https://www.opencaching.ro/viewlogs.php?logid=1921

And this is how the email looks like:

Note: database fields have, at least in part, been updated according to #2399

This is select hex(text) from cache_logs where id=1921:
4345524221234543484F3C6272202F3E0A3C6272202F3E0A45524155534F52F09F9880F09F9880F09F98804D4941555049534943F09FA5B3F09FA5B3F09F98B8F09F98BAF09F98BCF09F929BF09F929B

[stefopl]

watches_waiting.watchtext column requires an update

[andrixnet]

Applied ALTER TABLE watches_waiting CHANGE watchtext watchtext MEDIUMTEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;

I'll test and get back.

P.S. we should prepare a complete set of queries to run by each site admin to solve this

[stefopl]

The correct query should be:
ALTER TABLE watches_waiting CHANGE watchtext watchtext MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
I had previously started working on a complete solution that will appear under the Sysadmin panel -> Database updates.
I will resume work on that soon.

[stefopl]

Thank you for the previously shared .sql file.
I just noticed that OCRO is using utf8mb3_uca1400_ai_ci, so now I’m wondering whether I should switch to utf8mb4_uca1400_ai_ci instead of utf8mb4_general_ci.

[andrixnet]

About utf8mb3 vs utf8mb4, probably my mistake or an older thought. I looked into the history and most changes I made on my database were utf8mb3.
I don't get it. Where is utf8mb3_uca1400_ai_ci coming from?

I just ran show create table ocro.watches_waiting and select * from columns where table_schema='ocro' and table_name='watches_waiting'; They both say simply just utf8 and utf8_general_ci

I ran again my previous query with utf8mb3; Rechecked. Same result.
Ran the query with utf8mb4. Now I see:
CREATE TABLE `watches_waiting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL DEFAULT '0', `object_id` int(11) NOT NULL DEFAULT '0', `object_type` int(11) NOT NULL DEFAULT '0', `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `watchtext` mediumtext CHARACTER SET utf8mb4, `watchtype` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `object_id` (`object_id`), KEY `date_added` (`date_added`) ) ENGINE=MyISAM AUTO_INCREMENT=2260 DEFAULT CHARSET=utf8

and

So I should revise the changes I did previously and redo them with utf8mb4.

[andrixnet]

These are the queries I ran previously, from history:

ALTER TABLE `cache_notes` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `caches` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PowerTrail_comments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `cache_desc` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `news` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `cache_logs` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PowerTrail_cacheCandidate` CHANGE `link` `link` TEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '';
ALTER TABLE `PowerTrail_cacheCandidate` CHANGE `link` `link` TEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '';
ALTER TABLE `gk_item` CHANGE `stateid` `stateid` TINYINT(4) NOT NULL DEFAULT '0';
ALTER TABLE `gk_item` CHANGE `typeid` `typeid` INT(11) NOT NULL DEFAULT '0';
ALTER TABLE `gk_item` CHANGE `datemodified` `datemodified` DATETIME NULL DEFAULT NULL;
ALTER TABLE `gk_item` CHANGE `datecreated` `datecreated` DATETIME NULL DEFAULT NULL;
ALTER TABLE `gk_item` CHANGE `userid` `userid` INT(11) NOT NULL DEFAULT '0';
ALTER TABLE `gk_item` CHANGE `description` `description` LONGTEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '';
ALTER TABLE `PowerTrail` CHANGE `image` `image` TEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '';
ALTER TABLE watches_waiting CHANGE watchtext watchtext MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

There may be a few more, but probably from a session that wasn't saved in history.

I am redoing the queries with utf8mb3 to utf8mb4.

for PowerTrail_cacheCandidate, gk_item and PowerTrail I get the warning:
Warning (Code 1101): BLOB/TEXT column 'link' can't have a default value
or column description or image.