MySQL Bugs: #83745: mysqldump does not properly encode json columns

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

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"

Hello Gunter,

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

Thanks,
Umesh

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?

This is affecting my ability to migrate a database where we are using JSON columns.

Posted by developer:
 
--hex-blob does not work as a workaround

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.

Fixed as part of Bug#87722