Issue with migrate on SQL Server #4598

thebiglabasky · 2020-12-10T18:05:33Z

Bug description

Running migrate after making changes to my schema triggered a weird issue which seems related to drift detection/shadow database.

How to reproduce

Steps to reproduce the behavior:

Introspected my database which you could create as follows:

-- master.dbo.[User] definition

-- Drop table

-- DROP TABLE master.dbo.[User] GO

CREATE TABLE [User] (
	email varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	id int IDENTITY(0,1) NOT NULL,
	CONSTRAINT User_PK2 PRIMARY KEY (id)
) GO;


-- master.dbo.Post definition

-- Drop table

-- DROP TABLE master.dbo.Post GO

CREATE TABLE Post (
	authorId int NULL,
	content varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	published int NULL,
	title varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	id int IDENTITY(0,1) NOT NULL,
	CONSTRAINT Post_pk PRIMARY KEY (id),
	CONSTRAINT Post_FK_2 FOREIGN KEY (authorId) REFERENCES [User](id)
) GO
 CREATE  UNIQUE NONCLUSTERED INDEX Post_id_uindex ON dbo.Post (  id ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ]  GO;

The schema I got from that after introspecting:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

model User {
  email String?
  name  String?
  id    Int     @id @default(autoincrement())
  posts Post[]
}

model Post {
  authorId  Int?
  content   String?
  published Int?
  title     String?
  id        Int     @id @default(autoincrement())
  author    User?   @relation(fields: [authorId], references: [id])
}

Make changes to the schema as follows:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

model User {
  email    String?   @unique
  name     String?
  id       Int       @id @default(autoincrement())
  posts    Post[]
  comments Comment[]
}

model Post {
  authorId  Int?
  content   String?
  published Boolean
  title     String?
  id        Int       @id @default(autoincrement())
  author    User?     @relation(fields: [authorId], references: [id])
  comments  Comment[]
}

model Comment {
  id       Int    @id @default(autoincrement())
  title    String
  body     String
  post     Post   @relation(fields: [postId], references: [id])
  author   User?  @relation(fields: [authorId], references: [id])
  authorId Int?
  postId   Int
}

Run yarn prisma migrate dev --preview-feature
See error

