🐛 Bug: Upgrading from 0.109.7 to 0.111.0 fails

[WeT-Klb]

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Steps to reproduce ?

When i try to install the docker image 0.11.0 i get this errors in docker console:

Error: alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists
at Packet.asError (/usr/src/app/node_modules/mysql2/lib/packets/packet.js:728:17)
at Query.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:29:26)
at Connection.handlePacket (/usr/src/app/node_modules/mysql2/lib/connection.js:489:32)
at PacketParser.onPacket (/usr/src/app/node_modules/mysql2/lib/connection.js:94:12)
at PacketParser.executeStart (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket. (/usr/src/app/node_modules/mysql2/lib/connection.js:101:25)
at Socket.emit (node:events:513:28)
at Socket.emit (node:domain:489:12)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)

Desired Behavior

Updating should work without problems

Project Details

Node: v16.20.1
Arch: x64
Platform: linux
Docker: true
RootDB: mysql2
PackageVersion: 0.109.7

MariaDB 10.5.8

Attachments

No response

[pranavxc]

Could you please provide the following details:

• Can you confirm whether the nc_bases_v2 foreign key constraint was removed manually using SQL or another tool?
• Additionally, please clarify whether you restarted the app during the boot-up process, started it again, and encountered the above-mentioned issue.

[WeT-Klb]

• I didn´t remove it manually - even don´t know how to do that. Once @mertmit helped me to update - but that´s already some releases before.
• i deleted the old 0.109.0 docker instance, downloaded the new docker file and installed it the same way like i always do on my QNAP NAS. When i retry to start i get this in console:

Error: alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists
at Packet.asError (/usr/src/app/node_modules/mysql2/lib/packets/packet.js:728:17)
at Query.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:29:26)
at Connection.handlePacket (/usr/src/app/node_modules/mysql2/lib/connection.js:489:32)
at PacketParser.onPacket (/usr/src/app/node_modules/mysql2/lib/connection.js:94:12)
at PacketParser.executeStart (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket. (/usr/src/app/node_modules/mysql2/lib/connection.js:101:25)
at Socket.emit (node:events:513:28)
at Socket.emit (node:domain:489:12)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)
[Nest] 9 - 08/25/2023, 9:53:36 AM LOG [NestFactory] Starting Nest application...
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] o dependencies initialized +160ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] PassportModule dependencies initialized +1ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] MulterModule dependencies initialized +1ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] MulterModule dependencies initialized +2ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] o dependencies initialized +239ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] DiscoveryModule dependencies initialized +3ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] ConfigHostModule dependencies initialized +2ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] EventEmitterModule dependencies initialized +8ms
[Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] ConfigModule dependencies initialized +3ms
(node:9) NOTE: We are formalizing our plans to enter AWS SDK for JavaScript (v2) into maintenance mode in 2023.

Please migrate your code to use AWS SDK for JavaScript (v3).
For more information, check the migration guide at https://a.co/7PzMCcy
(Use node --trace-warnings ... to show where the warning was created)
migration file "nc_031_remove_fk_and_add_idx" failed
migration failed with error: alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists
[Nest] 9 - 08/25/2023, 9:53:37 AM ERROR [ExceptionHandler] alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists

[pranavxc]

• I didn´t remove it manually - even don´t know how to do that. Once @mertmit helped me to update - but that´s already some releases before.
• i deleted the old 0.109.0 docker instance, downloaded the new docker file and installed it the same way like i always do on my QNAP NAS. When i retry to start i get this in console:

