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:

Screenshot of initial error

Your initial thought might be to bluntly update the DB schema using bin/console doctrine:schema:update, but it will probably give similar errors:

Screenshot of CLI error

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:

Screenshot of Query 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.

Database Json Mysql Doctrine Symfony

Permalink - Written by Bob on January 14, 2021.

« Simple OG-tags for Facebook, Twitter and others - How to configure SEO-friendly URLs without ContentType »