Migrate: Modifying related models triggers columns drop/add on ALL referencing models

[stansm]

Hi,

Consider I have this schema:

model User {
  id        String  @default(cuid()) @id
}
model SomeEntity {
  id            String  @default(cuid()) @id
  user          User
}
model SomeEntity1 {
  id            String  @default(cuid()) @id
  user          User
}

Whenever I'm adding/dropping a field somefield String on either SomeEntity or SomeEntity1 - Lift tries to recreate user fields on both of the models:

ALTER TABLE `SomeEntity` DROP COLUMN `user`,ADD COLUMN `user` varchar(191)  ,
ADD FOREIGN KEY (`user`) REFERENCES `User`(`id`) ON DELETE SET NULL;

ALTER TABLE `SomeEntity1` DROP COLUMN `somefield`,DROP COLUMN `user`,ADD COLUMN `user` varchar(191)  ,
ADD FOREIGN KEY (`user`) REFERENCES `User`(`id`) ON DELETE SET NULL;

Am I missing something? This seems wrong and makes impossible to migrate the schema

[divyenduz]

@stansm Can you please share your Prisma 2 CLI version?
Also, please try this on the latest alpha. This was fixed a few days ago.

[stansm]

For some reason I was still on preview-9.1. upgrading to preview-11 fixed the issue.
Thank you, @divyenduz!

P.S. Good to so see it's moving forward so fast :) Can't wait for the release

[stansm]