Error: alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists at Packet.asError (/usr/src/app/node_modules/mysql2/lib/packets/packet.js:728:17) at Query.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:29:26) at Connection.handlePacket (/usr/src/app/node_modules/mysql2/lib/connection.js:489:32) at PacketParser.onPacket (/usr/src/app/node_modules/mysql2/lib/connection.js:94:12) at PacketParser.executeStart (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:75:16) at Socket. (/usr/src/app/node_modules/mysql2/lib/connection.js:101:25) at Socket.emit (node:events:513:28) at Socket.emit (node:domain:489:12) at addChunk (node:internal/streams/readable:315:12) at readableAddChunk (node:internal/streams/readable:289:9) [Nest] 9 - 08/25/2023, 9:53:36 AM LOG [NestFactory] Starting Nest application... [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] o dependencies initialized +160ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] PassportModule dependencies initialized +1ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] MulterModule dependencies initialized +1ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] MulterModule dependencies initialized +2ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] o dependencies initialized +239ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] DiscoveryModule dependencies initialized +3ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] ConfigHostModule dependencies initialized +2ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] EventEmitterModule dependencies initialized +8ms [Nest] 9 - 08/25/2023, 9:53:37 AM LOG [InstanceLoader] ConfigModule dependencies initialized +3ms (node:9) NOTE: We are formalizing our plans to enter AWS SDK for JavaScript (v2) into maintenance mode in 2023.

Please migrate your code to use AWS SDK for JavaScript (v3). For more information, check the migration guide at https://a.co/7PzMCcy (Use node --trace-warnings ... to show where the warning was created) migration file "nc_031_remove_fk_and_add_idx" failed migration failed with error: alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists [Nest] 9 - 08/25/2023, 9:53:37 AM ERROR [ExceptionHandler] alter table nc_bases_v2 drop foreign key nc_bases_v2_project_id_foreign - Can't DROP FOREIGN KEY nc_bases_v2_project_id_foreign; check that it exists

Thank you for your prompt response. We are currently investigating the issue, and we will reach out to you as soon as we have identified the issue.

[pranavxc]

@WeT-Klb Can we get on a call? You can ping me in our discord channel ( user @pranavxc ) -
https://discord.gg/5RgZmkW

[WeT-Klb]

@pranavxc
I ping you in Discord but unfortunately didn´t get any reply.

[pranavxc]

@WeT-Klb : Sorry, no message was received can you share your ID?

[WeT-Klb]

I sent a direct message to you and I also asked for friendship. My ID is wetklb and the name is set to WeT-Klb.

[pranavxc]

@WeT-Klb Somehow not received the request, I just sent a request now.

[WeT-Klb]

After session with pranavxc NocoDB now works again.

[pranavxc]

Posting the steps and cause here for any future reference.

Cause

Root cause of the issue is one of the following

• Missing foreign key due to some manual change done earlier
• App crashed/stopped while the migration was running

Steps followed to recover from the issue

