Migration failed to apply cleanly to shadow database

[rpbarnes]

Bug description

We're running into an issue with npx prisma migrate dev where it's failing to apply the first migration in our migration history cleanly to the shadow db and failing with the error code

Migration 20221209171922_initial_migration failed to apply cleanly to the shadow database.
Error code: P1001
Error:
Can't reach database server at [dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com](http://dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com/):3306
Please make sure your database server is running at [dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com](http://dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com/):3306.

However it is very clearly able to connect to the db because before this error is thrown it created the db unwrap_v2 and applies all the migrations to the db. I can also go into the DB and see all the tables created. Is there a way in the short term to turn off the shadow db check?

See the full stack trace below.

MySQL database unwrap_v2 created at dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com:3306
The following migration(s) have been applied:
migrations/
  └─ 20221209171922_initial_migration/
    └─ migration.sql
  └─ 20221213074154_fix_ids/
    └─ migration.sql
  └─ 20221213074456_lowercase_fields_on_alerts_notifications/
    └─ migration.sql
  └─ 20221213080911_make_alert_id_unique_on_std_alerts_values/
    └─ migration.sql
  └─ 20221214043818_fix_id_on_notifications/
    └─ migration.sql
  └─ 20221214051916_fix_wrong_table_reference/
    └─ migration.sql
  └─ 20221214060301_sent_status_on_alert_notifications/
    └─ migration.sql
  └─ 20221214211105_rename_alerts_table/
    └─ migration.sql
  └─ 20221215065930_notification_id_on_new_cluster_alerts/
    └─ migration.sql
  └─ 20221215070831_actually_add_notification_id_on_new_cluster_alerts/
    └─ migration.sql
  └─ 20221215164501_announcement_emails/
    └─ migration.sql
  └─ 20221215181611_announcement_recipients/
    └─ migration.sql
  └─ 20221215190716_/
    └─ migration.sql
  └─ 20221216021405_add_status_to_invite_link/
    └─ migration.sql
  └─ 20221216183638_rename_threshold_and_cluster_notification_alert_values/
    └─ migration.sql
  └─ 20221216193230_add_name_to_alerts/
    └─ migration.sql
  └─ 20230106215508_search_create/
    └─ migration.sql
  └─ 20230107011039_search_mapping_date_non_null/
    └─ migration.sql
  └─ 20230111033031_unique_sentence_search_id/
    └─ migration.sql
  └─ 20230113185843_zapier_add_data_source/
    └─ migration.sql
  └─ 20230118170115_sentiment_override/
    └─ migration.sql
  └─ 20230118184445_add_progress_and_gpt_decision/
    └─ migration.sql
  └─ 20230131163618_sentence_sentiment_override/
    └─ migration.sql
  └─ 20230202151639_preview_page_search/
    └─ migration.sql
  └─ 20230202152050_require_email_preview_page/
    └─ migration.sql
  └─ 20230202153325_add_preview_uuid_to_searches/
    └─ migration.sql
  └─ 20230202153938_added_created_date/
    └─ migration.sql
  └─ 20230203150850_nullable_user_sentence_search_group_mapping_overrides/
    └─ migration.sql
  └─ 20230203211607_setup_initial_group_tables/
    └─ migration.sql
  └─ 20230204002153_/
    └─ migration.sql
  └─ 20230204020923_alerts_filter_dates/
    └─ migration.sql
  └─ 20230206173517_preview_page_groups/
    └─ migration.sql
  └─ 20230208001345_sentiment_model_uri/
    └─ migration.sql
  └─ 20230210152049_integration_type_frontend_toggle/
    └─ migration.sql
  └─ 20230213223822_feedback_entry_sentiment_uri/
    └─ migration.sql
  └─ 20230215185433_group_insights/
    └─ migration.sql
  └─ 20230220192450_feedback_sentences_index_id_team_id/
    └─ migration.sql
  └─ 20230221152657_group_tagging/
    └─ migration.sql
  └─ 20230302230111_group_gpt_actionability_flag/
    └─ migration.sql
  └─ 20230321234956_nullable_cluster_id_on_announcement_emails/
    └─ migration.sql
  └─ 20230322210131_drop_cluster_id_on_announcement_emails/
    └─ migration.sql
  └─ 20230329202728_last_processed_group/
    └─ migration.sql
  └─ 20230330050913_stars_on_alerts/
    └─ migration.sql
  └─ 20230330163542_sources_on_alerts/
    └─ migration.sql
  └─ 20230403202603_group_exact_match_only/
    └─ migration.sql
  └─ 20230410064149_team_dashboard_defaults/
    └─ migration.sql
  └─ 20230410064725_team_dashboard_defaults_add_team_id/
    └─ migration.sql
  └─ 20230410231538_group_insights_updates/
    └─ migration.sql
  └─ 20230411000457_customer_facing_not_null/
    └─ migration.sql
  └─ 20230413205103_charts_first_pass/
    └─ migration.sql
  └─ 20230413222140_nullable_owner/
    └─ migration.sql
  └─ 20230414174156_send_grid_emails/
    └─ migration.sql
  └─ 20230414222913_user_last_access_token_grant/
    └─ migration.sql
  └─ 20230415002521_/
    └─ migration.sql
  └─ 20230417050003_/
    └─ migration.sql
  └─ 20230418054125_/
    └─ migration.sql
  └─ 20230418220026_organization_identity_pool/
    └─ migration.sql
  └─ 20230419153617_/
    └─ migration.sql
  └─ 20230420224343_org_access_control_by_id_pool/
    └─ migration.sql
  └─ 20230426233338_segment_group_id_on_charts/
    └─ migration.sql
  └─ 20230427200956_add_chart_type/
    └─ migration.sql
  └─ 20230502032612_team_id_override_data_series/
    └─ migration.sql
  └─ 20230502203552_segment_group_id_on_series/
    └─ migration.sql
  └─ 20230504165852_kill_low_dimension_sentence_embeddings/
    └─ migration.sql
  └─ 20230504173631_search_group_byeeeeeee/
    └─ migration.sql
  └─ 20230504193506_entry_date_only_byeeeee/
    └─ migration.sql
  └─ 20230508123049_data_on_sources/
    └─ migration.sql
  └─ 20230509234359_sentence_override_system/
    └─ migration.sql
  └─ 20230510141742_cluster_configs/
    └─ migration.sql
  └─ 20230512142842_group_views/
    └─ migration.sql
  └─ 20230512194839_chart_no_segment/
    └─ migration.sql
  └─ 20230516220633_slack_channel_name/
    └─ migration.sql
  └─ 20230516230132_creator_on_team/
    └─ migration.sql
  └─ 20230518145604_campaign/
    └─ migration.sql
  └─ 20230518210443_add_team_creator/
    └─ migration.sql
  └─ 20230522063106_reminder_date_on_invite_link/
    └─ migration.sql
  └─ 20230522064359_created_date_on_invite_link/
    └─ migration.sql
  └─ 20230523215541_add_report_columns_to_group_insights/
    └─ migration.sql
  └─ 20230529034904_make_org_conneciton_id_on_alerts_a_foreign_key/
    └─ migration.sql
  └─ 20230531151342_group_relationships/
    └─ migration.sql
  └─ 20230606173248_add_new_users_to_campaigns/
    └─ migration.sql
  └─ 20230706231945_organizations_customer_facing_default/
    └─ migration.sql
  └─ 20230720053607_group_subscription_updates/
    └─ migration.sql
  └─ 20230720062259_removing_org_id_from_alerts/
    └─ migration.sql
  └─ 20230720064330_make_cadence_daily_or_weekly/
    └─ migration.sql
  └─ 20230720072421_filter_relation_on_alerts/
    └─ migration.sql
  └─ 20230721055814_add_threshold_to_alerts/
    └─ migration.sql
  └─ 20230721060650_remove_org_id_from_notifications/
    └─ migration.sql
  └─ 20230721063121_remove_accidental_orgid_on_notifications/
    └─ migration.sql
  └─ 20230724230935_indexes_for_speed/
    └─ migration.sql
  └─ 20230728033229_nullable_min_cluster/
    └─ migration.sql
  └─ 20230731160417_addition_type_on_taxonomy/
    └─ migration.sql
  └─ 20230801135504_group_trending/
    └─ migration.sql
  └─ 20230802030837_/
    └─ migration.sql
  └─ 20230809033715_feature_flags/
    └─ migration.sql
  └─ 20230811074759_drop_old_alert_talbes/
    └─ migration.sql
  └─ 20230816002622_insight_title/
    └─ migration.sql
  └─ 20230817135626_indexonsegment/
    └─ migration.sql
  └─ 20230817144547_underscore/
    └─ migration.sql
  └─ 20230817150154_sentiment/
    └─ migration.sql
  └─ 20230823211918_sentiment_binning/
    └─ migration.sql
  └─ 20230901152617_sentiment_binned_indexed_sentences/
    └─ migration.sql
  └─ 20230901163128_index_sentence_sentiment_bin/
    └─ migration.sql
  └─ 20230912153724_distillates_tables/
    └─ migration.sql
  └─ 20230912155217_distillations_integration_with_existing_tables/
    └─ migration.sql
  └─ 20230912162639_distillates_raw_sentence_mappings/
    └─ migration.sql
  └─ 20230918081607_org_domains/
    └─ migration.sql
  └─ 20230918164640_org_domain_unique_constraint/
    └─ migration.sql
  └─ 20230918214116_default_segments/
    └─ migration.sql
  └─ 20230922162729_obfuscate_requirement_value/
    └─ migration.sql
  └─ 20231002221244_team_distillate_migration/
    └─ migration.sql
  └─ 20231003181209_user_oauth_secret_state/
    └─ migration.sql
  └─ 20231003183221_add_org_to_user_oauth/
    └─ migration.sql
  └─ 20231005184743_/
    └─ migration.sql
  └─ 20231007145356_display_name_unique/
    └─ migration.sql
  └─ 20231009211818_distillate_status/
    └─ migration.sql
  └─ 20231011203156_itr_auth_redirect/
    └─ migration.sql
  └─ 20231013215659_group_least_matching_sentence_id/
    └─ migration.sql
  └─ 20231014002254_has_been_exhaustively_searched/
    └─ migration.sql
  └─ 20231024162731_feedback_integration_schedule/
    └─ migration.sql
  └─ 20231024200859_change_field_to_time/
    └─ migration.sql
  └─ 20231024202235_index_on_start_time/
    └─ migration.sql
  └─ 20231130220706_integration_category/
    └─ migration.sql
  └─ 20231201003056_new_keyvalue_table/
    └─ migration.sql
  └─ 20231205175715_integration_requirement_key_uniqueness_per_type/
    └─ migration.sql
  └─ 20231205181350_add_feedback_replies_table/
    └─ migration.sql
  └─ 20231206012603_change_req_value_text_field_type/
    └─ migration.sql
  └─ 20231207222143_remove_replies_and_update_conv_parts/
    └─ migration.sql
  └─ 20231208011011_make_user_id_foreign_key_on_conv_parts/
    └─ migration.sql
  └─ 20231213235228_segment_group_flag_to_prevent_frontend_loading/
    └─ migration.sql
  └─ 20231226063008_add_default_distillate_display_to_integrations/
    └─ migration.sql
  └─ 20240103222502_banner_table/
    └─ migration.sql
  └─ 20240117002403_digest_setup/
    └─ migration.sql
  └─ 20240118220446_add_group_membership_prompt_type/
    └─ migration.sql
  └─ 20240119232723_group_membership_propmt_set_default_v2_only_zero_shot/
    └─ migration.sql
  └─ 20240119235143_digest_insights/
    └─ migration.sql
  └─ 20240120005318_unique_insights_per_digest/
    └─ migration.sql
  └─ 20240122182049_digest_rename/
    └─ migration.sql
  └─ 20240130185433_insight_feedback_status/
    └─ migration.sql
  └─ 20240214175009_digest_sending/
    └─ migration.sql
  └─ 20240214183850_exclude_from_new_filter/
    └─ migration.sql
  └─ 20240214183908_sendgrid_list_string/
    └─ migration.sql
  └─ 20240222232732_order_for_digest_insight/
    └─ migration.sql
  └─ 20240223223941_v3_zero_shot_enum/
    └─ migration.sql
  └─ 20240227002026_default_import_category/
    └─ migration.sql
  └─ 20240227011305_sgm_decision_probability/
    └─ migration.sql
  └─ 20240228192649_/
    └─ migration.sql
  └─ 20240301042358_insight_source/
    └─ migration.sql
  └─ 20240304205844_redaction_strategies/
    └─ migration.sql
  └─ 20240304220503_group_membership_config/
    └─ migration.sql
  └─ 20240305185335_exclude_insight_from_digest/
    └─ migration.sql
  └─ 20240305234522_membership_technique_override/
    └─ migration.sql
  └─ 20240308050949_start_end_default_dates/
    └─ migration.sql
  └─ 20240308061008_team_defaults_unique_teamid/
    └─ migration.sql
  └─ 20240308062205_making_default_date_window_optional/
    └─ migration.sql
  └─ 20240309000030_/
    └─ migration.sql
  └─ 20240313213459_/
    └─ migration.sql
  └─ 20240315002207_sync_with_prod/
    └─ migration.sql
  └─ 20240316144541_computed_group_entry_mappings/
    └─ migration.sql
  └─ 20240319234331_group_entries/
    └─ migration.sql
  └─ 20240320204806_metadata_integration_category/
    └─ migration.sql
  └─ 20240320215537_enable_subclustering_by_default/
    └─ migration.sql
  └─ 20240328171414_add_membership_probability_threshold/
    └─ migration.sql
  └─ 20240404185947_index_on_entries/
    └─ migration.sql
  └─ 20240412052216_feedback_integration_type/
    └─ migration.sql
  └─ 20240414193136_actionability_n_dupe_version_control/
    └─ migration.sql
  └─ 20240415024354_aws_categories/
    └─ migration.sql
  └─ 20240419200305_date_on_segment/
    └─ migration.sql
  └─ 20240422231126_datetime_on_digest_insights/
    └─ migration.sql
  └─ 20240423000654_adjust_datetime_precision/
    └─ migration.sql
  └─ 20240425163229_default_regex_replacements/
    └─ migration.sql
Error: P3006
Migration 20221209171922_initial_migration failed to apply cleanly to the shadow database.
Error code: P1001
Error:
Can't reach database server at [dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com](http://dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com/):3306
Please make sure your database server is running at [dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com](http://dev-unwrap-service-db-dbcluster15af587f-nghplpmzwp98.cluster-cjcc6iyom5aw.us-east-2.rds.amazonaws.com/):3306.

How to reproduce

I'm not actually sure how to reproduce. I can only get this to happen on two of our dev db's. This is verified on a fresh db however, we dropped the database unwrap_v2 as well as all shadow databases. It happens reproducibly on two of our dbs.

Expected behavior

It should just create and apply the migration file

Prisma information

// Add your schema.prisma

// Add your code using Prisma Client

Environment & setup

• OS: macOs
• Database: MySQL
• Node.js version: 20.12.1

Prisma Version

5.0.0

[janpio]

Is there a way in the short term to turn off the shadow db check?

No, it is not only a "shadow database check" but actually how Prisma figures out which SQL has to be executed to bring your database into the desired state. You can of course avoid that by not using migrate dev and creating the migration file by hand - but I assume that would defeat the purpose.

The real question here is why this is failing on some of your database servers. Any idea what could be configured differently there to cause a migration to fail? Can you maybe enable query logging on the database level, and see what is being executed under the hood - and where it fails with which error? Seems Can't reach database server at ... is a misleading error message on our side which somehow is triggered when something else goes wrong.

[rpbarnes]

Yeah, we can enable some query logging. Since posting we've tried increasing the connect timeout and query timeouts on the db but with no success.

Is there a way to turn on query logging on the prisma side that would apply to the migrate dev script? Or should we do this on the DB side. Just thinking that client side would be a bit easier to figure out.

[janpio]

Unfortunately not for migrations, sorry.