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

Bug #1730317 reported by jianhaiqing
6
This bug affects 1 person
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/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

Revision history for this message
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  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.