1

I have been trying to get my DB to accept Emoji's by following steps from this tutorial as it appears to be the one that is linked to the most.

https://mathiasbynens.be/notes/mysql-utf8mb4

Even with a test DB I can't get this to work and so far I can't find the answer. Here is some test code that I'm running.

CREATE DATABASE testDB;

ALTER DATABASE testDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE testDB.test_table (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(191) NULL,
    PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

INSERT INTO testDB.test_table (data) VALUES ('foobar');

And the error that I recieve

Error Code: 1366. Incorrect string value: '\xF0\x9D\x8C\x86ba...' for column 'data' at row 1

There must be something else that I'm missing to make this work. So.. what is it.

Another clue to the puzzle is

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Returns

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

Which isn't correct but when I check the Options file in MySQL Workbench is see that under the General/International I have the following settings

Character-set-filesystem = binary
Character-set-server = utf8mb4
Collation-server = utf8mb4_unicode_ci

Contents of my.ini

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Thanks for your time and any help that you can provide.

Paul
  • 328
  • 1
  • 5
  • 17
  • Does anything change if you execute `SET NAMES utf8mb4;` as first statement before CREATE TABLE ... and the rest? – CBroe Dec 16 '17 at 21:53
  • Just looped back to this and yes this did correct the problem. Now how do I correct the issues on an already existing DB without having to rebuild the whole thing? – Paul Dec 18 '17 at 14:24
  • So I just ran this on the existing server and it works. So what has this done? – Paul Dec 18 '17 at 18:36

0 Answers0