Migrate: "Connector error. (error: Error querying the database: Error querying the database: Error querying the database: Server error: ERROR 42S02 (1146): Table '_issue_client_502._migration' doesn't exist')" + "DROP TABLE `_issue_client_502`.`_migration`;" in migration README

[janpio]

1. Clone https://github.com/harshit-test-org/prisma2-client-502
2. yarn
3. Adapt prisma/.env to point to local MySQL 5.7 installation
4. yarn prisma2 migrate save --experimental
5. yarn prisma2 migrate up --experimental
6. See:
   `` You can get the detailed db changes with prisma2 migrate up --experimental --verbose Or read about them in the ./migrations/MIGRATION_ID/README.md ERROR Oops, an unexpected error occured! Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: Error querying the database: Server error:ERROR 42S02 (1146): Table '_issue_client_502._migration' doesn't exist')
   Please help us improve Prisma 2 by submitting an error report.
   Error reports never contain personal or other sensitive information.
   Learn more: https://pris.ly/d/telemetry

7. open prisma\migrations\20200319005000-init\README.md and see:

   ...
   ALTER TABLE `_issue_client_502`.`UserOrganization` ADD FOREIGN KEY (`user`) REFERENCES `_issue_client_502`.`User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
   
   DROP TABLE `_issue_client_502`.`_migration`;
   

(preview022 + preview024)

[webnoob]

I've been running into a similar issue as this.

I run npx prisma2 migrate up --experimental and it complains the __migrations table doesn't exist. I then run it a second time and it complains my tables already exist and from that point on my db is complete and has the __migrations table and all the other tables from my model.

[pantharshit00]

@janpio I am unable to reproduce this now.

I adjusted the datamodel to match the new spec, here it is now:

model Organization {
  id        Int       @default(autoincrement()) @id
  uid       String?   @unique
  name      String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?
}

model UserOrganization {
  organization   Organization @relation(fields: [organizationId], references: [id])
  organizationId Int
  role           Role         @relation(fields: [roleId], references: [id])
  roleId         Int
  user           User         @relation(fields: [userId], references: [id])
  userId         Int
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt

  @@id([roleId, userId, organizationId])
}

model User {
  id        Int                @default(autoincrement()) @id
  uid       String?            @unique
  email     String             @unique
  firstName String?
  lastName  String?
  createdAt DateTime           @default(now())
  updatedAt DateTime           @updatedAt
  deletedAt DateTime?
  orgs      UserOrganization[]
}

model Role {
  id          Int                @default(autoincrement()) @id
  uid         String?            @unique
  name        String
  description String?
  createdAt   DateTime           @default(now())
  updatedAt   DateTime           @updatedAt
  orgs        UserOrganization[]
}

[webnoob]

I still get this on whenever I do a migration on the latest version with MySQL 8.0.

I can't really share my schema but it's nothing special and the schema you've posted should be pretty similar in terms of relations etc.

For info: I migrate up, and it complains migrate doesn't exist (but creates all the other tables). I run it again and it creates the migration table. This is where I leave it - it does actually create everything but I have to run it twice and it errors.

[pantharshit00]

@webnoob Without a reproduction, unfortunately, we can't help here.

[webnoob]

I'll try and make a minimal repro.

[webnoob]

@pantharshit00 Can I share my schema in private somehow?

[pantharshit00]

Yep, [email protected] works for that :)

[webnoob]

You've got mail ;)

[pantharshit00]

@webnoob I am still unable to reproduce this.

You might need to drop the _Migrations table and try again.

[webnoob]

@pantharshit00
This happens on a clean DB with no tables in it at all. Does that help? Perhaps that's the issue?

I'm in early stage development of my app so when I make DB changes, I just drop all tables and then run the lift save, lift up so this always happens when I've dropped the migrations table.

I'm still on beta 2, not 3 - perhaps I should try again once I've updated?

Is there any other info that might help in recreating this?

[pantharshit00]

I am using the latest version, so please do try the latest version:

Maybe send your migrations folder as well to [email protected] if you can still reproduce. Also, try resetting everything by deleting that folder and trying again

[webnoob]

Ok will do - just to clarify, I always start from scratch. Drop the tables and delete the migrations folder creating it again with lift save.

I won't be able to test this properly on beta.3 until next week as I'm going through some refactoring.

I'll come back ASAP. Shall I email migrations to the same [email protected] email address?

[pantharshit00]

Ok will do - just to clarify, I always start from scratch. Drop the tables and delete the migrations folder creating it again with lift save.

lift save was rename to migrate save a few releases back.

I'll come back ASAP. Shall I email migrations to the same [email protected] email address?

Yep, same email

[webnoob]

lift save was rename to migrate save a few releases back.

Sorry, stuck on the same terminology. I call the correct call, don't worry :)

I call npx prisma migrate save --experimental

then

npx prisma migrate up --experimental

Will report back ASAP.

[webnoob]

@pantharshit00

Ok, still getting the error. I'm going to be as verbose as possible and will also send an email with my shema, migrations and verbose output.

Windows 10.
MySQL 8.0
DB name: dev
Using beta.3 for both @prisma/client and @prisma/cli

1. Starting with clean DB - no tables, functions, stored procedures etc.
2. Starting with only schema.prisma file in my /prisma directory.
3. Run SET DATABASE_URL=mysql://user:password@localhost:3306/dev&& prisma migrate save --experimental
4. Migration Name: Init. Note: Output of this is contained in the emailed file ConsoleOutputFromMigrateSave.txt
5. At this point, if I refresh the DB tables, I now have a _migration table which is empty.
6. Run SET DATABASE_URL=mysql://user:password@localhost:3306/dev&& prisma migrate up --experimental --verbose
7. ERROR. Note: Output of this is contained in the emailed file ConsoleOutPutFromMigrateSave.txt

Excerpt:

Database Changes:

Migration            Database actions                         Status

20200429225740-init  1 DropTable, 12 CreateTable statements.

You can get the detailed db changes with prisma migrate up --experimental --verbose
Or read about them in the ./migrations/MIGRATION_ID/README.md
 ERROR  Oops, an unexpected error occured!
Please help us improve Prisma 2 by submitting an error report.
Error reports never contain personal or other sensitive information.

1. If I refresh the DB tables list now, the _migration table is gone but the other tables have been created.
2. Run the migrate up command again
3. Error (more or less the same as the other, but with this as the output at the end:

Database Changes:

Migration            Database actions                          Status

20200429225740-init  12 DropTable, 11 CreateTable statements.

You can get the detailed db changes with prisma migrate up --experimental --verbose
Or read about them in the ./migrations/MIGRATION_ID/README.md
 ERROR  Oops, an unexpected error occured!
Please help us improve Prisma 2 by submitting an error report.
Error reports never contain personal or other sensitive information.

Now if I refresh my tables, they are all present and I have a failed migration in the _migration table with the errors of:

["ConnectorError { user_facing_error: None, kind: QueryError(Error querying the database: Server error: `ERROR 42S01 (1050): Table 'book' already exists') }"]

