MySQL Bugs: #106928: Can not restore mysqldump that was taken using charset binary

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

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.

update

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.

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.

Hi,

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