Emoji.

[Theo101]

Can you add emoji to your phpBB forums at this time ?

[Oyabun1]

No, the character set and collation used by the database does not support it at this time.

[MrKel]

The database supports it now. How might one enable emoji?

[MrKel]

The only way I could manage it was to modify phpbb to disable the UNSUPPORTED_CHARACTER error entirely, then change the code for the database driver files to switch their character sets from utf8 to utf8mb4.

The only way it works though is update the individual column collation in mysql to support emoji. This was done in phpmyadmin to any field that might contain an emoji; post text, post subjects, etc. Collation was changed to utf8mb4-bin, and the field sizes were quadrupled. (Since utf8mb4 characters are 4 times the byte size)

Now emoji can be freely posted to our board, and will be read properly by devices that support emoji. PC users will need to enable plugins for their browsers to properly see them, but our user base is primarily mobile.

I'm surprised that phpBB requires such heavy modifications to make emoji work. It's 2015 people.

Sorry for the lack of specific instructions. Much of this was done by the seat of my pants. Hopefully it at least helps point folks in the right direction.

{edit}

[AmigoJack]

disable the UNSUPPORTED_CHARACTER error

This doesn't exist in 3.0.x - you're definitly referring to 3.1.x, so your reply in this topic is wrong - consider opening one in the appropriate forum.

change the code for the database driver files to switch their character sets from utf8 to utf8mb4

Sounds like you're referring to MySQL as DBMS - keep in mind other DBMSes support UTF-8 more consistently and do not need such changes.

field sizes were quadrupled. (Since utf8mb4 characters are 4 times the byte size)

No.

phpBB requires such heavy modifications to make emoji work

It's the underlying DBMS. Making sure it supports UTF-8 in its latest version (Emojis uses Unicode 6, hence 32 bit) (and configure them accordingly and set up the table and column definitions accordingly) is the major "solution". The only change in phpBB (at least in 3.0.x) is to search files for SET NAMES 'utf8' and change it to SET NAMES 'utf8mb4'. Tested successfully.

As a summary/tutorial I suggest reading How to support full Unicode in MySQL databases · Mathias Bynens (Alternative title: The things we do to store U+01F4A9 PILE OF POO correctly)

[AmigoJack]

In case of MySQL 5.1 onwards these are the table changes you have to perform:

Code: Select all

ALTER TABLE phpbb_acl_groups           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE phpbb_acl_options          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE phpbb_acl_roles            CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE phpbb_acl_roles_data       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE phpbb_acl_users            CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE phpbb_attachments          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_banlist              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_bbcodes              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_bookmarks            CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_bots                 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_captcha_answers      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_captcha_questions    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_config DROP PRIMARY KEY;
ALTER TABLE phpbb_config ADD PRIMARY KEY( config_name( 191 ) );
ALTER TABLE phpbb_config               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_confirm              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_disallow             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_drafts               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_extension_groups     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_extensions           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_forums               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_forums_access        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_forums_track         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_forums_watch         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_groups DROP KEY group_legend_name;
ALTER TABLE phpbb_groups ADD KEY group_legend_name( group_legend, group_name( 191 ) );
ALTER TABLE phpbb_groups               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_icons                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_lang                 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_log                  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_login_attempts DROP KEY att_for;
ALTER TABLE phpbb_login_attempts ADD KEY att_for( attempt_forwarded_for( 191 ), attempt_time );
ALTER TABLE phpbb_login_attempts       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_moderator_cache      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_mods                 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_modules              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_online               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_poll_options         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_poll_votes           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_posts                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_privmsgs             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_privmsgs_folder      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_privmsgs_rules       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_privmsgs_to          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_profile_fields       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_profile_fields_data  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_profile_fields_lang  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_profile_lang         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_qa_confirm           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_ranks                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_reports              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_reports_reasons      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_search_results       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_search_wordlist DROP KEY wrd_txt;
ALTER TABLE phpbb_search_wordlist ADD UNIQUE KEY wrd_txt( word_text( 191 ) );
ALTER TABLE phpbb_search_wordlist      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_search_wordmatch     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_sessions             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_sessions_keys        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_sitelist             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_smilies              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_styles DROP KEY style_name;
ALTER TABLE phpbb_styles ADD KEY style_name( style_name( 191 ) );
ALTER TABLE phpbb_styles               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_styles_imageset DROP KEY imgset_nm;
ALTER TABLE phpbb_styles_imageset ADD UNIQUE KEY imgset_nm( imageset_name( 191 ) );
ALTER TABLE phpbb_styles_imageset      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_styles_imageset_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_styles_template DROP KEY tmplte_nm;
ALTER TABLE phpbb_styles_template ADD UNIQUE KEY tmplte_nm( template_name( 191 ) );
ALTER TABLE phpbb_styles_template      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_styles_template_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_styles_theme DROP KEY theme_name;
ALTER TABLE phpbb_styles_theme ADD UNIQUE KEY theme_name( theme_name( 191 ) );
ALTER TABLE phpbb_styles_theme         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_topics               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_topics_posted        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_topics_track         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_topics_watch         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_user_group           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_users DROP KEY username_clean;
ALTER TABLE phpbb_users ADD UNIQUE KEY username_clean( username_clean( 191 ) );
ALTER TABLE phpbb_users                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_warnings             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_words                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE phpbb_zebra                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[Lumpy Burgertushie]

my question would have to be; what the heck is emoji and why do we need it?

robert

[AmigoJack]

They're (now) just Unicode symbols introduced with version 6.0 (why not simply asking Wikipedia?) - since this installation is using MySQL and that setup is not prepared to expect UTF-8 characters longer than 3 byte I can easily provoke crashes by pasting one of those symbols into my profile details and trying to submit it:

[Devolv]

The only way I could manage it was to modify phpbb to disable the UNSUPPORTED_CHARACTER error entirely, then change the code for the database driver files to switch their character sets from utf8 to utf8mb4.

Can you elaborate how you 'disabled' the UNSUPPORTED_CHARACTER error? I've done all the driver file changes and character sets to utf8mb4. this would help greatly.