MigrateEngine:rpc starting migration engine with binary: /Users/hervelabas/Dev/studio-qa/prisma-mssql-openssl/migration-engine +0ms
  MigrateEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"diagnoseMigrationHistory","params":{"migrationsDirectoryPath":"/path/to/my/project/prisma/migrations","optInToShadowDatabase":true}} +3ms
  MigrateEngine:stderr Dec 10 18:50:02.790  INFO migration_engine: Starting migration engine RPC server git_hash="626fcb87e24db886bb885c976d49debe7af90acc" +0ms
  MigrateEngine:stderr Dec 10 18:50:02.795  INFO tiberius::client::connection: Performing a TLS handshake +4ms
  MigrateEngine:stderr Dec 10 18:50:02.795  WARN tiberius::client::connection: Trusting the server certificate without validation. +1ms
  MigrateEngine:stderr Dec 10 18:50:02.808  INFO tiberius::client::connection: TLS handshake successful +12ms
  MigrateEngine:stderr Dec 10 18:50:02.813  INFO tiberius::tds::stream::token: Database change from 'master' to 'master' +5ms
  MigrateEngine:stderr Dec 10 18:50:02.813  INFO tiberius::tds::stream::token: Changed database context to 'master'. +0ms
  MigrateEngine:stderr Dec 10 18:50:02.813  INFO tiberius::tds::stream::token: SQL collation change from None to windows-1252/windows-1252 +0ms
  MigrateEngine:stderr Dec 10 18:50:02.813  INFO tiberius::tds::stream::token: Microsoft SQL Server version 3742302223 +0ms
  MigrateEngine:stderr Dec 10 18:50:02.813  INFO tiberius::tds::stream::token: Packet size change from '4096' to '4096' +0ms
  MigrateEngine:stderr Dec 10 18:50:02.813  INFO quaint::single: Starting a mssql connection. +0ms
  MigrateEngine:stderr Dec 10 18:50:02.820 ERROR DiagnoseMigrationHistory:list_migrations: tiberius::tds::stream::token: Invalid object name 'dbo._prisma_migrations'. code=208 +7ms
  MigrateEngine:stderr Dec 10 18:50:03.289  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::client::connection: Performing a TLS handshake +469ms
  MigrateEngine:stderr Dec 10 18:50:03.289  WARN DiagnoseMigrationHistory:calculate_drift: tiberius::client::connection: Trusting the server certificate without validation. +1ms
  MigrateEngine:stderr Dec 10 18:50:03.301  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::client::connection: TLS handshake successful +11ms
  MigrateEngine:stderr Dec 10 18:50:03.306  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::tds::stream::token: Database change from 'prisma_shadow_dbb5b76c9a-7091-4eb5-8422-9975ac30b62b' to 'master' +5ms
  MigrateEngine:stderr Dec 10 18:50:03.306  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::tds::stream::token: Changed database context to 'prisma_shadow_dbb5b76c9a-7091-4eb5-8422-9975ac30b62b'. +0ms
  MigrateEngine:stderr Dec 10 18:50:03.306  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::tds::stream::token: SQL collation change from None to windows-1252/windows-1252 +0ms
  MigrateEngine:stderr Dec 10 18:50:03.306  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::tds::stream::token: Microsoft SQL Server version 3742302223 +0ms
  MigrateEngine:stderr Dec 10 18:50:03.306  INFO DiagnoseMigrationHistory:calculate_drift: tiberius::tds::stream::token: Packet size change from '4096' to '4096' +1s
  MigrateEngine:stderr Dec 10 18:50:03.306  INFO DiagnoseMigrationHistory:calculate_drift: quaint::single: Starting a mssql connection. +1ms
  MigrateEngine:stderr Dec 10 18:50:04.101  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::client::connection: Performing a TLS handshake +0ms
  MigrateEngine:stderr Dec 10 18:50:04.101  WARN DiagnoseMigrationHistory:validate_migrations: tiberius::client::connection: Trusting the server certificate without validation. +0ms
  MigrateEngine:stderr Dec 10 18:50:04.112  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::client::connection: TLS handshake successful +0ms
  MigrateEngine:stderr Dec 10 18:50:04.116  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::tds::stream::token: Database change from 'prisma_shadow_db5090846c-bb0d-40a0-80bc-560435248178' to 'master' +3ms
  MigrateEngine:stderr Dec 10 18:50:04.116  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::tds::stream::token: Changed database context to 'prisma_shadow_db5090846c-bb0d-40a0-80bc-560435248178'. +0ms
  MigrateEngine:stderr Dec 10 18:50:04.116  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::tds::stream::token: SQL collation change from None to windows-1252/windows-1252 +0ms
  MigrateEngine:stderr Dec 10 18:50:04.116  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::tds::stream::token: Microsoft SQL Server version 3742302223 +0ms
  MigrateEngine:stderr Dec 10 18:50:04.116  INFO DiagnoseMigrationHistory:validate_migrations: tiberius::tds::stream::token: Packet size change from '4096' to '4096' +0ms
  MigrateEngine:stderr Dec 10 18:50:04.116  INFO DiagnoseMigrationHistory:validate_migrations: quaint::single: Starting a mssql connection. +4ms
  migrate:dev {
  migrate:dev   diagnoseResult: {
  migrate:dev     drift: {
  migrate:dev       diagnostic: 'driftDetected',
  migrate:dev       rollback: '/*\n' +
  migrate:dev         '  Warnings:\n' +
  migrate:dev         '\n' +
  migrate:dev         '  - You are about to drop the column `name` on the `User` table. All the data in the column will be lost.\n' +
  migrate:dev         '  - You are about to drop the `Post` table. If the table is not empty, all the data it contains will be lost.\n' +
  migrate:dev         '  - The migration will add a unique constraint covering the columns `[email]` on the table `User`. If there are existing duplicate values, the migration will fail.\n' +
  migrate:dev         '  - Made the column `email` on table `User` required. The migration will fail if there are existing NULL values in that column.\n' +
  migrate:dev         '\n' +
  migrate:dev         '*/\n' +
  migrate:dev         '-- DropForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[Post] DROP CONSTRAINT [Post_FK_2];\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AlterTable\n' +
  migrate:dev         'ALTER TABLE [dbo].[User] DROP CONSTRAINT [UQ__email];\n' +
  migrate:dev         'ALTER TABLE [dbo].[User] ALTER COLUMN [email] varchar(150) NOT NULL;\n' +
  migrate:dev         'ALTER TABLE [dbo].[User] DROP COLUMN [name];\n' +
  migrate:dev         'ALTER TABLE [dbo].[User] ADD CONSTRAINT PK__User__email UNIQUE ([email]);\n' +
  migrate:dev         'ALTER TABLE [dbo].[User] ADD [firstName] varchar(100),\n' +
  migrate:dev         '[lastName] varchar(150),\n' +
  migrate:dev         '[social] nvarchar(max),\n' +
  migrate:dev         '[isAdmin] bit NOT NULL CONSTRAINT [DF__User__isAdmin] DEFAULT 0;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- CreateTable\n' +
  migrate:dev         'CREATE TABLE [dbo].[Course] (\n' +
  migrate:dev         '    [id] INT IDENTITY(1,1),\n' +
  migrate:dev         '    [name] varchar(255) NOT NULL,\n' +
  migrate:dev         '    [courseDetails] nvarchar(max),\n' +
  migrate:dev         '    CONSTRAINT [PK__Course__id] PRIMARY KEY ([id])\n' +
  migrate:dev         ');\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- CreateTable\n' +
  migrate:dev         'CREATE TABLE [dbo].[CourseEnrollment] (\n' +
  migrate:dev         '    [userId] int NOT NULL,\n' +
  migrate:dev         '    [courseId] int NOT NULL,\n' +
  migrate:dev         '    [role] varchar(20) NOT NULL,\n' +
  migrate:dev         '    [createdAt] datetime NOT NULL CONSTRAINT [DF__CourseEnrollment__createdAt] DEFAULT CURRENT_TIMESTAMP,\n' +
  migrate:dev         '    CONSTRAINT [PK__CourseEnrollment__userId_courseId] PRIMARY KEY ([userId],[courseId])\n' +
  migrate:dev         ');\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- CreateTable\n' +
  migrate:dev         'CREATE TABLE [dbo].[Test] (\n' +
  migrate:dev         '    [id] INT IDENTITY(1,1),\n' +
  migrate:dev         '    [updatedAt] datetime NOT NULL,\n' +
  migrate:dev         '    [courseId] int NOT NULL,\n' +
  migrate:dev         '    [name] varchar(255) NOT NULL,\n' +
  migrate:dev         '    [date] datetime NOT NULL,\n' +
  migrate:dev         '    CONSTRAINT [PK__Test__id] PRIMARY KEY ([id])\n' +
  migrate:dev         ');\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- CreateTable\n' +
  migrate:dev         'CREATE TABLE [dbo].[TestResult] (\n' +
  migrate:dev         '    [id] INT IDENTITY(1,1),\n' +
  migrate:dev         '    [createdAt] datetime NOT NULL CONSTRAINT [DF__TestResult__createdAt] DEFAULT CURRENT_TIMESTAMP,\n' +
  migrate:dev         '    [result] int NOT NULL,\n' +
  migrate:dev         '    [studentId] int NOT NULL,\n' +
  migrate:dev         '    [graderId] int NOT NULL,\n' +
  migrate:dev         '    [testId] int NOT NULL,\n' +
  migrate:dev         '    CONSTRAINT [PK__TestResult__id] PRIMARY KEY ([id])\n' +
  migrate:dev         ');\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- CreateTable\n' +
  migrate:dev         'CREATE TABLE [dbo].[Token] (\n' +
  migrate:dev         '    [id] INT IDENTITY(1,1),\n' +
  migrate:dev         '    [createdAt] datetime NOT NULL CONSTRAINT [DF__Token__createdAt] DEFAULT CURRENT_TIMESTAMP,\n' +
  migrate:dev         '    [updatedAt] datetime NOT NULL,\n' +
  migrate:dev         '    [emailToken] varchar(255),\n' +
  migrate:dev         '    [valid] bit NOT NULL CONSTRAINT [DF__Token__valid] DEFAULT 1,\n' +
  migrate:dev         '    [expiration] datetime NOT NULL,\n' +
  migrate:dev         '    [userId] int NOT NULL,\n' +
  migrate:dev         '    [type] varchar(10) NOT NULL,\n' +
  migrate:dev         '    CONSTRAINT [PK__Token__id] PRIMARY KEY ([id]),\n' +
  migrate:dev         '    CONSTRAINT [UQ__emailToken] UNIQUE ([emailToken])\n' +
  migrate:dev         ');\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- DropTable\n' +
  migrate:dev         'DROP TABLE [dbo].[Post];\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- CreateIndex\n' +
  migrate:dev         'CREATE UNIQUE INDEX [UQ__email] ON [dbo].[User]([email]);\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[CourseEnrollment] ADD CONSTRAINT [FK__CourseEnr__courseId] FOREIGN KEY ([courseId]) REFERENCES [dbo].[Course]([id])  ON UPDATE CASCADE;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[CourseEnrollment] ADD CONSTRAINT [FK__CourseEnr__userId] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id])  ON UPDATE CASCADE;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[Test] ADD CONSTRAINT [FK__Test__courseId] FOREIGN KEY ([courseId]) REFERENCES [dbo].[Course]([id])  ON UPDATE CASCADE;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[TestResult] ADD CONSTRAINT [FK__TestResult_grader] FOREIGN KEY ([graderId]) REFERENCES [dbo].[User]([id])  ON UPDATE CASCADE;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[TestResult] ADD CONSTRAINT [FK__TestResult_student] FOREIGN KEY ([studentId]) REFERENCES [dbo].[User]([id])  ON UPDATE CASCADE;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[TestResult] ADD CONSTRAINT [FK__TestResult_test] FOREIGN KEY ([testId]) REFERENCES [dbo].[Test]([id])  ON UPDATE CASCADE;\n' +
  migrate:dev         '\n' +
  migrate:dev         '-- AddForeignKey\n' +
  migrate:dev         'ALTER TABLE [dbo].[Token] ADD CONSTRAINT [FK__Token__userId] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id])  ON UPDATE CASCADE;\n'
  migrate:dev     },
  migrate:dev     history: null,
  migrate:dev     failedMigrationNames: [],
  migrate:dev     editedMigrationNames: [],
  migrate:dev     errorInUnappliedMigration: null,
  migrate:dev     hasMigrationsTable: false
  migrate:dev   }
  migrate:dev } +0ms
  MigrateEngine:rpc SENDING RPC CALL {"id":2,"jsonrpc":"2.0","method":"evaluateDataLoss","params":{"migrationsDirectoryPath":"/path/to/my/project/prisma/migrations","prismaSchema":"generator client {\n  provider        = \"prisma-client-js\"\n  previewFeatures = [\"microsoftSqlServer\"]\n}\n\ndatasource db {\n  provider = \"sqlserver\"\n  url      = env(\"DATABASE_URL\")\n}\n\nmodel User {\n  email   String?   @unique\n  name    String?\n  id      Int       @id @default(autoincrement())\n  posts   Post[]\n  Comment Comment[]\n}\n\nmodel Post {\n  authorId  Int?\n  content   String?\n  published Boolean\n  title     String?\n  id        Int       @id @default(autoincrement())\n  author    User?     @relation(fields: [authorId], references: [id])\n  Comment   Comment[]\n}\n\nmodel Comment {\n  id       Int    @id @default(autoincrement())\n  title    String\n  body     String\n  post     Post   @relation(fields: [postId], references: [id])\n  author   User?  @relation(fields: [authorId], references: [id])\n  authorId Int?\n  postId   Int\n}\n"}} +2s
  MigrateEngine:stderr Dec 10 18:50:05.113  INFO EvaluateDataLoss:infer_next_migration: tiberius::client::connection: Performing a TLS handshake +502ms
  MigrateEngine:stderr Dec 10 18:50:05.113  WARN EvaluateDataLoss:infer_next_migration: tiberius::client::connection: Trusting the server certificate without validation. +0ms
  MigrateEngine:stderr Dec 10 18:50:05.124  INFO EvaluateDataLoss:infer_next_migration: tiberius::client::connection: TLS handshake successful +10ms
  MigrateEngine:stderr Dec 10 18:50:05.128  INFO EvaluateDataLoss:infer_next_migration: tiberius::tds::stream::token: Database change from 'prisma_shadow_db9b24c130-0ce1-43c4-9093-02b4c75c8978' to 'master' +4ms
  MigrateEngine:stderr Dec 10 18:50:05.128  INFO EvaluateDataLoss:infer_next_migration: tiberius::tds::stream::token: Changed database context to 'prisma_shadow_db9b24c130-0ce1-43c4-9093-02b4c75c8978'. +0ms
  MigrateEngine:stderr Dec 10 18:50:05.128  INFO EvaluateDataLoss:infer_next_migration: tiberius::tds::stream::token: SQL collation change from None to windows-1252/windows-1252 +0ms
  MigrateEngine:stderr Dec 10 18:50:05.128  INFO EvaluateDataLoss:infer_next_migration: tiberius::tds::stream::token: Microsoft SQL Server version 3742302223 +0ms
  MigrateEngine:stderr Dec 10 18:50:05.128  INFO EvaluateDataLoss:infer_next_migration: tiberius::tds::stream::token: Packet size change from '4096' to '4096' +5ms
  MigrateEngine:stderr Dec 10 18:50:05.128  INFO EvaluateDataLoss:infer_next_migration: quaint::single: Starting a mssql connection. +0ms
  MigrateEngine:stderr Dec 10 18:50:05.374 ERROR EvaluateDataLoss: tiberius::tds::stream::token: Invalid column name 'firstName'. code=207 +241ms
  MigrateEngine:rpc {
  MigrateEngine:rpc   jsonrpc: '2.0',
  MigrateEngine:rpc   error: {
  MigrateEngine:rpc     code: 4466,
  MigrateEngine:rpc     message: 'An error happened. Check the data field for details.',
  MigrateEngine:rpc     data: {
  MigrateEngine:rpc       is_panic: false,
  MigrateEngine:rpc       message: 'Database error: Error accessing result set, column not found: firstName\n' +
  MigrateEngine:rpc         '   0: migration_core::api::EvaluateDataLoss\n' +
  MigrateEngine:rpc         '             at migration-engine/core/src/api.rs:154',
  MigrateEngine:rpc       backtrace: null
  MigrateEngine:rpc     }
  MigrateEngine:rpc   },
  MigrateEngine:rpc   id: 2
  MigrateEngine:rpc } +758ms
