rubenv / sql-migrate

SQL schema migration tool for Go.
MIT License
3.19k stars 275 forks source link

Limit the ID column length of the migration table to fix issue with mssql #68

Closed alaczi closed 2 years ago

alaczi commented 7 years ago

On mssql there is an issue creating the migrations table. Error reported in the golang error:

Could not create constraint or index. See previous errors.

This is due to the creation of the migration table as follow

if object_id('gorp_migrations') is null create table [gorp_migrations] ([id] nvarchar(max) not null primary key, [applied_at] datetime2) ;;

The full error message when manually executing the query:

[S0001][1919] Column 'id' in table 'gorp_migrations' is of a type that is invalid for use as a key column in an index.

The error caused by the limitation of mssql. Read more here: https://msdn.microsoft.com/en-us/library/ms191241.aspx

This PR will limit the length of the id field in 450 characters, which should be sufficient to store the file names of the migrations. Mssql will only use the first 900 bytes for indexing, and nvarchar used 2bytes / character for storage.

alaczi commented 7 years ago

Uhh-ohh.. Seems this a little bit more difficult than I first thought