@divyenduz found a case where it breaks
(naming matters. I've seen in some other thread that you are sorting models alphabetically somewhere):

model User {
  id        String  @default(cuid()) @id
}
model UserProfile {
  id            String  @default(cuid()) @id
  user          User
}
model SomeEntity1 {
  id            String  @default(cuid()) @id
  user          User
}

Now if I'll change User model:

model User {
  id        String  @default(cuid()) @id
  someField String?
}

Migration breaks:

Error: QueryError(QueryError(QueryError(MySqlError { ERROR 1828 (HY000): Cannot drop column 'userProfile': needed in a foreign key constraint 'User_ibfk_1' }

ALTER TABLE `User` ADD COLUMN `someField` varchar(191)   ,
DROP COLUMN `userProfile`,
ADD COLUMN `userProfile` varchar(191)  ,
ADD FOREIGN KEY (`userProfile`) REFERENCES `UserProfile`(`id`) ON DELETE SET NULL;

Even when I define a relation User -> UserProfile in the User model it still tries to drop it:

model User {
  id        String  @default(cuid()) @id
  profile   UserProfile?
}

Workaround:
Once I rename UserProfile to Profile - everything starts working as expected

Version: 2.0.0-alpha.205

[divyenduz]

@stansm : I am unable to reproduce this with 2.0.0-alpha.206, please share more details, which DB did you use? I tried it on SQLite and I started from scratch.

Edit: From the error I see that you are using MySQL, trying it with that.
-> Still unable to reproduce

[auderer]

I had this issue on 2.0.0-preview-12 but updating to the latest alpha fixed it for me.

[stansm]

@divyenduz

Tested again with prisma2 2.0.0-alpha.219, still reproducible.
node: v11.15.0
mysql: 5.7.26

Clone this repo: https://github.com/stansm/prisma2-issue133 and follow steps in the README

Stacktrace:

Error: QueryError(QueryError(QueryError(MySqlError { ERROR 1828 (HY000): Cannot drop column 'userProfile': needed in a foreign key constraint 'User_ibfk_1' }

stack backtrace:
   0: backtrace::backtrace::trace
   1: backtrace::capture::Backtrace::new_unresolved
   2: failure::backtrace::internal::InternalBacktrace::new
   3: <failure::backtrace::Backtrace as core::default::Default>::default
   4: prisma_query::connector::mysql::error::<impl core::convert::From<mysql::error::Error> for prisma_query::error::Error>::from
   5: prisma_query::connector::metrics::query
   6: <prisma_query::connector::mysql::Mysql as prisma_query::connector::queryable::Queryable>::query_raw
   7: <sql_migration_connector::migration_database::Mysql as sql_migration_connector::migration_database::MigrationDatabase>::query_raw
   8: sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier::apply_next_step
   9: <sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier as migration_connector::database_migration_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
  10: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::MigrationApplier<T>>::apply
  11: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
  12: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::MigrationCommand>::execute
  13: <migration_engine::api::MigrationApi<C,D> as migration_engine::api::GenericApi>::apply_migration
  14: migration_engine::api::rpc::RpcApi::create_sync_handler
  15: tokio_executor::enter::exit
  16: tokio_threadpool::blocking::blocking
  17: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  18: futures::future::chain::Chain<A,B,C>::poll
  19: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  20: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  21: futures::future::chain::Chain<A,B,C>::poll
  22: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  23: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  24: <futures::future::either::Either<A,B> as futures::future::Future>::poll
  25: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  26: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  27: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
  28: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
  29: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  30: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  31: futures::task_impl::std::set
  32: std::panicking::try::do_call
  33: __rust_maybe_catch_panic
  34: tokio_threadpool::task::Task::run
  35: tokio_threadpool::worker::Worker::run_task
  36: tokio_threadpool::worker::Worker::run
  37: std::thread::local::LocalKey<T>::with
  38: std::thread::local::LocalKey<T>::with
  39: tokio_reactor::with_default
  40: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
  41: std::thread::local::LocalKey<T>::with
  42: std::thread::local::LocalKey<T>::with
  43: std::sys_common::backtrace::__rust_begin_short_backtrace
  44: std::panicking::try::do_call
  45: __rust_maybe_catch_panic
  46: core::ops::function::FnOnce::call_once{{vtable.shim}}
  47: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
  48: std::sys::unix::thread::Thread::new::thread_start
  49: _pthread_body
  50: _pthread_start
)

stack backtrace:
   0: backtrace::backtrace::trace
   1: backtrace::capture::Backtrace::new_unresolved
   2: failure::backtrace::internal::InternalBacktrace::new
   3: <failure::backtrace::Backtrace as core::default::Default>::default
   4: <sql_migration_connector::error::SqlError as core::convert::From<prisma_query::error::Error>>::from
   5: sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier::apply_next_step
   6: <sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier as migration_connector::database_migration_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
   7: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::MigrationApplier<T>>::apply
   8: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
   9: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::MigrationCommand>::execute
  10: <migration_engine::api::MigrationApi<C,D> as migration_engine::api::GenericApi>::apply_migration
  11: migration_engine::api::rpc::RpcApi::create_sync_handler
  12: tokio_executor::enter::exit
  13: tokio_threadpool::blocking::blocking
  14: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  15: futures::future::chain::Chain<A,B,C>::poll
  16: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  17: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  18: futures::future::chain::Chain<A,B,C>::poll
  19: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  20: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  21: <futures::future::either::Either<A,B> as futures::future::Future>::poll
  22: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  23: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  24: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
  25: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
  26: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  27: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  28: futures::task_impl::std::set
  29: std::panicking::try::do_call
  30: __rust_maybe_catch_panic
  31: tokio_threadpool::task::Task::run
  32: tokio_threadpool::worker::Worker::run_task
  33: tokio_threadpool::worker::Worker::run
  34: std::thread::local::LocalKey<T>::with
  35: std::thread::local::LocalKey<T>::with
  36: tokio_reactor::with_default
  37: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
  38: std::thread::local::LocalKey<T>::with
  39: std::thread::local::LocalKey<T>::with
  40: std::sys_common::backtrace::__rust_begin_short_backtrace
  41: std::panicking::try::do_call
  42: __rust_maybe_catch_panic
  43: core::ops::function::FnOnce::call_once{{vtable.shim}}
  44: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
  45: std::sys::unix::thread::Thread::new::thread_start
  46: _pthread_body
  47: _pthread_start
)

stack backtrace:
   0: backtrace::backtrace::trace
   1: backtrace::capture::Backtrace::new_unresolved
   2: failure::backtrace::internal::InternalBacktrace::new
   3: <failure::backtrace::Backtrace as core::default::Default>::default
   4: sql_migration_connector::error::<impl core::convert::From<sql_migration_connector::error::SqlError> for migration_connector::error::ConnectorError>::from
   5: <sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier as migration_connector::database_migration_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
   6: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::MigrationApplier<T>>::apply
   7: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
   8: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::MigrationCommand>::execute
   9: <migration_engine::api::MigrationApi<C,D> as migration_engine::api::GenericApi>::apply_migration
  10: migration_engine::api::rpc::RpcApi::create_sync_handler
  11: tokio_executor::enter::exit
  12: tokio_threadpool::blocking::blocking
  13: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  14: futures::future::chain::Chain<A,B,C>::poll
  15: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  16: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  17: futures::future::chain::Chain<A,B,C>::poll
  18: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  19: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  20: <futures::future::either::Either<A,B> as futures::future::Future>::poll
  21: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  22: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  23: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
  24: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
  25: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  26: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  27: futures::task_impl::std::set
  28: std::panicking::try::do_call
  29: __rust_maybe_catch_panic
  30: tokio_threadpool::task::Task::run
  31: tokio_threadpool::worker::Worker::run_task
  32: tokio_threadpool::worker::Worker::run
  33: std::thread::local::LocalKey<T>::with
  34: std::thread::local::LocalKey<T>::with
  35: tokio_reactor::with_default
  36: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
  37: std::thread::local::LocalKey<T>::with
  38: std::thread::local::LocalKey<T>::with
  39: std::sys_common::backtrace::__rust_begin_short_backtrace
  40: std::panicking::try::do_call
  41: __rust_maybe_catch_panic
  42: core::ops::function::FnOnce::call_once{{vtable.shim}}
  43: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
  44: std::sys::unix::thread::Thread::new::thread_start
  45: _pthread_body
  46: _pthread_start
)
error Command failed with exit code 1

[stansm]

Tried downgrading to node 10.13.0 - same
Tried switching to 2.0.0-preview-13 - same

on sqlite - not reproducible

[pantharshit00]

I am unable to reproduce this [email protected], binary version: e7579bd35e0938dbf773f1706c098a0d14a5a038. I think this issue is outdated now.

Please open a new one if you encounter this again.