Error: Error: Database error: Error accessing result set, column not found: firstName
   0: migration_core::api::EvaluateDataLoss
             at migration-engine/core/src/api.rs:154

    at Object.<anonymous> (/path/to/my/project/node_modules/@prisma/cli/build/index.js:53983:26)
    at MigrateEngine.handleResponse (/path/to/my/project/node_modules/@prisma/cli/build/index.js:53856:38)
    at LineStream.<anonymous> (/path/to/my/project/node_modules/@prisma/cli/build/index.js:53941:18)
    at LineStream.emit (events.js:315:20)
    at LineStream.EventEmitter.emit (domain.js:486:12)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
    at LineStream.Readable.push (_stream_readable.js:223:10)
    at LineStream.Transform.push (_stream_transform.js:166:32)
    at LineStream._pushBuffer (/path/to/my/project/node_modules/@prisma/cli/build/index.js:53674:19)
error Command failed with exit code 1.

Expected behavior

I would have expected migrate to detect a drift and offer to reset the dev database or to successfully migrate.

Prisma information

{
  "devDependencies": {
    "@prisma/cli": "^2.14.0-dev.13"
  },
  "dependencies": {
    "@prisma/client": "^2.14.0-dev.13"
  }
}

Using custom binaries built to enable TLS to work on macOS.

