Search code examples
mysqlutf-8utf8mb4

Why does a utf8mb4 column reject 4-byte characters in MySQL 5.7.22?


I'm trying to save a string of all 4-byte characters to a MySQL utf8mb4 column.

UPDATE `uga_libsteam`.`group_history` SET `source_name`='𝓔𝓶𝓹𝓻𝓮𝓼𝓼' WHERE `group_id`='103582791430024497' and`history_id`='1655';

The characters are as follows.

However, when I run this query, I receive this error.

Executing:
UPDATE `uga_libsteam`.`group_history` SET `source_name`='𝓔𝓶𝓹𝓻𝓮𝓼𝓼' WHERE `group_id`='103582791430024497' and`history_id`='1655';

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1366: 1366: Incorrect string value: '\xF0\x9D\x93\x94\xF0\x9D...' for column 'source_name' at row 1
SQL Statement:
UPDATE `uga_libsteam`.`group_history` SET `source_name`='𝓔𝓶𝓹𝓻𝓮𝓼𝓼' WHERE `group_id`='103582791430024497' and`history_id`='1655'

Here is my schema. Title is deprecated and eventually I'll be removing it. Previously, source_name and target_name were both utf8/utf8_unicode_ci, but I was able to fix that last night.

CREATE TABLE `group_history` (
  `group_id` bigint(20) unsigned NOT NULL,
  `history_id` bigint(20) unsigned NOT NULL,
  `type_id` tinyint(2) DEFAULT NULL,
  `title` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `display_date` datetime DEFAULT NULL,
  `year_offset` tinyint(2) unsigned DEFAULT NULL,
  `month` tinyint(2) unsigned DEFAULT NULL,
  `day` tinyint(2) unsigned DEFAULT NULL,
  `time` time DEFAULT NULL,
  `source_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_steam_id` bigint(20) DEFAULT NULL,
  `target_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `target_steam_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`group_id`,`history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Why is the database rejecting characters that should be legal to input? Do I have to switch to UTF-32 for this to work?


Solution

  • Thanks to this answer, I had to run this before inserts into my database would work...

    SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
    

    So good news is I now have inserts into my database working, bad news is I'm writing this for a PHP application and PDO isn't working and is saving ??????? to the database.

    Edit: Thanks to this question, I figured out the solution to my PHP question as well. This is what my code looked like before:

        $dsn = "mysql:host=$this->hostname;port=$this->port;dbname=$this->database;charset=utf8mb4";
        try {
            $this->pdo = new \PDO($dsn, $this->username, $this->password, array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION));
            $this->pdo->exec('SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;');
        } catch (\PDOException $e) {
            die($e->getMessage());
        }
    

    This is what I had to change it to:

        $dsn = "mysql:host=$this->hostname;port=$this->port;dbname=$this->database;charset=utf8mb4";
        try {
            $this->pdo = new \PDO($dsn, $this->username, $this->password, array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'));
        } catch (\PDOException $e) {
            die($e->getMessage());
        }