Fixing JSON errors after database conversion from MySQL 5.6 to 5.7
If you're migrating a database from MySQL 5.6 to 5.7, you might bump into issues with the JSON encoding of certain fields. This probably happens because only in MySQL 5.7, JSON columns are properly supported. The improved support for native JSON makes it more strict, which can cause errors after bumping the database version or after exporting and subsequently importing it.
For example, you might see an error like this:
Your initial thought might be to bluntly update the DB schema using
bin/console doctrine:schema:update, but it will probably give similar errors:
What's happening in this case, is that the
value column of the
bolt_field_translation table contains invalid JSON. In order to fix this, first we'll need to determine which rows in the table contain malformed JSON. You can do this with the following SQL query:
SELECT * FROM bolt_field_translation WHERE JSON_VALID(value)=0;
With a bit of luck, you’ll only get a single or a few results:
If you don’t care about the contents of these specific columns, you can ‘fix’ them by setting the value to - Ahem - valid JSON:
UPDATE bolt_field_translation SET value='[""]' WHERE JSON_VALID(value)=0;
A better, but more time-consuming fix is to actually ensure the JSON is valid. Grab the contents of the
value column, and paste it into a JSON validator JSONLint - The JSON Validator . It will tell you where the problem is, allowing you to fix it, and update the value in the database accordingly.