MSSQL snapshot names exceed the 128 character object name length and break plans

[codykonior-hsi]

I received this error while doing a plan today on an existing model with schema length of 3 and name length of 103:

Error: (103, b"The identifier that starts with 'xxx__xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx___4034064078_schema_' is too long. Maximum length is 128.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

In MSSQL the maximum length of each part of the identifier is 128 characters and it seems that sqlmesh isn't following that for its internal snapshot naming.

I noticed that the max identifier length setting is missing from mssql.py:

MAX_IDENTIFIER_LENGTH = 128

But adding this just provides a prettier error message that you're sticking _tmp onto the end in _migrate_target_table and this is what is breaking it:

Error: Identifier name 'xxx__xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx___4034064078_schema_tmp' (length 131) exceeds Tsql's max identifier limit of 128 characters

Also, reducing that to 120 still gives the same error, so it appears that setting isn't being used to limit your snapshot naming length either.

I can't find where you actually construct your snapshot names or how to shorten it to within these limits.

[codykonior-hsi]

(For anyone finding this later, we had a chat about how, aside from the message, SQL Mesh can't do anything about overly long snapshot names, because whatever they do is going to cause problems one way or another.

If you have this kind of issue and really need names reaching the limits of your database engine, the solution is to use physical_table_naming_convention: hash_md5 so that it uses a shorter hash of the desired name for snapshot tables instead.

The caveat as stated in the documentation https://sqlmesh.readthedocs.io/en/stable/guides/configuration/#md5-hash is if you are looking at tables from the SQL side it's more confusing, but at the same time, there are ways around it writing wrappers that parse the internal data structures to work stuff out anyway.)

Thanks to Themis for taking the time to talk about and handle this.