Migrate: Unexpected error occured when running migrate up in latest alpha.

[AlexanderProd]

When running npx prisma2 migrate up --experimental in the latest alpha 2.0.0-alpha.618 there's an unexpected error.

Checking the datasource for potential data loss...
ERROR  Oops, an unexpected error occured!

Unfortunately there aren't more details to the error.
I've tried it a couple times and checked if there's a problem with my db or connection.

OS: macOS
Prisma2 version: 2.0.0-alpha.618 (using alpha because of prisma/prisma2#1419 )
DB: MySQL
Report id: 1354

Schema:

datasource mysql {
  url      = "mysql://******:*****@******/*****"
  provider = "mysql"
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  phone     String?
  role      Role     @default(USER)
  posts     Post[]
}

model Post {
  id         String     @id @default(cuid())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  author     User
  title      String
  categories Category[]
  published  Boolean    @default(false)
}

model Category {
  id    String  @id @default(cuid())
  name  String 
}

enum Role {
  USER
  ADMIN
}

those are the changes that would've been applied:

[timsuchanek]

@AlexanderProd, unfortunately, we can't reproduce this.

You mention that you used npx prisma2 migrate up --experimental - that however would run the "latest" version from npm, not the "alpha" version. Was it just a typo or did you indeed use the latest?

[AlexanderProd]

@timsuchanek Yeah I did indeed run it using npx (thought running with npx would use the version installed in my local node_modules), but I also tried it without npx using the global version (Installed the alpha globaly too). The error occurs in both cases.

[janpio]

(Using npx with the alpha in package.json should indeed use that alpha version afaik)

[AlexanderProd]

The alpha is indeed in my package.json.

"prisma2": "^2.0.0-alpha.618",

If it wasn't the alpha npx prisma2 migrate save --experimental wouldn't have worked either because of prisma/prisma2#1419 .

[janpio]

(You can also confirm with npx prisma2 -v)

[AlexanderProd]

@janpio Thanks, this is the exact version I'm using
[email protected], binary version: 0150d2c09b7ddee6b866a924dea38ba157ad4980

[AhmedElywa]

@AlexanderProd i get error like you not your issue error. when you use version like this ^2.0.0-alpha.618 with ^ it's get latest version and you can see this in yarn.lock file

[AlexanderProd]

I guess the problem originates from my MySQL (Maria DB) version.

I've tried running the generated SQL script from inside the migrations folder and I get an error.

CREATE TABLE `521_prisma-test`.`Category` (
    `id` varchar(191) NOT NULL  ,
    `name` varchar(191) NOT NULL DEFAULT '' ,
    `post` varchar(191)  ,
    PRIMARY KEY (`id`)
) 
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

ALTER TABLE `521_prisma-test`.`Category` ADD FOREIGN KEY (`post`) REFERENCES `521_prisma-test`.`Post`(`id`) ON DELETE SET NULL

This is the error I'm getting when running the SQL myself.

You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for the right syntax to use near
'ALTER TABLE `521_prisma-test`.`Category` ADD FOREIGN KEY (`post`) REFERENCES `52' at line 9

I'm using MySQL 5.5.5 (MariaDB 10.0.38)

I'm not sure though if this is because im using a MySQL (MariaDB) version that isn't supported by Prisma or if prisma made a mistake generating the correct script for this version.

[tomhoule]

@AlexanderProd I think the problem here could be that the database name starts with a number. I'll try to confirm this in tests.

[tomhoule]

I just confirmed it's not because of the digits in the database name.

[AlexanderProd]

I've can also successfully run the generated SQL query by doing it step by step (first creating the new table and then altering it). So the SQL is actually fine.

Btw. The generated .md file for migration is an awesome feature of prisma!

[pantharshit00]

I am unable to reproduce this Mysql 5.7 and prisma2 version [email protected], binary version: 0150d2c09b7ddee6b866a924dea38ba157ad4980 which contains the fix for the other issue that you have mentioned.

I will install MariaDB and try to reproduce this again with that.

[tvvignesh]

I get this error when running migrate up on alpine but not on ubuntu. Not sure why OS plays a role here. Using prisma2 2.0.0-alpha.636 @prisma/migrate: 0.3.374

I had deleted the migrations table from the database since yesterday's breaking change. All migrations got applied successfully when I run it via Ubuntu, but if the same command is running in alpine it throws this error.

Not sure why I am the magnet of all errors or maybe I am doing something wrong :cry:

[tomhoule]

@tvvignesh Alpha 626 shouldn't exhibit this bug anymore - are you sure it happens with latest alpha?

[tomhoule]

@AlexanderProd we fixed a few issues since yesterday, it would be super helpful if you could try again with the latest prisma2@alpha :)

[AlexanderProd]

@tomhoule just tried npx prisma2 migrate up --experimental and still getting the unexpected error.

I'm using

[email protected], binary version: 45dd3493ff2fd09b5a8cdf697de276e368f7ebee

also submitted a new error report with id 1389.

[AlexanderProd]

I've also just tried npx prisma2 migrate up --experimental (the same schema and migration) against a completely empty DB and this time on a proper MySQL 5.7.29.

This is what would've been done

But I'm still getting the unexpected error unfortunately.

Noteworthy is that it successfully created the _Migration table on the db but that's about it.

[tvvignesh]

@tvvignesh Alpha 626 shouldn't exhibit this bug anymore - are you sure it happens with latest alpha?

Yup. It happens in the latest alpha, but interestingly, not in Ubuntu, only in alpine. I am not sure why. If I figure out, I will let you know.

[AlexanderProd]

I've created a new project without any legacy migrations from prior version and this time migrate up works fine.

[pantharshit00]

@tvvignesh Are you still facing this? Otherwise, we can close this

[tvvignesh]

@tvvignesh Are you still facing this? Otherwise, we can close this

@pantharshit00 Since I had this error, I upgraded to latest version of prisma, changed lift to migrate and also cleaned up the database and local migrations and also stopped applying it everytime the server starts. After that, things are going smooth so far.

You can close this for now, if I face it again, will let you know. Thanks.