Forum for discussing general topics related to Couch.
6 posts Page 1 of 1
Hello KK,

I have some contents with emoji.
But tables of MySQL with utf8_general_ci can not save emoji.

So as a solution for this problem,

I try to make changes for tables with comands like

Code: Select all
ALTER TABLE data_text DEFAULT CHARACTER SET utf8mb4;
ALTER TABLE fulltext DEFAULT CHARACTER SET utf8mb4;


Are there anything else to do?
If you have experience for emoji, can I share your solutions?
Hi,

I'll look into this matter and get back.
Please grant me some time.

Thanks.
Hello KK,
Thank you for your reply.

It looks db.php is needed some fix.

CHARACTER SET utf8mb4
COLLATE utf8mb4_bin

This db.php is too sensitive for me.

I will wait your survey.
Thank you.
Hi,

As promised, here is a fix for the emoji issue.

Storing emojis in MySQL, as you found out, requires changing the database's and tables' character-set/collation from 'utf8' (Couch's default) to 'utf8mb4' (utf8 uses 3 bytes while the latter uses 4 bytes but is fully backward compatible with utf8).

IMP: Before using this solution do make sure to take a backup of your database.

OK, with that caveat, here is the fix. It will require two steps -

1. Download the attached PHP script (unzip to extract the file named convertdb.php) and place it within your site's 'couch' folder (or whatever you have renamed the folder to). Then login as admin or super-admin and access the script e.g.
https://yoursite.coml/couch/convertdb.php

Hopefully, you'll see a 'Done!' message which means the conversion went without errors.
You may now delete this script as this is only a one-time task.

2. As the second and last step, open couch/db.php file in your editor and find the following piece of code (at line 94 as of this post) -
@mysql_query( "SET NAMES 'utf8'", $this->conn );
@mysql_query( "SET COLLATION_CONNECTION=utf8_general_ci", $this->conn );

Edit to make it as follows -
@mysql_query( "SET NAMES 'utf8mb4'", $this->conn );
@mysql_query( "SET COLLATION_CONNECTION=utf8mb4_unicode_ci", $this->conn );

And that should be it. Test and you should be able to save emojis.

Hope this helps.

Attachments

Hello KK,

Thank you for your code.
It works perfect!

I changed your code just one place,
because my DB name includes "-".

Line13 in convertdb.php
"Original"
Code: Select all
    $sql = "ALTER DATABASE ".$DB->database." CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci";


"After changed"
Code: Select all
    $sql = "ALTER DATABASE `".$DB->database."` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci";
Thanks for letting me know, @gem3.
I'm glad it helped.
6 posts Page 1 of 1