prisma migration issue (Wishlist) - with fix

[ionflow]

When running the migration script npx prisma migrate dev I was getting this error:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "singitronic_nextjs" at "localhost:3306"

MySQL database singitronic_nextjs created at localhost:3306

Applying migration `20240320142857_podesavanje_prizme`
Applying migration `20240413064716_added_order_table`
Applying migration `20240414064137_added_category_table_and_added_role_column`
Applying migration `20240415100000_added_category_id_field_in_product_table`
Applying migration `20240415130405_added_relationship_between_product_table_and_category_table`
Applying migration `20240418151340_added_new_customer_order_table`
Applying migration `20240512145715_bojan_update_za_customer_order_product`
Applying migration `20240515154444_added_necessary_fields_for_customer_order_table`
Applying migration `20240602092804_added_wishlist_table`
Applying migration `20240607074201_added_cascade_delete_in_wishlist_table`
Applying migration `20240607075549_added_cascade_delete_for_categories_in_product_table`
Applying migration `20240607083528_added_cascade_delete_for_wishlist_in_product_table`
Applying migration `20240607111047_added_unique_constraint_to_name_column_in_the_category_table`

The following migration(s) have been applied:

migrations/
  └─ 20240320142857_podesavanje_prizme/
    └─ migration.sql
  └─ 20240413064716_added_order_table/
    └─ migration.sql
  └─ 20240414064137_added_category_table_and_added_role_column/
    └─ migration.sql
  └─ 20240415100000_added_category_id_field_in_product_table/
    └─ migration.sql
  └─ 20240415130405_added_relationship_between_product_table_and_category_table/
    └─ migration.sql
  └─ 20240418151340_added_new_customer_order_table/
    └─ migration.sql
  └─ 20240512145715_bojan_update_za_customer_order_product/
    └─ migration.sql
  └─ 20240515154444_added_necessary_fields_for_customer_order_table/
    └─ migration.sql
  └─ 20240602092804_added_wishlist_table/
    └─ migration.sql
  └─ 20240607074201_added_cascade_delete_in_wishlist_table/
    └─ migration.sql
  └─ 20240607075549_added_cascade_delete_for_categories_in_product_table/
    └─ migration.sql
  └─ 20240607083528_added_cascade_delete_for_wishlist_in_product_table/
    └─ migration.sql
  └─ 20240607111047_added_unique_constraint_to_name_column_in_the_category_table/
    └─ migration.sql
✔ Enter a name for the new migration: … singitronic_nextjs
Applying migration `20241101192936_singitronic_nextjs`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20241101192936_singitronic_nextjs

Database error code: 1553

Database error:
Cannot drop index 'Wishlist_userId_fkey': needed in a foreign key constraint

Please check the query number 1 from the migration file.

To fix I had to remove two files:

prisma/migrations/20240607074201_added_cascade_delete_in_wishlist_table/migration.sql
prisma/migrations/20240607083528_added_cascade_delete_for_wishlist_in_product_table/migration.sql

And force the order of operations in the migration by updating prisma/migrations/20240602092804_added_wishlist_table/migration.sql with:

-- First create the table
CREATE TABLE IF NOT EXISTS `Wishlist` (
    `id` VARCHAR(191) NOT NULL,
    `productId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- DropForeignKey
ALTER TABLE `wishlist` DROP FOREIGN KEY `Wishlist_productId_fkey`;

-- Then drop any existing foreign key constraints
ALTER TABLE `Wishlist` DROP FOREIGN KEY IF EXISTS `Wishlist_productId_fkey`;
ALTER TABLE `Wishlist` DROP FOREIGN KEY IF EXISTS `Wishlist_userId_fkey`;

-- Finally add the foreign key constraints with CASCADE behavior
ALTER TABLE `Wishlist` ADD CONSTRAINT `Wishlist_productId_fkey` 
    FOREIGN KEY (`productId`) REFERENCES `Product`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    
ALTER TABLE `Wishlist` ADD CONSTRAINT `Wishlist_userId_fkey` 
    FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

[sandeepdotnetdeveloper]

Remove all migration file from migration folder then execute npx prisma migrate dev #2 #3