Fix for utf8mb4 emojis

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Anti-Spam Guide
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Fix for utf8mb4 emojis

Post by olsserik »

Hi,
The last year I´ve seen an increase in mysql errors like "Incorrect string value 'xF0 ...' in the logs.

Many users would like to use native emojis provided in Android and iOS I guess.

I´ve searched around phpbb for a correct solution but not found what I´m looking for. Can someone link to a KB or topic where there is a correct workaround for this?

If not, I have tested a workaround on my testserver (mysql + 5.5 ...) and it is working, I dont know if this fix has any drawbacks though so therefor, I would like to have feedback for it.

I ran this on the db:

Code: Select all

ALTER TABLE `phpbb_posts` CHANGE `post_text` `post_text` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
And then on line 73 in includes/db/mysqli.php

I changed:

Code: Select all

@mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");
to

Code: Select all

@mysqli_query($this->db_connect_id, "SET NAMES 'utf8mb4'");
Now it is working, I can post messages with Android emojis.

But are there any drawbacks in doing this?

Thanks!
User avatar
david63
Registered User
Posts: 18454
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Contact:

Re: Fix for utf8mb4 emojis

Post by david63 »

olsserik wrote:But are there any drawbacks in doing this?
This will only work if you are running mySql 5.5.3 (or greater) see viewtopic.php?p=13693316#p13693316
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored
olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Re: Fix for utf8mb4 emojis

Post by olsserik »

Thanks for the reply David.

Yes, luckely for me Im running on 5.5.44 on both servers.

My test above only concerns text in topics and posts, I guess it could be applied to e.g column for PM text and so on as well, but the main concern is in the forum.

I would really like to add this fix to my live forum, many of my users wants to use these emojis, but I wonder if this update maybe can create unwanted errors, anyone who knows?
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Fix for utf8mb4 emojis

Post by Oyabun1 »

Just be aware that only changing the posts table will only work for emojis in posts. If they are used elsewhere, such as in PMs, forum descriptions, or topic subjects, you will get the same type of errors.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Re: Fix for utf8mb4 emojis

Post by olsserik »

Thanks Oyabun for the reply.
Im aware of this and will have that in mind.

I wonder if there are any pitfalls otherwise in doing this.
Example: "Yes, you can do this, but you won´t be able to upgrade to new releases beacuse converter scripts will fail."

Therefor I wonder if there are any known issues doing the fix above.
User avatar
AmigoJack
Registered User
Posts: 5782
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Fix for utf8mb4 emojis

Post by AmigoJack »

Read Re: Emoji. and Re: Emoji. (different posts). Pitfalls in case of MySQL are shorter indices (that's why you're enforced to drop some keys and re-create them with a shorter version than the original column length). Upgrading to new releases should be avoided, since the risk is too phpBB's automatic process would misconcept collations and character sets, thus corrupting your existing data.
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Re: Fix for utf8mb4 emojis

Post by olsserik »

Thanks AmigoJack, I missed that topic in my search!

Supplementary, if Im satisfied with altering phpbb_posts, do I need to perform altering of other tables to make it work properly?

I dont think so, but I have not tested it out properly. E.g, I was concerned that 'phpbb_search_wordlist' needed to be altered but when I checked this is seems to work without adjustments with full_text_native.

Furthermore I have not tested to roll back to utf8 from utf8mb4 (if needed in the future), that is, with utf8mb4 emojis data in the column.
User avatar
AmigoJack
Registered User
Posts: 5782
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Fix for utf8mb4 emojis

Post by AmigoJack »

Whenever you join the _posts with another table you have to expect mismatching collations. The point of converting all tables (and columns) is, that MySQL's FULLTEXT search won't complain anymore. A "rollback" should fail as well, since MySQL should then complain about improper data, as its utf8 definition. But you're just minutes away from testing it yourself: create a custom table with utf8mb4 character set, fill in Unicode 6 code points, then change the character set to utf8. I expect MySQL doesn't even allow you to change it.
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Re: Fix for utf8mb4 emojis

Post by olsserik »

Thanks!
I will try this out.

It is not always easy ... :D
Locked

Return to “[3.0.x] Support Forum”