Error: Check the Manual That Corresponds to Your MariaDB Server Version for the Right Syntax to Use Near JSON

Issue

Syncs associated with MySQL destinations fail. The following error appears:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON' WHERE xxxxx IS NOT NULL' at line 1

Environment

All MySQL destinations

Resolution

To resolve this issue, do the following:

1. Determine whether your destination database is MariaDB by running the following command:

   select @@version;
   

2. If the database is MariaDB, set up a MariaDB destination in Fivetran. For more information, see our MariaDB destination setup guide.

Cause

We support connecting with five different MySQL implementations. For more information, see our supported MySQL implementations documentation. Syntax that works in other MySQL implementations may not be compatible with MariaDB. For example, MariaDB doesn't support casting source values as the JSON data type.

However, our MariaDB destinations handle this incompatibility by casting source values as STRING instead of JSON. Therefore, this issue occurs when you set up a MySQL destination in Fivetran using a MariaDB database instead of setting up a MariaDB destination.