Error while incremental load using Fabric Copy Job from MySQL (MariaDB) via Gateway–SQL syntax issue

Hi Lalit_Pruthi 

It appears from the documentation that you've gotta be using the Maria DB version 10 or 11 for incremental refresh to work successfully. https://learn.microsoft.com/en-au/fabric/data-factory/connector-mariadb-copy-activity 

Hi Lalit_Pruthi 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

Hi Lalit_Pruthi,

This looks like a MariaDB compatibility issue with the SQL that Copy Job auto-generates for incremental loads, not a Fabric bug.

The watermark query wraps identifiers and aliases in double quotes (e.g. MAX("Updated") AS "nextCheckpoint"). MariaDB/MySQL only treat "..." as identifiers when ANSI_QUOTES is included in sql_mode. On MariaDB 5.5.68 with the default sql_mode, those double quotes are parsed as string literals — which is exactly why the full load works but the incremental step throws a syntax error.

As far as I know, the generated incremental SQL in Copy Job isn't user-overridable — checkpoint logic and query shape are managed internally. I'd verify against the latest docs in case that has changed.

Practical workarounds:

• Switch to a Data Pipeline Copy activity — you can set Use query → Query, write your own incremental SELECT with backticks, and manage the watermark with a Lookup activity. Most reliable option when you need control over the SQL.
• Enable ANSI_QUOTES in sql_mode on MariaDB (session or global) so double-quoted identifiers are accepted. Test first — it can break app code that uses "..." as string literals.
• Expose a source view on MariaDB with clean column names and point Copy Job at it. Doesn't fix the quoting itself but sometimes sidesteps issues.
• Try MariaDB Connector/ODBC instead of MySQL ODBC 9.7 — 5.5.68 is old and newer MySQL drivers aren't always backward-friendly.
• Upgrade MariaDB if feasible. 5.5 has been EOL for a long time and this won't be the last compatibility issue.
  
  

Things to check:

• SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
• The exact identifiers in the failing query from the error message
• Gateway version (needs 3000.214.2 or later for Fabric pipelines)
• ODBC driver version on the gateway, and whether swapping drivers changes behavior
• Whether your watermark column type is one Copy Job officially supports

Useful docs:

Incremental copy in Copy job
Configure MySQL in a copy activity
On-premises data gateway in Fabric
MariaDB SQL_MODE (see ANSI_QUOTES)
MariaDB identifier quoting rules

If this got you what you needed, a Kudos and an Accepted Solution mark would be great — it helps others searching for the same thing find the answer quicker.