0

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?

PatPeter
  • 394
  • 2
  • 17

2 Answers2

2

Previously, source_name and target_name were both utf8/utf8_unicode_ci, but I was able to fix that last night.

I guess you tried to fix the charset by changing the table's default charset. 

ALTER TABLE group_history DEFAULT CHARSET utf8mb4;

This does NOT change the charset of existing columns of the table, it only changes the table's default charset, which will be used only if you subsequently add new columns. Existing columns are still encoded with the old charset, utf8.

To convert the existing columns, you must use:

ALTER TABLE group_history CONVERT TO CHARACTER SET utf8mb4;

This will rewrite all the existing string columns using the new character set, and subsequently allow you to insert 4-byte character values.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This is a very useful command, thanks a lot! I don't know if MySQL workbench has an option for the `CONVERT TO CHARACTER SET`, but I probably just changed the default character set like you said. I was able to figure out how to fix MySQL workbench to get these characters to save, but not PDO in PHP. – PatPeter Jul 05 '18 at 21:13
  • Configuring PHP is a separate question. – Bill Karwin Jul 05 '18 at 21:14
  • I was about to make one when I stumbled across another answer the solved my PHP issue. – PatPeter Jul 06 '18 at 00:14
0

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());
    }
PatPeter
  • 394
  • 2
  • 17