Bug #106928 Can not restore mysqldump that was taken using charset binary
Submitted: 6 Apr 2022 11:39 Modified: 7 Apr 2022 11:14
Reporter: lalit Choudhary Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[6 Apr 2022 11:39] lalit Choudhary
Description:
mysqldump backup of JSON datatype table with --default-character-set=binary restore fails with below error,

ERROR 3144 (22032) at line 36: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

there are few reports of similar issues, some of them are closed not sure why.

https://bugs.mysql.com/bug.php?id=88288
https://bugs.mysql.com/bug.php?id=79066
https://bugs.mysql.com/bug.php?id=86709

How to repeat:
Reproducible test:

CREATE TABLE t1 (`col1` json DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=binary;
INSERT INTO t1 VALUES ('{"pid": 100, "name": "name0"}');

$ ./mysqldump -u msandbox -p  --socket=/tmp/mysql_sandbox5737.sock  --default-character-set=binary  --databases test --tables t1   --set-gtid-purged=OFF > test.sql

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `col1` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=binary;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES ('{\"pid\": 100, \"name\": \"name0\"}');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

$ ./mysql -u msandbox -p  --socket=/tmp/mysql_sandbox5737.sock   --default-character-set=binary testcopy < test.sql 
Enter password: 
ERROR 3144 (22032) at line 36: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

Tested with both 5.7 and 8.0 version

Suggested fix:
mysqldump successful with --default-character-set=binary , but the restore fails. If this is not bug that this should be documented for why charset=binary can not be used with JSON and this restore error issue.
[6 Apr 2022 11:40] lalit Choudhary
update
[6 Apr 2022 12:11] MySQL Verification Team
Hi Mr. Choudhary,

Thank you for your bug report.

We repeated the behaviour that you reported. We do not know at the moment whether this is a code bug or documentation bug. In our opinion dumping that table should be changed to fail with the error and a message that DDL is required for the purpose.

Verified as reported.
[7 Apr 2022 11:14] lalit Choudhary
another example of when using charset binary,
mysql> set names binary;                                                                                                                                                                                           
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo_json (data json) charset=binary;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into foo_json values('{"pid": 100, "name": "name0"}');
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
mysql> 

so the question here is whether setting charset=binary for a table with JSON datatype is allowed or not.
[7 Apr 2022 12:22] MySQL Verification Team
Hi,

It should not be allowed, which is why this is a verified bug.