Environment & setup

OS: macOS Big Sur (11.0.1)
Database: Docker SQL Server 2019
Node.js version: v14.15.1
Prisma version:

Environment variables loaded from prisma/.env
@prisma/cli          : 2.14.0-dev.13
@prisma/client       : 2.14.0-dev.13
Current platform     : darwin
Query Engine         : query-engine 626fcb87e24db886bb885c976d49debe7af90acc (at ../prisma-mssql-openssl/query-engine, resolved by PRISMA_QUERY_ENGINE_BINARY)
Migration Engine     : migration-engine-cli 626fcb87e24db886bb885c976d49debe7af90acc (at ../prisma-mssql-openssl/migration-engine, resolved by PRISMA_MIGRATION_ENGINE_BINARY)
Introspection Engine : introspection-core 626fcb87e24db886bb885c976d49debe7af90acc (at ../prisma-mssql-openssl/introspection-engine, resolved by PRISMA_INTROSPECTION_ENGINE_BINARY)
Format Binary        : prisma-fmt 626fcb87e24db886bb885c976d49debe7af90acc (at ../prisma-mssql-openssl/prisma-fmt, resolved by PRISMA_FMT_BINARY)
Studio               : 0.331.0
Preview Features     : microsoftSqlServer

The text was updated successfully, but these errors were encountered:

