Skip to content

MySQL restore fails - cannot delete tables with cascade

Zendesk: https://gitlab.zendesk.com/agent/tickets/28719

MySQL refuses to do a cascading drop so the GitLab restore process fails with foreign constraint problems.

Unpacking backup ... done 
Before restoring the database we recommend removing all existing 
tables to avoid future upgrade problems. Be aware that if you have 
custom tables in the GitLab database these tables and all data will be 
removed.

Do you want to continue (yes/no)? yes 
Removing all tables. Press `Ctrl-C` within 5 seconds to abort 
Cleaning the database ... 
rake aborted! 
ActiveRecord::StatementInvalid: Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails: DROP TABLE IF EXISTS `projects` CASCADE 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `query' 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `block in execute' 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_adapter.rb:472:in `block in log' 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activesupport-4.2.6/lib/active_support/notifications/instrumenter.rb:20:in `instrument' 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log' 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `execute' 
/opt/gitlab/embedded/service/gem/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute' 
/opt/gitlab/embedded/service/gitlab-rails/config/initializers/connection_fix.rb:20:in `execute' 
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:37:in `block (4 levels) in <top (required)>' 
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:37:in `each' 
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:37:in `block (3 levels) in <top (required)>' 
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:51:in `block (3 levels) in <top (required)>' 
Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails

The solution is to SET FOREIGN_KEY_CHECKS=0;, run the drop, then SET FOREIGN_KEY_CHECKS=1;. We need to do this within the context of the rake task.

  • How can we detect it's MySQL?