Cannot create a JSON value from a string with CHARACTER SET 'binary'
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Data Dumper |
New
|
Undecided
|
Unassigned |
Bug Description
show create table user;
+------
| Table | Create Table |
+------
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`lastlogininfo` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
select * from user;
+----+-
| id | name | lastlogininfo |
+----+-
| 1 | lucy | {"ip": "192.168.1.1", "time": "2015-01-01 13:00:00", "result": "fail"} |
| 2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
| 3 | jianhaiqing | {"ip": "192.168.1.1", "time": "2017-10-25 17:10:44.000000", "result": "fail"} |
+----+-
# mydumper -v 3 -h `hostname -i ` -u root -p xxx -P 3307 -r 300000 --regex '^(?!(mysql|test))' --skip-tz-utc -o /data/backup/
# cat jianhaiqing.
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_
INSERT INTO `user` VALUES
(1,"lucy","{\"ip\": \"192.168.1.1\", \"time\": \"2015-01-01 13:00:00\", \"result\": \"fail\"}"),
(2,"bobo","{\"ip\": \"192.168.1.0\", \"time\": \"2015-10-07 06:44:00\", \"result\": \"success\"}"),
(3,"jianhaiqing
mysql> source /data/backup/
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'
mydumper -V
mydumper 0.9.3, built against MySQL 5.7.19-17
description: | updated |
i think the exported *.sql which included data should use the names from creating statement;
e.g.
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`lastlogininfo` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
the exported sql could be : KEY_CHECKS= 0*/; ","{\"ip\ ": \"192.168.1.1\", \"time\": \"2017-10-25 17:10:44.000000\", \"result\": \"fail\"}");
/*!40101 SET NAMES utf8mb4*/;
/*!40014 SET FOREIGN_
INSERT INTO `user` VALUES
(1,"lucy","{\"ip\": \"192.168.1.1\", \"time\": \"2015-01-01 13:00:00\", \"result\": \"fail\"}"),
(2,"bobo","{\"ip\": \"192.168.1.0\", \"time\": \"2015-10-07 06:44:00\", \"result\": \"success\"}"),
(3,"jianhaiqing
but the error only caused by json type. i don't know whether there is better solution or not.