Full _migration row contents included in file MigrationRowAfterSecondMigrateUp.txt

These are the exact steps I am taking (I've run them each in turn as I've been writing the email) so can't provide any more information than this. If you can't recreate it, perhaps make sure you're doing the same thing with env vars etc!?

If you really can't recreate it, I'd be happy to do a screen share over zoom / something else to show you the issue this end. Not sure what the policy is at this point :)

[webnoob]

Any update on this one?

[grunghi]

I'm having similiar issues with fresh MySQL database on Windows.

My environment:
Windows 10
MariaDB 10.1.38
@prisma/cli 2.0.0-beta.5

When I run npx prisma migrate up --experimental for the first time it crashes like this:

Database Changes:

Migration            Database actions                        Status

20200512194047-init  1 DropTable, 6 CreateTable statements.

Failure during a migration command: Connector error.
(error: Error querying the database: Error querying the database: 
Server error: `ERROR 42S02 (1146): Table 'prisma-migrate-test._migration' doesn't exist'
   0: migration_core::api::ApplyMigration
           with migration_id="20200512194047-init"

(Report id 5312)

When I run it for the second time it crashes like this:

Database Changes:

Migration            Database actions                        Status

20200512194047-init  7 DropTable, 6 CreateTable statements.

Failure during a migration command: Connector error. 
(error: Error querying the database: Error querying the database:
 Server error: `ERROR 42S01 (1050): Table 'user' already exists'
   0: migration_core::api::ApplyMigration
           with migration_id="20200512194047-init"

(Report id 5313)

[grunghi]

I've just tried this on a different Windows 10 machine with fresh MySQL 8.0.20 install and the migrate command fails in the same fashion.

Migrations work fine when using the PostgreSQL connector.

[xlxAciDxlx]

Referencing this issue because it's relevant. It still occurs to this day, even on a fresh install of Prisma, fresh schema, etc.

[hood]

Just installed tried Prisma for the first time. Issue #339 just happened to me too.

[webnoob]

It'd be really nice to at least get a bug confirmed tag on this one to see some progress. It's been over 2 months and there seems to be plenty of recreation info.

[pantharshit00]

@webnoob Yeah, sorry we are focusing on Prisma client right now so there is a delay. This issue is windows specific. Related to how MySQL's innodb engine handles casing on windows.

I am going to close this as a duplicate of https://github.com/prisma/migrate/issues/339

[govofc]

This still happen, any fix? I send the migrations folder to [email protected]

Error: Failure during a migration command: Connector error. (error: Error querying the database: No such table: _migration 0: migration_core::api::ApplyMigration with migration_id="20200905172123-db-init" at migration-engine\core\src\api.rs:79)

[Karl-EdwardFPJeanMehu]

Any updates?

[remjx]

I thought I might be able to get around this issue by running blitz db migrate within Windows Subsystem for Linux (WSL) but I still get the same error.

Error: P1014

The underlying table for model `_migration` does not exist.

Will be keeping an eye on https://github.com/prisma/prisma-engines/issues/950 for the fix, thanks

[tomhoule]

@markjackson02 migrate can run inside or outside the WSL — the difference is where the MySQL server is run. If it is run within the WSL, it will be case sensitive and migrate will work.

[jerots]

I just had this issue on my Mac - MySQL RDS. turns out the db user i was using didn't have enough permission. I gave it full permission to the schema and it fixed my _Migration issue