Fix for utf8mb4 emojis

[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!

[david63]

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

[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?

[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.

[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.

[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.

[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.

[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.

[olsserik]

Thanks!
I will try this out.

It is not always easy ...