Synchronization errors with Azure Synapse

I configured Metabase with Azure Synapse and it worked fine. At some point, table columns were not showing up, although queries still work. I don’t know what changed. Updating to version 0.37.7 didn’t help.

I can find many synchronization errors Error syncing FKs Incorrect syntax near ‘table’ in the logs, like this one:

	[2dd6a07a-d668-4892-85cb-13cd65bcc201] 2021-02-02T03:10:45+01:00 ERROR metabase.sync.util Error syncing FKs for Table 378 'stripe.events__data__object__balance_transactions__fee_details'
com.microsoft.sqlserver.jdbc.SQLServerException: Parse error at line: 1, column: 33: Incorrect syntax near 'table'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.executeSPFkeys(SQLServerDatabaseMetaData.java:1026)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getCrossReference(SQLServerDatabaseMetaData.java:920)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getImportedKeys(SQLServerDatabaseMetaData.java:992)
	at com.mchange.v2.c3p0.impl.NewProxyDatabaseMetaData.getImportedKeys(NewProxyDatabaseMetaData.java:3296)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fks_STAR_$fn__76897.invoke(describe_table.clj:178)
	at metabase.driver.sql_jdbc.sync.common$reducible_results$reify__76740.reduce(common.clj:34)
	at clojure.core$reduce.invokeStatic(core.clj:6827)
	at clojure.core$into.invokeStatic(core.clj:6895)
	at clojure.core$into.invoke(core.clj:6887)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fks_STAR_.invokeStatic(describe_table.clj:176)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fks_STAR_.doInvoke(describe_table.clj:174)
	at clojure.lang.RestFn.invoke(RestFn.java:467)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fks.invokeStatic(describe_table.clj:193)
	at metabase.driver.sql_jdbc.sync.describe_table$describe_table_fks.doInvoke(describe_table.clj:186)
	at clojure.lang.RestFn.invoke(RestFn.java:445)
	at metabase.driver.sql_jdbc$fn__78162.invokeStatic(sql_jdbc.clj:65)
	at metabase.driver.sql_jdbc$fn__78162.invoke(sql_jdbc.clj:63)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.sync.fetch_metadata$fn__58146$fk_metadata__58151$fn__58152.invoke(fetch_metadata.clj:25)
	at metabase.sync.fetch_metadata$fn__58146$fk_metadata__58151.invoke(fetch_metadata.clj:20)
	at metabase.sync.sync_metadata.fks$fn__59053$sync_fks_for_table_BANG___59062$fn__59065$fn__59066.invoke(fks.clj:68)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
	at metabase.sync.sync_metadata.fks$fn__59053$sync_fks_for_table_BANG___59062$fn__59065.invoke(fks.clj:67)
	at metabase.sync.sync_metadata.fks$fn__59053$sync_fks_for_table_BANG___59062.invoke(fks.clj:62)
	at metabase.sync.sync_metadata.fks$fn__59093$sync_fks_BANG___59098$fn__59099$fn__59100.invoke(fks.clj:81)
	at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
	at clojure.core$reduce.invokeStatic(core.clj:6827)
	at clojure.core$reduce.invoke(core.clj:6810)
	at metabase.sync.sync_metadata.fks$fn__59093$sync_fks_BANG___59098$fn__59099.invoke(fks.clj:80)
	at metabase.sync.sync_metadata.fks$fn__59093$sync_fks_BANG___59098.invoke(fks.clj:76)
	at metabase.sync.util$fn__40718$run_step_with_metadata__40723$fn__40727$fn__40729.invoke(util.clj:345)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
	at metabase.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:119)
	at metabase.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:116)
	at metabase.sync.util$fn__40718$run_step_with_metadata__40723$fn__40727.invoke(util.clj:342)
	at metabase.sync.util$fn__40718$run_step_with_metadata__40723.invoke(util.clj:337)
	at metabase.sync.util$fn__40910$run_sync_operation__40915$fn__40916$fn__40917.invoke(util.clj:430)
	at clojure.core$mapv$fn__8445.invoke(core.clj:6912)
	at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
	at clojure.core$reduce.invokeStatic(core.clj:6827)
	at clojure.core$mapv.invokeStatic(core.clj:6903)
	at clojure.core$mapv.invoke(core.clj:6903)
	at metabase.sync.util$fn__40910$run_sync_operation__40915$fn__40916.invoke(util.clj:430)
	at metabase.sync.util$fn__40910$run_sync_operation__40915.invoke(util.clj:424)
	at metabase.sync.sync_metadata$fn__59638$sync_db_metadata_BANG___59643$fn__59644$fn__59645.invoke(sync_metadata.clj:51)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:145)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:142)
	at clojure.core$partial$fn__5839.invoke(core.clj:2623)
	at metabase.driver$fn__22482.invokeStatic(driver.clj:550)
	at metabase.driver$fn__22482.invoke(driver.clj:550)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.sync.util$sync_in_context$fn__40630.invoke(util.clj:138)
	at metabase.sync.util$with_db_logging_disabled$fn__40627.invoke(util.clj:129)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
	at metabase.sync.util$with_start_and_finish_logging$fn__40616.invoke(util.clj:114)
	at metabase.sync.util$with_sync_events$fn__40611.invoke(util.clj:88)
	at metabase.sync.util$with_duplicate_ops_prevented$fn__40602.invoke(util.clj:67)
	at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:164)
	at metabase.sync.util$do_sync_operation.invoke(util.clj:161)
	at metabase.sync.sync_metadata$fn__59638$sync_db_metadata_BANG___59643$fn__59644.invoke(sync_metadata.clj:50)
	at metabase.sync.sync_metadata$fn__59638$sync_db_metadata_BANG___59643.invoke(sync_metadata.clj:47)
	at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invokeStatic(sync_databases.clj:49)
	at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invoke(sync_databases.clj:40)
	at metabase.task.sync_databases.SyncAndAnalyzeDatabase.execute(sync_databases.clj:55)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)

