Unable to run migration on assets_meta table (SQL Server)

[PFEW-ITSD]

Bug description

On an existing project, I've updated both Statamic (from 4.55 to 5.4) and Eloquent Driver (from 2.4 to 4.0) and now I'm trying to run the migrations to get the assets to work, but I'm encountering issues between the handle and container columns.

How to reproduce

1. Update both Statamic and Eloquent Driver on existing project using SQL Server database
2. Run php artisan migrate - this should bring up this error:

   ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'container' cannot be added to non-empty table 'assets_meta' because it does not satisfy these conditions.

3. Change batch number for create_asset_table migration to separate migration and run php artisan migrate:rollback to drop the table
4. Run php artisan migrate again and this error comes up:

   The index 'assets_meta_handle_index' is dependent on column 'handle'. (Connection: sqlsrv, SQL: DECLARE @sql NVAR CHAR(MAX) = '';SELECT @sql += 'ALTER TABLE [dbo].[assets_meta] DROP CONSTRAINT ' + OBJECT_NAME([default_object_id]) + ';' FROM sys.columns WHERE [object_id] = OBJECT_ID('[dbo].[assets_meta]') AND [name] in ('handle') AND [default_object_id] <> 0;EXEC(@sql);alter table "assets_meta" drop column "handle")

Logs

No response

Environment

Environment
Application Name: Statamic
Laravel Version: 10.48.11
PHP Version: 8.1.4
Composer Version: 2.6.5
Environment: local
Debug Mode: ENABLED
URL: events.test
Maintenance Mode: OFF

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: statamic
Database: sqlsrv
Logs: daily
Mail: smtp
Queue: sync
Session: file

Statamic
Addons: 1
Sites: 1
Stache Watcher: Enabled
Static Caching: Disabled
Version: 5.4.0 PRO

Statamic Addons
statamic/eloquent-driver: 4.0.0

Statamic Eloquent Driver
Asset Containers: eloquent
Assets: eloquent
Blueprints: eloquent
Collection Trees: eloquent
Collections: eloquent
Entries: eloquent
Forms: eloquent
Global Sets: eloquent
Global Variables: eloquent
Navigation Trees: eloquent
Navigations: eloquent
Revisions: eloquent
Taxonomies: eloquent
Terms: eloquent

Additional details

No response

[ryanmitchell]

@PFEW-ITSD Yes depending on what version you are updating from the migrations are somewhat messy. We've taken a new approach from v4 on which should prevent this from happening going forward, but that doesn't help you.

It looks like you need to drop the 'assets_meta_handle_index' index in a migration or manually for that migration to run.

[PFEW-ITSD]

It looks like you need to drop the 'assets_meta_handle_index' index in a migration or manually for that migration to run.

That seems to have resolved that, thanks @ryanmitchell! I did have the same issue with both form_submissions_form_id indexes as well, plus there was a duplicate migration for updating the assets_meta table but I just manually inserted that migration into the database to skip it and now everything seems to be uploading as normal