1

enter image description here

while getting emojies stored in table column of type varchar(2000) with character set utf8mb4 and collation utf8mb4_unicode_ci of MySQL database having version innodb_version 5.6.26-74.0 it get displayed as ? in apple's ios ipad application.

I have change character set and collation of all table where I storing my emojies (smileys) with below mysql sql ..

enter image description here

ALTER TABLE studentresponse
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;


ALTER TABLE observation
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

By searching solution over the internet I got something like this.. You have to set connection encoding to utf8mb4 , otherwise MySQL will convert the stored utf8mb4 data to utf8,

so please help me out in this...

ALTER TABLE studentwadata
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

but on apples ipad application it shows as ? for all smileys that have enter from ipad and stored into mysql table as ? only.

Anoop LL
  • 1,548
  • 2
  • 21
  • 32
Ameya Patkar
  • 91
  • 1
  • 2
  • 10
  • Does this answer your question? [How to preserve UTF8mb4 data with mysqldump?](https://stackoverflow.com/questions/28061609/how-to-preserve-utf8mb4-data-with-mysqldump) – Channa Apr 17 '21 at 07:41

1 Answers1

2

It is not sufficient to change the table.

When establishing the connection between client and server, you need to say that the chatter will be in utf8mb4. You first .jpg show that it is currently set to only utf8.

Are you using spring?

You can execute SET NAMES utf8mb4 after connecting to change those settings.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • HI rick, I m using java play hibernate with netty server. – Ameya Patkar Mar 08 '16 at 08:46
  • SHOW global VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; All are set to utf8mb4 and its collation is utf8mb4_unicode_ci except character_set_filesystem= binary and character_set_system = utf8 – Ameya Patkar Mar 08 '16 at 08:50
  • my connection string is db.default.url="jdbc:mysql://localhost:3306/dbname?zeroDateTimeBehavior=convertToNull" Is there I need to change in connection string or in persistent.xml file – Ameya Patkar Mar 08 '16 at 08:53
  • where I have to execute set names utf8mb4 while executing queries from java side or I have to execute them from mySql workbench – Ameya Patkar Mar 08 '16 at 09:04
  • I have change my.cnf file .. character_set_server = utf8mb4 and collation_server = utf8mb4_unicode_ci but still I am getting ? mark instead of emojis on ipad application. – Ameya Patkar Mar 08 '16 at 09:06
  • I have refer this article to get solution on this emoji issue https://mathiasbynens.be/notes/mysql-utf8mb4 – Ameya Patkar Mar 08 '16 at 09:08
  • I have also tried with this connection string db.default.url="jdbc:mysql://11.000.37.438:3306/dfaultdbname?useUnicode=true" – Ameya Patkar Mar 08 '16 at 12:00
  • 1
    `?useUnicode=yes&characterEncoding=UTF-8` in the getConnection() call. – Rick James Mar 08 '16 at 18:09
  • For forms, you may need `
    `.
     – Rick James Mar 08 '16 at 18:09
  • hi rick, we have iPad application that sends emojis to mysql server through java play netty server application so is there we need to change at Ipad application side to show emojis stored in mysql database. – Ameya Patkar Mar 09 '16 at 07:12
  • Do I need to change mysql java connector to latest version currently I am using 5.1.18 to support fetching emojis from mysql server??? – Ameya Patkar Mar 09 '16 at 10:10
  • I have change all the glober variable to utf8mb4 and thier collation to utf8mb4_unicode _ci. Let me know is that right...??? – Ameya Patkar Mar 09 '16 at 10:11
  • Connector/J -- 5.1.18 was not a GA version. That seems to be 5.1.38. Consider upgrading. (I doubt if it will solve this problem, but maybe.) – Rick James Mar 09 '16 at 20:06
  • There are too many subtle things that could go wrong -- try it. – Rick James Mar 09 '16 at 20:10
  • utf8mb4_unicode_ci and utf8mb4_unicode_520_ci differ (I think) in whether they treat Emoji as same or different. (This may not matter in your code.) – Rick James Mar 09 '16 at 20:11
  • HI Rick,Connector/J -- 5.1.38 has not solved my issue. – Ameya Patkar Mar 10 '16 at 06:51
  • my session level mysql system variable as below >>>> character_set_client =utf8 character_set_connection =utf8 character_set_database =utf8mb4 character_set_filesystem =binary character_set_results =utf8 character_set_server =utf8mb4 character_set_system =utf8 collation_connection =utf8_general_ci collation_database =utf8mb4_unicode_ci collation_server =utf8mb4_unicode_ci – Ameya Patkar Mar 10 '16 at 06:52
  • my global level syatem variable in mysql are >>>>> – Ameya Patkar Mar 10 '16 at 06:53
  • character_set_client = utf8mb4, character_set_connection =utf8mb4, character_set_database =utf8mb4, character_set_filesystem =binary, character_set_results =utf8mb4, character_set_server =utf8mb4, character_set_system =utf8, collation_connection =utf8mb4_unicode_ci, collation_database =utf8mb4_unicode_ci, collation_server =utf8mb4_unicode_ci – Ameya Patkar Mar 10 '16 at 06:54
  • Well, I don't know how the `SESSION` got to be only utf8. Perhaps the connector is explicitly setting utf8, thereby overriding the `GLOBAL` utf8mb4. If you can add `SET NAMES utf8mb4` in your code, that might be a 'work around'. – Rick James Mar 11 '16 at 01:57
  • hi rick, I have created new db (dra2plus_perf) and created tables with character set utf8mb4 and its collation as utf8mb4_unicode _ci and dump my UAT data to this new db (dra2plus_perf) . And pointing to this db though Linux app server (which is our UAT server) emojis are getting fetch perfectly but when pointing same db from my local window app server emojis are displayed as ? mark only instead of emoji. – Ameya Patkar Mar 15 '16 at 08:27
  • I have also change my.cnf to default_charset to utf8mb4 and character_set_database = utf8mb4,collation_database=utf8mb4_unicode_ci, character_set_server = utf8mb4, character_set_connection = utf8mb4,collation_connection = utf8mb4_unicode_ci, – Ameya Patkar Mar 15 '16 at 08:31
  • Still have question mark(s)? This thread is getting too cluttered. Please start a new question and carefully list all the places that you say `utf8imb4` or `UTF-8`. That way, I may be easy to spot what is missing. There should be about 4 places in configuration, code, etc. – Rick James Mar 15 '16 at 15:42