Sequelpro: MySQL : Copy / Duplicate a ROW with JSON column copied the data as Binary, which is NOT valid JSON value

[mattbryson]

If you try to duplicate a row with a JSON data typed column in it, or copy the SQL INSERT string and then run it, it will fail with the error..

Cannot create a JSON value from a string with CHARACTER SET 'binary'.

This is because the default format of the JSON column is copied as BINARY but it needs to be copied as UTF8MB4.

The generated SQL from the console is...

INSERT INTO `my_table` (`name`, `config`) VALUES ('test', X'7B22736F6D655F6A736F6E223A20223132333435227D');

Where it should be this for JSON data type

INSERT INTO `my_table` (`name`, `config`) VALUES ('test', '{\"some_json\": \"12345\"}');

[gregblass]

+1, this is preventing me from exporting data to load on a production server because I'm using JSON data types.

Anyone have any idea if there is another way to export MYSQL data on OSX without using Sequel Pro until this is fixed?

[gregblass]

If anyone needs to export a database using JSON data types in the mean time, you can do it using this command:

mysqldump -u root -p [password or blank for no password] database_name > filename.sql

[mattbryson]

If you want a dump of the DB, you can still use sequelpro, just untick the box that says output blob fields as hex. Unless you have other blobs that are not JSON which need to be hex...

[mattbryson]

Also, i modified one of the bundles to allow me to copy selected rows from the DB as SQL inserts, but NOT exporting JSON as hex, so you can then run the generated SQL as an insert....
CopyAsSQL4JSON.spBundle.zip

[dmoagx]

I think this has already been fixed in #2199 and will be included in the next release.

You can try a nightly build from https://sequelpro.com/nightly/