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?
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());
}