Store AddonSettings in mysql DB with ACE 3.X.X

Hi guys,

As you know, the jugglingdb support has been deprecated from ACE 3.X. Thus, and with the update for GDPR, we had to update the ACE lib as well. So, using an older version of ACE is not an option for us.

To use sequelize native ACE adapter, I changed the store key from config.json to:

{
    "adapter": "sequelize",
    "dialect": "mysql",
    "url": "$DB_URL"
}

However, when I try to install the addon, the /installed endpoint does not respond. After some debugging, I noticed that the sequelize adapter is returning this message

code: 'ER_PARSE_ERROR',
     errno: 1064,
     sqlState: '42000',
     sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'JSON, PRIMARY KEY (`id`)) ENGINE=InnoDB\' at line 1',
     sql: 'CREATE TABLE IF NOT EXISTS `AddonSettings` (`id` INTEGER NOT NULL auto_increment , `clientKey` VARCHAR(255), `key` VARCHAR(255), `val` JSON, PRIMARY KEY (`id`)) ENGINE=InnoDB;' },
  sql: 'CREATE TABLE IF NOT EXISTS `AddonSettings` (`id` INTEGER NOT NULL auto_increment , `clientKey` VARCHAR(255), `key` VARCHAR(255), `val` JSON, PRIMARY KEY (`id`)) ENGINE=InnoDB;' }

What can I do to use the mysql database with this new version of ACE?

Thanks!
Luís

Which version of MySQL are you running? Also - what happens if you execute the statement yourself directly?

I’m running MYSQL 5.X.

If I run the query by myself I get the same error due to the JSON datatype. Apparently, JSON datatype is not supported.

It looks like it was introduces in 5.7.8 https://dev.mysql.com/doc/refman/5.7/en/json.html . You can either patch ACE or upgrade Mysql?