Bug #83745 mysqldump does not properly encode json columns
Submitted: 8 Nov 2016 15:22 Modified: 9 Oct 2017 7:46
Reporter: Gunter Grodotzki (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.7.16 OS:Linux
Assigned to: CPU Architecture:Any
Tags: ASCII, cli, control characters, json, mysqldump, Output

[8 Nov 2016 15:22] Gunter Grodotzki
Description:
Dumps created with mysqldump that contain certain control characters are not properly escaped during the process (or even when viewing the db via mysql-cli) resulting a corrupt .sql file that is not importable.

How to repeat:
Step 1) Create Table

mysql -e 'CREATE TABLE `foobar` (`meta` JSON);'

Step 2) Insert Data

echo json_encode('foo' . chr(31) . 'bar') . PHP_EOL;
exit;

mysql -e 'INSERT INTO `foobar` (`meta`) VALUES ('"'"'"foo\\u001fbar"'"'"');'

Step 3) Dump

mysqldump -r dump.sql ebdb foobar

Step 4) Import

mysql ebdb < dump.sql
ERROR 3140 (22032) at line 36: Invalid JSON text: "Invalid escape character in string." at position 3 in value for column 'foobar.meta'.

Suggested fix:
the json should be displayed as escaped string such as: "foo\u001fbar"
[9 Nov 2016 6:21] MySQL Verification Team
Hello Gunter,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[22 Jun 2017 13:29] Gunter Grodotzki
this is still a issue with mysql 5.7.18 - except there is now a special trick to do mysql-dumps that I am missing?
[30 Jun 2017 16:07] Eli Boaz
This is affecting my ability to migrate a database where we are using JSON columns.
[3 Jul 2017 0:53] Jesper wisborg Krogh
Posted by developer:
 
--hex-blob does not work as a workaround
[8 Aug 2017 7:17] Mahesh Kalluri
It's affecting me when am trying to import the data from the dump. 
"ERROR 3140 (22032) at line 37: Invalid JSON text: "Invalid escape character in string." at position 3088 in value for column '#sql-4c59_f7a98.extn_data'."

The same dump worked fine for me when am on 5.7.11 version.
[9 Oct 2017 7:46] Shishir Jaiswal
Fixed as part of Bug#87722