StandardError: An error has occurred, all later migrations canceled: Mysql2::Error: Error on rename of './blacklight_prod/#sql-4e33_224' to './blacklight_prod/backup_locations' (errno: 150): ALTER TABLE `backup_locations` CHANGE `library_id` `backup_libr

[pgwillia]

discovery/lib/generators/symphony_nightly/templates/update_symphony_nightly.erb

Lines 16 to 22 in 4b12952

	# Creating the locations table later on without this gives errors that were
	# difficult to to make sense of.
	foreign_keys('backup_locations').each do |fkey|
	rename_column fkey.from_table, fkey.options[:column], 'backup_' + fkey.options[:column]
	remove_foreign_key fkey.from_table, fkey.to_table
	add_foreign_key fkey.from_table, fkey.to_table
	end

Line 19 is the problem. When I try to rename the library_id column to backup_library_id a foreign key constraint is broken. However this works fine in MySQL 5.7 but not in MySQL 5.5 that is used in staging and production.

# mysql > SHOW ENGINE INNODB STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
210112 11:24:02 Error in foreign key constraint of table blacklight_prod/backup_locations:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "fk_rails_59c722edc1" FOREIGN KEY ("library_id") REFERENCES "backup_libraries" ("id")

MariaDB version == the standard that comes with CentOS7:

    [root@masham ~]# rpm -qa | grep mariadb
    mariadb-5.5.68-1.el7.x86_64
    mariadb-libs-5.5.68-1.el7.x86_64
    mariadb-server-5.5.68-1.el7.x86_64

View details in Rollbar: https://rollbar.com/ualbertalib/discovery/items/316/

Mysql2::Error: Error on rename of './blacklight_prod/#sql-4e33_224' to './blacklight_prod/backup_locations' (errno: 150)
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/mysql2-0.4.10/lib/mysql2/client.rb", line 120, in _query
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/mysql2-0.4.10/lib/mysql2/client.rb", line 120, in block in query
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/mysql2-0.4.10/lib/mysql2/client.rb", line 119, in handle_interrupt
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/mysql2-0.4.10/lib/mysql2/client.rb", line 119, in query
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb", line 317, in block in execute
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/abstract_adapter.rb", line 484, in block in log
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activesupport-4.2.11.3/lib/active_support/notifications/instrumenter.rb", line 20, in instrument
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/abstract_adapter.rb", line 478, in log
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb", line 317, in execute
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/mysql2_adapter.rb", line 217, in execute
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb", line 543, in rename_column
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 665, in block in method_missing
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 634, in block in say_with_time
  File "/usr/lib64/ruby/2.5.0/benchmark.rb", line 293, in measure
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 634, in say_with_time
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 654, in method_missing
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 416, in method_missing
  File "/var/www/sites/blacklight/db/migrate/20210112182257_update_symphony_nightly_20210112.rb", line 19, in block in up
  File "/var/www/sites/blacklight/db/migrate/20210112182257_update_symphony_nightly_20210112.rb", line 18, in each
  File "/var/www/sites/blacklight/db/migrate/20210112182257_update_symphony_nightly_20210112.rb", line 18, in up
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 571, in up
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 611, in exec_migration
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 592, in block (2 levels) in migrate
  File "/usr/lib64/ruby/2.5.0/benchmark.rb", line 293, in measure
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 591, in block in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/connection_adapters/abstract/connection_pool.rb", line 292, in with_connection
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 590, in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 768, in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 1023, in block in execute_migration_in_transaction
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 1071, in ddl_transaction
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 1022, in execute_migration_in_transaction
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 984, in block in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 980, in each
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 980, in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 823, in up
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/migration.rb", line 801, in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/tasks/database_tasks.rb", line 139, in migrate
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/activerecord-4.2.11.3/lib/active_record/railties/databases.rake", line 44, in block (2 levels) in <top (required)>
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/task.rb", line 281, in block in execute
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/task.rb", line 281, in each
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/task.rb", line 281, in execute
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/task.rb", line 219, in block in invoke_with_call_chain
  File "/usr/lib64/ruby/2.5.0/monitor.rb", line 235, in mon_synchronize
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/task.rb", line 199, in invoke_with_call_chain
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/task.rb", line 188, in invoke
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/application.rb", line 160, in invoke_task
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/application.rb", line 116, in block (2 levels) in top_level
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/application.rb", line 116, in each
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/application.rb", line 116, in block in top_level
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/rake/application.rb", line 125, in run_with_threads
  File "/var/www/sites/blacklight/vendor/ruby/2.5.0/gems/rake-13.0.1/lib/