pantharshit00 · 2020-12-17T13:47:42Z

I tried reproducing this and I got a different error. I did ask me to reset the database, I answered with yes to the prompt and it threw the following:

╭─     ~/code/reproductions/issue-4598                      ✔  took 50s   ─╮
╰─ yarn prisma migrate dev --preview-feature                                     ─╯
yarn run v1.22.10
$ /home/harshit/code/reproductions/issue-4598/node_modules/.bin/prisma migrate dev --preview-feature
Environment variables loaded from prisma/.env
Prisma schema loaded from prisma/schema.prisma
Datasource "db"


✔ Name of migration … init

The following migration was created from new schema changes:

migrations/
  └─ 20201217134547_init/
    └─ migration.sql

Drift detected: Your database schema is not in sync with your migration history.

✔ We need to reset the database. All data will be lost.
Do you want to continue? … yes

Error: Database error: Error querying the database: 'Introducing FOREIGN KEY constraint 'FK__Comment__authorId' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.' on server 3c1bde994488 executing  on line 37 (code: 1785, state: 0, class: 16)
   0: migration_core::api::ApplyMigrations
             at migration-engine/core/src/api.rs:102

error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

Looks like we generate some invalid sql here for sql server

pantharshit00 · 2020-12-17T13:48:12Z

