Migrate: `prisma2 dev` tries to drop column that is a foreign key

[jonaswide]

Hi guys, awesome project!

I tried to initialize prisma2 (photon+lift) on an existing MySQL DB with lots of relations and foreign keys.

After running prisma2 dev for the first time it fails with an error saying Cannot drop column 'CountryID': needed in a for foreign key constraint 'hotel_ibfk_1'. The FK being a FK between CountryID on the same table and a country table (duh)..

Referring to lift/100 it seems that it's trying to drop all columns before readding them, which obviously fails due to the FKs.

I'll be happy to provide more code, I'm simply not sure what.

generated schema.prisma stripped to relevant fields:

generator photon {
  provider = "photonjs"
}

datasource db {
  provider = "mysql"
  url      = "mysql://XXXX:XXXX@localhost:3306/TABLE"
}

model Country {
  ID Int @id

  @@map("country")
}

model Hotel {
  ID        Int      @id
  countryID Country? @map("CountryID")

  @@map("hotel")
}

Full error message:

$ prisma2 dev

 Error
   Error: QueryError(QueryError(QueryError(MySqlError { ERROR 1828 (HY000): Cannot drop column 'CountryID': needed in a for foreign key constraint 'hotel_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::Err
or>::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::MigrationDatab
ase>::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_migra
tion_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
     10: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::Migrat
ionApplier<T>>::apply
     11: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
     12: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::Migrat
ionCommand>::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::then::Then<A,B,F> as futures::future::Future>::poll
     22: <futures::future::map::Map<A,F> as futures::future::Future>::poll
     23: <futures::future::either::Either<A,B> as futures::future::Future>::poll
     24: <futures::future::map::Map<A,F> as futures::future::Future>::poll
     25: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
     26: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
     27: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
     28: <futures::future::map::Map<A,F> as futures::future::Future>::poll
     29: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
     30: futures::task_impl::std::set
     31: std::panicking::try::do_call
     32: __rust_maybe_catch_panic
     33: tokio_threadpool::task::Task::run
     34: tokio_threadpool::worker::Worker::run_task
     35: tokio_threadpool::worker::Worker::run
     36: std::thread::local::LocalKey<T>::with
     37: std::thread::local::LocalKey<T>::with
     38: std::thread::local::LocalKey<T>::with
     39: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
     40: std::thread::local::LocalKey<T>::with
     41: std::thread::local::LocalKey<T>::with
     42: std::sys_common::backtrace::__rust_begin_short_backtrace
     43: std::panicking::try::do_call
     44: __rust_maybe_catch_panic
     45: core::ops::function::FnOnce::call_once{{vtable.shim}}
     46: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
     47: std::sys::unix::thread::Thread::new::thread_start

[pantharshit00]

I am unable to reproduce this with the current information.

Can you please share schema of your database in SQL(omit parts of it if you want)? We are always interested in testing Prisma 2 with large schemas. It would be great if you can share that with us. If you are more comfortable sharing it privately with an NDA, please let me know.

[janpio]

(Moved this to prisma/lift as the Lift/Migration part of prisma2 dev is causing problems here)

[jonaswide]

@pantharshit00 yes, sure! I've made a wetransfer link only containing the 2 tables in play here (and a _Migration table that I believe Prisma has created somehow? And a separate file only containing the data of the migration table. https://we.tl/t-vZIXxRFcdJ

I'll be more than happy to help testing here, keep me in the loop !

Update:

I experienced a different error today, still related. This time it just tries to drop the index key instead of the column itself, and throws the same error: Error: QueryError( QueryError( QueryError( MySqlError { ERROR 1553 (HY000): Cannot drop index 'hotel_ibfk_1_idx': needed in a foreign key constraint }. Probably just a SQL order matter, since I've been playing around trying to fix it as well by dropping the relations and re-adding them.

Versions:

• Connector: MySQL
• Prisma2: [email protected], binary version: 14576370b55d5152d9cb90f4f509bfa56760dbbd
• OS: OS X [email protected]

[jonaswide]

Okay, so I have an update and a huge concern.
After updating the schema.prisma file to only containing models for the 2 tables in play (Hotel & Country), exactly as above, and actually saving it and re-running prisma2 dev it actually deleted all my other tables alongside the omitted columns from the above tables.

Luckily I'm playing around on a backup test database, so no damage is done here. But it quite is.

It makes me a little concerned that I can accidentally delete and drop data in the DB by simply not providing Prisma2 access to specific tables my removing the model definitions for them.

Again, I can provide more info/code if needed, I just don't know what you'd need at this point, let me know.

[janpio]

Thanks @jonaswide for the update, as far as I am aware this is currently expected behavior - but feedback like yours is exactly what we are looking for to reconsider and inform decisions like this. Could you please turn your last comment into its own issue with its own title so we can start a discussion about this there? Thanks 🚀

[janpio]

prisma dev does not exist any more.