5

I am trying to store emoji to the database in my server. I am using AWS EC2 instance as server, my server details are listed below:

OS: ubuntu0.14.04.1

MySQL version: 5.6.19-0ubuntu0.14.04.1 - (Ubuntu)

Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503

I created a database test and table emoji in the server with following SQL:

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `test`;

CREATE TABLE IF NOT EXISTS `emoji` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

When I tried to execute the following insert, a warning appears and data are not stored properly:

INSERT INTO  `test`.`emoji` (`id` , `text`) VALUES (NULL ,  '   ');

Inserted row id: 3

Warning: #1366 Incorrect string value: '\xF0\x9F\x91\x86 \xF0...' for column 'text' at row 1

The value stored in the text column is: ???? ???? ???? ????

The same scenario work for my local database and the values are stored properly. Almost all configurations are similar in my local except the OS (Windows).

Code Help
  • 72
  • 1
  • 6
  • 1
    Please do `SHOW CREATE TABLE test.emoji\G` -- I want to see if it got mangled somehow after you created it. – Rick James May 18 '15 at 03:37
  • Wat what prompt did you run the `INSERT` that is failing? (The F09F9186 looks correct.) – Rick James May 18 '15 at 03:38
  • CREATE TABLE `emoji` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 – Code Help May 18 '15 at 05:14
  • 1
    Please provide `SELECT text, HEX(text) FROM emoji` (for the row in question). Something strange is going on. – Rick James May 18 '15 at 05:17
  • Am using drupal for my application. For testing the emoji features, I am using PhpMyAdmin interface, which shows the warning messages also. WHen executing from console, gives the warning as well: `Query OK, 1 row affected, 1 warning (0.00 sec)` – Code Help May 18 '15 at 05:17
  • 1
    `1 warning` -- run the query again, then promptly do `SHOW WARNINGS;` – Rick James May 18 '15 at 05:19
  • `+-----------------------------------+---------------------------------------------------+ | text | HEX(`text`) | +-----------------------------------+---------------------------------------------------+ | ???? ???? ???? ???? | 3F3F3F3F203F3F3F3F203F3F3F3F203F3F3F3F +-----------------------------------+--------------------------------------------------------------------+ ` – Code Help May 18 '15 at 05:20
  • phpmyadmin and/or drupal may be changing things in mysterious ways. Which one gave you `????`? Which one gave you #1366? – Rick James May 18 '15 at 05:20
  • Grrr... The only way I know to get that hex in the table is for the column to be declared `latin1`, which it clearly is not. – Rick James May 18 '15 at 05:21
  • Warning | 1366 | Incorrect string value: '\xF0\x9F\x91\x86 \xF0...' for column 'text' at row 1 | – Code Help May 18 '15 at 05:22
  • What is the charset of the connection? (Both phpmyadmin and drupal). Look in the dsn or connection parameters. Are you connecting as `root`? – Rick James May 18 '15 at 05:22
  • The last warning posted here is from the mysql console – Code Help May 18 '15 at 05:23
  • "mysql console" -- are you referring to the "mysql commandline tool"? – Rick James May 18 '15 at 05:24
  • @RickJames `| Variable_name | Value | | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ ` – Code Help May 18 '15 at 05:26
  • @RickJames Yes, am using mysql command line tool from server SSH connection. The same behavior on phpmyadmin as well. – Code Help May 18 '15 at 05:27
  • it's probably the database connection settings see: http://stackoverflow.com/questions/35125933/mysql-utf8mb4-errors-when-saving-emojis?rq=1 – mike_l Nov 15 '16 at 02:23

3 Answers3

5

I was able to recreate your issue using SqlWorkbench.

You're client most likely has established a connection to the db whose character set does not match the character set of the table:

run this statement before you run the insert statement to align the character set and collation of the connection:

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci

Hope this helps, character sets can be tricky.

Bob Lukens
  • 700
  • 6
  • 10
0

Trying to save emoji's in my existing database table using the following stack Node-Js 12.13.x , Mysql 5.6.

Way around:

  1. Either follow this solution
  2. Or change the column data type to BLOB i.e

ALTER TABLE table_name CHANGE column column BLOB NULL

Hope this trick will work for you!

Haisum Usman
  • 518
  • 5
  • 13
-1

Migration from MSSQL to MySQL using workbench always getting problem.

Workbench already sets utf8mb4, and still getting error.

Then i follow @Haisum Usman's suggestion:

  1. Set column as Blob on migration sql generated.
  2. Migrate data
  3. Change column to LONGTEXT!

Lots of time invested to get this working.

user2823085
  • 1