Cannot insert duplicate key in object 'sqlmesh._snapshots'. The duplicate key value is (, ) #4505
Description
When a model plan fails to execute, on re-run there's an error with the snapshots table.
Unfortunately the temporary table is dropped immediately so it's impossible to see exactly what's going on. Is there a way to preserve the table?
I'm forced to entirely drop everything and rebuild to recover from this error. How can I help debug this?
Particulars: mssql (tsql), Mac or Linux, SQLMesh 0.184.2, pymssql 2.3.1, Python 3.12.10.
Error on first plan
sqlmesh.utils.concurrency.NodeExecutionFailedError: Execution failed for node SnapshotId<"catalog"."schema"."table": 221271077>
Error on re-running the same command, plan
[11:40:02] Starting plan ec756ec4fced47a1984973eba16e870f console.py:3393
2025-05-22 11:40:02,926 - MainThread - sqlmesh.core.engine_adapter.base - DEBUG - Pinging the database to check the connection (base.py:2090)
2025-05-22 11:40:02,953 - MainThread - sqlmesh.core.engine_adapter.base - DEBUG - Executing SQL: SELECT [name], [identifier] FROM [sqlmesh].[_snapshots] WHERE ([name] = '"catalog"."schema"."OTCSWOS"' AND [identifier] = '1927178929') OR ([name] = '"catalog"."schema"."OP"' AND [identifier] = '1952265021') OR ([name] = '"catalog"."schema"."O"' AND [identifier] = '4204331938') OR ([name] = '"catalog"."schema"."P"' AND [identifier] = '3352439455') OR ([name] = '"catalog"."schema"."PCSWOS"' AND [identifier] = '3391683041') OR ([name] = '"catalog"."schema"."PCS"' AND [identifier] = '289656854') OR ([name] = '"catalog"."schema"."O"' AND [identifier] = '3885910077') OR ([name] = '"catalog"."schema"."PC"' AND [identifier] = '2876963711') OR ([name] = '"catalog"."schema"."POS"' AND [identifier] = '2460111087'); (base.py:2172)
2025-05-22 11:40:03,051 - MainThread - sqlmesh.core.engine_adapter.base - DEBUG - Executing SQL: SELECT 1 FROM [information_schema].[tables] WHERE [table_name] = '__temp__snapshots_7fzobfyx' AND [table_schema] = 'sqlmesh'; (base.py:2172)
2025-05-22 11:40:03,102 - MainThread - sqlmesh.core.engine_adapter.base - DEBUG - Executing SQL: IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = '__temp__snapshots_7fzobfyx' AND table_schema = 'sqlmesh') EXEC('CREATE TABLE [sqlmesh].[__temp__snapshots_7fzobfyx] ([name] VARCHAR(450), [identifier] VARCHAR(450), [version] VARCHAR(450), [snapshot] VARCHAR(MAX), [kind_name] VARCHAR(MAX), [updated_ts] BIGINT, [unpaused_ts] BIGINT, [ttl_ms] BIGINT, [unrestorable] BIT)'); (base.py:2172)
2025-05-22 11:40:03,271 - MainThread - sqlmesh.core.engine_adapter.base - DEBUG - Executing SQL: INSERT INTO [sqlmesh].[_snapshots] ([name], [identifier], [version], [snapshot], [kind_name], [updated_ts], [unpaused_ts], [ttl_ms], [unrestorable]) SELECT CAST([name] AS VARCHAR(450)) AS [name], CAST([identifier] AS VARCHAR(450)) AS [identifier], CAST([version] AS VARCHAR(450)) AS [version], CAST([snapshot] AS VARCHAR(MAX)) AS [snapshot], CAST([kind_name] AS VARCHAR(MAX)) AS [kind_name], CAST([updated_ts] AS BIGINT) AS [updated_ts], CAST([unpaused_ts] AS BIGINT) AS [unpaused_ts], CAST([ttl_ms] AS BIGINT) AS [ttl_ms], CAST([unrestorable] AS BIT) AS [unrestorable] FROM [sqlmesh].[__temp__snapshots_7fzobfyx]; (base.py:2172)
2025-05-22 11:40:03,313 - MainThread - sqlmesh.core.engine_adapter.base - DEBUG - Executing SQL: DROP TABLE IF EXISTS [sqlmesh].[__temp__snapshots_7fzobfyx]; (base.py:2172)
[11:40:03] Stopping plan console.py:3393
2025-05-22 11:40:03,446 - MainThread - sqlmesh.core.context - INFO - Plan application failed. (context.py:1561)
Traceback (most recent call last):
File "src/pymssql/_pymssql.pyx", line 447, in pymssql._pymssql.Cursor.execute
File "src/pymssql/_mssql.pyx", line 1125, in pymssql._mssql.MSSQLConnection.execute_query
File "src/pymssql/_mssql.pyx", line 1156, in pymssql._mssql.MSSQLConnection.execute_query
File "src/pymssql/_mssql.pyx", line 1289, in pymssql._mssql.MSSQLConnection.format_and_run_query
File "src/pymssql/_mssql.pyx", line 1855, in pymssql._mssql.check_cancel_and_raise
File "src/pymssql/_mssql.pyx", line 1901, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (2627, b"Violation of PRIMARY KEY constraint 'PK___snapsho__FFF001CE2B97CB40'. Cannot insert duplicate key in object 'sqlmesh._snapshots'. The duplicate key value is (, ).DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\n")