Hi @fcastells

Please post “Diagnostic Info” from Admin > Troubleshooting.

Do you know when this was last working? Perhaps in earlier 0.37.x release or even 0.36.x?
Locating when it stopped working will help understanding which changes could have caused this problem.

I’m guessing Azure Synapse is setup in Metabase as a SQL Server connection?
Does Synapse have a way to see the detailed error for a failed query? Since it’s failing during one (or all) of the sync queries and gives a fairly unhelpful error message back to Metabase (MySQL has the same problem).

Here’s the Diagnostic Info:

 	{
  "browser-info": {
	"language": "en-GB",
	"platform": "Win32",
	"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.104 Safari/537.36",
	"vendor": "Google Inc."
  },
  "system-info": {
	"file.encoding": "UTF-8",
	"java.runtime.name": "OpenJDK Runtime Environment",
	"java.runtime.version": "11.0.7+10",
	"java.vendor": "AdoptOpenJDK",
	"java.vendor.url": "https://adoptopenjdk.net/",
	"java.version": "11.0.7",
	"java.vm.name": "OpenJDK 64-Bit Server VM",
	"java.vm.version": "11.0.7+10",
	"os.name": "Linux",
	"os.version": "4.14.171-105.231.amzn1.x86_64",
	"user.language": "en",
	"user.timezone": "GMT"
  },
  "metabase-info": {
	"databases": [
	  "sqlserver"
	],
	"hosting-env": "elastic-beanstalk",
	"application-database": "postgres",
	"application-database-details": {
	  "database": {
		"name": "PostgreSQL",
		"version": "10.13"
	  },
	  "jdbc-driver": {
		"name": "PostgreSQL JDBC Driver",
		"version": "42.2.8"
	  }
	},
	"run-mode": "prod",
	"version": {
	  "date": "2021-01-20",
	  "tag": "v0.37.7",
	  "branch": "release-x.37.x",
	  "hash": "2b034aa"
	},
	"settings": {
	  "report-timezone": null
	}
  }
}

We recently updated from 0.35.5 to 0.37.7, but it was already failing in 0.35.5. Unfortunately, I cannot find any indication of when it stopped working and what we did.

I’ve actually managed to get one of the failing queries from Synapse:

   DECLARE @jdbc_temp_fkeys_result table (PKTABLE_QUALIFIER sysname, PKTABLE_OWNER sysname, PKTABLE_NAME sysname, PKCOLUMN_NAME sysname, FKTABLE_QUALIFIER sysname, FKTABLE_OWNER sysname, FKTABLE_NAME sysname, FKCOLUMN_NAME sysname, KEY_SEQ smallint, UPDATE_RULE smallint, DELETE_RULE smallint, FK_NAME sysname, PK_NAME sysname, DEFERRABILITY smallint);
   INSERT INTO @jdbc_temp_fkeys_result EXEC sp_fkeys @P0,@P1,@P2,@P3,@P4,@P5;SELECT  t.PKTABLE_QUALIFIER AS PKTABLE_CAT, t.PKTABLE_OWNER AS PKTABLE_SCHEM, t.PKTABLE_NAME, t.PKCOLUMN_NAME, t.FKTABLE_QUALIFIER AS FKTABLE_CAT, t.FKTABLE_OWNER AS FKTABLE_SCHEM, t.FKTABLE_NAME, t.FKCOLUMN_NAME, t.KEY_SEQ, CASE s.update_referential_action WHEN 1 THEN 0 WHEN 0 THEN 3 WHEN 2 THEN 2 WHEN 3 THEN 4 END as UPDATE_RULE, CASE s.delete_referential_action WHEN 1 THEN 0 WHEN 0 THEN 3 WHEN 2 THEN 2 WHEN 3 THEN 4 END as DELETE_RULE, t.FK_NAME, t.PK_NAME, t.DEFERRABILITY FROM @jdbc_temp_fkeys_result t LEFT JOIN sys.foreign_keys s ON t.FK_NAME = s.name collate database_default;

@fcastells That helped a lot! It’s a bug in the MSSQL JDBC driver:
https://github.com/microsoft/mssql-jdbc/issues/1145
Looks like it was addressed in version 8.1.1, but Metabase is currently using 7.4.1

I have opened an issue:
https://github.com/metabase/metabase/issues/14625 - upvote by clicking :+1: on the first post
If you have any additional details about this, then please comment on the issue.

@flamber that’s great that you could identify this so quickly! I’ll be watching that ticket for the resolution.

Thanks!