• Run the sql migration queries manually step by step, while running this few steps may fail since it may already applied.

  alter table `nc_bases_v2` drop foreign key `nc_bases_v2_project_id_foreign`;
  alter table `nc_bases_v2` add index `nc_bases_v2_project_id_index`(`project_id`)
  
  # step 2
  alter table `nc_models_v2` drop foreign key `nc_models_v2_base_id_foreign`;
  alter table `nc_models_v2` add index `nc_models_v2_base_id_index`(`base_id`);
  alter table `nc_models_v2` drop foreign key `nc_models_v2_project_id_foreign`;
  alter table `nc_models_v2` add index `nc_models_v2_project_id_index`(`project_id`)
  
  # step 3
  alter table `nc_columns_v2` drop foreign key `nc_columns_v2_fk_model_id_foreign`;
  alter table `nc_columns_v2` add index `nc_columns_v2_fk_model_id_index`(`fk_model_id`)
  
  # step 4
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_column_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_column_id_index`(`fk_column_id`);
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_related_model_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_related_model_id_index`(`fk_related_model_id`);
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_child_column_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_child_column_id_index`(`fk_child_column_id`);
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_parent_column_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_parent_column_id_index`(`fk_parent_column_id`);
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_mm_model_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_mm_model_id_index`(`fk_mm_model_id`);
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_mm_child_column_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_mm_child_column_id_index`(`fk_mm_child_column_id`);
  alter table `nc_col_relations_v2` drop foreign key `nc_col_relations_v2_fk_mm_parent_column_id_foreign`;
  alter table `nc_col_relations_v2` add index `nc_col_relations_v2_fk_mm_parent_column_id_index`(`fk_mm_parent_column_id`)
  
  # step 5
  alter table `nc_col_select_options_v2` drop foreign key `nc_col_select_options_v2_fk_column_id_foreign`;
  alter table `nc_col_select_options_v2` add index `nc_col_select_options_v2_fk_column_id_index`(`fk_column_id`)
  
  # step 6
  alter table `nc_col_lookup_v2` drop foreign key `nc_col_lookup_v2_fk_column_id_foreign`;
  alter table `nc_col_lookup_v2` add index `nc_col_lookup_v2_fk_column_id_index`(`fk_column_id`);
  alter table `nc_col_lookup_v2` drop foreign key `nc_col_lookup_v2_fk_relation_column_id_foreign`;
  alter table `nc_col_lookup_v2` add index `nc_col_lookup_v2_fk_relation_column_id_index`(`fk_relation_column_id`);
  alter table `nc_col_lookup_v2` drop foreign key `nc_col_lookup_v2_fk_lookup_column_id_foreign`;
  alter table `nc_col_lookup_v2` add index `nc_col_lookup_v2_fk_lookup_column_id_index`(`fk_lookup_column_id`)
  
  # step 7
  alter table `nc_col_qrcode_v2` drop foreign key `nc_col_qrcode_v2_fk_column_id_foreign`;
  alter table `nc_col_qrcode_v2` add index `nc_col_qrcode_v2_fk_column_id_index`(`fk_column_id`);
  alter table `nc_col_qrcode_v2` drop foreign key `nc_col_qrcode_v2_fk_qr_value_column_id_foreign`
  
  # step 8
  alter table `nc_col_barcode_v2` drop foreign key `nc_col_barcode_v2_fk_column_id_foreign`;
  alter table `nc_col_barcode_v2` add index `nc_col_barcode_v2_fk_column_id_index`(`fk_column_id`);
  alter table `nc_col_barcode_v2` drop foreign key `nc_col_barcode_v2_fk_barcode_value_column_id_foreign`
  
  # step 9
  alter table `nc_col_formula_v2` drop foreign key `nc_col_formula_v2_fk_column_id_foreign`;
  alter table `nc_col_formula_v2` add index `nc_col_formula_v2_fk_column_id_index`(`fk_column_id`);
  
  # step 10
  alter table `nc_col_rollup_v2` drop foreign key `nc_col_rollup_v2_fk_column_id_foreign`;
  alter table `nc_col_rollup_v2` add index `nc_col_rollup_v2_fk_column_id_index`(`fk_column_id`);
  alter table `nc_col_rollup_v2` drop foreign key `nc_col_rollup_v2_fk_relation_column_id_foreign`;
  alter table `nc_col_rollup_v2` add index `nc_col_rollup_v2_fk_relation_column_id_index`(`fk_relation_column_id`);
  alter table `nc_col_rollup_v2` drop foreign key `nc_col_rollup_v2_fk_rollup_column_id_foreign`;
  alter table `nc_col_rollup_v2` add index `nc_col_rollup_v2_fk_rollup_column_id_index`(`fk_rollup_column_id`);
  
  # step 11
  alter table `nc_views_v2` drop foreign key `nc_views_v2_fk_model_id_foreign`;
  alter table `nc_views_v2` add index `nc_views_v2_fk_model_id_index`(`fk_model_id`);
  
  # step 12
  alter table `nc_hooks_v2` drop foreign key `nc_hooks_v2_fk_model_id_foreign`;
  alter table `nc_hooks_v2` add index `nc_hooks_v2_fk_model_id_index`(`fk_model_id`);
  
  # step 13
  alter table `nc_filter_exp_v2` drop foreign key `nc_filter_exp_v2_fk_view_id_foreign`;
  alter table `nc_filter_exp_v2` add index `nc_filter_exp_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_filter_exp_v2` drop foreign key `nc_filter_exp_v2_fk_hook_id_foreign`;
  alter table `nc_filter_exp_v2` add index `nc_filter_exp_v2_fk_hook_id_index`(`fk_hook_id`);
  alter table `nc_filter_exp_v2` drop foreign key `nc_filter_exp_v2_fk_column_id_foreign`;
  alter table `nc_filter_exp_v2` add index `nc_filter_exp_v2_fk_column_id_index`(`fk_column_id`);
  alter table `nc_filter_exp_v2` drop foreign key `nc_filter_exp_v2_fk_parent_id_foreign`;
  alter table `nc_filter_exp_v2` add index `nc_filter_exp_v2_fk_parent_id_index`(`fk_parent_id`);
  
  # step 14
  alter table `nc_sort_v2` drop foreign key `nc_sort_v2_fk_view_id_foreign`;
  alter table `nc_sort_v2` add index `nc_sort_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_sort_v2` drop foreign key `nc_sort_v2_fk_column_id_foreign`;
  alter table `nc_sort_v2` add index `nc_sort_v2_fk_column_id_index`(`fk_column_id`);
  
  # step 15
  alter table `nc_shared_views_v2` drop foreign key `nc_shared_views_v2_fk_view_id_foreign`;
  alter table `nc_shared_views_v2` add index `nc_shared_views_v2_fk_view_id_index`(`fk_view_id`);
  
  # step 16
  alter table `nc_form_view_v2` drop foreign key `nc_form_view_v2_fk_view_id_foreign`;
  alter table `nc_form_view_v2` add index `nc_form_view_v2_fk_view_id_index`(`fk_view_id`);
  
  # step 17
  alter table `nc_form_view_columns_v2` drop foreign key `nc_form_view_columns_v2_fk_view_id_foreign`;
  alter table `nc_form_view_columns_v2` add index `nc_form_view_columns_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_form_view_columns_v2` drop foreign key `nc_form_view_columns_v2_fk_column_id_foreign`;
  alter table `nc_form_view_columns_v2` add index `nc_form_view_columns_v2_fk_column_id_index`(`fk_column_id`);
  
  # step 18
  alter table `nc_gallery_view_v2` drop foreign key `nc_gallery_view_v2_fk_view_id_foreign`;
  alter table `nc_gallery_view_v2` add index `nc_gallery_view_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_gallery_view_v2` drop foreign key `nc_gallery_view_v2_fk_cover_image_col_id_foreign`;
  
  # step 19
  alter table `nc_gallery_view_columns_v2` drop foreign key `nc_gallery_view_columns_v2_fk_view_id_foreign`;
  alter table `nc_gallery_view_columns_v2` add index `nc_gallery_view_columns_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_gallery_view_columns_v2` drop foreign key `nc_gallery_view_columns_v2_fk_column_id_foreign`;
  alter table `nc_gallery_view_columns_v2` add index `nc_gallery_view_columns_v2_fk_column_id_index`(`fk_column_id`);
  
  # step 20
  alter table `nc_grid_view_v2` drop foreign key `nc_grid_view_v2_fk_view_id_foreign`;
  alter table `nc_grid_view_v2` add index `nc_grid_view_v2_fk_view_id_index`(`fk_view_id`);
  
  # step 21
  alter table `nc_grid_view_columns_v2` drop foreign key `nc_grid_view_columns_v2_fk_view_id_foreign`;
  alter table `nc_grid_view_columns_v2` add index `nc_grid_view_columns_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_grid_view_columns_v2` drop foreign key `nc_grid_view_columns_v2_fk_column_id_foreign`;
  alter table `nc_grid_view_columns_v2` add index `nc_grid_view_columns_v2_fk_column_id_index`(`fk_column_id`);
  
  # step 22
  alter table `nc_map_view_v2` drop foreign key `nc_map_view_v2_fk_view_id_foreign`;
  alter table `nc_map_view_v2` add index `nc_map_view_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_map_view_v2` drop foreign key `nc_map_view_v2_fk_geo_data_col_id_foreign`;
  alter table `nc_map_view_v2` add index `nc_map_view_v2_fk_geo_data_col_id_index`(`fk_geo_data_col_id`);
  alter table `nc_map_view_v2` drop foreign key `nc_map_view_v2_base_id_foreign`;
  alter table `nc_map_view_v2` drop foreign key `nc_map_view_v2_project_id_foreign`;
  
  # step 23
  alter table `nc_map_view_columns_v2` drop foreign key `nc_map_view_columns_v2_fk_view_id_foreign`;
  alter table `nc_map_view_columns_v2` add index `nc_map_view_columns_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_map_view_columns_v2` drop foreign key `nc_map_view_columns_v2_fk_column_id_foreign`;
  alter table `nc_map_view_columns_v2` add index `nc_map_view_columns_v2_fk_column_id_index`(`fk_column_id`);
  
  
  # step 24
  alter table `nc_kanban_view_v2` drop foreign key `nc_kanban_view_v2_fk_view_id_foreign`;
  alter table `nc_kanban_view_v2` add index `nc_kanban_view_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_kanban_view_v2` drop foreign key `nc_kanban_view_v2_fk_grp_col_id_foreign`;
  alter table `nc_kanban_view_v2` add index `nc_kanban_view_v2_fk_grp_col_id_index`(`fk_grp_col_id`);
  alter table `nc_kanban_view_v2` drop foreign key `nc_kanban_view_v2_fk_cover_image_col_id_foreign`;
  
  # step 25
  alter table `nc_kanban_view_columns_v2` drop foreign key `nc_kanban_view_columns_v2_fk_view_id_foreign`;
  alter table `nc_kanban_view_columns_v2` add index `nc_kanban_view_columns_v2_fk_view_id_index`(`fk_view_id`);
  alter table `nc_kanban_view_columns_v2` drop foreign key `nc_kanban_view_columns_v2_fk_column_id_foreign`;
  alter table `nc_kanban_view_columns_v2` add index `nc_kanban_view_columns_v2_fk_column_id_index`(`fk_column_id`);
  
  # step 26
  alter table `nc_project_users_v2` drop foreign key `nc_project_users_v2_project_id_foreign`;
  alter table `nc_project_users_v2` add index `nc_project_users_v2_project_id_index`(`project_id`);
  alter table `nc_project_users_v2` drop foreign key `nc_project_users_v2_fk_user_id_foreign`;
  alter table `nc_project_users_v2` add index `nc_project_users_v2_fk_user_id_index`(`fk_user_id`);
  
  # step 27
  alter table `nc_audit_v2` drop foreign key `nc_audit_v2_project_id_foreign`;
  alter table `nc_audit_v2` add index `nc_audit_v2_project_id_index`(`project_id`);
  alter table `nc_audit_v2` drop foreign key `nc_audit_v2_fk_model_id_foreign`;
  alter table `nc_audit_v2` add index `nc_audit_v2_fk_model_id_index`(`fk_model_id`);
  
  # step 28
  alter table `nc_disabled_models_for_role_v2` drop foreign key `nc_disabled_models_for_role_v2_fk_view_id_foreign`;
  alter table `nc_disabled_models_for_role_v2` add index `nc_disabled_models_for_role_v2_fk_view_id_index`(`fk_view_id`);
  
  # step 29
  alter table `nc_sync_source_v2` drop foreign key `nc_sync_source_v2_project_id_foreign`;
  alter table `nc_sync_source_v2` add index `nc_sync_source_v2_project_id_index`(`project_id`);
  alter table `nc_sync_source_v2` drop foreign key `nc_sync_source_v2_base_id_foreign`;
  alter table `nc_sync_source_v2` add index `nc_sync_source_v2_base_id_index`(`base_id`);
  
  # step 30
  alter table `nc_api_tokens` drop foreign key `nc_api_tokens_fk_user_id_foreign`;

• Update the knex migration table using following

  INSERT INTO `xc_knex_migrationsv2` (`name`, `batch`, `migration_time`) VALUES ('nc_031_remove_fk_and_add_idx', 1, NOW());

• Now you can start the app without any problem.