Cannot create a JSON value from a string with CHARACTER SET 'binary'

Bug #1730317 reported by jianhaiqing on 2017-11-06
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Data Dumper
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/mydumper/

# cat jianhaiqing.user.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
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","{\"ip\": \"192.168.1.1\", \"time\": \"2017-10-25 17:10:44.000000\", \"result\": \"fail\"}");

mysql> source /data/backup/mydumper/jianhaiqing.user.sql
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

jianhaiqing (jianhaiqing) wrote :

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 :
/*!40101 SET NAMES utf8mb4*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
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","{\"ip\": \"192.168.1.1\", \"time\": \"2017-10-25 17:10:44.000000\", \"result\": \"fail\"}");

but the error only caused by json type. i don't know whether there is better solution or not.

description: updated
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers