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

[Lalit_Pruthi]

Hi Team,

I’m facing an issue while performing incremental data load (watermark-based) using a Fabric Copy Job and would appreciate any guidance.

Setup Details

• Source: MySQL (MariaDB 5.5.68)
• Destination: Azure SQL Database
• Connectivity: On-premises gateway
• Driver: MySQL ODBC 9.7 Unicode Driver

Error Encountered

Copy failed with error:
Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=ERROR [42000] [MySQL][ODBC 9.7(w) Driver][mysqld-5.5.68-MariaDB] 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 .""Updated"") AS ""nextCheckpoint"" FROM (SELECT a.TransactionId,  a.Cal' at line 1

Observation

• The error indicates a SQL syntax issue, likely related to:
  • Quoting style (""Updated"")
  • Generated query for incremental load (checkpoint logic)

MariaDB 5.5 might not fully support the SQL syntax being generated by Fabric (especially double quotes or aliasing style).

Questions

1. Is there a known compatibility issue between:
   • Fabric Copy Job (incremental load)
   • MySQL/MariaDB (especially older versions like 5.5)?
   • Can we control or override the generated incremental query (especially quoting/aliasing)?
   • Would switching to a different ODBC driver or version help?

Are there recommended configurations for MySQL/MariaDB sources via gateway in Fabric?

Additional Notes

• Full load works fine.
• Issue happens only when enabling incremental load / checkpoint logic.

  Any suggestions or similar experiences would be really helpful.

  Thanks in advance!

[GilbertQ · Accepted solution]

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 

[v-aatheeque · Accepted solution]

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.

[Dev_Dholakia · Accepted solution]

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.

[v-aatheeque · Accepted solution]

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.

[v-aatheeque]

Hi @Lalit_Pruthi 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[Yash8160]

Hello @Lalit_Pruthi 

i think you find solution from below link
link:-https://zappysys.com/api/integration-hub/mariadb-connector/microsoft-fabric

if my suggestion helps you than please mark my reply

[GilbertQ · Accepted solution]

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