Here is the initial migration file:

-- CreateTable
CREATE TABLE [dbo].[User] (
    [email] NVARCHAR(1000),
    [name] NVARCHAR(1000),
    [id] INT IDENTITY(1,1),
    CONSTRAINT [PK__User__id] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_unique] UNIQUE ([email])
);

-- CreateTable
CREATE TABLE [dbo].[Post] (
    [authorId] INT,
    [content] NVARCHAR(1000),
    [published] BIT NOT NULL,
    [title] NVARCHAR(1000),
    [id] INT IDENTITY(1,1),
    CONSTRAINT [PK__Post__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Comment] (
    [id] INT IDENTITY(1,1),
    [title] NVARCHAR(1000) NOT NULL,
    [body] NVARCHAR(1000) NOT NULL,
    [authorId] INT,
    [postId] INT NOT NULL,
    CONSTRAINT [PK__Comment__id] PRIMARY KEY ([id])
);

-- AddForeignKey
ALTER TABLE [dbo].[Post] ADD CONSTRAINT [FK__Post__authorId] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__postId] FOREIGN KEY ([postId]) REFERENCES [dbo].[Post]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__authorId] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE SET NULL ON UPDATE CASCADE;

pimeys · 2021-01-04T14:30:27Z

Ok, I think @thebiglabasky has something weird in their setup, looking into that firstName that's not in the schema anywhere.

For @pantharshit00, and for this migration in general is it creates a cascading loop that is not allowed in SQL Server, and should be resolved in a following issue where user can describe the cascading rules per relation.

pimeys · 2021-01-04T14:31:57Z

Closing this in favor of #4580

thebiglabasky added kind/bug A reported bug. team/schema Issue for team Schema. topic: migrate topic: sql server Microsoft SQL Server labels Dec 10, 2020

tomhoule added the process/candidate label Dec 11, 2020

pantharshit00 added the bug/2-confirmed Bug has been reproduced and confirmed. label Dec 17, 2020

pimeys self-assigned this Jan 4, 2021

pimeys closed this as completed Jan 4, 2021

janpio removed the process/candidate label Jan 21, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Issue with migrate on SQL Server #4598

Issue with migrate on SQL Server #4598

thebiglabasky commented Dec 10, 2020

pantharshit00 commented Dec 17, 2020

pantharshit00 commented Dec 17, 2020

pimeys commented Jan 4, 2021

pimeys commented Jan 4, 2021

Issue with migrate on SQL Server #4598

Issue with migrate on SQL Server #4598

Comments

thebiglabasky commented Dec 10, 2020

Bug description

How to reproduce

Expected behavior

Prisma information

Environment & setup

pantharshit00 commented Dec 17, 2020

pantharshit00 commented Dec 17, 2020

pimeys commented Jan 4, 2021

pimeys commented Jan 4, 2021