Unable to see Emojis in Blogposts, Forums and Messages

[Philippe Adrian]

Mahara Version: 20.10.0
Operating System: Ubuntu 18.04.5 LTS
Database: MySQL 5.7

After upgrading from Mahara version 18.04 to 20.10 we ran into a small problem regarding Emojis.
Emoji's in Messages, Blogposts and Forums turned into '?'

Using the steps from https://bugs.launchpad.net/mahara/20.10/+bug/1895259 we went into the Mahara Database and did the following:

For Blogs and Forum posts:
ALTER TABLE interaction_forum_post CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE artefact_blog_blogpost CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE artefact default charset=utf8mb4 collate=utf8mb4_unicode_ci;
ALTER TABLE artefact modify column description text character set utf8mb4 collate utf8mb4_unicode_ci null;

For Messages:
ALTER TABLE maharadb.notification_internal_activity DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE maharadb.notification_internal_activity MODIFY COLUMN message text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;

This solved our problem

Regards,

Philippe and Jochen

[Gold (gold.catalyst)]

Thanks Philippe and Jochen for providing the solution.

This does resolve the issue for the tables you found. However this an issue with all fields that have content from TinyMCE. If you want to solve this issue for all tables you may want to give the following a go.

Upgrading MySQL to the utf8mb4 character set is not an operation that is supported by Mahara as it is a database-specific operation. For this reason we will set this bug to 'Won't fix'. Short of upgrading one table at a time and dealing with foreign key constraints, the easiest way appears to be to
1. export the DB without character set details
2. remove character set and collation details from the table and column definitions
3. create a new database with the correct character set and collation
4. import the database specifying the explicit character set.

Details that would help you achieve that follow. Please test this process thoroughly and consult your database expert:

# Step 1 - dump the database without character set details.
mysqldump -u maharauser -p -h localhost --no-tablespaces --skip-set-charset -r mahara-dump.sql maharadb

# Step 2 - clear out the character set and collation details from the table and column definitions
sed -i 's/ DEFAULT CHARSET=utf8 COLLATE=utf8_bin//g' mahara-dump.sql
sed -i 's/ COLLATE utf8_bin//g' mahara-dump.sql

# Step 3 - drop and recreate the database with the correct character set and collation.
mysql> drop database maharadb;
mysql> create database maharadb character set UTF8mb4 collate utf8mb4_bin;

# Step 4 - import the database specifying the explicit character set.
mysql -u maharauser -p -h localhost --default-character-set=utf8mb4 maharadb < mahara-dump.sql

# Step 5 - test your site.

Testing:

* Stand up an old version of Mahara with a version of the DB that doesn't support emojis.
mysql> drop database maharadb; create database maharadb character set UTF8 collate utf8_bin;
1. Install Mahara
2. Create a journal entry with an emoji in the journal 'Entry'
3. Check that the emoji isn't stored correctly
4. Perform the upgrade steps above
5. Log in and edit the journal entry and add an emoji
6. Check that the new emoji saved and displays
7. Edit the journal entry and check that the new emoji is still visible in the editor.