ERROR 3144 (22032) at line 36: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
General
Escalation
General
Escalation
Description
Environment
None
AFFECTED CS IDs
CS0026134
Smart Checklist
Activity
Show:
George Lorch April 6, 2022 at 2:58 PM
I believe this might be the operating issue:
[17 Nov 2015 8:08] Knut Anders Hatlen
Changing to feature request, since this is an intentional restriction. Binary strings are not accepted as JSON text because one cannot reliably tell how the string is encoded. Instead of guessing the encoding and possibly producing wrong results, we thought it was safer to raise an error so that the user could clear up the ambiguity by stating the encoding of the binary string explicitly (for example with CONVERT, as suggested in the bug description). If this turns out to be a major inconvenience, we could reconsider.Details
Details
Assignee
Unassigned
UnassignedReporter
Lalit ChoudharyUpstream Bug URL
Affects versions
Priority
MediumSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created April 6, 2022 at 11:53 AM
Updated March 6, 2024 at 10:13 AM
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
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_sandbox8026.sock --default-character-set=binary --databases test --tables sampletable --set-gtid-purged=OFF > test.sql test.sql: -- -- Table structure for table `sampletable` -- DROP TABLE IF EXISTS `sampletable`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `sampletable` ( `col1` json DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=binary; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sampletable` -- LOCK TABLES `sampletable` WRITE; /*!40000 ALTER TABLE `sampletable` DISABLE KEYS */; INSERT INTO `sampletable` VALUES ('{\"pid\": 100, \"name\": \"name0\"}'); /*!40000 ALTER TABLE `sampletable` ENABLE KEYS */; UNLOCK TABLES; $ ./mysql -u msandbox -p --socket=/tmp/mysql_sandbox8026.sock --default-character-set=binary testcopy < test.sql Enter password: ERROR 3144 (22032) at line 45: Cannot create a JSON value from a string with CHARACTER SET 'binary'.See the same issue in 5.7.37 and 8.0.26.
With --default-character-set=utf8mb4 it works.
$ ./mysqldump -u msandbox -pmsandbox --socket=/tmp/mysql_sandbox8026.sock --default-character-set=utf8mb4 --databases test --tables sampletable --set-gtid-purged=OFF > test.sql -- -- Table structure for table `sampletable` -- DROP TABLE IF EXISTS `sampletable`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `sampletable` ( `col1` json DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=binary; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sampletable` -- LOCK TABLES `sampletable` WRITE; /*!40000 ALTER TABLE `sampletable` DISABLE KEYS */; INSERT INTO `sampletable` VALUES ('{\"pid\": 100, \"name\": \"name0\"}'); /*!40000 ALTER TABLE `sampletable` ENABLE KEYS */; UNLOCK TABLES; $ ./mysql -u msandbox -p --socket=/tmp/mysql_sandbox8026.sock --default-character-set=utf8mb4 testcopy < test.sql mysql [localhost] {msandbox} (test) > show create table test.sampletable\G *************************** 1. row *************************** Table: sampletable Create Table: CREATE TABLE `sampletable` ( `col1` json DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=binary 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > show create table testcopy.sampletable\G *************************** 1. row *************************** Table: sampletable Create Table: CREATE TABLE `sampletable` ( `col1` json DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=binary 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from test.sampletable; +-------------------------------+ | col1 | +-------------------------------+ | {"pid": 100, "name": "name0"} | +-------------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from testcopy.sampletable; +-------------------------------+ | col1 | +-------------------------------+ | {"pid": 100, "name": "name0"} | +-------------------------------+ 1 row in set (0.00 sec)